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:
CREATE TABLE test1 (id int);
CREATE TABLE test2 (id int);
INSERT INTO test1 VALUES (1);
INSERT INTO test2 VALUES (1);
INSERT INTO test2 VALUES (1);
SELECT * FROM test1, test2
WHERE test1.id = test2.id
AND test1.id = (SELECT 123 WHERE test1.id = 100 AND test2.id = 100);
The query incorrectly returns one row, instead of none, with this plan:
QUERY PLAN
-----------------------------------------------------------
Nested Loop (cost=0.00..2.03 rows=1 width=8)
Join Filter: (test1.id = test2.id)
-> Seq Scan on test1 (cost=0.00..1.01 rows=1 width=4)
-> Seq Scan on test2 (cost=0.00..1.01 rows=1 width=4)
(4 rows)
I don't have the time to dig deeper right now, but I hope this helps
someone else to get started...
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
