Re: sql question

> From: Jim Gordon <jgordon@ssf-sys.dhl.com> > Subject: Re: sql question > > Bob Baskett writes:- > > can someone explain why the optimizer doesnt short circuit on the following > > query: > > > > [SQL munched] > > I would answer why should it. You have asked it to scan the table and > compare the rows against a filter which will always be false. This > will return no rows. Much the same as saying 1=1 where all rows are > returned. Now on the other side of the coin, I've seen queries similiar to: select * from x,y where x.this=y.that and 1=1 that have gone ballistic with the '1=1', when it should have been short circuited. Of course, there is nothing consistent as to WHICH queries take so long, as most uses of '1=1' are related to some kind of dynamic SQL built on the fly. It seems like the optimizer should be 'aware' that it is dealing with constants that are unaffected by the table data, and be able to make some assumptions about that data. Hmm...now that I think about it, I bet that the optimizer would throw that expression out on a multi-table join (which table do I scan for the '1=1' anyway), yet on a single table it may just scan the whole dang thing. Of course, I'm sure that the decision is made relative to the length of the table -- the longer the table, the better the chance of it scanning the whole thing :-). Now if you did something like: select * from x where x.id_no != x.id_no I wouldn't expect the optimizer to be able to make any assumptions. It would note that it is comparing two columns, but not necesarily realize that the columns were the same and that the filter would always yield false (which is really not true if x.id_no is NULL, but that's a nit for this discussion). Anyway, I still think that the optimizer should focus on the results as well as the process, and that it should short circuit. Then again, the programmer shouldn't be so lazy as to send "1=2" to the engine in the first place. (Of course, at this point, I'm sure he won't :-). Will (la4gen!villy@uunet.uu.net)