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

In article <3na60a$6ff@ixnews3.ix.netcom.com>, bmaclean@ix.netcom.com (Bill MacLean) wrote: > [CLIPPED] > 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)? > > 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. > > > Thanks, > > Bill MacLean I was recently "bitten" by an Informix outer join problem running under V4.0 - I'm not sure if its a bug, or handled differently in newer releases. I was trying to use an outer join to find keys in table "B" which did not exist in table "A", without using NOT IN or NOT EXISTS. The query looked something like: select A.colA, B.colB from B, outer A where A.colA = B.colB and A.colA is null; The problem is in the "A.colA is null" part, and the query returns both the normal join items, and the outer items, but colA is null for ALL returned records! I'm pretty sure I've used this technique in Oracle without problems, although I didn't go back to verify it. -Peter Sylvester