AW: A fine point about OUTER JOIN semantics

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