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

Commit624046a

Browse files
committed
Postpone aggregate checks until after collation is assigned.
Previously, parseCheckAggregates was run beforeassign_query_collations, but this causes problems if any expressionhas already had a collation assigned by some transform function (e.g.transformCaseExpr) before parseCheckAggregates runs. The differingcollations would cause expressions not to be recognized as equal tothe ones in the GROUP BY clause, leading to spurious errors aboutunaggregated column references.The result was that CASE expr WHEN val ... would fail when "expr"contained a GROUPING() expression or matched one of the group byexpressions, and where collatable types were involved; whereas thesupposedly identical CASE WHEN expr = val ... would succeed.Backpatch all the way; this appears to have been wrong ever sincecollations were introduced.Per report from Guillaume Lelarge, analysis and patch by me.Discussion:https://postgr.es/m/CAECtzeVSO_US8C2Khgfv54ZMUOBR4sWq+6_bLrETnWExHT=rFg@mail.gmail.comDiscussion:https://postgr.es/m/87muo0k0c7.fsf@news-spur.riddles.org.uk
1 parent3814096 commit624046a

File tree

5 files changed

+75
-6
lines changed

5 files changed

+75
-6
lines changed

‎src/backend/parser/analyze.c

Lines changed: 12 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -418,11 +418,13 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
418418
qry->hasSubLinks=pstate->p_hasSubLinks;
419419
qry->hasWindowFuncs=pstate->p_hasWindowFuncs;
420420
qry->hasAggs=pstate->p_hasAggs;
421-
if (pstate->p_hasAggs)
422-
parseCheckAggregates(pstate,qry);
423421

424422
assign_query_collations(pstate,qry);
425423

424+
/* this must be done after collations, for reliable comparison of exprs */
425+
if (pstate->p_hasAggs)
426+
parseCheckAggregates(pstate,qry);
427+
426428
returnqry;
427429
}
428430

@@ -1255,8 +1257,6 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
12551257
qry->hasSubLinks=pstate->p_hasSubLinks;
12561258
qry->hasWindowFuncs=pstate->p_hasWindowFuncs;
12571259
qry->hasAggs=pstate->p_hasAggs;
1258-
if (pstate->p_hasAggs||qry->groupClause||qry->groupingSets||qry->havingQual)
1259-
parseCheckAggregates(pstate,qry);
12601260

12611261
foreach(l,stmt->lockingClause)
12621262
{
@@ -1266,6 +1266,10 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
12661266

12671267
assign_query_collations(pstate,qry);
12681268

1269+
/* this must be done after collations, for reliable comparison of exprs */
1270+
if (pstate->p_hasAggs||qry->groupClause||qry->groupingSets||qry->havingQual)
1271+
parseCheckAggregates(pstate,qry);
1272+
12691273
returnqry;
12701274
}
12711275

@@ -1715,8 +1719,6 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
17151719
qry->hasSubLinks=pstate->p_hasSubLinks;
17161720
qry->hasWindowFuncs=pstate->p_hasWindowFuncs;
17171721
qry->hasAggs=pstate->p_hasAggs;
1718-
if (pstate->p_hasAggs||qry->groupClause||qry->groupingSets||qry->havingQual)
1719-
parseCheckAggregates(pstate,qry);
17201722

17211723
foreach(l,lockingClause)
17221724
{
@@ -1726,6 +1728,10 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
17261728

17271729
assign_query_collations(pstate,qry);
17281730

1731+
/* this must be done after collations, for reliable comparison of exprs */
1732+
if (pstate->p_hasAggs||qry->groupClause||qry->groupingSets||qry->havingQual)
1733+
parseCheckAggregates(pstate,qry);
1734+
17291735
returnqry;
17301736
}
17311737

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

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2047,3 +2047,22 @@ SELECT balk(hundred) FROM tenk1;
20472047
(1 row)
20482048

20492049
ROLLBACK;
2050+
-- check collation-sensitive matching between grouping expressions
2051+
select v||'a', case v||'a' when 'aa' then 1 else 0 end, count(*)
2052+
from unnest(array['a','b']) u(v)
2053+
group by v||'a' order by 1;
2054+
?column? | case | count
2055+
----------+------+-------
2056+
aa | 1 | 1
2057+
ba | 0 | 1
2058+
(2 rows)
2059+
2060+
select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*)
2061+
from unnest(array['a','b']) u(v)
2062+
group by v||'a' order by 1;
2063+
?column? | case | count
2064+
----------+------+-------
2065+
aa | 1 | 1
2066+
ba | 0 | 1
2067+
(2 rows)
2068+

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

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -921,4 +921,29 @@ select sum(ten) from onek group by rollup(four::text), two order by 1;
921921
2500
922922
(6 rows)
923923

924+
-- check collation-sensitive matching between grouping expressions
925+
-- (similar to a check for aggregates, but there are additional code
926+
-- paths for GROUPING, so check again here)
927+
select v||'a', case grouping(v||'a') when 1 then 1 else 0 end, count(*)
928+
from unnest(array[1,1], array['a','b']) u(i,v)
929+
group by rollup(i, v||'a') order by 1,3;
930+
?column? | case | count
931+
----------+------+-------
932+
aa | 0 | 1
933+
ba | 0 | 1
934+
| 1 | 2
935+
| 1 | 2
936+
(4 rows)
937+
938+
select v||'a', case when grouping(v||'a') = 1 then 1 else 0 end, count(*)
939+
from unnest(array[1,1], array['a','b']) u(i,v)
940+
group by rollup(i, v||'a') order by 1,3;
941+
?column? | case | count
942+
----------+------+-------
943+
aa | 0 | 1
944+
ba | 0 | 1
945+
| 1 | 2
946+
| 1 | 2
947+
(4 rows)
948+
924949
-- end

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

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -900,3 +900,11 @@ EXPLAIN (COSTS OFF) SELECT balk(hundred) FROM tenk1;
900900
SELECT balk(hundred)FROM tenk1;
901901

902902
ROLLBACK;
903+
904+
-- check collation-sensitive matching between grouping expressions
905+
select v||'a', case v||'a' when'aa' then1 else0 end,count(*)
906+
from unnest(array['a','b']) u(v)
907+
group by v||'a'order by1;
908+
select v||'a', case when v||'a'='aa' then1 else0 end,count(*)
909+
from unnest(array['a','b']) u(v)
910+
group by v||'a'order by1;

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -255,4 +255,15 @@ select array(select row(v.a,s1.*) from (select two,four, count(*) from onek grou
255255
selectsum(ten)from onekgroup by two, rollup(four::text)order by1;
256256
selectsum(ten)from onekgroup by rollup(four::text), twoorder by1;
257257

258+
-- check collation-sensitive matching between grouping expressions
259+
-- (similar to a check for aggregates, but there are additional code
260+
-- paths for GROUPING, so check again here)
261+
262+
select v||'a', case grouping(v||'a') when1 then1 else0 end,count(*)
263+
from unnest(array[1,1], array['a','b']) u(i,v)
264+
group by rollup(i, v||'a')order by1,3;
265+
select v||'a', case when grouping(v||'a')=1 then1 else0 end,count(*)
266+
from unnest(array[1,1], array['a','b']) u(i,v)
267+
group by rollup(i, v||'a')order by1,3;
268+
258269
-- end

0 commit comments

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