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

Commitc4c2885

Browse files
committed
Fix UNION/INTERSECT/EXCEPT over no columns.
Since 9.4, we've allowed the syntax "select union select" and variantsof that. However, the planner wasn't expecting a no-column set operationand ended up treating the set operation as if it were UNION ALL.Turns out it's trivial to fix in v10 and later; we just need to be carefulabout not generating a Sort node with no sort keys. However, since a weirdcorner case like this is never going to be exercised by developers, we'dbetter have thorough regression tests if we want to consider it supported.Per report from Victor Yegorov.Discussion:https://postgr.es/m/CAGnEbojGJrRSOgJwNGM7JSJZpVAf8xXcVPbVrGdhbVEHZ-BUMw@mail.gmail.com
1 parent854823f commitc4c2885

File tree

4 files changed

+168
-17
lines changed

4 files changed

+168
-17
lines changed

‎src/backend/optimizer/plan/createplan.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6326,7 +6326,6 @@ make_setop(SetOpCmd cmd, SetOpStrategy strategy, Plan *lefttree,
63266326
* convert SortGroupClause list into arrays of attr indexes and equality
63276327
* operators, as wanted by executor
63286328
*/
6329-
Assert(numCols>0);
63306329
dupColIdx= (AttrNumber*)palloc(sizeof(AttrNumber)*numCols);
63316330
dupOperators= (Oid*)palloc(sizeof(Oid)*numCols);
63326331

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

Lines changed: 10 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -711,10 +711,6 @@ generate_nonunion_path(SetOperationStmt *op, PlannerInfo *root,
711711
/* Identify the grouping semantics */
712712
groupList=generate_setop_grouplist(op,tlist);
713713

714-
/* punt if nothing to group on (can this happen?) */
715-
if (groupList==NIL)
716-
returnpath;
717-
718714
/*
719715
* Estimate number of distinct groups that we'll need hashtable entries
720716
* for; this is the size of the left-hand input for EXCEPT, or the smaller
@@ -741,7 +737,7 @@ generate_nonunion_path(SetOperationStmt *op, PlannerInfo *root,
741737
dNumGroups,dNumOutputRows,
742738
(op->op==SETOP_INTERSECT) ?"INTERSECT" :"EXCEPT");
743739

744-
if (!use_hash)
740+
if (groupList&&!use_hash)
745741
path= (Path*)create_sort_path(root,
746742
result_rel,
747743
path,
@@ -864,10 +860,6 @@ make_union_unique(SetOperationStmt *op, Path *path, List *tlist,
864860
/* Identify the grouping semantics */
865861
groupList=generate_setop_grouplist(op,tlist);
866862

867-
/* punt if nothing to group on (can this happen?) */
868-
if (groupList==NIL)
869-
returnpath;
870-
871863
/*
872864
* XXX for the moment, take the number of distinct groups as equal to the
873865
* total input size, ie, the worst case. This is too conservative, but we
@@ -898,13 +890,15 @@ make_union_unique(SetOperationStmt *op, Path *path, List *tlist,
898890
else
899891
{
900892
/* Sort and Unique */
901-
path= (Path*)create_sort_path(root,
902-
result_rel,
903-
path,
904-
make_pathkeys_for_sortclauses(root,
905-
groupList,
906-
tlist),
907-
-1.0);
893+
if (groupList)
894+
path= (Path*)
895+
create_sort_path(root,
896+
result_rel,
897+
path,
898+
make_pathkeys_for_sortclauses(root,
899+
groupList,
900+
tlist),
901+
-1.0);
908902
/* We have to manually jam the right tlist into the path; ick */
909903
path->pathtarget=create_pathtarget(root,tlist);
910904
path= (Path*)create_upper_unique_path(root,

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

Lines changed: 115 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -552,6 +552,121 @@ SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)))
552552
4567890123456789 | -4567890123456789
553553
(5 rows)
554554

