Gsets: ROW expression semantic broken between 9.4 and 9.5

Поиск
Список
Период
Сортировка
От Jeevan Chalke
Тема Gsets: ROW expression semantic broken between 9.4 and 9.5
Дата
Msg-id CAM2+6=UhMCGM2Z76z6_-DL_1ZS1HRY0CUF6mc3AW2q002W_5tg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Gsets: ROW expression semantic broken between 9.4 and 9.5
Re: Gsets: ROW expression semantic broken between 9.4 and 9.5
Список 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

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Robert Haas
Дата:
Сообщение: Re: "A huge debt of gratitude" - Michael Stonebraker
Следующее
От: Fabien COELHO
Дата:
Сообщение: Re: pgbench stats per script & other stuff