Re: Outer join with constants

On 19 Sep 2004 03:16:46 -0700, laojiqi_at_hotmail.com (LJQ) wrote:

>I as using Oracle9i Enterprise Edition Release 9.2.0.1.0 for HPUX and
>Windows.
>
>Outer join supposes to handly the relation with 2 tables, how does
>ANSI (SQL99) define this (join two contants with no relation on the
>target tables)?

 The join predicate is not restricted to have anything to do with the two joined sets; all that matters is whether it's true or false for each of the of the rows of the cartesian product of the two original sets.

 If you make it a constant false with 1=0, then you get trivial results depending on the type of join. If you put a superfluous 1=1 then it will make no difference to the truth of the expression, so no difference to the result set.

>The standards only say that the JOIN ON clause using
>the same syntax with where clause but it does not give the expected
>result.

 What's "it" in this sentence - the standards, or Oracle 9.2.0.1.0?

 Oracle 9.2.0.1.0 doesn't give the expected result, but that's because it's buggy.

>I have checked the FIXED BUG list for 9.2.0.5.0 as well, it does not
>mention this one.

 There have been a lot of bugs, and many fixes, in Oracle's ANSI join support over the past few patchsets, one of which clearly fixes this case.

 This bug from the fixed bug list looks like the obvious candidate:

9203 2656627

    Wrong results from ANSI OUTER JOIN with CONSTANT predicates in ON clause

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool