Re: sql question

Jim Gordon <jgordon@ssf-sys.dhl.com> writes } Bill Hails writes:- } > graeme@pyra.co.uk writes: } > > In <27o4ejINNdfs@emory.mathcs.emory.edu> inf_bb@hermes1.sps.mot.com (Bob Baskett) writes: } > > >can someone explain why the optimizer doesnt short circuit on the following } > > >query: } > > > } > > > select * } > > > from book_bill_back } > > > where 1=2 } > > > } > > > [..] } > } > > 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). } I'm not sure I understand this. What is the complaint. Is it that it } allows the constructs 1=1 and 1=2 or that the engine should not } bother to scan an entire table when it has a permanently false filter. Sorry if I was not making myself clear, I was on a bit of a flame. My beef was not particularily with the optimizer, nor with that particular query example, but with the statement: } > > 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> !!! :-) Which assumes that *you* are always writing the queries directly, an as I said, many programs generate the query, for example from command line arguments, and the result may be more naive than one written directly, so the optimiser should not make assumptions about the `intellegence' of the query writer. } [interesting stuff deleted] } What are people out there expecting 1=2 and 1=1 to do? } [...] As I understand it, the `where 1 = 1' is mostly used in updates and deletes where there is no other condition, in order that the `update or delete with no where clause' warning flag (sqlca.sqlwarn[4]) does not get set. Which as it happens invalidates this example, but not my general point. Cheers Bill =========================================================================== | 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 | | ===========================================================================