You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by ma...@apache.org on 2025/01/02 10:22:03 UTC
(cloudberry) 09/15: [ORCA] Enable CUBE result grouping set (#14329)
This is an automated email from the ASF dual-hosted git repository.
maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit cc6a521ae8d150ea2613514f54942c55f51f67ae
Author: David Kimura <dk...@vmware.com>
AuthorDate: Mon Oct 31 09:52:20 2022 -0700
[ORCA] Enable CUBE result grouping set (#14329)
This builds on the work of commit df3ce784285 to implement CUBE. Following now
works in ORCA:
EXPLAIN (COSTS OFF) SELECT i FROM generate_series(1, 3) i GROUP BY CUBE(i, i);
Note: Current optimization time for CUBE is slow. Seems ORCA is generating many
alternatives. Need to look into if this can be reduced.
Update tsrf answer file. Issue is that regress is not smart about ORDER BY.
Utility atmsort is only used if there is an ORDER BY clause, however if there
are duplicate values in the ORDER BY column with different whole row values
then we can incorrectly flag a result as bad. Workaround is to specify enough
rows in the ORDER BY that we have a guaranteed ordering.
---
src/backend/gpopt/translate/CTranslatorUtils.cpp | 50 +++++-
src/include/gpopt/translate/CTranslatorUtils.h | 5 +
src/test/regress/expected/gporca_optimizer.out | 45 +++--
.../regress/expected/groupingsets_optimizer.out | 191 ++++++++++++++-------
.../orca_groupingsets_fallbacks_optimizer.out | 2 -
src/test/regress/expected/tsrf.out | 36 ++--
src/test/regress/expected/tsrf_optimizer.out | 36 ++--
src/test/regress/sql/tsrf.sql | 8 +-
8 files changed, 252 insertions(+), 121 deletions(-)
diff --git a/src/backend/gpopt/translate/CTranslatorUtils.cpp b/src/backend/gpopt/translate/CTranslatorUtils.cpp
index c5cac56e35..e5b8b2cf31 100644
--- a/src/backend/gpopt/translate/CTranslatorUtils.cpp
+++ b/src/backend/gpopt/translate/CTranslatorUtils.cpp
@@ -981,8 +981,9 @@ CTranslatorUtils::GetColumnAttnosForGroupBy(
}
case GROUPING_SET_CUBE:
{
- GPOS_RAISE(gpdxl::ExmaDXL, gpdxl::ExmiQuery2DXLUnsupportedFeature,
- GPOS_WSZ_LIT("Cube"));
+ col_attnos_arr = CreateGroupingSetsForCube(
+ mp, grouping_set, num_cols, group_cols, group_col_pos);
+ break;
}
case GROUPING_SET_SETS:
{
@@ -1086,6 +1087,51 @@ CTranslatorUtils::CreateGroupingSetsForRollup(CMemoryPool *mp,
return col_attnos_arr;
}
+//---------------------------------------------------------------------------
+// @function:
+// CTranslatorUtils::CreateGroupingSetsForCube
+//
+// @doc:
+// Construct a dynamic array of sets of column attnos for a cube
+//
+//---------------------------------------------------------------------------
+CBitSetArray *
+CTranslatorUtils::CreateGroupingSetsForCube(CMemoryPool *mp,
+ const GroupingSet *grouping_set,
+ ULONG num_cols, CBitSet *group_cols,
+ UlongToUlongMap *group_col_pos)
+{
+ GPOS_ASSERT(nullptr != grouping_set);
+ GPOS_ASSERT(grouping_set->kind == GROUPING_SET_CUBE);
+ CBitSetArray *col_attnos_arr = GPOS_NEW(mp) CBitSetArray(mp);
+
+ // add an empty set
+ col_attnos_arr->Append(GPOS_NEW(mp) CBitSet(mp));
+
+ ListCell *lc = nullptr;
+ ForEach(lc, grouping_set->content)
+ {
+ ULONG current_results_size = col_attnos_arr->Size();
+
+ for (ULONG ul = 0; ul < current_results_size; ul++)
+ {
+ CBitSet *current_result =
+ GPOS_NEW(mp) CBitSet(mp, *(*col_attnos_arr)[ul]);
+
+ GroupingSet *gs_current = (GroupingSet *) lfirst(lc);
+ GPOS_ASSERT(gs_current->kind == GROUPING_SET_SIMPLE);
+
+ CBitSet *bset = CreateAttnoSetForGroupingSet(
+ mp, gs_current->content, num_cols, group_col_pos, group_cols,
+ false /* use_group_clause */);
+ current_result->Union(bset);
+ bset->Release();
+ col_attnos_arr->Append(GPOS_NEW(mp) CBitSet(mp, *current_result));
+ }
+ }
+ return col_attnos_arr;
+}
+
//---------------------------------------------------------------------------
// @function:
// CTranslatorUtils::CreateAttnoSetForGroupingSet
diff --git a/src/include/gpopt/translate/CTranslatorUtils.h b/src/include/gpopt/translate/CTranslatorUtils.h
index a8088737aa..8c54788702 100644
--- a/src/include/gpopt/translate/CTranslatorUtils.h
+++ b/src/include/gpopt/translate/CTranslatorUtils.h
@@ -109,6 +109,11 @@ private:
CMemoryPool *mp, const GroupingSet *grouping_set, ULONG num_cols,
CBitSet *group_cols, UlongToUlongMap *group_col_pos);
+ // create a set of grouping sets for a cube
+ static CBitSetArray *CreateGroupingSetsForCube(
+ CMemoryPool *mp, const GroupingSet *grouping_set, ULONG num_cols,
+ CBitSet *group_cols, UlongToUlongMap *group_col_pos);
+
// create a set of grouping sets for a grouping sets subclause
static CBitSetArray *CreateGroupingSetsForSets(
CMemoryPool *mp, const GroupingSet *grouping_set_node, ULONG num_cols,
diff --git a/src/test/regress/expected/gporca_optimizer.out b/src/test/regress/expected/gporca_optimizer.out
index 65b2858493..974e7f831d 100644
--- a/src/test/regress/expected/gporca_optimizer.out
+++ b/src/test/regress/expected/gporca_optimizer.out
@@ -10775,25 +10775,32 @@ set log_statement='none';
set log_min_duration_statement=-1;
set client_min_messages='log';
explain select count(*) from foo group by cube(a,b);
-LOG: 2020-10-19 17:17:37:192241 PDT,THD000,NOTICE,"Feature not supported: Cube",
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Cube
- QUERY PLAN
-------------------------------------------------------------------------------------------------------
- Gather Motion 3:1 (slice1; segments: 3) (cost=1319.44..1496.29 rows=10611 width=16)
- -> Finalize HashAggregate (cost=1319.44..1354.81 rows=3537 width=16)
- Group Key: a, b, (GROUPINGSET_ID())
- -> Redistribute Motion 3:3 (slice2; segments: 3) (cost=0.00..1213.33 rows=10611 width=16)
- Hash Key: a, b, (GROUPINGSET_ID())
- -> Partial MixedAggregate (cost=0.00..1001.11 rows=10611 width=16)
- Hash Key: a, b
- Hash Key: a
- Hash Key: b
- Group Key: ()
- Planned Partitions: 4
- -> Seq Scan on foo (cost=0.00..321.00 rows=28700 width=8)
- Optimizer: Postgres query optimizer
-(15 rows)
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Sequence (cost=0.00..2155.00 rows=1 width=8)
+ -> Shared Scan (share slice:id 0:0) (cost=0.00..431.00 rows=1 width=1)
+ -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=8)
+ -> Seq Scan on foo (cost=0.00..431.00 rows=1 width=8)
+ -> Append (cost=0.00..1724.00 rows=1 width=8)
+ -> GroupAggregate (cost=0.00..431.00 rows=1 width=8)
+ Group Key: share0_ref2.a, share0_ref2.b
+ -> Sort (cost=0.00..431.00 rows=1 width=8)
+ Sort Key: share0_ref2.a, share0_ref2.b
+ -> Shared Scan (share slice:id 0:0) (cost=0.00..431.00 rows=1 width=8)
+ -> GroupAggregate (cost=0.00..431.00 rows=1 width=8)
+ Group Key: share0_ref3.b
+ -> Sort (cost=0.00..431.00 rows=1 width=4)
+ Sort Key: share0_ref3.b
+ -> Shared Scan (share slice:id 0:0) (cost=0.00..431.00 rows=1 width=4)
+ -> GroupAggregate (cost=0.00..431.00 rows=1 width=8)
+ Group Key: share0_ref4.a
+ -> Sort (cost=0.00..431.00 rows=1 width=4)
+ Sort Key: share0_ref4.a
+ -> Shared Scan (share slice:id 0:0) (cost=0.00..431.00 rows=1 width=4)
+ -> Aggregate (cost=0.00..431.00 rows=1 width=8)
+ -> Shared Scan (share slice:id 0:0) (cost=0.00..431.00 rows=1 width=1)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(23 rows)
reset client_min_messages;
reset log_statement;
diff --git a/src/test/regress/expected/groupingsets_optimizer.out b/src/test/regress/expected/groupingsets_optimizer.out
index dbe0d1260c..6a1f249c21 100644
--- a/src/test/regress/expected/groupingsets_optimizer.out
+++ b/src/test/regress/expected/groupingsets_optimizer.out
@@ -1645,31 +1645,49 @@ select a, b, sum(c), sum(d), sum(e), sum(sum(c)) over (order by a,b) as rsum
explain (costs off)
select a, b, sum(c), sum(d), sum(e), sum(sum(c)) over (order by a,b) as rsum
from gstest2 group by cube (a,b) order by rsum, a, b;
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------
Sort
- Sort Key: (sum((sum(c))) OVER (?)), a, b
+ Sort Key: (sum((sum(share0_ref2.c))) OVER (?)), share0_ref2.a, share0_ref2.b
-> WindowAgg
- Order By: a, b
+ Order By: share0_ref2.a, share0_ref2.b
-> Gather Motion 3:1 (slice1; segments: 3)
- Merge Key: a, b
+ Merge Key: share0_ref2.a, share0_ref2.b
-> Sort
- Sort Key: a, b
- -> Finalize HashAggregate
- Group Key: a, b, (GROUPINGSET_ID())
- -> Redistribute Motion 3:3 (slice2; segments: 3)
- Hash Key: a, b, (GROUPINGSET_ID())
- -> Partial GroupAggregate
- Group Key: a, b
- Group Key: a
- Group Key: ()
- Sort Key: b
- Group Key: b
+ Sort Key: share0_ref2.a, share0_ref2.b
+ -> Sequence
+ -> Shared Scan (share slice:id 1:0)
+ -> Seq Scan on gstest2
+ -> Append
+ -> GroupAggregate
+ Group Key: share0_ref2.a, share0_ref2.b
-> Sort
- Sort Key: a, b
- -> Seq Scan on gstest2
- Optimizer: Postgres query optimizer
-(22 rows)
+ Sort Key: share0_ref2.a, share0_ref2.b
+ -> Shared Scan (share slice:id 1:0)
+ -> Finalize GroupAggregate
+ Group Key: share0_ref3.b
+ -> Sort
+ Sort Key: share0_ref3.b
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: share0_ref3.b
+ -> Partial GroupAggregate
+ Group Key: share0_ref3.b
+ -> Sort
+ Sort Key: share0_ref3.b
+ -> Shared Scan (share slice:id 2:0)
+ -> GroupAggregate
+ Group Key: share0_ref4.a
+ -> Sort
+ Sort Key: share0_ref4.a
+ -> Shared Scan (share slice:id 1:0)
+ -> Result
+ -> Redistribute Motion 1:3 (slice3)
+ -> Finalize Aggregate
+ -> Gather Motion 3:1 (slice4; segments: 3)
+ -> Partial Aggregate
+ -> Shared Scan (share slice:id 4:0)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(40 rows)
select a, b, sum(v.x)
from (values (1),(2)) v(x), gstest_data(v.x)
@@ -2334,30 +2352,59 @@ select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from
(select g%1000 as g1000, g%100 as g100, g%10 as g10, g
from generate_series(0,199999) g) s
group by cube (g1000,g100,g10);
- QUERY PLAN
----------------------------------------------------------------
- GroupAggregate
- Group Key: ((g.g % 1000)), ((g.g % 100)), ((g.g % 10))
- Group Key: ((g.g % 1000)), ((g.g % 100))
- Group Key: ((g.g % 1000))
- Group Key: ()
- Sort Key: ((g.g % 100)), ((g.g % 10))
- Group Key: ((g.g % 100)), ((g.g % 10))
- Group Key: ((g.g % 100))
- Sort Key: ((g.g % 10)), ((g.g % 1000))
- Group Key: ((g.g % 10)), ((g.g % 1000))
- Group Key: ((g.g % 10))
- -> Sort
- Sort Key: ((g.g % 1000)), ((g.g % 100)), ((g.g % 10))
- -> Function Scan on generate_series g
- Optimizer: Postgres query optimizer
-(15 rows)
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Sequence
+ -> Shared Scan (share slice:id 0:0)
+ -> Function Scan on generate_series
+ -> Append
+ -> HashAggregate
+ Group Key: share0_ref2.g1000, share0_ref2.g100, share0_ref2.g10
+ -> Shared Scan (share slice:id 0:0)
+ -> HashAggregate
+ Group Key: share0_ref3.g100, share0_ref3.g10
+ -> Shared Scan (share slice:id 0:0)
+ -> HashAggregate
+ Group Key: share0_ref4.g1000, share0_ref4.g10
+ -> Shared Scan (share slice:id 0:0)
+ -> HashAggregate
+ Group Key: share0_ref5.g10
+ -> Shared Scan (share slice:id 0:0)
+ -> HashAggregate
+ Group Key: share0_ref6.g1000, share0_ref6.g100
+ -> Shared Scan (share slice:id 0:0)
+ -> HashAggregate
+ Group Key: share0_ref7.g100
+ -> Shared Scan (share slice:id 0:0)
+ -> HashAggregate
+ Group Key: share0_ref8.g1000
+ -> Shared Scan (share slice:id 0:0)
+ -> Aggregate
+ -> Shared Scan (share slice:id 0:0)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(28 rows)
create table gs_group_1 as
select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from
(select g%1000 as g1000, g%100 as g100, g%10 as g10, g
from generate_series(0,199999) g) s
-group by cube (g1000,g100,g10) distributed by (g1000);
+group by cube (g1000,g100,g10);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'g1000' as the Greenplum Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
+set jit_above_cost to default;
+create table gs_group_2 as
+select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from
+ (select g/20 as g1000, g/200 as g100, g/2000 as g10, g
+ from generate_series(0,19999) g) s
+group by cube (g1000,g100,g10);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'g1000' as the Greenplum Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
+create table gs_group_3 as
+select g100, g10, array_agg(g) as a, count(*) as c, max(g::text) as m from
+ (select g/200 as g100, g/2000 as g10, g
+ from generate_series(0,19999) g) s
+group by grouping sets (g100,g10);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry.
-- Produce results with hash aggregation.
set enable_hashagg = true;
set enable_sort = false;
@@ -2366,31 +2413,59 @@ select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from
(select g%1000 as g1000, g%100 as g100, g%10 as g10, g
from generate_series(0,199999) g) s
group by cube (g1000,g100,g10);
- QUERY PLAN
----------------------------------------------------------------
- GroupAggregate
- Group Key: ((g.g % 1000)), ((g.g % 100)), ((g.g % 10))
- Group Key: ((g.g % 1000)), ((g.g % 100))
- Group Key: ((g.g % 1000))
- Group Key: ()
- Sort Key: ((g.g % 100)), ((g.g % 10))
- Group Key: ((g.g % 100)), ((g.g % 10))
- Group Key: ((g.g % 100))
- Sort Key: ((g.g % 10)), ((g.g % 1000))
- Group Key: ((g.g % 10)), ((g.g % 1000))
- Group Key: ((g.g % 10))
- -> Sort
- Sort Key: ((g.g % 1000)), ((g.g % 100)), ((g.g % 10))
- -> Function Scan on generate_series g
- Optimizer: Postgres query optimizer
-(15 rows)
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Sequence
+ -> Shared Scan (share slice:id 0:0)
+ -> Function Scan on generate_series
+ -> Append
+ -> HashAggregate
+ Group Key: share0_ref2.g1000, share0_ref2.g100, share0_ref2.g10
+ -> Shared Scan (share slice:id 0:0)
+ -> HashAggregate
+ Group Key: share0_ref3.g100, share0_ref3.g10
+ -> Shared Scan (share slice:id 0:0)
+ -> HashAggregate
+ Group Key: share0_ref4.g1000, share0_ref4.g10
+ -> Shared Scan (share slice:id 0:0)
+ -> HashAggregate
+ Group Key: share0_ref5.g10
+ -> Shared Scan (share slice:id 0:0)
+ -> HashAggregate
+ Group Key: share0_ref6.g1000, share0_ref6.g100
+ -> Shared Scan (share slice:id 0:0)
+ -> HashAggregate
+ Group Key: share0_ref7.g100
+ -> Shared Scan (share slice:id 0:0)
+ -> HashAggregate
+ Group Key: share0_ref8.g1000
+ -> Shared Scan (share slice:id 0:0)
+ -> Aggregate
+ -> Shared Scan (share slice:id 0:0)
+ Optimizer: Pivotal Optimizer (GPORCA)
+(28 rows)
create table gs_hash_1 as
select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from
(select g%1000 as g1000, g%100 as g100, g%10 as g10, g
from generate_series(0,199999) g) s
-group by cube (g1000,g100,g10) distributed by (g1000);
+group by cube (g1000,g100,g10);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'g1000' as the Greenplum Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
set jit_above_cost to default;
+create table gs_hash_2 as
+select g1000, g100, g10, sum(g::numeric), count(*), max(g::text) from
+ (select g/20 as g1000, g/200 as g100, g/2000 as g10, g
+ from generate_series(0,19999) g) s
+group by cube (g1000,g100,g10);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'g1000' as the Greenplum Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
+create table gs_hash_3 as
+select g100, g10, array_agg(g) as a, count(*) as c, max(g::text) as m from
+ (select g/200 as g100, g/2000 as g10, g
+ from generate_series(0,19999) g) s
+group by grouping sets (g100,g10);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry.
set enable_sort = true;
set work_mem to default;
WARNING: "work_mem": setting is deprecated, and may be removed in a future release.
diff --git a/src/test/regress/expected/orca_groupingsets_fallbacks_optimizer.out b/src/test/regress/expected/orca_groupingsets_fallbacks_optimizer.out
index 0b25ea39cc..d4242bceb3 100644
--- a/src/test/regress/expected/orca_groupingsets_fallbacks_optimizer.out
+++ b/src/test/regress/expected/orca_groupingsets_fallbacks_optimizer.out
@@ -16,8 +16,6 @@ insert into gstest1 values (2, 5, 30, 21, 300);
insert into gstest1 values (2, 42, 40, 53, 400);
-- Orca falls back due to Cube
select a, b, c, sum(v) from gstest1 group by cube(a, b, c);
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Cube
a | b | c | sum
---+----+----+------
1 | 5 | 10 | 100
diff --git a/src/test/regress/expected/tsrf.out b/src/test/regress/expected/tsrf.out
index 1327af03ca..0599abba94 100644
--- a/src/test/regress/expected/tsrf.out
+++ b/src/test/regress/expected/tsrf.out
@@ -325,7 +325,7 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
| foo | 2 | 1
(16 rows)
-SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY dataa;
+SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY dataa, b, g;
dataa | b | g | count
-------+-----+---+-------
a | bar | 1 | 1
@@ -346,7 +346,7 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
| | 2 | 3
(16 rows)
-SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY g;
+SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY g, dataa, b;
dataa | b | g | count
-------+-----+---+-------
a | bar | 1 | 1
@@ -354,17 +354,17 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
a | | 1 | 2
b | bar | 1 | 1
b | | 1 | 1
- | | 1 | 3
| bar | 1 | 2
| foo | 1 | 1
- | foo | 2 | 1
+ | | 1 | 3
a | bar | 2 | 1
- b | | 2 | 1
a | foo | 2 | 1
- | bar | 2 | 2
a | | 2 | 2
- | | 2 | 3
b | bar | 2 | 1
+ b | | 2 | 1
+ | bar | 2 | 2
+ | foo | 2 | 1
+ | | 2 | 3
(16 rows)
SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g);
@@ -396,7 +396,7 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
| | 2 | 3
(24 rows)
-SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa;
+SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa, b, g;
dataa | b | g | count
-------+-----+---+-------
a | bar | 1 | 1
@@ -425,33 +425,33 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
| | | 6
(24 rows)
-SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY g;
+SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY g, dataa, b;
dataa | b | g | count
-------+-----+---+-------
a | bar | 1 | 1
a | foo | 1 | 1
+ a | | 1 | 2
b | bar | 1 | 1
+ b | | 1 | 1
| bar | 1 | 2
| foo | 1 | 1
- a | | 1 | 2
- b | | 1 | 1
| | 1 | 3
+ a | bar | 2 | 1
+ a | foo | 2 | 1
a | | 2 | 2
+ b | bar | 2 | 1
b | | 2 | 1
| bar | 2 | 2
- | | 2 | 3
| foo | 2 | 1
- a | bar | 2 | 1
- a | foo | 2 | 1
- b | bar | 2 | 1
+ | | 2 | 3
+ a | bar | | 2
+ a | foo | | 2
a | | | 4
b | bar | | 2
b | | | 2
- | | | 6
- a | foo | | 2
- a | bar | | 2
| bar | | 4
| foo | | 2
+ | | | 6
(24 rows)
reset enable_hashagg;
diff --git a/src/test/regress/expected/tsrf_optimizer.out b/src/test/regress/expected/tsrf_optimizer.out
index 11d5b31af5..1555897e4d 100644
--- a/src/test/regress/expected/tsrf_optimizer.out
+++ b/src/test/regress/expected/tsrf_optimizer.out
@@ -339,7 +339,7 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
| foo | 2 | 1
(16 rows)
-SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY dataa;
+SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY dataa, b, g;
dataa | b | g | count
-------+-----+---+-------
a | bar | 1 | 1
@@ -360,7 +360,7 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
| | 2 | 3
(16 rows)
-SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY g;
+SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY g, dataa, b;
dataa | b | g | count
-------+-----+---+-------
a | bar | 1 | 1
@@ -368,17 +368,17 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
a | | 1 | 2
b | bar | 1 | 1
b | | 1 | 1
- | | 1 | 3
| bar | 1 | 2
| foo | 1 | 1
- | foo | 2 | 1
+ | | 1 | 3
a | bar | 2 | 1
- b | | 2 | 1
a | foo | 2 | 1
- | bar | 2 | 2
a | | 2 | 2
- | | 2 | 3
b | bar | 2 | 1
+ b | | 2 | 1
+ | bar | 2 | 2
+ | foo | 2 | 1
+ | | 2 | 3
(16 rows)
SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g);
@@ -410,7 +410,7 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
| | 2 | 3
(24 rows)
-SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa;
+SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa, b, g;
dataa | b | g | count
-------+-----+---+-------
a | bar | 1 | 1
@@ -439,33 +439,33 @@ SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(d
| | | 6
(24 rows)
-SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY g;
+SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY g, dataa, b;
dataa | b | g | count
-------+-----+---+-------
a | bar | 1 | 1
a | foo | 1 | 1
+ a | | 1 | 2
b | bar | 1 | 1
+ b | | 1 | 1
| bar | 1 | 2
| foo | 1 | 1
- a | | 1 | 2
- b | | 1 | 1
| | 1 | 3
+ a | bar | 2 | 1
+ a | foo | 2 | 1
a | | 2 | 2
+ b | bar | 2 | 1
b | | 2 | 1
| bar | 2 | 2
- | | 2 | 3
| foo | 2 | 1
- a | bar | 2 | 1
- a | foo | 2 | 1
- b | bar | 2 | 1
+ | | 2 | 3
+ a | bar | | 2
+ a | foo | | 2
a | | | 4
b | bar | | 2
b | | | 2
- | | | 6
- a | foo | | 2
- a | bar | | 2
| bar | | 4
| foo | | 2
+ | | | 6
(24 rows)
reset enable_hashagg;
diff --git a/src/test/regress/sql/tsrf.sql b/src/test/regress/sql/tsrf.sql
index b181c675d9..c5fb24ec41 100644
--- a/src/test/regress/sql/tsrf.sql
+++ b/src/test/regress/sql/tsrf.sql
@@ -94,11 +94,11 @@ SELECT few.dataa, count(*), min(id), max(id), generate_series(1,3) FROM few GROU
-- grouping sets are a bit special, they produce NULLs in columns not actually NULL
set enable_hashagg = false;
SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab);
-SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY dataa;
-SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY g;
+SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY dataa, b, g;
+SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY g, dataa, b;
SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g);
-SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa;
-SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY g;
+SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa, b, g;
+SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY g, dataa, b;
reset enable_hashagg;
-- case with degenerate ORDER BY
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org