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

[Tom Lane]

Joe Conway [EMAIL PROTECTED] 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

Ick.

 I'm just starting to dig in to this, but was hoping for any thoughts or
 guidance I can get.

I can look at this, unless you really want to solve it yourself ...

 p.s. Below is a backtrace:

The debug output:

TRAP: FailedAssertion(!(var-varlevelsup  0  var-varlevelsup  
PlannerQueryLevel), File: subselect.c, Line: 81)

suggests that the problem is with variable depth --- I'm guessing that
we're not adjusting varlevelsup correctly at some step of the planning
process.  Offhand I'd expect the innermost select to be pulled up into
the parent select (the argument of EXISTS) and probably something is
going wrong with that.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [HACKERS] subselect bug (was Re: [GENERAL] DBLink: interesting

[Joe Conway]

Tom Lane wrote:
I'm just starting to dig in to this, but was hoping for any thoughts or
guidance I can get.
 
 I can look at this, unless you really want to solve it yourself ...
 

I'll look into it a bit for my own edification, but if you have the time to 
solve it, I wouldn't want to get in the way. In any case, if you think it 
should be fixed before beta2, I'd give you better odds than me ;-)

Joe


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

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

[Tom Lane]

Joe Conway [EMAIL PROTECTED] 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.cTue 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

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]