You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@flink.apache.org by Tisya Bhatia via dev <de...@flink.apache.org> on 2026/07/14 17:17:30 UTC
[DISCUSS] FLIP-XXX: Add support for GROUP BY , GROUP BY ALL, and ORDER BY ALL
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://docs.google.com/document/d/16R83T86X1ATmmPe_QFnnUMdnpiOcvyMrUOOctmm59Gk/edit?usp=sharing
*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://github.com/apache/calcite/pull/5009>, CALCITE-7597
<https://github.com/apache/calcite/pull/5010>), targeted for Calcite
1.43.0; SELECT * support in both is in review (CALCITE-7647
<https://github.com/apache/calcite/pull/5089>). 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
Re: [DISCUSS] FLIP-XXX: Add support for GROUP BY , GROUP BY ALL, and ORDER BY ALL
Posted by Tisya Bhatia via dev <de...@flink.apache.org>.
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 <sn...@gmail.com> 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 <sn...@gmail.com>
> 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
> > <de...@flink.apache.org> 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 <tw...@apache.org>
> 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 <
> > > > dev@flink.apache.org>
> > > > > 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
>
Re: [DISCUSS] FLIP-XXX: Add support for GROUP BY , GROUP BY ALL, and ORDER BY ALL
Posted by Sergey Nuyanzin <sn...@gmail.com>.
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 <sn...@gmail.com> 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
> <de...@flink.apache.org> 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 <tw...@apache.org> 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 <
> > > dev@flink.apache.org>
> > > > 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
Re: [DISCUSS] FLIP-XXX: Add support for GROUP BY , GROUP BY ALL, and ORDER BY ALL
Posted by Sergey Nuyanzin <sn...@gmail.com>.
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
<de...@flink.apache.org> 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 <tw...@apache.org> 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 <
> > dev@flink.apache.org>
> > > 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
Re: [DISCUSS] FLIP-XXX: Add support for GROUP BY , GROUP BY ALL, and ORDER BY ALL
Posted by Tisya Bhatia via dev <de...@flink.apache.org>.
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 <tw...@apache.org> 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 <
> dev@flink.apache.org>
> > 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
> >>
> >
>
>
Re: [DISCUSS] FLIP-XXX: Add support for GROUP BY , GROUP BY ALL, and ORDER BY ALL
Posted by Timo Walther <tw...@apache.org>.
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 <de...@flink.apache.org>
> 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
>>
>
Re: [DISCUSS] FLIP-XXX: Add support for GROUP BY , GROUP BY ALL, and ORDER BY ALL
Posted by Jim Hughes via dev <de...@flink.apache.org>.
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 <de...@flink.apache.org>
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
>