Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc0a493e

Browse files
committed
Fix planner error (or assert trap) with nested set operations.
As reported by Sean Johnston in bug #14614, since 9.6 the planner can faildue to trying to look up the referent of a Var with varno 0. This happensbecause we generate such Vars in generate_append_tlist, for lack of anybetter way to describe the output of a SetOp node. In typical situationsnothing really cares about that, but given nested set-operation querieswe will call estimate_num_groups on the output of the subquery, and thatwants to know what a Var actually refers to. That logic used to look atsubquery->targetList, but in commit3fc6e2d I'd switched it to look atsubroot->processed_tlist, ie the actual output of the subquery plan not theparser's idea of the result. It seemed like a good idea at the time :-(.As a band-aid fix, change it back.Really we ought to have an honest way of naming the outputs of SetOp steps,which suggests that it'd be a good idea for the parser to emit an RTEcorresponding to each one. But that's a task for another day, and itcertainly wouldn't yield a back-patchable fix.Report:https://postgr.es/m/20170407115808.25934.51866@wrigleys.postgresql.org
1 parentdd93afc commitc0a493e

File tree

3 files changed

+42
-1
lines changed

3 files changed

+42
-1
lines changed

‎src/backend/optimizer/prep/prepunion.c

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -338,6 +338,16 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
338338
* Estimate number of groups if caller wants it. If the subquery used
339339
* grouping or aggregation, its output is probably mostly unique
340340
* anyway; otherwise do statistical estimation.
341+
*
342+
* XXX you don't really want to know about this: we do the estimation
343+
* using the subquery's original targetlist expressions, not the
344+
* subroot->processed_tlist which might seem more appropriate. The
345+
* reason is that if the subquery is itself a setop, it may return a
346+
* processed_tlist containing "varno 0" Vars generated by
347+
* generate_append_tlist, and those would confuse estimate_num_groups
348+
* mightily. We ought to get rid of the "varno 0" hack, but that
349+
* requires a redesign of the parsetree representation of setops, so
350+
* that there can be an RTE corresponding to each setop's output.
341351
*/
342352
if (pNumGroups)
343353
{
@@ -347,7 +357,7 @@ recurse_set_operations(Node *setOp, PlannerInfo *root,
347357
*pNumGroups=subpath->rows;
348358
else
349359
*pNumGroups=estimate_num_groups(subroot,
350-
get_tlist_exprs(subroot->processed_tlist, false),
360+
get_tlist_exprs(subquery->targetList, false),
351361
subpath->rows,
352362
NULL);
353363
}

‎src/test/regress/expected/union.out

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -319,6 +319,31 @@ SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl;
319319

320320
SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE;
321321
ERROR: FOR NO KEY UPDATE is not allowed with UNION/INTERSECT/EXCEPT
322+
-- nested cases
323+
(SELECT 1,2,3 UNION SELECT 4,5,6) INTERSECT SELECT 4,5,6;
324+
?column? | ?column? | ?column?
325+
----------+----------+----------
326+
4 | 5 | 6
327+
(1 row)
328+
329+
(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) INTERSECT SELECT 4,5,6;
330+
?column? | ?column? | ?column?
331+
----------+----------+----------
332+
4 | 5 | 6
333+
(1 row)
334+
335+
(SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6;
336+
?column? | ?column? | ?column?
337+
----------+----------+----------
338+
1 | 2 | 3
339+
(1 row)
340+
341+
(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6;
342+
?column? | ?column? | ?column?
343+
----------+----------+----------
344+
1 | 2 | 3
345+
(1 row)
346+
322347
--
323348
-- Mixed types
324349
--

‎src/test/regress/sql/union.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -111,6 +111,12 @@ SELECT q1 FROM int8_tbl EXCEPT ALL SELECT DISTINCT q2 FROM int8_tbl;
111111

112112
SELECT q1FROM int8_tbl EXCEPT ALLSELECT q1FROM int8_tbl FOR NO KEYUPDATE;
113113

114+
-- nested cases
115+
(SELECT1,2,3UNIONSELECT4,5,6) INTERSECTSELECT4,5,6;
116+
(SELECT1,2,3UNIONSELECT4,5,6ORDER BY1,2) INTERSECTSELECT4,5,6;
117+
(SELECT1,2,3UNIONSELECT4,5,6) EXCEPTSELECT4,5,6;
118+
(SELECT1,2,3UNIONSELECT4,5,6ORDER BY1,2) EXCEPTSELECT4,5,6;
119+
114120
--
115121
-- Mixed types
116122
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp