Re: sql question

graeme@pyra.co.uk writes: > In <27o4ejINNdfs@emory.mathcs.emory.edu> inf_bb@hermes1.sps.mot.com (Bob Baskett) writes: > >netters, > >can someone explain why the optimizer doesnt short circuit on the following > >query: > > > > QUERY: > > ------ > > select * > > from book_bill_back > > where 1=2> > > > [..] > This is treated as WHERE <expression> = <expression>. > The optimiser assumes that an expression is likely to be variable and > hands it off to the engine. > In some ways this behaviour is reasonable, after all *you* should know > whether 1 = 2, the optimiser doesn't expect you to be daft enough to say > WHERE <constant> = <constant> !!! :-) This is a perfect example (IMHO) of possibly *the* major failing of Informix and other Relational database products in that they make the fallacious assumption that the end user always generates the query directly, and always reads the results directly. The query may have been generated by another program (I write them all the time) and that program may not know that 1 does not equal 2, but should reasonably expect the optimiser to notice. (for example a template query which requires a filter, because it's easier to write that way, might just have the identity filter: `where 1 = 1' slotted in if no other filter is appropriate, or `where 1 = 2' when no output is desired). In the same vein, I've wasted many hours writing programs to parse the output of tbcheck, tbstat etc, to produce more digestable information or to mail me when the logical log tape appears to be full. I do not want to continually have to inspect the status of the database manually. Basically Informix seems to exhibit a complete disregard for the whole UNIX philosophy of accepting input that is easily machine-produceable, and producing output that is easily machine-digestible. Possibly the only saving grace in this regard is the unload format, which I use all the time to get the results of a query from one program to another. Sorry to flame like that, but this one's been bugging me for a long while and that example just set me off. Hope something constructive arises out of this. Cheers =========================================================================== | Bill Hails <bill@tardis.co.uk> | | | C.L.I. Connect Ltd. | README: permission denied | | 19, Quarry St., Guildford, Surrey | | | GU1 3UY. Tel (UK) 0483 300 200 | | ===========================================================================