Re: [HACKERS] subselect bug (was Re: [GENERAL] DBLink: interesting issue)
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]
Re: [HACKERS] subselect bug (was Re: [GENERAL] DBLink: interesting
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)
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?
> Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > select * from table_a a > > where ((select data_a from table_a where id = a.id) > > >(select data_b from table_a where id = a.id)); > > ERROR: parser: parse error at or near ">" > > I think I finally got this right ... see if you can break the revised > grammar I just committed ... Thanks. Works fine now. -- Tatsuo Ishii
Re: [HACKERS] subselect bug?
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > select * from table_a a > where ((select data_a from table_a where id = a.id) > >(select data_b from table_a where id = a.id)); > ERROR: parser: parse error at or near ">" I think I finally got this right ... see if you can break the revised grammar I just committed ... regards, tom lane
Re: [HACKERS] subselect bug?
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > select * from table_a a > where ((select data_a from table_a where id = a.id) > >(select data_b from table_a where id = a.id)); > ERROR: parser: parse error at or near ">" Ugh. The grammar does some pretty squirrely things with parentheses around selects, and I guess it's getting confused on this. Don't know why offhand ... regards, tom lane