Skip to content

Commit 409230a

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

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
@@ -442,11 +442,13 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
442442
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
443443
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
444444
qry->hasAggs = pstate->p_hasAggs;
445-
if (pstate->p_hasAggs)
446-
parseCheckAggregates(pstate, qry);
447445

448446
assign_query_collations(pstate, qry);
449447

448+
/* this must be done after collations, for reliable comparison of exprs */
449+
if (pstate->p_hasAggs)
450+
parseCheckAggregates(pstate, qry);
451+
450452
return qry;
451453
}
452454

@@ -1317,8 +1319,6 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
13171319
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
13181320
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
13191321
qry->hasAggs = pstate->p_hasAggs;
1320-
if (pstate->p_hasAggs || qry->groupClause || qry->groupingSets || qry->havingQual)
1321-
parseCheckAggregates(pstate, qry);
13221322

13231323
foreach(l, stmt->lockingClause)
13241324
{
@@ -1328,6 +1328,10 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
13281328

13291329
assign_query_collations(pstate, qry);
13301330

1331+
/* this must be done after collations, for reliable comparison of exprs */
1332+
if (pstate->p_hasAggs || qry->groupClause || qry->groupingSets || qry->havingQual)
1333+
parseCheckAggregates(pstate, qry);
1334+
13311335
return qry;
13321336
}
13331337

@@ -1789,8 +1793,6 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
17891793
qry->hasWindowFuncs = pstate->p_hasWindowFuncs;
17901794
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
17911795
qry->hasAggs = pstate->p_hasAggs;
1792-
if (pstate->p_hasAggs || qry->groupClause || qry->groupingSets || qry->havingQual)
1793-
parseCheckAggregates(pstate, qry);
17941796

17951797
foreach(l, lockingClause)
17961798
{
@@ -1800,6 +1802,10 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
18001802

18011803
assign_query_collations(pstate, qry);
18021804

1805+
/* this must be done after collations, for reliable comparison of exprs */
1806+
if (pstate->p_hasAggs || qry->groupClause || qry->groupingSets || qry->havingQual)
1807+
parseCheckAggregates(pstate, qry);
1808+
18031809
return qry;
18041810
}
18051811

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

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2081,3 +2081,22 @@ SELECT variance(unique1::int4), sum(unique1::int8) FROM tenk1;
20812081
(1 row)
20822082

20832083
ROLLBACK;
2084+
-- check collation-sensitive matching between grouping expressions
2085+
select v||'a', case v||'a' when 'aa' then 1 else 0 end, count(*)
2086+
from unnest(array['a','b']) u(v)
2087+
group by v||'a' order by 1;
2088+
?column? | case | count
2089+
----------+------+-------
2090+
aa | 1 | 1
2091+
ba | 0 | 1
2092+
(2 rows)
2093+
2094+
select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*)
2095+
from unnest(array['a','b']) u(v)
2096+
group by v||'a' order by 1;
2097+
?column? | case | count
2098+
----------+------+-------
2099+
aa | 1 | 1
2100+
ba | 0 | 1
2101+
(2 rows)
2102+

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

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1540,4 +1540,29 @@ explain (costs off)
15401540
-> Seq Scan on tenk1
15411541
(12 rows)
15421542

1543+
-- check collation-sensitive matching between grouping expressions
1544+
-- (similar to a check for aggregates, but there are additional code
1545+
-- paths for GROUPING, so check again here)
1546+
select v||'a', case grouping(v||'a') when 1 then 1 else 0 end, count(*)
1547+
from unnest(array[1,1], array['a','b']) u(i,v)
1548+
group by rollup(i, v||'a') order by 1,3;
1549+
?column? | case | count
1550+
----------+------+-------
1551+
aa | 0 | 1
1552+
ba | 0 | 1
1553+
| 1 | 2
1554+
| 1 | 2
1555+
(4 rows)
1556+
1557+
select v||'a', case when grouping(v||'a') = 1 then 1 else 0 end, count(*)
1558+
from unnest(array[1,1], array['a','b']) u(i,v)
1559+
group by rollup(i, v||'a') order by 1,3;
1560+
?column? | case | count
1561+
----------+------+-------
1562+
aa | 0 | 1
1563+
ba | 0 | 1
1564+
| 1 | 2
1565+
| 1 | 2
1566+
(4 rows)
1567+
15431568
-- end

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

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -918,3 +918,11 @@ EXPLAIN (COSTS OFF)
918918
SELECT variance(unique1::int4), sum(unique1::int8) FROM tenk1;
919919

920920
ROLLBACK;
921+
922+
-- check collation-sensitive matching between grouping expressions
923+
select v||'a', case v||'a' when 'aa' then 1 else 0 end, count(*)
924+
from unnest(array['a','b']) u(v)
925+
group by v||'a' order by 1;
926+
select v||'a', case when v||'a' = 'aa' then 1 else 0 end, count(*)
927+
from unnest(array['a','b']) u(v)
928+
group by v||'a' order by 1;

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -415,4 +415,15 @@ explain (costs off)
415415
count(*)
416416
from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two);
417417

418+
-- check collation-sensitive matching between grouping expressions
419+
-- (similar to a check for aggregates, but there are additional code
420+
-- paths for GROUPING, so check again here)
421+
422+
select v||'a', case grouping(v||'a') when 1 then 1 else 0 end, count(*)
423+
from unnest(array[1,1], array['a','b']) u(i,v)
424+
group by rollup(i, v||'a') order by 1,3;
425+
select v||'a', case when grouping(v||'a') = 1 then 1 else 0 end, count(*)
426+
from unnest(array[1,1], array['a','b']) u(i,v)
427+
group by rollup(i, v||'a') order by 1,3;
428+
418429
-- end

0 commit comments

Comments
 (0)