I can tell you the results Informix produces:

> Am I right in thinking that the WHERE clause of a query must logically
> be applied *after* any joins specified in the FROM clause?
> 
> For example, suppose that we have table t1 (x int) containing the
> values 1, 2, 3, 4, and table t2 (y int) containing the values 1, 2, 4.
> It's clear that the result of
> 	SELECT * FROM t1 LEFT JOIN t2 ON (x = y);
> should be
> 	x	y
> 
> 	1	1
> 	2	2
> 	3	NULL
> 	4	4

same

> 
> But suppose we make the query
> 	SELECT * FROM t1 LEFT JOIN t2 ON (x = y) WHERE y <> 2;
> It seems to me this should yield
> 	x	y
> 
> 	1	1
> 	3	NULL
> 	4	4
> 
> and not
> 	x	y
> 
> 	1	1
> 	2	NULL
> 	3	NULL
> 	4	4

          x           y

          1           1
          4           4

> 
> which is what you'd get if the y=2 tuple were filtered out before
> reaching the left-join stage.  Does anyone read the spec differently,
> or get the latter result from another implementation?
> 
> The reason this is interesting is that this example breaks a rather
> fundamental assumption in our planner/optimizer, namely that WHERE
> conditions can be pushed down to the lowest level at which all the
> variables they mention are available.  Thus the planner would normally
> apply "y <> 2" during its bottom-level scan of t2, which 
> would cause the
> LEFT JOIN to decide that x = 2 is an unmatched value, and thus produce
> a "2 NULL" output row.
> 
> An even more interesting example is
> 	SELECT * FROM t1 FULL JOIN t2 ON (x = y AND y <> 2);
> My interpretation is that this should produce
> 	x	y
> 
> 	1	1
> 	2	NULL
> 	NULL	2
> 	3	NULL
> 	4	4

          x           y

          1           1
          4           4

> since both t1's x=2 and t2's y=2 tuple will appear "unmatched".
> This is *not* the same output you'd get from
> 	SELECT * FROM t1 FULL JOIN t2 ON (x = y) WHERE y <> 2;
> which I think should yield
> 	x	y
> 
> 	1	1
> 	3	NULL
> 	4	4
> This shows that JOIN/ON conditions for outer joins are not 
> semantically
> interchangeable with WHERE conditions.

          x           y

          1           1
          4           4

> 
> This is going to be a bit of work to fix, so I thought I'd better
> confirm that I'm reading the spec correctly before I dive into it.

No idea if they interpret correctly, but seems they hand it interchangeably.
Someone want to check Oracle and MS Sql ?

Andreas

Tue, 5 Sep 2000 16:56:59 +0200 
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
AW: A fine point about OUTER JOIN semantics
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
>> But suppose we make the query
>> SELECT * FROM t1 LEFT JOIN t2 ON (x = y) WHERE y <> 2;
>> It seems to me this should yield
>> x	y
>> 
>> 1	1
>> 3	NULL
>> 4	4

>           x           y

>           1           1
>           4           4

Oh, my mistake, I forgot that the WHERE clause would filter out NULLs.
Try
SELECT * FROM t1 LEFT JOIN t2 ON (x = y) WHERE y <> 2 OR y IS NULL;

>> An even more interesting example is
>> SELECT * FROM t1 FULL JOIN t2 ON (x = y AND y <> 2);
>> My interpretation is that this should produce
>> x	y
>> 
>> 1	1
>> 2	NULL
>> NULL	2
>> 3	NULL
>> 4	4

>           x           y

>           1           1
>           4           4

Here I believe Informix is broken.  Their result clearly does not
agree with the spec's definition of a FULL JOIN ... indeed it looks
exactly like an inner join.

			regards, tom lane

Tue, 05 Sep 2000 11:30:15 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: A fine point about OUTER JOIN semantics 