Thank you for driving this Tisya I looked into implementation in Calcite and still have a question
Imagine there is a query like SELECT count(*) AS cnt, sum(abc) AS sm, 42 AS just_a_constant FROM ... GROUP BY ALL; in Calcite there is so called unparsed functionality (in tests could be seen as SqlValidatorFicture#rewritesTo) We use this for expanded queries in views and materialized tables. after testing the query above it looks like the query will be rewritten to SELECT count(*) AS cnt, sum(abc) AS sm, 42 AS just_a_constant FROM ... GROUP BY 42; so it seems invalid query now or did I miss something here? On Thu, Jul 23, 2026 at 7:26 PM Tisya Bhatia via dev <[email protected]> wrote: > > Hi Jim, Timo, thanks for the reviews. > > Jim- > Agreed, $rowtime is Confluent Cloud specific. I'll reword the FLIP to the > general case: virtual metadata columns whose inclusion in SELECT * follows > table.column-expansion-strategy. > A release isn't strictly required; we could vendor the Calcite changes but > we'd have to carry a patched parser/validator for something already merged > upstream. So we'll pick up CALCITE-7594/7597 (and 7647) by bumping the > Calcite version. GROUP BY <ordinal> has no such dependency and can land now. > > Timo- > Agreed that positional grouping should be the default eventually. Grouping > by a constant is rare, and most engines already treat an integer as a > position. Since it's still a silent change for existing constant-grouping > queries, I'd propose a staged rollout: default-off in the release that > introduces it, flip to default-on the next, called out in the release > notes. Happy to flip sooner if others agree. > > Thanks, > Tisya > > On Thu, Jul 23, 2026 at 5:25 AM Timo Walther <[email protected]> wrote: > > > Hi Tisya, > > > > thanks for the FLIP and thank you for contributing to Apache Calcite to > > make this feature available. Overall I'm +1 on this. All major moderns > > SQL vendors seem to support GROUP BY ALL and ordinal positions. > > > > Snowflake, Databricks SQL, DuckDB, Google BigQuery, ClickHouse to name a > > few. > > > > Ordinal support is even broader incl. Postgres and MySQL, Oracle. > > > > I would suggest that we enable it by default going forward. It should be > > very uncommon to group by constants, what do others think? > > > > Cheers, > > Timo > > > > > > On 20.07.26 20:02, Jim Hughes via dev wrote: > > > Hi Tisya, > > > > > > Overall, the FLIP looks good to me. > > > > > > 1. As a small note, $rowtime may be specific to Confluent Cloud and/or > > > tables using the Kafka connector. > > > > > > 2. For the Calcite dependencies, will you be able to copy the work > > > for CALCITE-7594, 7597, and 7647 into the Flink codebase or will a > > release > > > be required? (The FLIP seems to suggest the latter.) > > > > > > Looks like you've got a good handle on many of the corner and edge cases. > > > > > > Cheers, > > > > > > Jim > > > > > > > > > On Tue, Jul 14, 2026 at 1:18 PM Tisya Bhatia via dev < > > [email protected]> > > > wrote: > > > > > >> Hi everyone, > > >> > > >> I'd like to start a discussion on a FLIP that adds three SQL ergonomics > > >> features to Flink SQL: GROUP BY <ordinal>, GROUP BY ALL, and ORDER BY > > ALL. > > >> > > >> FLIP: > > >> > > >> > > https://urldefense.com/v3/__https://docs.google.com/document/d/16R83T86X1ATmmPe_QFnnUMdnpiOcvyMrUOOctmm59Gk/edit?usp=sharing__;!!Ayb5sqE7!qoWB3uMUCCZwcI5ZhS1Rje-44Bqi6oqmZH3bmz_AzEUololczRs1hWZIUWPCXIxWbRjtGAnbuYQYZnljHwg$ > > >> > > >> *Motivation* > > >> Users migrating to Flink SQL from Snowflake, DuckDB, BigQuery, and > > others > > >> expect positional grouping and ALL grouping/sorting. Their absence > > forces > > >> query rewrites during migration and raises time-to-first-query. These > > are > > >> validation-time conveniences that expand into standard > > grouping/sorting, so > > >> the planning and execution pipeline is unchanged across streaming and > > >> batch. > > >> > > >> *Summary* > > >> - GROUP BY <ordinal>: "GROUP BY 1, 2" groups by the 1st and 2nd SELECT > > >> expressions. This redefines today's "GROUP BY <constant>" behavior, so > > it > > >> is a breaking change - gated behind a config option, default off. It > > reuses > > >> Calcite's existing isGroupByOrdinal() resolution, no custom code. > > >> - GROUP BY ALL: groups by every SELECT expression that is not an > > aggregate > > >> or window function. > > >> - ORDER BY ALL: sorts by every projected column, left to right, with an > > >> optional trailing ASC/DESC and NULLS FIRST/NULLS LAST applied to all > > keys. > > >> > > >> All three features are gated behind Boolean options in > > TableConfigOptions, > > >> default false, for a staged rollout. With the options off, behavior is > > >> unchanged. > > >> > > >> GROUP BY ALL and ORDER BY ALL are net-new syntax and depend on Calcite > > >> changes already merged upstream (CALCITE-7594 > > >> < > > >> > > https://urldefense.com/v3/__https://github.com/apache/calcite/pull/5009__;!!Ayb5sqE7!qoWB3uMUCCZwcI5ZhS1Rje-44Bqi6oqmZH3bmz_AzEUololczRs1hWZIUWPCXIxWbRjtGAnbuYQYPqYIsN8$ > > >>> , CALCITE-7597 > > >> < > > >> > > https://urldefense.com/v3/__https://github.com/apache/calcite/pull/5010__;!!Ayb5sqE7!qoWB3uMUCCZwcI5ZhS1Rje-44Bqi6oqmZH3bmz_AzEUololczRs1hWZIUWPCXIxWbRjtGAnbuYQYGoDhKMo$ > > >>> ), targeted for Calcite > > >> 1.43.0; SELECT * support in both is in review (CALCITE-7647 > > >> < > > >> > > https://urldefense.com/v3/__https://github.com/apache/calcite/pull/5089__;!!Ayb5sqE7!qoWB3uMUCCZwcI5ZhS1Rje-44Bqi6oqmZH3bmz_AzEUololczRs1hWZIUWPCXIxWbRjtGAnbuYQY5CzySdI$ > > >>> ). Because of this, the GROUP > > >> BY ALL / ORDER BY ALL parts of this FLIP once it upgrades its Calcite > > >> dependency to a release that includes these changes. The FLIP itself > > can be > > >> discussed and accepted now; merging / releasing those two features is > > gated > > >> by that Calcite upgrade. GROUP BY <ordinal> has no such dependency - it > > >> reuses Calcite functionality that already exists. > > >> > > >> Thanks, > > >> Tisya Bhatia > > >> > > > > > > > -- Best regards, Sergey
