@@ -48,6 +48,97 @@ NULL A 1 0 2 1
4848NULL B 1 0 2 1
4949NULL NULL 1 1 3 3
5050
51+ # Grouping sets must not use the ordered aggregation optimization even when the
52+ # input is ordered by a grouping expression that is present in every set.
53+ statement ok
54+ SET datafusion.execution.target_partitions = 1;
55+
56+ statement ok
57+ SET datafusion.execution.enable_migration_aggregate = false;
58+
59+ statement ok
60+ CREATE EXTERNAL TABLE grouping_set_ordered (
61+ a0 INTEGER,
62+ a INTEGER,
63+ b INTEGER,
64+ c INTEGER,
65+ d INTEGER
66+ )
67+ STORED AS CSV
68+ WITH ORDER (c)
69+ LOCATION '../core/tests/data/window_2.csv'
70+ OPTIONS ('format.has_header' 'true');
71+
72+ # An unordered AggregateExec omits `ordering_mode`; its absence below ensures
73+ # that grouping sets do not select ordered/streaming aggregation.
74+ query TT
75+ EXPLAIN
76+ SELECT b, c, grouping(b, c) AS gid
77+ FROM grouping_set_ordered
78+ GROUP BY GROUPING SETS ((b, c), (c));
79+ ----
80+ logical_plan
81+ 01)Projection: grouping_set_ordered.b, grouping_set_ordered.c, CAST(__grouping_id & UInt8(3) AS Int32) AS gid
82+ 02)--Aggregate: groupBy=[[GROUPING SETS ((grouping_set_ordered.b, grouping_set_ordered.c), (grouping_set_ordered.c))]], aggr=[[]]
83+ 03)----TableScan: grouping_set_ordered projection=[b, c]
84+ physical_plan
85+ 01)ProjectionExec: expr=[b@0 as b, c@1 as c, CAST(__grouping_id@2 & 3 AS Int32) as gid]
86+ 02)--AggregateExec: mode=Final, gby=[b@0 as b, c@1 as c, __grouping_id@2 as __grouping_id], aggr=[]
87+ 03)----AggregateExec: mode=Partial, gby=[(b@0 as b, c@1 as c), (NULL as b, c@1 as c)], aggr=[]
88+ 04)------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/window_2.csv]]}, projection=[b, c], output_ordering=[c@1 ASC NULLS LAST], file_type=csv, has_header=true
89+
90+ # The grouping sets produce two rows for each value of c.
91+ query I
92+ SELECT c
93+ FROM (
94+ SELECT b, c, grouping(b, c) AS gid
95+ FROM grouping_set_ordered
96+ GROUP BY GROUPING SETS ((b, c), (c))
97+ )
98+ ORDER BY c
99+ LIMIT 10;
100+ ----
101+ 0
102+ 0
103+ 1
104+ 1
105+ 2
106+ 2
107+ 3
108+ 3
109+ 4
110+ 4
111+
112+ statement ok
113+ SET datafusion.execution.enable_migration_aggregate = true;
114+
115+ query I
116+ SELECT c
117+ FROM (
118+ SELECT b, c, grouping(b, c) AS gid
119+ FROM grouping_set_ordered
120+ GROUP BY GROUPING SETS ((b, c), (c))
121+ )
122+ ORDER BY c
123+ LIMIT 10;
124+ ----
125+ 0
126+ 0
127+ 1
128+ 1
129+ 2
130+ 2
131+ 3
132+ 3
133+ 4
134+ 4
135+
136+ statement ok
137+ RESET datafusion.execution.enable_migration_aggregate;
138+
139+ statement ok
140+ SET datafusion.execution.target_partitions = 4;
141+
51142# grouping_with_cube
52143query TTIIII
53144select
0 commit comments