Skip to content

Commit 24ae599

Browse files
committed
test: cover ungrouped, empty, multi-batch and collated PivotFirst inputs
PivotFirst.sql gains a pivot with no grouping column, the same pivot over no rows, and a pivot over 20000 groups whose input is repartitioned by the pivot column, so that each group has several partial states to merge. PivotFirst_collation.sql checks on Spark 4.0+ that a UTF8_LCASE pivot column falls back. Every native query now also asserts that PivotFirst itself ran natively. That was the only thing the two CometAggregateSuite tests checked that the fixture did not, so they are removed. The fixture's comments no longer name a nonexistent PivotTransformer or threshold, or a cast the float query does not make.
1 parent cafc8d1 commit 24ae599

3 files changed

Lines changed: 120 additions & 119 deletions

File tree

‎spark/src/test/resources/sql-tests/expressions/aggregate/PivotFirst.sql‎

Lines changed: 82 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -15,13 +15,15 @@
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)
4747
SELECT * 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)
6767
SELECT * 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)
7777
SELECT * 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

8687
statement
@@ -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)
9798
SELECT * 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)
119120
SELECT * 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)
125126
SELECT * 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)
145146
SELECT * 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)
165166
SELECT * 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)
175176
SELECT * 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)
186187
SELECT * 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)
209210
SELECT * 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)
229230
SELECT * 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)
251252
SELECT * 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)
274275
SELECT * 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)
295296
SELECT * 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)
316317
SELECT * 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)
348349
SELECT * 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)
355356
SELECT * 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)
375376
SELECT * 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)
386387
SELECT * 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)
398399
SELECT * 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)
425426
SELECT 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)
431432
SELECT * 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)
436437
SELECT * 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)
441442
SELECT * 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)
446447
SELECT * 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)
451452
SELECT * 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)
457458
SELECT * 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
Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,38 @@
1+
-- Licensed to the Apache Software Foundation (ASF) under one
2+
-- or more contributor license agreements. See the NOTICE file
3+
-- distributed with this work for additional information
4+
-- regarding copyright ownership. The ASF licenses this file
5+
-- to you under the Apache License, Version 2.0 (the
6+
-- "License"); you may not use this file except in compliance
7+
-- with the License. You may obtain a copy of the License at
8+
--
9+
-- http://www.apache.org/licenses/LICENSE-2.0
10+
--
11+
-- Unless required by applicable law or agreed to in writing,
12+
-- software distributed under the License is distributed on an
13+
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14+
-- KIND, either express or implied. See the License for the
15+
-- specific language governing permissions and limitations
16+
-- under the License.
17+
18+
-- From 4.2, Spark matches the values of a pivot column with a non-UTF8_BINARY collation by
19+
-- that collation, so under UTF8_LCASE the pivot value 'Q1' collects the 'q1' rows. 4.0 and 4.1
20+
-- match the bytes. The native lookup compares bytes, so PivotFirst falls back for these pivot
21+
-- columns on every version. Collation syntax requires Spark 4.0+.
22+
23+
-- MinSparkVersion: 4.0
24+
25+
statement
26+
CREATE TABLE pf_lcase (quarter string COLLATE UTF8_LCASE, course string, earnings int) USING parquet
27+
28+
statement
29+
INSERT INTO pf_lcase VALUES
30+
('q1', 'dotNET', 10000),
31+
('q2', 'dotNET', 15000),
32+
('q1', 'Java', 20000),
33+
('q2', 'Java', 30000)
34+
35+
query expect_fallback(Unsupported pivot column data type)
36+
SELECT * FROM pf_lcase
37+
PIVOT (sum(earnings) FOR quarter IN ('Q1' AS Q1, 'Q2' AS Q2))
38+
ORDER BY course

0 commit comments

Comments
 (0)