Skip to content

Commit 0ab79db

Browse files
committed
test: move the ordinary DECIMAL RANGE frame cases to window_functions.sql
1 parent 3e8fb02 commit 0ab79db

2 files changed

Lines changed: 77 additions & 251 deletions

File tree

‎spark/src/test/resources/sql-tests/windows/window_functions.sql‎

Lines changed: 73 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -244,7 +244,7 @@ FROM date_events
244244
ORDER BY cate, val_date
245245

246246
-- ============================================================
247-
-- 1.12: RANGE BETWEEN with DECIMAL ORDER BY DESC
247+
-- 1.12: RANGE BETWEEN with DECIMAL ORDER BY
248248
-- Ported from Spark's typeCoercion/native/windowFrameCoercion.sql:
249249
-- SELECT COUNT(*) OVER (PARTITION BY 1 ORDER BY cast(1 as decimal(10, 0)) DESC
250250
-- RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM t
@@ -261,6 +261,78 @@ SELECT COUNT(*) OVER (PARTITION BY 1
261261
RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING) AS c
262262
FROM t_single
263263

264+
-- 30 rows in three partitions. The fractional part rotates through
265+
-- .00/.25/.50/.75 so RANGE peers vary, and the same values are kept at three
266+
-- precisions and scales.
267+
statement
268+
CREATE TABLE dec_range(a int, d decimal(10, 2), d0 decimal(10, 0), d6 decimal(18, 6), c int) USING parquet
269+
270+
statement
271+
INSERT INTO dec_range
272+
SELECT CAST(id % 3 AS int),
273+
CAST(id % 10 + (id % 4) * 0.25 AS decimal(10, 2)),
274+
CAST(id % 10 + (id % 4) * 0.25 AS decimal(10, 0)),
275+
CAST(id % 10 + (id % 4) * 0.25 AS decimal(18, 6)),
276+
CAST(id * 2 AS int)
277+
FROM range(30)
278+
279+
-- Each frame shape with an explicit offset
280+
query
281+
SELECT a, d, c,
282+
SUM(c) OVER (PARTITION BY a ORDER BY d RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS s_pf,
283+
SUM(c) OVER (PARTITION BY a ORDER BY d RANGE BETWEEN 3 PRECEDING AND CURRENT ROW) AS s_pc,
284+
SUM(c) OVER (PARTITION BY a ORDER BY d RANGE BETWEEN CURRENT ROW AND 3 FOLLOWING) AS s_cf,
285+
SUM(c) OVER (PARTITION BY a ORDER BY d RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING) AS s_uf
286+
FROM dec_range
287+
288+
-- Descending order swaps the direction of `current +/- offset`, and a
289+
-- fractional offset exercises scale-aware boundary arithmetic
290+
query
291+
SELECT a, d, c,
292+
SUM(c) OVER (PARTITION BY a ORDER BY d DESC RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS s_desc,
293+
SUM(c) OVER (PARTITION BY a ORDER BY d RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING) AS s_frac
294+
FROM dec_range
295+
296+
-- Zero scale and a higher precision and scale
297+
query
298+
SELECT a, d0, d6, c,
299+
SUM(c) OVER (PARTITION BY a ORDER BY d0 RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS s_d0,
300+
SUM(c) OVER (PARTITION BY a ORDER BY d6 RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS s_d6
301+
FROM dec_range
302+
303+
-- Partition 1 holds duplicate values, which RANGE (unlike ROWS) takes in as
304+
-- peers. Partition 2 holds NULL keys, which sort first under ASC. Partition 3
305+
-- holds negative values, so `current +/- offset` crosses zero.
306+
statement
307+
CREATE TABLE dec_range_edge(a int, d decimal(10, 2), c int) USING parquet
308+
309+
statement
310+
INSERT INTO dec_range_edge VALUES
311+
(1, 1.00, 10), (1, 1.00, 20), (1, 2.00, 30), (1, 2.00, 40), (1, 5.00, 50), (1, 5.00, 60),
312+
(2, NULL, 10), (2, 1.00, 20), (2, 2.00, 30), (2, NULL, 40), (2, 5.00, 50),
313+
(3, -5.50, 10), (3, -3.00, 20), (3, -1.25, 30), (3, 0.00, 40), (3, 1.25, 50), (3, 3.00, 60),
314+
(3, 5.50, 70)
315+
316+
query
317+
SELECT a, d, c,
318+
SUM(c) OVER (PARTITION BY a ORDER BY d RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS s_1,
319+
SUM(c) OVER (PARTITION BY a ORDER BY d RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS s_2
320+
FROM dec_range_edge
321+
322+
-- Maximum precision, with values and offset far enough from the Decimal128
323+
-- limit that `current +/- offset` cannot overflow. The overflow cases fall
324+
-- back and are covered in CometWindowExecSuite.
325+
statement
326+
CREATE TABLE dec_range_max(a int, d decimal(38, 0), c int) USING parquet
327+
328+
statement
329+
INSERT INTO dec_range_max VALUES (1, 10, 10), (1, 13, 20), (1, 15, 30), (1, 18, 40), (1, 22, 50)
330+
331+
query
332+
SELECT a, d, c,
333+
SUM(c) OVER (PARTITION BY a ORDER BY d RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS s
334+
FROM dec_range_max
335+
264336
-- ============================================================
265337
-- 1.13: RANGE frame with BIGINT ORDER BY values near Long.MaxValue
266338
-- Ported from Spark's postgreSQL/window_part2.sql:

‎spark/src/test/scala/org/apache/comet/exec/CometWindowExecSuite.scala‎

Lines changed: 4 additions & 250 deletions
Original file line numberDiff line numberDiff line change
@@ -2076,258 +2076,12 @@ class CometWindowExecSuite extends CometTestBase {
20762076

20772077
// ---------------------------------------------------------------------------
20782078
// Tests for issue #4834: RANGE frame with explicit PRECEDING/FOLLOWING offset
2079-
// on DECIMAL ORDER BY. Comet previously fell back to Spark because DataFusion
2080-
// rejected boundary arithmetic that widened precision (e.g. Decimal(10,0) +
2081-
// offset -> Decimal(11,0) vs current Decimal(10,0), "Uncomparable values").
2082-
// The upstream fix (apache/datafusion#22174, closes #22113) makes
2083-
// ScalarValue::partial_cmp ignore precision for Decimal128 when scales match,
2084-
// and was first released in DataFusion 54.0.0. These tests pin the behavior
2085-
// with `checkSparkAnswerAndOperator` so results must match Spark AND run
2086-
// natively.
2079+
// on DECIMAL ORDER BY. The ordinary frame shapes run natively and are covered
2080+
// in windows/window_functions.sql. The tests here pin the precision
2081+
// boundaries, where Comet either falls back or must use the exact offset
2082+
// Spark evaluates.
20872083
// ---------------------------------------------------------------------------
20882084

2089-
private def writeDecimalWindowFixture(dir: java.io.File, decimalType: String): Unit = {
2090-
(0 until 30)
2091-
.map { i =>
2092-
// Fractional part rotates 0.00/0.25/0.50/0.75 so RANGE peers vary; c
2093-
// stays as the aggregate payload.
2094-
val whole = i % 10
2095-
val frac = (i % 4) * 25
2096-
(i % 3, f"$whole%d.$frac%02d", i * 2)
2097-
}
2098-
.toDF("a", "d_str", "c")
2099-
.selectExpr("a", s"CAST(d_str AS $decimalType) AS d", "c")
2100-
.repartition(3)
2101-
.write
2102-
.mode("overwrite")
2103-
.parquet(dir.toString)
2104-
}
2105-
2106-
test("window: RANGE BETWEEN N PRECEDING AND N FOLLOWING with DECIMAL ORDER BY") {
2107-
withTempDir { dir =>
2108-
writeDecimalWindowFixture(dir, "DECIMAL(10, 2)")
2109-
spark.read.parquet(dir.toString).createOrReplaceTempView("window_test_dec")
2110-
val df = sql("""
2111-
SELECT a, d, c,
2112-
SUM(c) OVER (PARTITION BY a ORDER BY d
2113-
RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) as sum_c
2114-
FROM window_test_dec
2115-
""")
2116-
checkSparkAnswerAndOperator(df)
2117-
}
2118-
}
2119-
2120-
test("window: RANGE BETWEEN N PRECEDING AND CURRENT ROW with DECIMAL ORDER BY") {
2121-
withTempDir { dir =>
2122-
writeDecimalWindowFixture(dir, "DECIMAL(10, 2)")
2123-
spark.read.parquet(dir.toString).createOrReplaceTempView("window_test_dec")
2124-
val df = sql("""
2125-
SELECT a, d, c,
2126-
SUM(c) OVER (PARTITION BY a ORDER BY d
2127-
RANGE BETWEEN 3 PRECEDING AND CURRENT ROW) as sum_c
2128-
FROM window_test_dec
2129-
""")
2130-
checkSparkAnswerAndOperator(df)
2131-
}
2132-
}
2133-
2134-
test("window: RANGE BETWEEN CURRENT ROW AND N FOLLOWING with DECIMAL ORDER BY") {
2135-
withTempDir { dir =>
2136-
writeDecimalWindowFixture(dir, "DECIMAL(10, 2)")
2137-
spark.read.parquet(dir.toString).createOrReplaceTempView("window_test_dec")
2138-
val df = sql("""
2139-
SELECT a, d, c,
2140-
SUM(c) OVER (PARTITION BY a ORDER BY d
2141-
RANGE BETWEEN CURRENT ROW AND 3 FOLLOWING) as sum_c
2142-
FROM window_test_dec
2143-
""")
2144-
checkSparkAnswerAndOperator(df)
2145-
}
2146-
}
2147-
2148-
test("window: RANGE BETWEEN UNBOUNDED PRECEDING AND N FOLLOWING with DECIMAL ORDER BY") {
2149-
withTempDir { dir =>
2150-
writeDecimalWindowFixture(dir, "DECIMAL(10, 2)")
2151-
spark.read.parquet(dir.toString).createOrReplaceTempView("window_test_dec")
2152-
val df = sql("""
2153-
SELECT a, d, c,
2154-
SUM(c) OVER (PARTITION BY a ORDER BY d
2155-
RANGE BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING) as sum_c
2156-
FROM window_test_dec
2157-
""")
2158-
checkSparkAnswerAndOperator(df)
2159-
}
2160-
}
2161-
2162-
test("window: RANGE BETWEEN with DECIMAL ORDER BY and duplicate values") {
2163-
// RANGE (not ROWS) must aggregate all peer rows that share the same value.
2164-
// Duplicates let us tell ROWS from RANGE and expose peer-boundary bugs.
2165-
withTempDir { dir =>
2166-
Seq(
2167-
(1, "1.00", 10),
2168-
(1, "1.00", 20),
2169-
(1, "2.00", 30),
2170-
(1, "2.00", 40),
2171-
(1, "5.00", 50),
2172-
(1, "5.00", 60))
2173-
.toDF("a", "d_str", "c")
2174-
.selectExpr("a", "CAST(d_str AS DECIMAL(10, 2)) AS d", "c")
2175-
.write
2176-
.mode("overwrite")
2177-
.parquet(dir.toString)
2178-
2179-
spark.read.parquet(dir.toString).createOrReplaceTempView("window_test_dec")
2180-
val df = sql("""
2181-
SELECT a, d, c,
2182-
SUM(c) OVER (PARTITION BY a ORDER BY d
2183-
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sum_c
2184-
FROM window_test_dec
2185-
""")
2186-
checkSparkAnswerAndOperator(df)
2187-
}
2188-
}
2189-
2190-
test("window: RANGE BETWEEN with DECIMAL ORDER BY and NULL values") {
2191-
// Null decimals land at the head under ASC NULLS FIRST (Spark's default).
2192-
// Ensures the native comparator handles Decimal nulls the same way.
2193-
withTempDir { dir =>
2194-
Seq(
2195-
(1, Option.empty[String], 10),
2196-
(1, Option("1.00"), 20),
2197-
(1, Option("2.00"), 30),
2198-
(1, Option.empty[String], 40),
2199-
(1, Option("5.00"), 50))
2200-
.toDF("a", "d_str", "c")
2201-
.selectExpr("a", "CAST(d_str AS DECIMAL(10, 2)) AS d", "c")
2202-
.write
2203-
.mode("overwrite")
2204-
.parquet(dir.toString)
2205-
2206-
spark.read.parquet(dir.toString).createOrReplaceTempView("window_test_dec")
2207-
val df = sql("""
2208-
SELECT a, d, c,
2209-
SUM(c) OVER (PARTITION BY a ORDER BY d
2210-
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sum_c
2211-
FROM window_test_dec
2212-
""")
2213-
checkSparkAnswerAndOperator(df)
2214-
}
2215-
}
2216-
2217-
test("window: RANGE BETWEEN with DECIMAL ORDER BY DESC") {
2218-
// Descending order swaps the direction of `current +/- offset` in
2219-
// calculate_index_of_row; a bug specific to DESC would only show here.
2220-
withTempDir { dir =>
2221-
writeDecimalWindowFixture(dir, "DECIMAL(10, 2)")
2222-
spark.read.parquet(dir.toString).createOrReplaceTempView("window_test_dec")
2223-
val df = sql("""
2224-
SELECT a, d, c,
2225-
SUM(c) OVER (PARTITION BY a ORDER BY d DESC
2226-
RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) as sum_c
2227-
FROM window_test_dec
2228-
""")
2229-
checkSparkAnswerAndOperator(df)
2230-
}
2231-
}
2232-
2233-
test("window: RANGE BETWEEN with DECIMAL(10, 0) ORDER BY (zero scale)") {
2234-
withTempDir { dir =>
2235-
writeDecimalWindowFixture(dir, "DECIMAL(10, 0)")
2236-
spark.read.parquet(dir.toString).createOrReplaceTempView("window_test_dec")
2237-
val df = sql("""
2238-
SELECT a, d, c,
2239-
SUM(c) OVER (PARTITION BY a ORDER BY d
2240-
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sum_c
2241-
FROM window_test_dec
2242-
""")
2243-
checkSparkAnswerAndOperator(df)
2244-
}
2245-
}
2246-
2247-
test("window: RANGE BETWEEN with DECIMAL(18, 6) ORDER BY (high precision & scale)") {
2248-
withTempDir { dir =>
2249-
writeDecimalWindowFixture(dir, "DECIMAL(18, 6)")
2250-
spark.read.parquet(dir.toString).createOrReplaceTempView("window_test_dec")
2251-
val df = sql("""
2252-
SELECT a, d, c,
2253-
SUM(c) OVER (PARTITION BY a ORDER BY d
2254-
RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) as sum_c
2255-
FROM window_test_dec
2256-
""")
2257-
checkSparkAnswerAndOperator(df)
2258-
}
2259-
}
2260-
2261-
test("window: RANGE BETWEEN with negative DECIMAL ORDER BY values") {
2262-
// Negative current + positive offset crosses zero, which is a common
2263-
// arithmetic edge for decimal signed comparisons.
2264-
withTempDir { dir =>
2265-
Seq(
2266-
(1, "-5.50", 10),
2267-
(1, "-3.00", 20),
2268-
(1, "-1.25", 30),
2269-
(1, "0.00", 40),
2270-
(1, "1.25", 50),
2271-
(1, "3.00", 60),
2272-
(1, "5.50", 70))
2273-
.toDF("a", "d_str", "c")
2274-
.selectExpr("a", "CAST(d_str AS DECIMAL(10, 2)) AS d", "c")
2275-
.write
2276-
.mode("overwrite")
2277-
.parquet(dir.toString)
2278-
2279-
spark.read.parquet(dir.toString).createOrReplaceTempView("window_test_dec")
2280-
val df = sql("""
2281-
SELECT a, d, c,
2282-
SUM(c) OVER (PARTITION BY a ORDER BY d
2283-
RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING) as sum_c
2284-
FROM window_test_dec
2285-
""")
2286-
checkSparkAnswerAndOperator(df)
2287-
}
2288-
}
2289-
2290-
test("window: RANGE BETWEEN with fractional offset on DECIMAL ORDER BY") {
2291-
// Fractional offsets (0.5 PRECEDING) exercise scale-aware boundary
2292-
// arithmetic. Spark accepts decimal literals as offsets when the ORDER BY
2293-
// column is decimal.
2294-
withTempDir { dir =>
2295-
writeDecimalWindowFixture(dir, "DECIMAL(10, 2)")
2296-
spark.read.parquet(dir.toString).createOrReplaceTempView("window_test_dec")
2297-
val df = sql("""
2298-
SELECT a, d, c,
2299-
SUM(c) OVER (PARTITION BY a ORDER BY d
2300-
RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING) as sum_c
2301-
FROM window_test_dec
2302-
""")
2303-
checkSparkAnswerAndOperator(df)
2304-
}
2305-
}
2306-
2307-
test("window: RANGE BETWEEN with DECIMAL(38, 0) ORDER BY (max precision)") {
2308-
// Max-precision Decimal storage. Values and offset are kept well away from
2309-
// the Decimal128 limit, so `current +/- offset` never overflows and the
2310-
// frame runs natively; the overflow case falls back and is covered by the
2311-
// tests further down.
2312-
withTempDir { dir =>
2313-
Seq((1, "10", 10), (1, "13", 20), (1, "15", 30), (1, "18", 40), (1, "22", 50))
2314-
.toDF("a", "d_str", "c")
2315-
.selectExpr("a", "CAST(d_str AS DECIMAL(38, 0)) AS d", "c")
2316-
.write
2317-
.mode("overwrite")
2318-
.parquet(dir.toString)
2319-
2320-
spark.read.parquet(dir.toString).createOrReplaceTempView("window_test_dec")
2321-
val df = sql("""
2322-
SELECT a, d, c,
2323-
SUM(c) OVER (PARTITION BY a ORDER BY d
2324-
RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING) as sum_c
2325-
FROM window_test_dec
2326-
""")
2327-
checkSparkAnswerAndOperator(df)
2328-
}
2329-
}
2330-
23312085
private def writeDecimalOverflowFixture(
23322086
dir: java.io.File,
23332087
values: Seq[String],

0 commit comments

Comments
 (0)