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

Commit6b701ea

Browse files
committed
Avoid pushing quals down into sub-queries that have grouping sets.
The trouble with doing this is that an apparently-constant subqueryoutput column isn't really constant if it is a grouping column thatappears in only some of the grouping sets. A qual using such acolumn would be subject to incorrect const-folding after push-down,as seen in bug #16585 from Paul Sivash.To fix, just disable qual pushdown altogether if the sub-query hasnonempty groupingSets. While we could imagine far less restrictivesolutions, there is not much point in working harder right now,because subquery_planner() won't move HAVING clauses to WHERE withinsuch a subquery. If the qual stays in HAVING it's not going to bea lot more useful than if we'd kept it at the outer level.Having said that, this restriction could be removed if we used aparsetree representation that distinguished such outputs from actualconstants, which is something I hope to do in future. Hence, makethe patch a minimal addition rather than integrating it more tightly(e.g. by renumbering the existing items in subquery_is_pushdown_safe'scomment).Back-patch to 9.5 where grouping sets were introduced.Discussion:https://postgr.es/m/16585-9d8c340d23ade8c1@postgresql.org
1 parent2060f00 commit6b701ea

File tree

3 files changed

+63
-0
lines changed

3 files changed

+63
-0
lines changed

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2969,6 +2969,17 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
29692969
* volatile qual could succeed for some SRF output rows and fail for others,
29702970
* a behavior that cannot occur if it's evaluated before SRF expansion.
29712971
*
2972+
* 6. If the subquery has nonempty grouping sets, we cannot push down any
2973+
* quals. The concern here is that a qual referencing a "constant" grouping
2974+
* column could get constant-folded, which would be improper because the value
2975+
* is potentially nullable by grouping-set expansion. This restriction could
2976+
* be removed if we had a parsetree representation that shows that such
2977+
* grouping columns are not really constant. (There are other ideas that
2978+
* could be used to relax this restriction, but that's the approach most
2979+
* likely to get taken in the future. Note that there's not much to be gained
2980+
* so long as subquery_planner can't move HAVING clauses to WHERE within such
2981+
* a subquery.)
2982+
*
29722983
* In addition, we make several checks on the subquery's output columns to see
29732984
* if it is safe to reference them in pushed-down quals. If output column k
29742985
* is found to be unsafe to reference, we set safetyInfo->unsafeColumns[k]
@@ -3013,6 +3024,10 @@ subquery_is_pushdown_safe(Query *subquery, Query *topquery,
30133024
if (subquery->limitOffset!=NULL||subquery->limitCount!=NULL)
30143025
return false;
30153026

3027+
/* Check point 6 */
3028+
if (subquery->groupClause&&subquery->groupingSets)
3029+
return false;
3030+
30163031
/* Check points 3, 4, and 5 */
30173032
if (subquery->distinctClause||
30183033
subquery->hasWindowFuncs||

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

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -434,6 +434,38 @@ select x, not x as not_x, q2 from
434434
| | 4567890123456789
435435
(5 rows)
436436

437+
-- check qual push-down rules for a subquery with grouping sets
438+
explain (verbose, costs off)
439+
select * from (
440+
select 1 as x, q1, sum(q2)
441+
from int8_tbl i1
442+
group by grouping sets(1, 2)
443+
) ss
444+
where x = 1 and q1 = 123;
445+
QUERY PLAN
446+
--------------------------------------------
447+
Subquery Scan on ss
448+
Output: ss.x, ss.q1, ss.sum
449+
Filter: ((ss.x = 1) AND (ss.q1 = 123))
450+
-> GroupAggregate
451+
Output: (1), i1.q1, sum(i1.q2)
452+
Group Key: 1
453+
Sort Key: i1.q1
454+
Group Key: i1.q1
455+
-> Seq Scan on public.int8_tbl i1
456+
Output: 1, i1.q1, i1.q2
457+
(10 rows)
458+
459+
select * from (
460+
select 1 as x, q1, sum(q2)
461+
from int8_tbl i1
462+
group by grouping sets(1, 2)
463+
) ss
464+
where x = 1 and q1 = 123;
465+
x | q1 | sum
466+
---+----+-----
467+
(0 rows)
468+
437469
-- simple rescan tests
438470
select a, b, sum(v.x)
439471
from (values (1),(2)) v(x), gstest_data(v.x)

‎src/test/regress/sql/groupingsets.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -172,6 +172,22 @@ select x, not x as not_x, q2 from
172172
group by grouping sets(x, q2)
173173
order by x, q2;
174174

175+
-- check qual push-down rules for a subquery with grouping sets
176+
explain (verbose, costs off)
177+
select*from (
178+
select1as x, q1,sum(q2)
179+
from int8_tbl i1
180+
group by grouping sets(1,2)
181+
) ss
182+
where x=1and q1=123;
183+
184+
select*from (
185+
select1as x, q1,sum(q2)
186+
from int8_tbl i1
187+
group by grouping sets(1,2)
188+
) ss
189+
where x=1and q1=123;
190+
175191
-- simple rescan tests
176192

177193
select a, b,sum(v.x)

0 commit comments

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