Support GROUP BY ALL

[proddata]

Problem Statement

When interactively writing queries in CrateDB one needs to specify the list of columns in two locations for aggregations, which can lead to broken queries and pontential bugs.

SELECT
  col1,
  agg(col2),
  col3
FROM t01;
GROUP BY
  col1, col3;

Possible Solutions

To prevent broken queries and pontential bugs by keeping the SELECT granularity aligned to the GROUP BY granularity one could either add support for GROUP BY ALL similiar to Snowflake, DuckDB, ClickHouse and others

SELECT
  col1,
  agg(col2),
  col3
FROM t01;
GROUP BY ALL;

ALL is already a reserved keyword - this wouldn't be a breaking change.

Considered Alternatives

No response

[mfussenegger]

Referencing the columns by ordinal is an alternative - although a bit less convenient:

SELECT
  col1,
  agg(col2),
  col3
FROM t01;
GROUP BY
  1, 3;

[DHRUV6029]

Hi, Can i Work on this feature

[matriv]

@DHRUV6029 Thx for volunteering to work on this!
Please let us know of any help you might need.

[DHRUV6029]

Hello , Thanks for assigning me the task.
I had few queries @mfussenegger mentioned do we need ordinal number refrence or, we just want to support Group by ALL
clause

Thanks

[proddata]

@DHRUV6029 Just GROUP BY ALL. Ordinal number references are already implemented.