Heikki Linnakangas <heikki@enterprisedb.com> writes:
> Sergey Burladyan wrote:
>> i have query with join of two table and 'where' filter it result by subplan
>> which have references to join result, but this subplan not executed and
>> result is incorrect. This subplan also not exist in explain analyze output.
> I can reproduce this on 8.1 and 8.2 branch heads, but not on 8.3 HEAD.
> I've simplified test case down to this:
You don't even need the sub-select:
regression=# explain SELECT * FROM test1, test2
WHERE test1.id = test2.id
AND test1.id = test1.id*test2.id;
QUERY PLAN
---------------------------------------------------------------------
Merge Join (cost=299.56..653.73 rows=22898 width=8)
Merge Cond: (test1.id = test2.id)
-> Sort (cost=149.78..155.13 rows=2140 width=4)
Sort Key: test1.id
-> Seq Scan on test1 (cost=0.00..31.40 rows=2140 width=4)
-> Sort (cost=149.78..155.13 rows=2140 width=4)
Sort Key: test2.id
-> Seq Scan on test2 (cost=0.00..31.40 rows=2140 width=4)
(8 rows)
It appears that join_clause_is_redundant() is rejecting the clause as
redundant. I suppose some part of that machinery gets confused by the
fact that the RHS of the clause references both relations. The
EquivalenceClass rewrite cleaned this whole area up greatly, so no
surprise that the bug is gone in HEAD. No time to look at it more now.
regards, tom lane