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

Commitd3237e0

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 parent6b711cf commitd3237e0

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
@@ -1973,12 +1973,12 @@ add_child_rel_equivalences(PlannerInfo *root,
19731973
/*
19741974
* mutate_eclass_expressions
19751975
* Apply an expression tree mutator to all expressions stored in
1976-
* equivalence classes.
1976+
* equivalence classes (but ignore child exprs unless include_child_exprs).
19771977
*
19781978
* This is a bit of a hack ... it's currently needed only by planagg.c,
19791979
* which needs to do a global search-and-replace of MIN/MAX Aggrefs
19801980
* after eclasses are already set up. Without changing the eclasses too,
1981-
* subsequent matching of ORDER BY clauses would fail.
1981+
* subsequent matching of ORDER BYand DISTINCTclauses would fail.
19821982
*
19831983
* Note that we assume the mutation won't affect relation membership or any
19841984
* other properties we keep track of (which is a bit bogus, but by the time
@@ -1988,7 +1988,8 @@ add_child_rel_equivalences(PlannerInfo *root,
19881988
void
19891989
mutate_eclass_expressions(PlannerInfo*root,
19901990
Node*(*mutator) (),
1991-
void*context)
1991+
void*context,
1992+
boolinclude_child_exprs)
19921993
{
19931994
ListCell*lc1;
19941995

@@ -2001,6 +2002,9 @@ mutate_eclass_expressions(PlannerInfo *root,
20012002
{
20022003
EquivalenceMember*cur_em= (EquivalenceMember*)lfirst(lc2);
20032004

2005+
if (cur_em->em_is_child&& !include_child_exprs)
2006+
continue;/* ignore children unless requested */
2007+
20042008
cur_em->em_expr= (Expr*)
20052009
mutator((Node*)cur_em->em_expr,context);
20062010
}

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

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

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

270274
/*
271275
* 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
@@ -124,7 +124,8 @@ extern void add_child_rel_equivalences(PlannerInfo *root,
124124
RelOptInfo*child_rel);
125125
externvoidmutate_eclass_expressions(PlannerInfo*root,
126126
Node*(*mutator) (),
127-
void*context);
127+
void*context,
128+
boolinclude_child_exprs);
128129
externList*generate_implied_equalities_for_indexcol(PlannerInfo*root,
129130
IndexOptInfo*index,
130131
intindexcol,

‎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: minmaxtest.f1
753+
-> Index Only Scan using minmaxtesti on minmaxtest
754+
Index Cond: (f1 IS NOT NULL)
755+
-> Index Only Scan using minmaxtest1i on minmaxtest1
756+
Index Cond: (f1 IS NOT NULL)
757+
-> Index Only Scan Backward using minmaxtest2i on minmaxtest2
758+
Index Cond: (f1 IS NOT NULL)
759+
-> Index Only Scan using minmaxtest3i on minmaxtest3
760+
Index Cond: (f1 IS NOT NULL)
761+
InitPlan 2 (returns $1)
762+
-> Limit
763+
-> Merge Append
764+
Sort Key: minmaxtest_1.f1
765+
-> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1
766+
Index Cond: (f1 IS NOT NULL)
767+
-> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1
768+
Index Cond: (f1 IS NOT NULL)
769+
-> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest2_1
770+
Index Cond: (f1 IS NOT NULL)
771+
-> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1
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
-- check for correct detection of nested-aggregate errors

0 commit comments

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