Thread
Commits
-
Postpone aggregate checks until after collation is assigned.
- d16d45387095 12.0 landed
- 174fab993414 9.4.21 landed
- 91448e7dcc1f 9.5.16 landed
- 624046abe23f 9.6.12 landed
- 409230a721cf 10.7 landed
- e74d8c5085aa 11.2 landed
-
Weird behaviour of ROLLUP/GROUPING
Guillaume Lelarge <guillaume@lelarge.info> — 2019-01-16T12:51:27Z
Hi, One of my customers found something quite weird on his 9.6 cluster. Here is a quick demo showing the issue: -- quick demo table CREATE TABLE t1 (a integer, b timestamp, c integer); -- a working query SELECT CASE grouping(a) WHEN 1 THEN 'some text' ELSE a::text END AS new_a, CASE WHEN grouping(to_char(b, 'MMYYYY')) = 1 THEN 'some date' ELSE to_char(b, 'MMYYYY') END AS new_b, sum(c) FROM t1 GROUP BY ROLLUP(a, to_char(b,'MMYYYY')); -- the non-working query SELECT CASE grouping(a) WHEN 1 THEN 'some text' ELSE a::text END AS new_a, CASE grouping(to_char(b, 'MMYYYY')) WHEN 1 THEN 'some date' ELSE to_char(b, 'MMYYYY') END AS new_b, sum(c) FROM t1 GROUP BY ROLLUP(a, to_char(b,'MMYYYY')); ERROR: arguments to GROUPING must be grouping expressions of the associated query level LINE 3: CASE grouping(to_char(b, 'MMYYYY')) WHEN 1 THEN 'some date' ... ^ AFAICT, both queries should behave the same, though their actual behaviours are quite opposite. Working fine for the first, erroring out on the second. Does anyone has any idea what's going on here? BTW, it shows the same issue on 11.1 (actually HEAD on REL_11_STABLE). Thanks. Regards. -- Guillaume. -
Re: Weird behaviour of ROLLUP/GROUPING
Pavel Stehule <pavel.stehule@gmail.com> — 2019-01-16T13:15:01Z
st 16. 1. 2019 v 13:51 odesílatel Guillaume Lelarge <guillaume@lelarge.info> napsal: > Hi, > > One of my customers found something quite weird on his 9.6 cluster. Here > is a quick demo showing the issue: > > -- quick demo table > CREATE TABLE t1 (a integer, b timestamp, c integer); > > -- a working query > SELECT > CASE grouping(a) WHEN 1 THEN 'some text' ELSE a::text END AS new_a, > CASE WHEN grouping(to_char(b, 'MMYYYY')) = 1 > THEN 'some date' > ELSE to_char(b, 'MMYYYY') END AS new_b, > sum(c) > FROM t1 > GROUP BY ROLLUP(a, to_char(b,'MMYYYY')); > > -- the non-working query > SELECT > CASE grouping(a) WHEN 1 THEN 'some text' ELSE a::text END AS new_a, > CASE grouping(to_char(b, 'MMYYYY')) > WHEN 1 THEN 'some date' > ELSE to_char(b, 'MMYYYY') END AS new_b, > sum(c) > FROM t1 > GROUP BY ROLLUP(a, to_char(b,'MMYYYY')); > ERROR: arguments to GROUPING must be grouping expressions of the > associated query level > LINE 3: CASE grouping(to_char(b, 'MMYYYY')) WHEN 1 THEN 'some date' ... > ^ > > AFAICT, both queries should behave the same, though their actual > behaviours are quite opposite. Working fine for the first, erroring out on > the second. > > Does anyone has any idea what's going on here? > > BTW, it shows the same issue on 11.1 (actually HEAD on REL_11_STABLE). > looks like PostgreSQL bug - it cannot to work with subqueries correctly Pavel > > Thanks. > > Regards. > > > -- > Guillaume. >
-
Re: Weird behaviour of ROLLUP/GROUPING
Andrew Gierth <andrew@tao11.riddles.org.uk> — 2019-01-16T15:55:21Z
>>>>> "Guillaume" == Guillaume Lelarge <guillaume@lelarge.info> writes: Guillaume> CASE grouping(to_char(b, 'MMYYYY')) Guillaume> ERROR: arguments to GROUPING must be grouping expressions of the Guillaume> associated query level Guillaume> LINE 3: CASE grouping(to_char(b, 'MMYYYY')) WHEN 1 THEN 'some date' ... Guillaume> AFAICT, both queries should behave the same, though their Guillaume> actual behaviours are quite opposite. Working fine for the Guillaume> first, erroring out on the second. Guillaume> Does anyone has any idea what's going on here? Not yet. But I will find out, since it seems to be a bug. -- Andrew (irc:RhodiumToad)
-
Re: Weird behaviour of ROLLUP/GROUPING
Andrew Gierth <andrew@tao11.riddles.org.uk> — 2019-01-16T16:40:49Z
>>>>> "Andrew" == Andrew Gierth <andrew@tao11.riddles.org.uk> writes: >>>>> "Guillaume" == Guillaume Lelarge <guillaume@lelarge.info> writes: Guillaume> CASE grouping(to_char(b, 'MMYYYY')) Guillaume> ERROR: arguments to GROUPING must be grouping expressions of the Guillaume> associated query level Guillaume> LINE 3: CASE grouping(to_char(b, 'MMYYYY')) WHEN 1 THEN 'some date' ... Guillaume> AFAICT, both queries should behave the same, though their Guillaume> actual behaviours are quite opposite. Working fine for the Guillaume> first, erroring out on the second. Guillaume> Does anyone has any idea what's going on here? Andrew> Not yet. But I will find out, since it seems to be a bug. It is definitely a bug, to do with assignment of collations. It specifically happens when you use GROUPING which contains any subexpression of a collatable type, inside a CASE expr WHEN clause, since that assigns collations to the expression much earlier in parsing than the rest of the query, so the code that validates GROUPING ends up trying to compare an expression which has had collations assigned to it to one which has not, and so it thinks they differ. I will see about fixing this, somehow. -- Andrew (irc:RhodiumToad)
-
Re: Weird behaviour of ROLLUP/GROUPING
Guillaume Lelarge <guillaume@lelarge.info> — 2019-01-16T17:05:39Z
Le mer. 16 janv. 2019 à 17:40, Andrew Gierth <andrew@tao11.riddles.org.uk> a écrit : > >>>>> "Andrew" == Andrew Gierth <andrew@tao11.riddles.org.uk> writes: > >>>>> "Guillaume" == Guillaume Lelarge <guillaume@lelarge.info> writes: > > Guillaume> CASE grouping(to_char(b, 'MMYYYY')) > > Guillaume> ERROR: arguments to GROUPING must be grouping expressions of > the > Guillaume> associated query level > Guillaume> LINE 3: CASE grouping(to_char(b, 'MMYYYY')) WHEN 1 THEN 'some > date' ... > > Guillaume> AFAICT, both queries should behave the same, though their > Guillaume> actual behaviours are quite opposite. Working fine for the > Guillaume> first, erroring out on the second. > > Guillaume> Does anyone has any idea what's going on here? > > Andrew> Not yet. But I will find out, since it seems to be a bug. > > It is definitely a bug, to do with assignment of collations. It > specifically happens when you use GROUPING which contains any > subexpression of a collatable type, inside a CASE expr WHEN clause, > since that assigns collations to the expression much earlier in parsing > than the rest of the query, so the code that validates GROUPING ends up > trying to compare an expression which has had collations assigned to it > to one which has not, and so it thinks they differ. > > I will see about fixing this, somehow. > > Thanks a lot. -- Guillaume.
-
Re: Weird behaviour of ROLLUP/GROUPING
Andrew Gierth <andrew@tao11.riddles.org.uk> — 2019-01-17T07:27:37Z
>>>>> "Guillaume" == Guillaume Lelarge <guillaume@lelarge.info> writes: >> I will see about fixing this, somehow. Guillaume> Thanks a lot. I've committed a fix (to all supported branches, since this bug actually precedes the addition of GROUPING SETS and can be triggered with a simple GROUP BY if you try hard enough). The regression test says it works now, but it'd be good if you could try it again on REL_11_STABLE (at commit e74d8c5085 or later) to check that it fixes your case. -- Andrew (irc:RhodiumToad)
-
Re: Weird behaviour of ROLLUP/GROUPING
Guillaume Lelarge <guillaume@lelarge.info> — 2019-01-17T08:23:29Z
Le jeu. 17 janv. 2019 à 08:27, Andrew Gierth <andrew@tao11.riddles.org.uk> a écrit : > >>>>> "Guillaume" == Guillaume Lelarge <guillaume@lelarge.info> writes: > > >> I will see about fixing this, somehow. > > Guillaume> Thanks a lot. > > I've committed a fix (to all supported branches, since this bug actually > precedes the addition of GROUPING SETS and can be triggered with a > simple GROUP BY if you try hard enough). The regression test says it > works now, but it'd be good if you could try it again on REL_11_STABLE > (at commit e74d8c5085 or later) to check that it fixes your case. > > I checked on REL9_6_STABLE and REL_11_STABLE, and it works great. Thank a lot for the quick fix! -- Guillaume.