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 ofSELECT * FROM t1 LEFT JOIN t2 ON (x = y);
should bex y
1 12 23 NULL4 4
But suppose we make the querySELECT * FROM t1 LEFT JOIN t2 ON (x = y) WHERE y <> 2;
It seems to me this should yieldx y
1 13 NULL4 4
and notx y
1 12 NULL3 NULL4 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 isSELECT * FROM t1 FULL JOIN t2 ON (x = y AND y <> 2);
My interpretation is that this should producex y
1 12 NULLNULL 23 NULL4 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 fromSELECT * FROM t1 FULL JOIN t2 ON (x = y) WHERE y <> 2;
which I think should yieldx y
1 13 NULL4 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