Re: Outer Join Bug (was It's Incredible)

>From: bmaclean@ix.netcom.com (Bill MacLean) >Date: 22 Apr 1995 05:59:38 GMT >X-Informix-List-Id: <news.13279> > >I too want to see what Informix says about the alleged outer join bug. >I just returned from Tucson where I spent 3 days tweaking a >PowerBuilder application that was running against a Watcom backend, >but is now running against Online 5.0 There are a number of outer >joins in the app, and they all returned the same thing in Informix that >they did in Watcom, and both returned what I expected. > >I asked the guy who originally posted the message about the outer join >bug to elaborate. He wrote back that he hadn't used Informix for 2 >years. It's entirely possible that there was a bug two years ago that >isn't there now. And it is very irritating not to have a succinct reproduction of the problem, because everyone is working in the dark. >I saw someone else (under the "Incredible" thread) say he had no >problems with outer joins, and I saw another person say that his server >crashed on certain outer joins. Crashing is bad, but incorrect result >sets would be worse. > >Can anyone speak authoritatively on this (hint to the Informix people, >please say something about this)? I attach an edited version of an email I sent recently to someone else who asked me about this. I am not aware of any bugs in OUTER join in Informix products, but I have not looked through the bugs database to double check on their existence in current products. Therefore, this is not an authoritative statement, despite being made by an Informix employee. I regard the unsubstantiated, undocumented 'OUTER JOIN' bug claim as a pestilence of a type which is not needed in comp.databases.informix! If you have a bug, then document how the problem is reproduced and which version it was found in. >If there is a bug, I want to know about it, since it will be bad news for >me, but I am wondering if there really is one or not. What the 'bug claimers', whose identity I have lost track of in the blizzard of articles on this subject, may be talking about is the documented behaviour of OUTER joins in Informix. This is not completely SQL-92 compliant (if only because SQL-92 has LEFT, RIGHT and FULL OUTER joins whereas Informix only has variations on the RIGHT OUTER join; also because the notation used by Informix -- which is different from Oracle's, and both of them are different from SQL-92 -- precedes the SQL-92 standard syntax by a good few years). Suppose we have a SELECT statement such as: SELECT A.Column01, B.Column01 FROM TableA A, OUTER TableB B WHERE A.Column02 = B.Column03 AND B.Column02 = "transient error"; The way Informix OUTER joins work means that if there is a row in TableA which joins with a row in TableB for which the condition on B.Column02 is false, then the row from TableA is still selected, albeit with NULL for the B.Column01 value. I personally regard this as a documented bug because I think the following pair of queries should be equivalent to that above, but they produce a different answer -- the row in TableA is not selected with the double query. SELECT A.Column01 A_Col01, B.Column01 B_Col01, B.Column02 B_Col02 FROM TableA A, OUTER TableB B WHERE A.Column02 = B.Column03 INTO TEMP bug; SELECT A_Col01, B_Col01 FROM bug WHERE B_Col02 = "transient error"; You can show this with the data: TableA: Column01 = 123; Column02 = 234. TableB: Column01 = 999; Column02 = "permanent error"; Column03 = 234. However, this is the way that Informix has chosen to document that its OUTER joins behave, and the behaviour is correct within that definition. It doesn't necessarily conform to SQL-92, as I said above (it almost certainly doesn't), but within the definition of Informix OUTER joins, the behaviour is correct. I haven't previously entered the fray because I didn't think it appropriate. I stand by the views presented here. I think that the design decision was wrong because of the argument presented above. However, one person's bug is another's feature, and the design calls for the present behaviour. If the design decision is fixed, then the existing product has a bug. Until then, Informix's OUTER join works correctly, even if unexpectedly. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>