Re: BUG #3494: may be Query Error: subplan does not executed

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