Re: sql question

In <27s1riINNqq3@emory.mathcs.emory.edu> bill@tardis.co.uk (William Hails) writes: >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). It depends on your definition of "reasonable" I suppose. Why is it not reasonable for the optimiser to assume that a query generation program, because it was compiled more recently than the optimiser and can build upon the base technology supplied by the RDBMS vendor, ought to be cleverer than the optimiser??? :-) >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. I can sympathise with that, but also with them. >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. I have always felt that Informix is much more UNIXy than the other RDBMSs and other products of comparable complexity. Most of the others don't even seem to treat stdin, stdout and stderr in a predictable way! >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 | | > =========================================================================== graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.