Tom I'd be happy to go back through any specs for second thoughts... do you
have a URL to go to?
At 9/1/2000 04:47 PM -0400, Tom Lane wrote:
>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
>
>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
>
>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
>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.
>
>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.
>
>Comments?
>
> regards, tom lane
