Thread: subselect bug (was Re: [GENERAL] DBLink: interesting issue)
Joe Conway wrote:> Oleg Lebedev wrote:>>> Ok, here are all the files.>> This dblink thread on GENERAL led me to a bug in the planner subselect code. Here is an example query that triggers it (independent of dblink and/or table functions): 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 This probably means the server terminated abnormally before orwhile processing the request. The connection to the server was lost. Attempting reset: Failed. It doesn't matter how foo is defined. I'm just starting to dig in to this, but was hoping for any thoughts or guidance I can get. Thanks, Joe p.s. Below is a backtrace: #3 0x081797a1 in ExceptionalCondition () at assert.c:46 #4 0x0810e102 in replace_var (var=0x82f73a8) at subselect.c:81 #5 0x0811293c in expression_tree_mutator (node=0x82f7438, mutator=0x810e96c <replace_correlation_vars_mutator>, context=0x0) at clauses.c:2314 #6 0x0810e9a5 in replace_correlation_vars_mutator (node=0x82f7438, context=0x0) at subselect.c:540 #7 0x08112718 in expression_tree_mutator (node=0x82f7454, mutator=0x810e96c <replace_correlation_vars_mutator>, context=0x0) at clauses.c:2179 #8 0x0810e9a5 in replace_correlation_vars_mutator (node=0x82f7454, context=0x0) at subselect.c:540 #9 0x0811293c in expression_tree_mutator (node=0x82f7480, mutator=0x810e96c <replace_correlation_vars_mutator>, context=0x0) at clauses.c:2314 #10 0x0810e9a5 in replace_correlation_vars_mutator (node=0x82f7480, context=0x0) at subselect.c:540 #11 0x0810e968 in SS_replace_correlation_vars (expr=0x82f7480) at subselect.c:525 #12 0x0810cef5 in preprocess_expression (parse=0x82f6830, expr=0x82f7064, kind=1) at planner.c:725 #13 0x0810cf7e in preprocess_qual_conditions (parse=0x82f6830, jtnode=0x82f6d70) at planner.c:775 #14 0x0810c75c in subquery_planner (parse=0x82f6830, tuple_fraction=1) at planner.c:168 #15 0x0810e260 in make_subplan (slink=0x82f6698) at subselect.c:185 #16 0x0811293c in expression_tree_mutator (node=0x82f6780, mutator=0x810e9bc <process_sublinks_mutator>, context=0x0) at clauses.c:2314 #17 0x0810ea35 in process_sublinks_mutator (node=0x82f6780, context=0x0) at subselect.c:586 #18 0x08112718 in expression_tree_mutator (node=0x82f6754, mutator=0x810e9bc <process_sublinks_mutator>, context=0x0) at clauses.c:2179 #19 0x0810ea35 in process_sublinks_mutator (node=0x82f6754, context=0x0) at subselect.c:586 #20 0x0811293c in expression_tree_mutator (node=0x82f679c, mutator=0x810e9bc <process_sublinks_mutator>, context=0x0) at clauses.c:2314 #21 0x0810ea35 in process_sublinks_mutator (node=0x82f679c, context=0x0) at subselect.c:586 #22 0x0810e9b8 in SS_process_sublinks (expr=0x82f679c) at subselect.c:553 #23 0x0810cede in preprocess_expression (parse=0x82f46d4, expr=0x82fc164, kind=1) at planner.c:721 #24 0x0810cf7e in preprocess_qual_conditions (parse=0x82f46d4, jtnode=0x82fc36c) at planner.c:775 #25 0x0810c75c in subquery_planner (parse=0x82f46d4, tuple_fraction=-1) at planner.c:168 #26 0x0810c68c in planner (parse=0x82f46d4) at planner.c:96
Joe Conway <mail@joeconway.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
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
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
Joe Conway <mail@joeconway.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'sreferences to the subquery's