May be we don't need to debug it in this thread, however it seems we probably should double check/fix implementation in Calcite and in Flink would be great to have tests with views/materialized tables where such kind of issues pop up automatically
On Fri, Jul 24, 2026 at 4:24 PM Sergey Nuyanzin <[email protected]> wrote: > > 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 -- Best regards, Sergey
