Excellent points Joe and I don't have the answers.
If I had to guess, it's probably because it would be very difficult to make
parsers smart enough to figure this out. Since it's not part of the
standard, I doubt that part will ever change.
Same goes for short-circuiting the execution path. I mean, until recently
when you did SELECT COUNT(*) FROM MYTABLE, DB2 engine would actually go
through each row in the table instead of retrieving the value from the data
space object. That caused REAL performance pains for years. Thankfully,
IBM corrected that.
I can only hope that parser and query engine get smarter as they evolve,
regardless of the standards. But that won't happen until customers get more
liberal with submitting DCRs, or LUG companies put their 2 cents in with
Rochester lab.
Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: Re: Classic trap : using SQL while not fully competent
Elvis Budimlic wrote:
[Joe wrote] I'm not sure why this gets past the syntax checker;
It is a valid syntax, except it wasn't what OP was after in his scenario.
Why is it valid? If it's an exception join, the only possible values in
the right-hand file are null. WHERE field=null is always true, WHERE
field=(anything but null) is always false, and, if you were going to
allow it at all, should end the query right there since nothing will
every match.
Then again, SQL considers SELECT * FROM FILE WHERE '1' = '0' to be valid
syntax, so I guess it only follows that the WHERE clause wouldn't cause
it to fail.
Joe
midrange.com
