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

David W. Lambert <lambert@mitre.org> wrote: > > 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. I've had this happen as well, but after some thought decided it was probably correct behavior, not a bug. Imagine if the same query was selecting for "A.colA = 10" rather than "A.colA IS NULL". It MUST return a row for every row in B, and in that row A.colA will be 10 or NULL. In the query above, A.colA will be NULL if A.colA IS NULL, or (because of the 'outer') NULL if it is not null.