Gsets: ROW expression semantic broken between 9.4 and 9.5

From: Jeevan Chalke <jeevan(dot)chalke(at)enterprisedb(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, Postgres-Bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Gsets: ROW expression semantic broken between 9.4 and 9.5
Date: 2015-07-22 18:23:26
Message-ID: CAM2+6=UhMCGM2Z76z6_-DL_1ZS1HRY0CUF6mc3AW2q002W_5tg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

Hi

It looks like we have broken the ROW expression without explicit
ROW keyword in GROUP BY.
I mean, after Grouping sets merge, if we have (c1, c2) in group by,
we are treating it as ROW expression for grouping, but at the same
time we are allowing individual column in the target list.
However this was not true with PG9.4 where we error out saying
"column "c1" must appear in the GROUP BY clause..".

But if I use explicit ROW keyword, like ROW(c1, c2), then on PG95
it error outs for individual column reference in select list.

Example may clear more:

ON PG 9.5 (after grouping sets implementation)

postgres=# create view gstest1(a,b,v)
postgres-# as values (1,1,10),(1,1,11),(1,2,12),(1,2,13),(1,3,14),
postgres-# (2,3,15),
postgres-# (3,3,16),(3,4,17),
postgres-# (4,1,18),(4,1,19);
CREATE VIEW

postgres=#
postgres=# SELECT a, b, max(v) FROM gstest1 GROUP BY (a, b)
ORDER BY 1, 2, 3 DESC;
a | b | max
---+---+-----
1 | 1 | 11
1 | 2 | 13
1 | 3 | 14
2 | 3 | 15
3 | 3 | 16
3 | 4 | 17
4 | 1 | 19
(7 rows)

postgres=# SELECT a, b, max(v) FROM gstest1 GROUP BY ROW(a, b)
ORDER BY 1, 2, 3 DESC;
ERROR: column "gstest1.a" must appear in the GROUP BY clause or be used in
an aggregate function
LINE 1: SELECT a, b, max(v) FROM gstest1 GROUP BY ROW(a, b) ORDER BY...
^

In above example, you see that when we have only (a, b), it is working fine.
But when we have ROW(a, b), it is throwing an error.
On PG 9.4 both cases are failing. Here it is:

postgres=# SELECT a, b, max(v) FROM gstest1 GROUP BY (a, b)
ORDER BY 1, 2, 3 DESC;
ERROR: column "gstest1.a" must appear in the GROUP BY clause or be used in
an aggregate function
LINE 1: SELECT a, b, max(v) FROM gstest1 GROUP BY (a, b) ORDER BY 1,...
^
postgres=# SELECT a, b, max(v) FROM gstest1 GROUP BY ROW(a, b)
ORDER BY 1, 2, 3 DESC;
ERROR: column "gstest1.a" must appear in the GROUP BY clause or be used in
an aggregate function
LINE 1: SELECT a, b, max(v) FROM gstest1 GROUP BY ROW(a, b) ORDER BY...
^

Do we broke ROW expression semantics in grouping sets implementation?

Any idea why is this happening?

Thanks
--
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andres Freund 2015-07-22 18:42:42 Re: [HACKERS] Gsets: ROW expression semantic broken between 9.4 and 9.5
Previous Message b.yordanov2 2015-07-22 14:39:33 BUG #13514: PostgreSQL backend process crashes on jsonb_object_agg()

Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2015-07-22 18:39:22 Re: pgbench stats per script & other stuff
Previous Message Robert Haas 2015-07-22 18:14:06 Re: "A huge debt of gratitude" - Michael Stonebraker