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 (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 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]
[HACKERS] 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 or while 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] subselect bug?
While below is ok: 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); but this fails: 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 "" Does anybody know why? -- 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 "" I think I finally got this right ... see if you can break the revised grammar I just committed ... Thanks. Works fine now. -- Tatsuo Ishii