Re: subselect bug (was Re: [GENERAL] DBLink: interesting issue)

Joe Conway <mail(at)joeconway(dot)com> writes:
> replica=# create table foo(f1 int);
> CREATE TABLE
> replica=# SELECT * FROM foo t WHERE NOT EXISTS (SELECT remoteid FROM (SELECT
> f1 as remoteid FROM foo WHERE f1 = t.f1) AS t1);
> server closed the connection unexpectedly

Got it --- this bug has been there awhile :-(, ever since we had the
pull-up-subquery logic, which was in 7.1 IIRC. The pullup code
neglected to adjust references to uplevel Vars. Surprising that no one
reported this sooner.

The attached patch is against CVS tip. It will not apply cleanly to
7.2 because pull_up_subqueries() has been modified since then, but if
anyone's desperate for a fix in 7.2 it could probably be adapted.

regards, tom lane

*** src/backend/optimizer/plan/planner.c.orig Wed Sep 4 17:30:30 2002
--- src/backend/optimizer/plan/planner.c Tue Sep 24 14:02:54 2002
***************
*** 337,352 ****

/*
* Now make a modifiable copy of the subquery that we can run
! * OffsetVarNodes on.
*/
subquery = copyObject(subquery);

/*
! * Adjust varnos in subquery so that we can append its
* rangetable to upper query's.
*/
rtoffset = length(parse->rtable);
OffsetVarNodes((Node *) subquery, rtoffset, 0);

/*
* Replace all of the top query's references to the subquery's
--- 337,358 ----

/*
* Now make a modifiable copy of the subquery that we can run
! * OffsetVarNodes and IncrementVarSublevelsUp on.
*/
subquery = copyObject(subquery);

/*
! * Adjust level-0 varnos in subquery so that we can append its
* rangetable to upper query's.
*/
rtoffset = length(parse->rtable);
OffsetVarNodes((Node *) subquery, rtoffset, 0);
+
+ /*
+ * Upper-level vars in subquery are now one level closer to their
+ * parent than before.
+ */
+ IncrementVarSublevelsUp((Node *) subquery, -1, 1);

/*
* Replace all of the top query's references to the subquery's