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

Commitbdceb86

Browse files
committed
Fix SELECT DISTINCT with index-optimized MIN/MAX on inheritance trees.
In a query such as "SELECT DISTINCT min(x) FROM tab", the DISTINCT ispretty useless (there being only one output row), but nonetheless itshouldn't fail. But it could fail if "tab" is an inheritance parent,because planagg.c's code for fixing up equivalence classes after making theindex-optimized MIN/MAX transformation wasn't prepared to find child-tableversions of the aggregate expression. The least ugly fix seems to beto add an option to mutate_eclass_expressions() to skip child-tableequivalence class members, which aren't used anymore at this stage ofplanning so it's not really necessary to fix them. Since child membersare ignored in many cases already, it seems plausible formutate_eclass_expressions() to have an option to ignore them too.Per bug #7703 from Maxim Boguk.Back-patch to 9.1. Although the same code exists before that, it cannotencounter child-table aggregates AFAICS, because the index optimizationtransformation cannot succeed on inheritance trees before 9.1 (for lackof MergeAppend).
1 parent38b38fb commitbdceb86

File tree

5 files changed

+59
-6
lines changed

5 files changed

+59
-6
lines changed

‎src/backend/optimizer/path/equivclass.c

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1897,12 +1897,12 @@ add_child_rel_equivalences(PlannerInfo *root,
18971897
/*
18981898
* mutate_eclass_expressions
18991899
* Apply an expression tree mutator to all expressions stored in
1900-
* equivalence classes.
1900+
* equivalence classes (but ignore child exprs unless include_child_exprs).
19011901
*
19021902
* This is a bit of a hack ... it's currently needed only by planagg.c,
19031903
* which needs to do a global search-and-replace of MIN/MAX Aggrefs
19041904
* after eclasses are already set up. Without changing the eclasses too,
1905-
* subsequent matching of ORDER BY clauses would fail.
1905+
* subsequent matching of ORDER BYand DISTINCTclauses would fail.
19061906
*
19071907
* Note that we assume the mutation won't affect relation membership or any
19081908
* other properties we keep track of (which is a bit bogus, but by the time
@@ -1912,7 +1912,8 @@ add_child_rel_equivalences(PlannerInfo *root,
19121912
void
19131913
mutate_eclass_expressions(PlannerInfo*root,
19141914
Node*(*mutator) (),
1915-
void*context)
1915+
void*context,
1916+
boolinclude_child_exprs)
19161917
{
19171918
ListCell*lc1;
19181919

@@ -1925,6 +1926,9 @@ mutate_eclass_expressions(PlannerInfo *root,
19251926
{
19261927
EquivalenceMember*cur_em= (EquivalenceMember*)lfirst(lc2);
19271928

1929+
if (cur_em->em_is_child&& !include_child_exprs)
1930+
continue;/* ignore children unless requested */
1931+
19281932
cur_em->em_expr= (Expr*)
19291933
mutator((Node*)cur_em->em_expr,context);
19301934
}

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

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -256,15 +256,19 @@ optimize_minmax_aggregates(PlannerInfo *root, List *tlist,
256256

257257
/*
258258
* We have to replace Aggrefs with Params in equivalence classes too, else
259-
* ORDER BY or DISTINCT on an optimized aggregate will fail.
259+
* ORDER BY or DISTINCT on an optimized aggregate will fail. We don't
260+
* need to process child eclass members though, since they aren't of
261+
* interest anymore --- and replace_aggs_with_params_mutator isn't able
262+
* to handle Aggrefs containing translated child Vars, anyway.
260263
*
261264
* Note: at some point it might become necessary to mutate other data
262265
* structures too, such as the query's sortClause or distinctClause. Right
263266
* now, those won't be examined after this point.
264267
*/
265268
mutate_eclass_expressions(root,
266269
replace_aggs_with_params_mutator,
267-
(void*)root);
270+
(void*)root,
271+
false);
268272

269273
/*
270274
* Generate the output plan --- basically just a Result

‎src/include/optimizer/paths.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -121,7 +121,8 @@ extern void add_child_rel_equivalences(PlannerInfo *root,
121121
RelOptInfo*child_rel);
122122
externvoidmutate_eclass_expressions(PlannerInfo*root,
123123
Node*(*mutator) (),
124-
void*context);
124+
void*context,
125+
boolinclude_child_exprs);
125126
externList*find_eclass_clauses_for_index_join(PlannerInfo*root,
126127
RelOptInfo*rel,
127128
Relidsouter_relids);

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

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -740,6 +740,45 @@ select min(f1), max(f1) from minmaxtest;
740740
11 | 18
741741
(1 row)
742742

743+
-- DISTINCT doesn't do anything useful here, but it shouldn't fail
744+
explain (costs off)
745+
select distinct min(f1), max(f1) from minmaxtest;
746+
QUERY PLAN
747+
--------------------------------------------------------------------------------------
748+
HashAggregate
749+
InitPlan 1 (returns $0)
750+
-> Limit
751+
-> Merge Append
752+
Sort Key: public.minmaxtest.f1
753+
-> Index Scan using minmaxtesti on minmaxtest
754+
Index Cond: (f1 IS NOT NULL)
755+
-> Index Scan using minmaxtest1i on minmaxtest1 minmaxtest
756+
Index Cond: (f1 IS NOT NULL)
757+
-> Index Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest
758+
Index Cond: (f1 IS NOT NULL)
759+
-> Index Scan using minmaxtest3i on minmaxtest3 minmaxtest
760+
Index Cond: (f1 IS NOT NULL)
761+
InitPlan 2 (returns $1)
762+
-> Limit
763+
-> Merge Append
764+
Sort Key: public.minmaxtest.f1
765+
-> Index Scan Backward using minmaxtesti on minmaxtest
766+
Index Cond: (f1 IS NOT NULL)
767+
-> Index Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest
768+
Index Cond: (f1 IS NOT NULL)
769+
-> Index Scan using minmaxtest2i on minmaxtest2 minmaxtest
770+
Index Cond: (f1 IS NOT NULL)
771+
-> Index Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest
772+
Index Cond: (f1 IS NOT NULL)
773+
-> Result
774+
(26 rows)
775+
776+
select distinct min(f1), max(f1) from minmaxtest;
777+
min | max
778+
-----+-----
779+
11 | 18
780+
(1 row)
781+
743782
drop table minmaxtest cascade;
744783
NOTICE: drop cascades to 3 other objects
745784
DETAIL: drop cascades to table minmaxtest1

‎src/test/regress/sql/aggregates.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -278,6 +278,11 @@ explain (costs off)
278278
selectmin(f1),max(f1)from minmaxtest;
279279
selectmin(f1),max(f1)from minmaxtest;
280280

281+
-- DISTINCT doesn't do anything useful here, but it shouldn't fail
282+
explain (costs off)
283+
select distinctmin(f1),max(f1)from minmaxtest;
284+
select distinctmin(f1),max(f1)from minmaxtest;
285+
281286
droptable minmaxtest cascade;
282287

283288
--

0 commit comments

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