Re: Subquery variants returning impossible(?) results

I, Jacob Salomon <jake@apparel.net>, posted: >:Hi Family. >: >:Preliminary: INFORMIX-OnLine Version 7.13.UC5 >:Machine: HP K-200 >:OS: HP-UX B.10.01 >: >:I am getting some results that make no sense. Before I toss the query >:at y'all, I will explain a little of what I am doing. -- SNIP -- >:My task: Find out how many tables/columns in our application no not >:have help text listed in stxhelpd. >: >:How does my query distinguish an application table from a fourgen >:table? >:Because my predecessors adopted the convention that all application >:tables begin with the prefix "zz". (I originally chafed at that but >:the benefits have outweighed my academic freedom. ;-) >: >:So here is my query: >: >: select unique tabname, colname >: from systables t, syscolumns c >: where t.tabid =3D c.tabid >: and t.tabname matches "zz*" >: and trim(tabname) || trim(colname) not in >: (select trim(hlp_module) || trim(hlp_program) >: from stxhelpd >: ) Nils Myklebust wrote: >This sub query may have a problem if hlp_module and/or hlp_program can >ever be null. I don't know exactly what it will return in that case, >but if it does return null it means it's unknown whether the >tabname/columname is included in it or not. In this case "not in" will >always be false and there will be no rows in your temp table. >May be this is the problem. I haven't otherwise really studied it to >see anything else. Thanks, Nils. I just tried it; here is the complete new query: select unique (trim(tabname) || "." || trim(colname)) tab_dot_col from systables t, syscolumns c where t.tabid =3D c.tabid and t.tabname matches "zz*" and trim(tabname) || trim(colname) not in (select trim(hlp_module) || trim(hlp_program) from stxhelpd where hlp_module is not null ) (I am omitting the INTO TEMP/SELECT COUNT from this excerpt.) Note the new WHERE clause in the subquery. Nils' explanation makes good sense, in retrospect. I recall playing with nulls in some 4GL code. The IF statement was: if var1 !=3D var2 where var2 was null and var1 was not. The boolean expression tested false. And yes, column hlp_module can very well be null. I think I can use this to explain why the NOT IN clause tested false: The internal test [for the "not in" condition] tests my expression (in this case, the horrid expression: trim(tabname) || trim(colname) ) to see if it is !=3D each value in the generated list (the subquery). = Because a null || non-null yields a null string (a behavior that has inspired much controversy), the !=3D test yields FALSE the first time it encounters a null value in hlp_module. WHEW! I sound like my professors who casually discussed convex linear combinations of differentiable covariant tensors! (babble babble =85.) -- = -- Jake (Possesses 20/20 hindsight, like most people) = +-----------------------------------------------------------+ | Impeccable Logic: A thought process which successfully | | resists chicken bites | +-----------------------------------------------------------+