Re: sql question

> >From article <27sqfjINNrch@emory.mathcs.emory.edu>, by jgordon@ssf-sys.dhl.com (Jim Gordon): > > > > What are people out there expecting 1=2 and 1=1 to do? Consider the > > case where 1=2 is one filter amongst many, part of a complex where > > clause which has many OR's and AND's. > > > > It should evaluate constant subexpressions at optimization > time, not row-by-row. I don't see how making the clause more > complex changes the nature of the problem, unless you are > maintaining that the optimizer, in addition to not optimizing > the trivial case of invariant filters, is also baffled by > multiple conditions. Well, what I was truely trying to do was find out what people were expecting it to do. From previous emails I got the impression that some people were expecting it to come back with an error or that it was coming back with an error. I would agree that it should evaluate the expressions at optimisation time and act accordingly. My only problem with this would be that, as we are only dealing with a semi-intelligent beast which does indeed get baffled with some multiple conditions, which would I prefer to see fixed first. My priorities "at the moment" would be:- 1) Optimise order by statements to use an appropriate index where this doesn't happen today when joins exist. 2) Optimise OR conditions better to avoid the need to use UNIONs so much. 3) Optimise choice of starting table in a set of joins to take into account data distribution. (Improving apparently in V6) Maybe others here like cross network optimisation stats, well maybe not - not a wide audience for this yet. ... n) Optimise constant declarations Off course everybody is free to disagree with me. Perhaps we should try and get a list of items that people want fixed in the optimiser and pass it on to Captain Panic (have I demoted him? Private?, Lieutenant (Lef-ten-ant)?, Major?, Brigadier?, Colonel?) et al. Cheers - Jim My opinions are my own. They may vary with time but they remain MINE! ---------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 375-5222 (Work) Address: 700 Airport Blvd. #300 (415) 882-9728 (Home) Burlingame, CA 94010-1937 Fax: (415) 375-5019 ----------------------------------------------------------------------