555+
--
556+
-- Check behavior with empty select list (allowed since 9.4)
557+
--
558+
select union select;
559+
--
560+
(1 row)
561+
562+
select intersect select;
563+
--
564+
(1 row)
565+
566+
select except select;
567+
--
568+
(0 rows)
569+
570+
-- check hashed implementation
571+
set enable_hashagg = true;
572+
set enable_sort = false;
573+
explain (costs off)
574+
select from generate_series(1,5) union select from generate_series(1,3);
575+
QUERY PLAN
576+
----------------------------------------------------------------
577+
HashAggregate
578+
-> Append
579+
-> Function Scan on generate_series
580+
-> Function Scan on generate_series generate_series_1
581+
(4 rows)
582+
583+
explain (costs off)
584+
select from generate_series(1,5) intersect select from generate_series(1,3);
585+
QUERY PLAN
586+
----------------------------------------------------------------------
587+
HashSetOp Intersect
588+
-> Append
589+
-> Subquery Scan on "*SELECT* 1"
590+
-> Function Scan on generate_series
591+
-> Subquery Scan on "*SELECT* 2"
592+
-> Function Scan on generate_series generate_series_1
593+
(6 rows)
594+
595+
select from generate_series(1,5) union select from generate_series(1,3);
596+
--
597+
(1 row)
598+
599+
select from generate_series(1,5) union all select from generate_series(1,3);
600+
--
601+
(8 rows)
602+
603+
select from generate_series(1,5) intersect select from generate_series(1,3);
604+
--
605+
(1 row)
606+
607+
select from generate_series(1,5) intersect all select from generate_series(1,3);
608+
--
609+
(3 rows)
610+
611+
select from generate_series(1,5) except select from generate_series(1,3);
612+
--
613+
(0 rows)
614+
615+
select from generate_series(1,5) except all select from generate_series(1,3);
616+
--
617+
(2 rows)
618+
619+
-- check sorted implementation
620+
set enable_hashagg = false;
621+
set enable_sort = true;
622+
explain (costs off)
623+
select from generate_series(1,5) union select from generate_series(1,3);
624+
QUERY PLAN
625+
----------------------------------------------------------------
626+
Unique
627+
-> Append
628+
-> Function Scan on generate_series
629+
-> Function Scan on generate_series generate_series_1
630+
(4 rows)
631+
632+
explain (costs off)
633+
select from generate_series(1,5) intersect select from generate_series(1,3);
634+
QUERY PLAN
635+
----------------------------------------------------------------------
636+
SetOp Intersect
637+
-> Append
638+
-> Subquery Scan on "*SELECT* 1"
639+
-> Function Scan on generate_series
640+
-> Subquery Scan on "*SELECT* 2"
641+
-> Function Scan on generate_series generate_series_1
642+
(6 rows)
643+
644+
select from generate_series(1,5) union select from generate_series(1,3);
645+
--
646+
(1 row)
647+
648+
select from generate_series(1,5) union all select from generate_series(1,3);
649+
--
650+
(8 rows)
651+
652+
select from generate_series(1,5) intersect select from generate_series(1,3);
653+
--
654+
(1 row)
655+
656+
select from generate_series(1,5) intersect all select from generate_series(1,3);
657+
--
658+
(3 rows)
659+
660+
select from generate_series(1,5) except select from generate_series(1,3);
661+
--
662+
(0 rows)
663+
664+
select from generate_series(1,5) except all select from generate_series(1,3);
665+
--
666+
(2 rows)
667+
668+
reset enable_hashagg;
669+
reset enable_sort;
555670
--
556671
-- Check handling of a case with unknown constants. We don't guarantee
557672
-- an undecorated constant will work in all cases, but historically this

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

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -190,6 +190,49 @@ SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)))
190190

191191
(((((select*from int8_tbl)))));
192192

193+
--
194+
-- Check behavior with empty select list (allowed since 9.4)
195+
--
196+
197+
selectunionselect;
198+
select intersectselect;
199+
select exceptselect;
200+
201+
-- check hashed implementation
202+
set enable_hashagg= true;
203+
set enable_sort= false;
204+
205+
explain (costs off)
206+
selectfrom generate_series(1,5)unionselectfrom generate_series(1,3);
207+
explain (costs off)
208+
selectfrom generate_series(1,5) intersectselectfrom generate_series(1,3);
209+
210+
selectfrom generate_series(1,5)unionselectfrom generate_series(1,3);
211+
selectfrom generate_series(1,5)union allselectfrom generate_series(1,3);
212+
selectfrom generate_series(1,5) intersectselectfrom generate_series(1,3);
213+
selectfrom generate_series(1,5) intersect allselectfrom generate_series(1,3);
214+
selectfrom generate_series(1,5) exceptselectfrom generate_series(1,3);
215+
selectfrom generate_series(1,5) except allselectfrom generate_series(1,3);
216+
217+
-- check sorted implementation
218+
set enable_hashagg= false;
219+
set enable_sort= true;
220+
221+
explain (costs off)
222+
selectfrom generate_series(1,5)unionselectfrom generate_series(1,3);
223+
explain (costs off)
224+
selectfrom generate_series(1,5) intersectselectfrom generate_series(1,3);
225+
226+
selectfrom generate_series(1,5)unionselectfrom generate_series(1,3);
227+
selectfrom generate_series(1,5)union allselectfrom generate_series(1,3);
228+
selectfrom generate_series(1,5) intersectselectfrom generate_series(1,3);
229+
selectfrom generate_series(1,5) intersect allselectfrom generate_series(1,3);
230+
selectfrom generate_series(1,5) exceptselectfrom generate_series(1,3);
231+
selectfrom generate_series(1,5) except allselectfrom generate_series(1,3);
232+
233+
reset enable_hashagg;
234+
reset enable_sort;
235+
193236
--
194237
-- Check handling of a case with unknown constants. We don't guarantee
195238
-- an undecorated constant will work in all cases, but historically this

0 commit comments

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