1515-- specific language governing permissions and limitations
1616-- under the License.
1717
18- -- PivotFirst is Spark's internal aggregate emitted by the two- phase optimized pivot plan
19- -- (PivotTransformer). It runs whenever every aggregate output type is in
18+ -- PivotFirst is Spark's internal aggregate for the second phase of the optimized pivot plan,
19+ -- which the analyzer rule ResolvePivot builds whenever every aggregate output type is in
2020-- PivotFirst.supportsDataType (Boolean, Byte, Short, Int, Long, Float, Double, Decimal).
2121-- These SQL tests exercise that path via SQL PIVOT clauses, one per supported value type and
2222-- per supported pivot column type, plus a handful of edge cases (unmatched pivot values,
23- -- all-null groups, null pivot column). The pivot column types and pivot lists whose matching
24- -- Comet does not reproduce come last and must fall back.
23+ -- all-null groups, null pivot column). Every native query asserts that PivotFirst itself ran
24+ -- natively, so a plan without it fails rather than passing on Spark's other pivot plan. The
25+ -- pivot column types and pivot lists whose matching Comet does not reproduce come last and
26+ -- must fall back.
2527
2628-- ============================================================
2729-- Setup: sales-like table used for most cases
@@ -38,12 +40,10 @@ INSERT INTO pf_sales VALUES
3840 (2013 , ' Java' , 30000 )
3941
4042-- ============================================================
41- -- Int values: canonical optimized-pivot query. Extra pivot values
42- -- (1..10) push the aggregate over the threshold that turns on the
43- -- PivotFirst plan.
43+ -- Int values: canonical optimized-pivot query.
4444-- ============================================================
4545
46- query
46+ query expect_native(pivotfirst)
4747SELECT * FROM pf_sales
4848 PIVOT (sum (earnings)
4949 FOR course IN (' dotNET' , ' Java' ))
@@ -63,7 +63,7 @@ INSERT INTO pf_sales_bi VALUES
6363 (2013 , ' dotNET' , 48000000000 ),
6464 (2013 , ' Java' , 30000000000 )
6565
66- query
66+ query expect_native(pivotfirst)
6767SELECT * FROM pf_sales_bi
6868 PIVOT (sum (earnings)
6969 FOR course IN (' dotNET' , ' Java' ))
@@ -73,14 +73,15 @@ SELECT * FROM pf_sales_bi
7373-- Double values: avg(earnings) is double.
7474-- ============================================================
7575
76- query
76+ query expect_native(pivotfirst)
7777SELECT * FROM pf_sales
7878 PIVOT (avg (earnings)
7979 FOR course IN (' dotNET' , ' Java' ))
8080 ORDER BY year
8181
8282-- ============================================================
83- -- Float values: cast the sum result to float via a wrapping min.
83+ -- Float values: the column is already float, and min keeps the
84+ -- type where sum would widen it to double.
8485-- ============================================================
8586
8687statement
@@ -93,7 +94,7 @@ INSERT INTO pf_sales_f VALUES
9394 (2013 , ' dotNET' , CAST(48000 AS FLOAT)),
9495 (2013 , ' Java' , CAST(30000 AS FLOAT))
9596
96- query
97+ query expect_native(pivotfirst)
9798SELECT * FROM pf_sales_f
9899 PIVOT (min (earnings)
99100 FOR course IN (' dotNET' , ' Java' ))
@@ -115,13 +116,13 @@ INSERT INTO pf_sales_small VALUES
115116 (2013 , ' dotNET' , 480 , 30 ),
116117 (2013 , ' Java' , 300 , 40 )
117118
118- query
119+ query expect_native(pivotfirst)
119120SELECT * FROM pf_sales_small
120121 PIVOT (min (s)
121122 FOR course IN (' dotNET' , ' Java' ))
122123 ORDER BY year
123124
124- query
125+ query expect_native(pivotfirst)
125126SELECT * FROM pf_sales_small
126127 PIVOT (min (b)
127128 FOR course IN (' dotNET' , ' Java' ))
@@ -141,7 +142,7 @@ INSERT INTO pf_flags VALUES
141142 (2013 , ' dotNET' , false),
142143 (2013 , ' Java' , true)
143144
144- query
145+ query expect_native(pivotfirst)
145146SELECT * FROM pf_flags
146147 PIVOT (bool_or(flag)
147148 FOR course IN (' dotNET' , ' Java' ))
@@ -161,7 +162,7 @@ INSERT INTO pf_sales_dec VALUES
161162 (2013 , ' dotNET' , 48000 .00 ),
162163 (2013 , ' Java' , 30000 .00 )
163164
164- query
165+ query expect_native(pivotfirst)
165166SELECT * FROM pf_sales_dec
166167 PIVOT (sum (earnings)
167168 FOR course IN (' dotNET' , ' Java' ))
@@ -171,7 +172,7 @@ SELECT * FROM pf_sales_dec
171172-- Multiple aggregates in a single pivot.
172173-- ============================================================
173174
174- query
175+ query expect_native(pivotfirst)
175176SELECT * FROM pf_sales
176177 PIVOT (sum (earnings) AS s, avg (earnings) AS a
177178 FOR course IN (' dotNET' , ' Java' ))
@@ -182,7 +183,7 @@ SELECT * FROM pf_sales
182183-- output slots must be NULL.
183184-- ============================================================
184185
185- query
186+ query expect_native(pivotfirst)
186187SELECT * FROM pf_sales
187188 PIVOT (sum (earnings)
188189 FOR course IN (' dotNET' , ' Java' , ' MissingA' , ' MissingB' ))
@@ -205,7 +206,7 @@ INSERT INTO pf_sales_extra VALUES
205206 (2013 , ' Java' , 30000 ),
206207 (2013 , ' Python' , 67890 )
207208
208- query
209+ query expect_native(pivotfirst)
209210SELECT * FROM pf_sales_extra
210211 PIVOT (sum (earnings)
211212 FOR course IN (' dotNET' , ' Java' ))
@@ -225,7 +226,7 @@ INSERT INTO pf_sales_partial VALUES
225226 (2012 , ' Java' , 20000 ),
226227 (2013 , ' Python' , 42000 )
227228
228- query
229+ query expect_native(pivotfirst)
229230SELECT * FROM pf_sales_partial
230231 PIVOT (sum (earnings)
231232 FOR course IN (' dotNET' , ' Java' ))
@@ -247,7 +248,7 @@ INSERT INTO pf_sales_nullable VALUES
247248 (2013 , ' dotNET' , 48000 ),
248249 (2013 , ' Java' , 30000 )
249250
250- query
251+ query expect_native(pivotfirst)
251252SELECT * FROM pf_sales_nullable
252253 PIVOT (sum (earnings)
253254 FOR course IN (' dotNET' , ' Java' ))
@@ -270,7 +271,7 @@ INSERT INTO pf_sales_nullpivot VALUES
270271 (2013 , ' Java' , 30000 ),
271272 (2013 , NULL , 42 )
272273
273- query
274+ query expect_native(pivotfirst)
274275SELECT * FROM pf_sales_nullpivot
275276 PIVOT (sum (earnings)
276277 FOR course IN (' dotNET' , ' Java' ))
@@ -291,7 +292,7 @@ INSERT INTO pf_sales_intpivot VALUES
291292 (' EU' , 2012 , 20000 ),
292293 (' EU' , 2013 , 30000 )
293294
294- query
295+ query expect_native(pivotfirst)
295296SELECT * FROM pf_sales_intpivot
296297 PIVOT (sum (earnings)
297298 FOR year IN (2012 , 2013 ))
@@ -312,7 +313,7 @@ INSERT INTO pf_sales_datepivot VALUES
312313 (' EU' , DATE ' 2024-01-01' , 20000 ),
313314 (' EU' , DATE ' 2024-06-15' , 30000 )
314315
315- query
316+ query expect_native(pivotfirst)
316317SELECT * FROM pf_sales_datepivot
317318 PIVOT (sum (earnings)
318319 FOR d IN (DATE ' 2024-01-01' , DATE ' 2024-06-15' ))
@@ -344,14 +345,14 @@ INSERT INTO pf_sales_dblpivot VALUES
344345
345346-- -0.0 in the pivot list has to collect both signs of zero (63000 / 30000),
346347-- and the NaN column has to stay NULL for every group.
347- query
348+ query expect_native(pivotfirst)
348349SELECT * FROM pf_sales_dblpivot
349350 PIVOT (sum (earnings)
350351 FOR k IN (- 0 .0D, double(' NaN' ), 1 .5D))
351352 ORDER BY region
352353
353354-- The mirror: a 0.0 entry in the pivot list must also catch the -0.0 rows.
354- query
355+ query expect_native(pivotfirst)
355356SELECT * FROM pf_sales_dblpivot
356357 PIVOT (sum (earnings)
357358 FOR k IN (0 .0D, 1 .5D))
@@ -371,7 +372,7 @@ INSERT INTO pf_sales_fltpivot VALUES
371372 (' NA' , float(' NaN' ), 11000 ),
372373 (' EU' , - 0 .0F, 30000 )
373374
374- query
375+ query expect_native(pivotfirst)
375376SELECT * FROM pf_sales_fltpivot
376377 PIVOT (sum (earnings)
377378 FOR k IN (- 0 .0F, float(' NaN' )))
@@ -382,7 +383,7 @@ SELECT * FROM pf_sales_fltpivot
382383-- NULL, as Spark's HashMap lookup of a null key does.
383384-- ============================================================
384385
385- query
386+ query expect_native(pivotfirst)
386387SELECT * FROM pf_sales_nullpivot
387388 PIVOT (sum (earnings)
388389 FOR course IN (' dotNET' , NULL ))
@@ -394,7 +395,7 @@ SELECT * FROM pf_sales_nullpivot
394395-- both columns stay NULL.
395396-- ============================================================
396397
397- query
398+ query expect_native(pivotfirst)
398399SELECT * FROM pf_sales_dblpivot
399400 PIVOT (sum (earnings)
400401 FOR k IN (double(' NaN' ), 1 .5D, double(' NaN' )))
@@ -421,44 +422,89 @@ INSERT INTO pf_sales_types VALUES
421422 (' EU' , NULL , 3 , 300 , 30000000000 , 3 .00 ,
422423 TIMESTAMP ' 2025-01-01 00:00:00' , TIMESTAMP_NTZ ' 2025-01-01 00:00:00' , 30000 )
423424
424- query
425+ query expect_native(pivotfirst)
425426SELECT region, ` true` , ` false` FROM (
426427 SELECT region, flag, earnings FROM pf_sales_types)
427428 PIVOT (sum (earnings) FOR flag IN (true, false))
428429 ORDER BY region
429430
430- query
431+ query expect_native(pivotfirst)
431432SELECT * FROM (SELECT region, b, earnings FROM pf_sales_types)
432433 PIVOT (sum (earnings) FOR b IN (CAST(1 AS TINYINT), CAST(2 AS TINYINT)))
433434 ORDER BY region
434435
435- query
436+ query expect_native(pivotfirst)
436437SELECT * FROM (SELECT region, s, earnings FROM pf_sales_types)
437438 PIVOT (sum (earnings) FOR s IN (CAST(100 AS SMALLINT ), CAST(300 AS SMALLINT )))
438439 ORDER BY region
439440
440- query
441+ query expect_native(pivotfirst)
441442SELECT * FROM (SELECT region, l, earnings FROM pf_sales_types)
442443 PIVOT (sum (earnings) FOR l IN (10000000000 , 20000000000 ))
443444 ORDER BY region
444445
445- query
446+ query expect_native(pivotfirst)
446447SELECT * FROM (SELECT region, d, earnings FROM pf_sales_types)
447448 PIVOT (sum (earnings) FOR d IN (2 .25 , 3 .00 ))
448449 ORDER BY region
449450
450- query
451+ query expect_native(pivotfirst)
451452SELECT * FROM (SELECT region, ts, earnings FROM pf_sales_types)
452453 PIVOT (sum (earnings)
453454 FOR ts IN (TIMESTAMP ' 2024-01-01 00:00:00' , TIMESTAMP ' 2024-06-15 12:30:00' ))
454455 ORDER BY region
455456
456- query
457+ query expect_native(pivotfirst)
457458SELECT * FROM (SELECT region, ntz, earnings FROM pf_sales_types)
458459 PIVOT (sum (earnings)
459460 FOR ntz IN (TIMESTAMP_NTZ ' 2024-06-15 12:30:00' , TIMESTAMP_NTZ ' 2025-01-01 00:00:00' ))
460461 ORDER BY region
461462
463+ -- ============================================================
464+ -- No grouping column: the pivoted relation has only the pivot and
465+ -- value columns, so PivotFirst runs ungrouped.
466+ -- ============================================================
467+
468+ query expect_native(pivotfirst)
469+ SELECT * FROM (SELECT course, earnings FROM pf_sales)
470+ PIVOT (sum (earnings) FOR course IN (' dotNET' , ' Java' ))
471+
472+ -- Over no rows the result is still one row, of nulls. A literal
473+ -- WHERE false lets the optimizer replace the scan with an empty
474+ -- local relation, which runs in Spark. These fixtures run without
475+ -- constant folding, so 1 = 0 keeps the scan and the filter native.
476+ query expect_native(pivotfirst)
477+ SELECT * FROM (SELECT course, earnings FROM pf_sales WHERE 1 = 0 )
478+ PIVOT (sum (earnings) FOR course IN (' dotNET' , ' Java' ))
479+
480+ -- ============================================================
481+ -- More groups than one batch holds, each with several partial
482+ -- states. Repartitioning the input by the pivot column means the
483+ -- first aggregation needs no shuffle, so each partition's partial
484+ -- PivotFirst writes the slots of its own pivot values for nearly
485+ -- every group, and the final aggregation merges those states.
486+ -- Without the hint, AQE coalesces the first aggregation's shuffle
487+ -- into one partition, and each group has a single partial state.
488+ -- Each (g, k) pair has at most one row, so the result is
489+ -- deterministic.
490+ -- ============================================================
491+
492+ statement
493+ CREATE TABLE pf_many_groups (g int , k string, v int ) USING parquet
494+
495+ statement
496+ INSERT INTO pf_many_groups
497+ SELECT g, k, v FROM (
498+ SELECT CAST(id % 20000 AS INT ) AS g,
499+ CASE id DIV 20000 WHEN 0 THEN ' a' WHEN 1 THEN ' b' ELSE ' c' END AS k,
500+ CASE WHEN id % 11 = 0 THEN NULL ELSE CAST(id AS INT ) END AS v
501+ FROM range(60000 ))
502+ WHERE NOT (k = ' b' AND g % 7 = 0 )
503+
504+ query expect_native(pivotfirst)
505+ SELECT * FROM (SELECT /* + REPARTITION(4, k) */ g, k, v FROM pf_many_groups)
506+ PIVOT (sum (v) FOR k IN (' a' , ' b' , ' c' , ' d' ))
507+
462508-- ============================================================
463509-- Pivot column types that fall back. Spark compares binary pivot
464510-- values by reference before 4.2, so no row matches there, and by
0 commit comments