Hi all, Sergey- I checked how DuckDB, Databricks, Snowflake handle this specific edge case- none of them document it. I'll file a Calcite fix for this, and I'll add the view/materialized-table regression tests you asked for so this class of bug gets caught automatically going forward.
Will fold this rule into the FLIP explicitly. Thanks both for digging in. Tisya On Fri, Jul 24, 2026 at 9:33 AM Sergey Nuyanzin <[email protected]> wrote: > 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 >
