Skip to main content

Other engines’ facts

74 facts: what the engine does, with provenance

Each fact records what the engine does at a pinned version, what the reference semantics does, and the checks behind it, from characterization/ (the format). Evidence runs from docs to source, engine_test, probe, fuzz and proof.

DataFusion: decimals

datafusion-S4: Decimal literals become doubles

Engine: datafusion 51.0.0; area: decimals; status: no-finding; evidence: engine_test.

Verdict: frontend

DataFusion’s SQL reads 99999999999999999999.99999 as a double, so 100000000000000000000 > 99999999999999999999.99999 is false.

The reference: A decimal literal is a decimal.

Options: literal_types.

Constructs: decimal literal.

See: upstream ledger DF-7.

Provenance:

DataFusion: relations

datafusion-S3: The Substrait producer drops VALUES rows

Engine: datafusion 51.0.0; area: relations; status: no-finding; evidence: engine_test.

Verdict: refused by the replay

DataFusion 51 writes FROM (VALUES …) as an empty virtual table.

The reference: The replay refuses these plans (producer: VALUES written as an empty virtual table).

Constructs: VALUES, VirtualTable.

See: upstream ledger DF-6.

Provenance:

  • 2026-11-01, replay, by sverify_dev.slt, DuckDB 2.0.0’s tests on DataFusion 51’s plans (commit 7dbf2f7): Recorded as S3 of characterization/duckdb/FACTS.md.

datafusion-S6: The SQL frontend plans another query

Engine: datafusion 51.0.0; area: relations; status: open; evidence: engine_test.

Verdict: frontend

In these cases Rel.eval and DuckDB running the written plan agree with each other, and both differ from DuckDB, because the plan DataFusion writes is not the query. Each is a plan sverify checks for a query the user did not write.

  • EXCEPT ALL is planned as a left anti join, which loses multiplicities: [1, 1, 2, 3] EXCEPT ALL [1, 3] gives [2], not [1, 2] (setops/test_pg_union.test:339,350).
  • FETCH FIRST n ROWS ONLY is dropped: the plan has no limit (parser/fetch_first.test).
  • The Substrait producer drops DISTINCT and FILTER (WHERE …) of a window aggregate: COUNT(DISTINCT s) OVER (…) counts every row (window/test_window_distinct.test:76, window/test_naive_aggregation.test:204, window/test_streaming_window.test:223).
  • GROUP BY k, k writes two grouping expressions, and the projection above reads the aggregate’s columns shifted by one (aggregate/group/test_group_by.test:149). Either the producer or Lean’s reading of a repeated grouping expression is wrong; this needs reading.
  • SELECT * of a NATURAL JOIN keeps both copies of the join columns (catalog/case_insensitive_using.test:123-138).

The reference: The plan should be the query’s.

Constructs: EXCEPT ALL, FETCH FIRST, COUNT(DISTINCT) OVER, FILTER (WHERE), GROUP BY, NATURAL JOIN.

See: upstream ledger DF-1, DF-2, DF-3, DF-4, DF-5.

Provenance:

Spark: integers

spark-int-abs: abs of integers: abs(MIN) raises under ANSI, is MIN without

Engine: spark 4.3.0; area: integers; status: no-finding; evidence: probe.

Verdict: ANSI on: overflow: ERROR; ANSI off: abs(MIN) is MIN

abs keeps the width. Under ANSI abs(MIN) raises ARITHMETIC_OVERFLOW at every width; without it, it returns MIN.

The reference: abs with overflow: ERROR, the only value the model accepts for it, so abs(MIN) is an overflow issue. Spark under ANSI is native.

Options: overflow.

Rows of the choice tables: int.abs overflow (i8, i16, i32, i64).

Constructs: abs.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-int-arith: Integer + - * and unary -: raise under ANSI, wrap without

Engine: spark 4.3.0; area: integers; status: no-finding; evidence: probe.

Verdict: ANSI on: overflow: ERROR at every width; ANSI off: SILENT

Spark keeps the operands’ width: TINYINT + TINYINT is a TINYINT, and so on up to BIGINT. With spark.sql.ansi.enabled on (the default since 4.0) +, -, * and unary - raise past the width (BINARY_ARITHMETIC_OVERFLOW for TINYINT and SMALLINT, ARITHMETIC_OVERFLOW for INT and BIGINT). With it off they wrap in two’s complement: 127Y + 1Y is -128, -(-2⁶³) is -2⁶³.

try_add, try_subtract and try_multiply give NULL on overflow in either mode (spark-try-variants). An integer literal without a suffix is an INT, so 2147483647 + 1 raises under ANSI (spark-literal-types).

The reference: add, subtract, multiply, negate with overflow: ERROR when the plan omits it (#202); SILENT when the plan asks. Spark under ANSI gives ERROR natively at every width; without ANSI it gives SILENT.

Options: overflow.

Rows of the choice tables: int.arith overflow (i8, i16, i32, i64).

Constructs: +, -, *, unary -, negative.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #202.

Provenance:

spark-int-divide: div truncates and returns BIGINT; / on integers is a double division

Engine: spark 4.3.0; area: integers; status: open; evidence: fuzz.

Verdict: div: truncate, BIGINT; zero divisor raises (ANSI) or NULL; MIN div -1 raises only at 64 bits

a div b (IntegralDivide) truncates toward zero (-7 div 2 is -3) and always returns BIGINT, whatever the operands’ width. So TINYINT -128 div -1 is 128, SMALLINT -32768 div -1 32768 and INT -2147483648 div -1 2147483648: no overflow below 64 bits. BIGINT MIN div -1 raises ARITHMETIC_OVERFLOW under ANSI and wraps to MIN without it.

A zero divisor raises DIVIDE_BY_ZERO under ANSI and gives NULL without it, for div and /.

a / b on integers casts both to DOUBLE and divides: -7 / 2 is -3.5 (spark-implicit-casts).

The reference: divide on integers truncates toward zero and returns the operands’ type, with on_division_by_zero: ERROR when the plan omits it (NULL when asked) and overflow: ERROR at MIN / -1 at the declared width; on_domain_error never applies. Spark’s div truncates natively and raises on zero under ANSI; at i8, i16, i32 its BIGINT result needs a cast back, which raises under ANSI where the reference does.

Options: division_type, on_division_by_zero, overflow, on_domain_error.

Rows of the choice tables: int.div division_type (i8, i16, i32, i64); int.div on_division_by_zero (i8, i16, i32, i64); int.div overflow (i8, i16, i32, i64); int.div on_domain_error (i8, i16, i32, i64).

Constructs: div, /.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

int_divide[0]: 300 rows, a div b, a / b, 2400 of 2400 results as the fact says (raised: DIVIDE_BY_ZERO 48); Spark differs from Rel.eval on 0 of 288; int_divide[1]: 300 rows, a div b, a / b, 2400 of 2400 results as the fact says (raised: DIVIDE_BY_ZERO 48); Spark differs from Rel.eval on 0 of 288; int_divide[2]: 300 rows, a div b, a / b, 2400 of 2400 results as the fact says (raised: DIVIDE_BY_ZERO 52); Spark differs from Rel.eval on 0 of 287; int_divide[3]: 300 rows, a div b, a / b, 2394 of 2394 results as the fact says (raised: ARITHMETIC_OVERFLOW 2, DIVIDE_BY_ZERO 58); Spark differs from Rel.eval on 0 of 283.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json int_divide[0], int_divide[1], int_divide[2], int_divide[3]

spark-int-modulus: % is the truncated remainder, pmod the floored one

Engine: spark 4.3.0; area: integers; status: no-finding; evidence: probe.

Verdict: %: TRUNCATE; MIN % -1 is 0; zero divisor raises (ANSI) or NULL

a % b keeps the width and takes the dividend’s sign (-7 % 2 is -1, 7 % -2 is 1): division_type: TRUNCATE. pmod(a, b) is the floored remainder (pmod(-7, 2) is 1, pmod(-7, -2) is -1).

MIN % -1 is 0 at every width, in both modes; % never overflows. A zero divisor raises REMAINDER_BY_ZERO under ANSI and gives NULL without it.

The reference: modulus with division_type: TRUNCATE (the remainder has the dividend’s sign), on_division_by_zero: ERROR when omitted, MIN % -1 is 0 (min_mod_neg_one), no overflow. Spark’s % under ANSI is native.

Options: division_type, on_division_by_zero, min_mod_neg_one, overflow.

Rows of the choice tables: int.mod division_type (i8, i16, i32, i64); int.mod on_division_by_zero (i8, i16, i32, i64); int.mod min_mod_neg_one (i8, i16, i32, i64); int.mod overflow (i8, i16, i32, i64).

Constructs: %, mod, pmod.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-int-pow: power of integers returns a double

Engine: spark 4.3.0; area: integers; status: open; evidence: fuzz.

Verdict: no integer power: power(2, 63) is 9.223372036854776E18

Spark’s power casts both arguments to DOUBLE and returns a DOUBLE (StrictMath.pow) for every integer width, so there is no integer power: power(2, 63) is 9.223372036854776E18, power(2, 64) 1.8446744073709552E19, power(-2, 1024) +inf, power(0, -1) +inf, power(2, -1) 0.5. Neither overflow nor a negative exponent raises, under ANSI or without it.

The reference: power on integers returns the integer type, with overflow: ERROR; a negative exponent raises (negExponent in Safety.lean), and on_domain_error never applies (ST-11). An integer power on Spark needs a cast of the double back, exact only below 2⁵³, or a refusal.

Options: overflow, on_domain_error.

Rows of the choice tables: int.pow overflow (i64); int.pow overflow (i8, i16, i32; evidence probe); int.pow on_domain_error (i64); int.pow on_domain_error (i8, i16, i32; evidence probe).

Constructs: power, pow.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #202.

Provenance:

  • 2026-11-05, source-reading, by Spark characterization session (#738): Read at tag v4.3.0 (commit 98bc0212).
  • 2026-11-05, probe, by sverify_dev.probe_spark on homelab (Spark characterization session, #738): sverify_dev.probe_spark runs each query four times on one local session: with spark.sql.ansi.enabled true and false, on the folded path (the optimizer evaluates a query over VALUES on the driver) and on the codegen path (ConvertToLocalRelation and ConstantFolding excluded, so the query runs as a job with whole-stage code generation). probes.json keeps one result per ANSI setting where the two paths agree. Spark 4.3.0 (pyspark 4.3.0), Java 21.0.12, Linux x86_64, local[2], session time zone UTC, spark.sql.timestampType=TIMESTAMP_NTZ.
  • 2026-11-05, fuzz, by sverify_dev.spark_fuzz on homelab (Spark evidence session): python -m sverify_dev.spark_fuzz --rows 300 --seed 738: random rows leaning on the fact’s edges, each construct run with spark.sql.ansi.enabled on and off, on the folded and the codegen path, and compared with a model of this fact (value, result type, error class). 1200 of 1200 results as the fact says; Spark’s value differs from Rel.eval’s (sverify’s lift, reference profile) on 37 of 300 rows compared.

int_pow[0]: 300 rows, power(a, b), 1200 of 1200 results as the fact says; Spark differs from Rel.eval on 37 of 300.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json int_pow[0]

spark-int-shift: Shifts promote to INT and take the count modulo the width

Engine: spark 4.3.0; area: integers; status: open; evidence: fuzz.

Verdict: shiftleft(a, n): Java’s <<, count mod 32 (INT) or mod 64 (BIGINT)

shiftleft, shiftright, shiftrightunsigned (and the operators <<, >>, >>>) take INT or BIGINT; a TINYINT or SMALLINT operand is promoted to INT, so the result is an INT. They are Java’s shifts: the count is taken modulo 32 for INT and 64 for BIGINT (shiftleft(-8, 32) is -8, shiftleft(-8, 33) -16, shiftleft(-8, -1) 0 for INT), and bits shifted out are dropped. Neither mode raises.

&, |, ^ and ~ keep the width (TINYINT & TINYINT is a TINYINT).

The reference: shift_left and shift_right (functions_arithmetic.yaml) with the range open (ST-1); the reference drops shifted-out bits and raises for a count outside [0, width). Spark’s masked count needs a guard, and an i8 or i16 shift a cast back to its width.

Options: shift_range.

Rows of the choice tables: int.shift shift_range (i8, i16, i32, i64).

Constructs: shiftleft, shiftright, shiftrightunsigned, <<, >>, >>>, &, |, ^, ~.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

int_shift[0]: 300 rows, shiftleft(a, n), shiftright(a, n), shiftrightunsigned(a, n), 3600 of 3600 results as the fact says; int_shift[1]: 300 rows, shiftleft(a, n), shiftright(a, n), shiftrightunsigned(a, n), 3600 of 3600 results as the fact says; int_shift[2]: 300 rows, shiftleft(a, n), shiftright(a, n), shiftrightunsigned(a, n), 3600 of 3600 results as the fact says; int_shift[3]: 300 rows, shiftleft(a, n), shiftright(a, n), shiftrightunsigned(a, n), 3600 of 3600 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json int_shift[0], int_shift[1], int_shift[2], int_shift[3]

Spark: decimals

spark-decimal-arith: Decimal arithmetic: Hive’s result types, half up, overflow raises (ANSI) or NULL

Engine: spark 4.3.0; area: decimals; status: no-finding; evidence: probe.

Verdict: result types borrow scale past 38 digits; decimal_rescale: TIE_AWAY_FROM_ZERO; overflow: ERROR (ANSI) or NULL

Result types follow Hive’s rules (DecimalPrecision): DECIMAL(10,2) + DECIMAL(10,1) is DECIMAL(12,2), * is DECIMAL(21,3) (scale s₁ + s₂), / is DECIMAL(22,13) (scale max(6, s₁ + p₂ + 1)), % is DECIMAL(10,2). Past 38 digits, with spark.sql.decimalOperations.allowPrecisionLoss on (the default), the scale is cut to no less than 6: DECIMAL(38,10) * DECIMAL(38,10) is DECIMAL(38,6); with it off the scale is kept and the integer digits shrink (DECIMAL(38,20)).

A result with more digits than its scale rounds half away from zero (ROUND_HALF_UP on java.math.BigDecimal): CAST(0.125 AS DECIMAL(10,2)) is 0.13 and -0.125 gives -0.13; round does the same, bround rounds half to even. 1.00 / 8 is 0.125000, 2 / 3 0.666667.

A result past its precision raises NUMERIC_VALUE_OUT_OF_RANGE under ANSI and is NULL without it (a + a for a = 10³⁸ - 1). sum of DECIMAL(p,s) returns DECIMAL(p + 10, s) and avg DECIMAL(p + 4, s + 4); a decimal sum past 38 digits raises under ANSI (ARITHMETIC_OVERFLOW on the codegen path, NUMERIC_VALUE_OUT_OF_RANGE on the folded one) and is NULL without it. DECIMAL(39,0) is refused when the query is analysed.

Negation and abs are different: they round to 34 significant digits (SP-1, spark-decimal-negate).

The reference: functions_arithmetic_decimal.yaml gives the result types (multiply: scale s₁ + s₂, divide: max(6, s₁ + p₂ + 1), precision capped at 38 by borrowing scale) and no rounding; the reference rounds half away from zero (decimal_rescale), with overflow: ERROR. Decimal division is refused until #27; ST-8 records the open rounding. Spark under ANSI gives the YAML’s types, the reference’s rounding and its overflow natively.

Options: overflow, decimal_rescale, decimal_result_type.

Rows of the choice tables: dec.arith overflow (decimal); dec.arith decimal_rescale (decimal).

Constructs: +, -, *, /, %, round, bround, CAST AS DECIMAL, sum, avg.

Settings: {"spark.sql.ansi.enabled": [true, false], "spark.sql.decimalOperations.allowPrecisionLoss": [true, false]}.

See: issues #27.

Provenance:

spark-decimal-negate: Negation of decimals rounds to 34 significant digits, half to even (SP-1)

Engine: spark 4.3.0; area: decimals; status: open; evidence: fuzz.

Verdict: -x, and abs(x) of a negative x, past 34 digits: rounded half to even, or a raise at 10³⁸ - 1

-x of a decimal with more than 34 significant digits rounds it to 34 digits, half to even, in both modes and on both paths: -12345678901234567890123456789012345678 (a DECIMAL(38,0)) gives -12345678901234567890123456789012350000, and -88926501604876820962013312199159205000 gives 88926501604876820962013312199159200000 (a tie, to the even digit). abs(x) returns a positive x unchanged and negates a negative one, so it rounds only those. At 10³⁸ - 1 the rounding reaches 10³⁸, which does not fit, so -x raises NUMERIC_VALUE_OUT_OF_RANGE even with ANSI off.

0 - x is exact where its type keeps the scale: an INT 0 is DECIMAL(10,0), so 0 - x of a DECIMAL(38,s) needs 39 digits and, with spark.sql.decimalOperations.allowPrecisionLoss on (the default), keeps the scale up to s = 6 and drops one digit past it, rounding half up: 0 - x of a DECIMAL(38,10) is a DECIMAL(38,9) (spark-decimal-arith).

Decimal.unary_- negates the Scala BigDecimal, whose default MathContext is DECIMAL128 (34 digits, HALF_EVEN); Decimal.abs calls it for a negative value only. Upstream bug SP-1.

The reference: negate and abs of decimals are exact, and never overflow (the negation of a DECIMAL(p,s) fits it). Spark is wrong past 34 digits; a writer spells -x as 0 - x and abs(x) as CASE WHEN x < 0 THEN 0 - x ELSE x END, exact for a scale up to 6; past it 0 - x loses the last digit unless precision loss is off.

Options: overflow, raise_set.

Rows of the choice tables: dec.arith overflow (decimal).

Constructs: unary -, negative, abs.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: upstream ledger SP-1.

Provenance:

decimal_negate[0]: 300 rows, -x, abs(x), 0 - x, 3536 of 3536 results as the fact says (raised: NUMERIC_VALUE_OUT_OF_RANGE.WITHOUT_SUGGESTION 120); Spark differs from Rel.eval on 394 of 554; decimal_negate[1]: 300 rows, -x, abs(x), 0 - x, 3548 of 3548 results as the fact says (raised: NUMERIC_VALUE_OUT_OF_RANGE.WITHOUT_SUGGESTION 120); Spark differs from Rel.eval on 401 of 557.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json decimal_negate[0], decimal_negate[1]

Spark: doubles

spark-double-arith: Double + - *: IEEE, NaN from inf - inf, ±inf on overflow

Engine: spark 4.3.0; area: doubles; status: open; evidence: fuzz.

Verdict: IEEE in both modes: nan_creation NaN, float_overflow ±inf

DOUBLE and FLOAT arithmetic is the JVM’s, IEEE 754 round to nearest even: 0.1 + 0.2 is 0.30000000000000004. ANSI mode checks no double arithmetic: inf - inf, inf + -inf and 0 * inf are NaN, and 1e308 * 10 is +inf, 1e308 + 1e308 +inf, in both modes. FLOAT stays single precision (3e38f * 10f is +inf as a FLOAT, spark-fp32).

Spark’s docs state the infinity rules: inf * 0 is NaN.

The reference: add, subtract, multiply round once (TIE_TO_EVEN) and raise a domain issue for inf - inf and 0 · inf (nan_creation: raise, #257, ST-5); a finite result past the largest double raises (#575, ST-9). Spark is native for rounding and needs guards for the other two.

Options: rounding, nan_creation, float_overflow.

Rows of the choice tables: fp.arith rounding (fp64); fp.arith rounding (fp32; evidence probe); fp.arith nan_creation (fp64); fp.arith nan_creation (fp32; evidence probe); fp.arith float_overflow (fp64); fp.arith float_overflow (fp32; evidence probe).

Constructs: +, -, *.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #257, #575.

Provenance:

double_arith[0]: 300 rows, a + b, a - b, a * b, 3600 of 3600 results as the fact says; Spark differs from Rel.eval on 86 of 900.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json double_arith[0]

spark-double-divide: Double /: a zero divisor raises (ANSI) or gives NULL, never inf

Engine: spark 4.3.0; area: doubles; status: open; evidence: fuzz.

Verdict: on_division_by_zero: ERROR (ANSI) or NULL; inf / inf NaN; overflow ±inf

a / b on doubles is IEEE except at a zero divisor: 1.0 / 0.0, 0.0 / 0.0, 1.0 / -0.0, NaN / 0.0 and inf / 0.0 raise DIVIDE_BY_ZERO under ANSI and are NULL without it; never ±inf or NaN. FLOAT the same. try_divide gives NULL in both modes.

Otherwise IEEE: inf / inf is NaN, 1e308 / 1e-308 +inf, the quotient rounded once.

The reference: divide with on_division_by_zero: ERROR when omitted; IEEE, NULL, LIMIT when asked. ±inf / ±inf is a domain issue (#257); a finite quotient past the largest double raises (#575). Spark under ANSI gives ERROR natively; without ANSI, NULL. IEEE needs the CASE that sverify.sql.spark writes.

Options: on_division_by_zero, nan_creation, on_domain_error, float_overflow, rounding.

Rows of the choice tables: fp.div on_division_by_zero (fp64); fp.div on_division_by_zero (fp32; evidence probe); fp.div nan_creation (fp64); fp.div nan_creation (fp32; evidence probe); fp.div on_domain_error (fp64); fp.div on_domain_error (fp32; evidence probe); fp.div float_overflow (fp64); fp.div float_overflow (fp32; evidence probe); fp.div rounding (fp64); fp.div rounding (fp32; evidence probe).

Constructs: /.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #257, #575.

Provenance:

double_divide_mod[0]: 300 rows, a / b, 1200 of 1200 results as the fact says (raised: DIVIDE_BY_ZERO 30); Spark differs from Rel.eval on 13 of 285.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json double_divide_mod[0]

spark-double-modulus: Double %: Java’s truncated remainder; pmod adds b to a negative remainder; zero divisor raises (ANSI) or NULL

Engine: spark 4.3.0; area: doubles; status: open; evidence: fuzz.

Verdict: %: fmod, fmod(x, ±inf) = x; zero divisor ERROR (ANSI) or NULL

a % b on doubles is Java’s %, C’s fmod: the dividend’s sign (-7.5 % 2 is -1.5, 7.5 % -2 1.5), -6.0 % 2 is -0.0, 5 % inf 5, inf % 2 NaN. A zero divisor raises REMAINDER_BY_ZERO under ANSI and is NULL without it.

pmod(a, b) is r = a % b, then (r + b) % b when r < 0 and r otherwise (MathUtils.pmod): pmod(-7.5, 2) is 0.5 and pmod(-5, inf) NaN, but a nonnegative remainder comes back as it is, also for a negative b (pmod(4.76, -3.625) is 1.135) and for -0.0 (pmod(-0.0, 5) is -0.0).

The reference: modulus on doubles (sverify’s sverify_fmod): the truncated remainder (fmod), with a zero divisor an error as on integers, and fmod(x, ±inf) = x; inf % y is a domain issue (nan_creation), and the zero is ONE_ZERO. Spark’s % under ANSI is native but for the sign of a zero remainder.

Options: division_type, on_division_by_zero, nan_creation, zero_sign.

Rows of the choice tables: fp.mod division_type (fp64); fp.mod division_type (fp32; evidence probe); fp.mod on_division_by_zero (fp64); fp.mod on_division_by_zero (fp32; evidence probe); fp.mod nan_creation (fp64); fp.mod nan_creation (fp32; evidence probe); fp.mod zero_sign (fp64); fp.mod zero_sign (fp32; evidence probe).

Constructs: %, mod, pmod.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

double_divide_mod[1]: 300 rows, a % b, pmod(a, b), 2380 of 2380 results as the fact says (raised: REMAINDER_BY_ZERO 60).

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json double_divide_mod[1]

spark-elem-rounding: Elementary functions: StrictMath (fdlibm) for exp, logs, pow; java.lang.Math for the rest

Engine: spark 4.3.0; area: doubles; status: open; evidence: fuzz.

Verdict: exp, ln, log10, pow: fdlibm, the same everywhere; sqrt, trig: Math, within 1 ulp, may vary

exp, expm1, ln, log10, log1p, log(b, x) and power call java.lang.StrictMath, whose results are fdlibm’s bit for bit on every JVM. log2(x) is StrictMath.log(x) / StrictMath.log(2), two roundings: it can be an ulp off the correctly rounded log₂. sqrt, sin, cos, tan, asin, acos, atan, atan2, cbrt and the hyperbolic functions call java.lang.Math, which the JIT may replace with intrinsics within 1 ulp of the exact result (Java’s spec), so the last bit can differ between platforms and JVMs. sqrt is correctly rounded on every JVM.

A FLOAT argument is widened to DOUBLE and the result is a DOUBLE (spark-fp32).

The reference: The reference computes these with Lean’s Float (the platform’s libm, in double) and fixes rounding: TIE_TO_EVEN; the last bits are each engine’s (#54). sverify’s ε (#85) per function and platform comes from these facts.

Options: rounding, platform.

Rows of the choice tables: fp.elem rounding (fp64); fp.elem rounding (fp32; evidence probe); fp.log rounding (fp64); fp.log rounding (fp32; evidence probe).

Constructs: exp, ln, log10, log2, log, power, sqrt, sin, cos, tan, asin, acos, atan, atan2, cbrt.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #54, #85.

Provenance:

elem_rounding[0]: 300 rows, exp(x), sqrt(x), sin(x), cos(x), tan(x), atan(x), cbrt(x), 8400 of 8400 results as the fact says; Spark differs from Rel.eval on 92 of 1500; elem_rounding[1]: 300 rows, ln(x), log10(x), log2(x), 3600 of 3600 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json elem_rounding[0], elem_rounding[1]

spark-fp32: FLOAT +, -, * in single precision; / and ANSI FLOAT + INT are DOUBLE

Engine: spark 4.3.0; area: doubles; status: open; evidence: fuzz.

Verdict: FLOAT + - * FLOAT is FLOAT; FLOAT / FLOAT DOUBLE; FLOAT + INT: DOUBLE (ANSI) or FLOAT; math functions return DOUBLE

FLOAT + FLOAT, - and * stay FLOAT, rounded to single precision: 0.1f + 0.2f is 0.30000001192092896, 16777216f + 1f 16777216. FLOAT + DOUBLE is DOUBLE. FLOAT + INT is DOUBLE under ANSI and FLOAT without it, and BIGINT + FLOAT the same. / casts both sides to DOUBLE and divides in double precision, so FLOAT / FLOAT is a DOUBLE.

sqrt and the other math functions return DOUBLE; round keeps FLOAT, floor gives BIGINT. sum and avg of FLOAT return DOUBLE (sum(0.1f, 0.2f) is 0.30000000447034836); max keeps FLOAT. A double past the float range casts to ±inf, in both modes (CAST(1e40D AS FLOAT)); a 1.5F literal is a FLOAT.

The reference: fp32 arithmetic is refused in every profile until #417’s stage 3. Spark has native single precision; its aggregates and math functions widen to double.

Options: fp32, implicit_casts.

Rows of the choice tables: fp32 (fp32).

Constructs: FLOAT, REAL, +, *, /, sqrt, sum, avg, max, CAST AS FLOAT.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #417.

Provenance:

fp32[0]: 300 rows, a + b, a * b, a / b, a + i, sqrt(a), 5970 of 5970 results as the fact says (raised: DIVIDE_BY_ZERO 30).

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json fp32[0]

spark-math-domain: Math domain errors: NaN for sqrt, asin, acos; NULL for logs of x ≤ 0

Engine: spark 4.3.0; area: doubles; status: open; evidence: fuzz.

Verdict: on_domain_error: NAN (sqrt, asin, acos, pow), NULL (logs); on_log_zero: NULL (ST-16)

sqrt(-1), asin(2.5), acos(2.5), power(-8, 1/3), power(±1, ±inf), power(1, NaN) and sin(±inf) are NaN, in both modes: power is StrictMath.pow, whose rules give NaN where C’s pow gives 1. atan2(0, 0) is 0, and Spark adds 0.0 to both arguments, so atan2(-0.0, -0.0) is 0.0 where C’s atan2 gives -π.

The logarithms ln, log10, log2 and log(b, x) return NULL for any x ≤ 0 (and b ≤ 0), a rule Spark keeps from Hive: ln(0), ln(-0.0) and ln(-1) are NULL, never -inf, NaN or a raise, ANSI or not. log(1, 8) is +inf. Substrait’s on_log_zero has no NULL value (spec gap ST-16).

The reference: on_domain_error: ERROR and on_log_zero: ERROR (#215); sin(±inf) is a domain issue; atan2(0, 0) is open (ST-2). Spark needs guards for every domain error; a NULL from a log has to be told apart from a NULL input.

Options: on_domain_error, on_log_zero.

Rows of the choice tables: fp.elem on_domain_error (fp64); fp.elem on_domain_error (fp32; evidence probe); fp.log on_domain_error (fp64); fp.log on_domain_error (fp32; evidence probe); fp.log on_log_zero (fp64); fp.log on_log_zero (fp32; evidence probe).

Constructs: sqrt, ln, log, log10, log2, asin, acos, atan2, power, sin, cos, tan, exp.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: upstream ledger ST-16; issues #215.

Provenance:

math_domain[0]: 300 rows, sqrt(a), asin(a), acos(a), ln(a), log10(a), log(a, b), power(a, b), atan2(a, b), sin(a), 10800 of 10800 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json math_domain[0]

spark-nan-equality: NaN equals NaN in =, grouping, DISTINCT and joins

Engine: spark 4.3.0; area: doubles; status: no-finding; evidence: probe.

Verdict: NAN_IS_NAN everywhere; -NaN groups with NaN

Spark documents NaN = NaN as true; NaN values group together and match in join keys. NaNs with the sign bit set group, deduplicate and join with NaN too: the optimizer’s NormalizeFloatingNumbers replaces every NaN (and -0.0) in grouping, join and window keys by the canonical NaN (and 0.0). count(DISTINCT x) over NaN, -NaN, 0.0, -0.0 is 2.

Arrays compare their elements the same way: array(NaN) = array(NaN) is true.

The reference: NAN_IS_NAN (#89, an override of IEEE equality) for =, grouping, DISTINCT and joins. Spark is native.

Options: nan_equality, nan_zero_grouping.

Rows of the choice tables: cmp nan_equality (fp32, fp64).

Constructs: =, <=>, GROUP BY, DISTINCT, JOIN, INTERSECT, count(DISTINCT).

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #89.

Provenance:

spark-nan-order: NaN sorts above +inf; max and greatest return NaN

Engine: spark 4.3.0; area: doubles; status: no-finding; evidence: probe.

Verdict: nan_order: GREATEST in sorts, <, max, greatest, quantiles

NaN is larger than every other value, +inf included: NaN > inf is true, 1 < NaN true; ascending sorts put NaN after +inf (NULLs come first by default); greatest(1, NaN) is NaN and least(1, NaN) 1; max over 1, NaN, -1 is NaN and min -1. Quantiles order NaN last: median(1, NaN, 3) is 3.0, percentile_cont(1.0) NaN.

SQLOrderingUtil.compareDoubles orders NaN above everything and -0.0 equal to 0.0.

The reference: GREATEST: NaN above +inf in sorts, <, MAX, greatest (#89). Spark is native.

Options: nan_order.

Rows of the choice tables: cmp nan_order (fp32, fp64); agg.order nan_order (fp32, fp64); agg.quant nan_order (fp64).

Constructs: <, >, ORDER BY, greatest, least, max, min, median, percentile_cont, percentile_disc.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #89.

Provenance:

spark-nan-sign: A NaN’s sign survives arithmetic and shows; grouping normalises it

Engine: spark 4.3.0; area: doubles; status: open; evidence: fuzz.

Verdict: nan_kind: the sign bit is kept by -x; inf - inf is -NaN on x86-64

Spark passes a NaN’s bits through: -x flips the sign bit of NaN, x * -1.0 keeps the sign of the NaN operand, abs clears it. A NaN that arithmetic creates (inf - inf, inf * 0, sqrt(-1)) has the sign bit set on the probes’ x86-64 JVM, as the x86 default NaN does; on aarch64 the default NaN is positive. CAST(x AS STRING) prints NaN for both. Grouping, joins and DISTINCT replace every NaN by the canonical one (spark-nan-equality).

The reference: One NaN (nan_kind: ONE_NAN). A writer canonicalises NaN where a result’s bits are observed (a CAST to binary, a Parquet sink).

Options: nan_kind, platform.

Rows of the choice tables: nan_kind (fp64); nan_kind (fp32; evidence probe).

Constructs: unary -, *, abs, CAST AS STRING.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

  • 2026-11-05, probe, by sverify_dev.probe_spark on homelab (Spark characterization session, #738): sverify_dev.probe_spark runs each query four times on one local session: with spark.sql.ansi.enabled true and false, on the folded path (the optimizer evaluates a query over VALUES on the driver) and on the codegen path (ConvertToLocalRelation and ConstantFolding excluded, so the query runs as a job with whole-stage code generation). probes.json keeps one result per ANSI setting where the two paths agree. Spark 4.3.0 (pyspark 4.3.0), Java 21.0.12, Linux x86_64, local[2], session time zone UTC, spark.sql.timestampType=TIMESTAMP_NTZ.
  • 2026-11-05, fuzz, by sverify_dev.spark_fuzz on homelab (Spark evidence session): python -m sverify_dev.spark_fuzz --rows 300 --seed 738: random rows leaning on the fact’s edges, each construct run with spark.sql.ansi.enabled on and off, on the folded and the codegen path, and compared with a model of this fact (value, result type, error class). 4800 of 4800 results as the fact says.

nan_sign[0]: 300 rows, -a, abs(a), a * -1.0D, b - b, 4800 of 4800 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json nan_sign[0]

spark-round: round rounds half up on the double’s shortest decimal; bround half even

Engine: spark 4.3.0; area: doubles; status: open; evidence: fuzz.

Verdict: round(x): TIE_AWAY_FROM_ZERO; round(x, d) on the decimal text: round(2.675, 2) is 2.68

round(x, d) on a double turns it into a BigDecimal from its shortest decimal text (Scala’s BigDecimal(d), Double.toString) and rounds that HALF_UP, then converts back. So round(x) is half away from zero (round(2.5) 3, round(-2.5) -3, round(0.5) 1), and round(x, d) rounds the decimal the double prints as, not its binary value: round(2.675, 2) is 2.68, round(0.285, 2) 0.29, round(1.005, 2) 1.01, where the binary values lie below the halves. Negative digits work (round(1250, -2) 1300). Integer round and bround keep the type, so negative digits can overflow it: round(127y, -1) (a TINYINT) raises ARITHMETIC_OVERFLOW under ANSI and wraps to -126 without it. NaN and ±inf come back unchanged; a rounded negative value that reaches zero is 0.0 (round(-0.4)). d must be a constant.

bround is the same with HALF_EVEN (bround(2.5) 2, bround(0.285, 2) 0.28). round of a FLOAT stays FLOAT.

floor and ceil of a double return BIGINT, not a double: they saturate without ANSI (floor(1e20) is 2⁶³ - 1), raise ARITHMETIC_OVERFLOW for ±inf and values past 64 bits under ANSI, and give 0 for NaN in both modes (SP-5). floor(x, d) returns a decimal.

The reference: round with rounding: TIE_AWAY_FROM_ZERO (#335), the only value the model accepts, to d digits with DuckDB’s formula on the binary value (x · 10ᵈ rounded, then divided), so round(2.675, 2) is 2.67. floor and ceil return the double. Spark’s round(x) is native; to d digits it differs where the decimal text sits on a half the binary value misses, and floor, ceil need a cast and guards.

Options: rounding.

Rows of the choice tables: fp.round rounding (fp64); fp.round rounding (fp32; evidence probe).

Constructs: round, bround, floor, ceil.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: upstream ledger SP-5; issues #335.

Provenance:

round[0]: 300 rows, round(x, 0), bround(x, 0), round(x, 1), bround(x, 1), round(x, 2), bround(x, 2), round(x, -2), bround(x, -2), floor(x), ceil(x), 11956 of 11956 results as the fact says (raised: ARITHMETIC_OVERFLOW 60); Spark differs from Rel.eval on 41 of 1200.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json round[0]

spark-zero-order: The two zeros compare equal; grouping normalises to 0.0; min, max keep the first

Engine: spark 4.3.0; area: doubles; status: open; evidence: fuzz.

Verdict: =: -0.0 = 0.0; group key 0.0; sorts tie; min/max return the zero seen first

-0.0 = 0.0 and -0.0 <=> 0.0 are true, -0.0 < 0.0 false; a sort keeps the zeros as ties in input order. Grouping, DISTINCT, joins and set operations normalise -0.0 to 0.0, so a group key is 0.0 whichever zero came first, and the joined side keeps its own value (0.0 joins -0.0).

min and max keep the first of two equal zeros: over (-0.0, 0.0) both are -0.0, over (0.0, -0.0) both 0.0. greatest(-0.0, 0.0) is -0.0.

The reference: ONE_ZERO (#89): -0.0 is 0.0 everywhere, so a group key and an aggregate’s result are never -0.0. Spark’s grouping is native; min, max, greatest, least and a sorted column need the zero canonicalised where it is observed.

Options: zero_sign, nan_zero_grouping.

Rows of the choice tables: cmp zero_sign (fp64); cmp zero_sign (fp32; evidence probe); agg.order zero_sign (fp64); agg.order zero_sign (fp32; evidence probe).

Constructs: =, <, ORDER BY, GROUP BY, DISTINCT, JOIN, min, max, greatest, least.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #89.

Provenance:

zero_order[0]: 300 rows, min(x), max(x), count(DISTINCT x), 984 of 984 results as the fact says; zero_order[1]: 300 rows, a = b, a < b, a <=> b, greatest(a, b), 4800 of 4800 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json zero_order[0], zero_order[1]

spark-zero-sign: Two zeros in double arithmetic; a -0.0 literal is a decimal

Engine: spark 4.3.0; area: doubles; status: open; evidence: fuzz.

Verdict: IEEE zeros: -0.0 * 1 is -0.0, -(0.0) is -0.0; -0.0 without D is 0.0

Arithmetic keeps IEEE’s two zeros: -0.0 + 0.0 is 0.0, -0.0 + -0.0 -0.0, -0.0 * 0.0 -0.0, -(0.0) -0.0, 0.0 / -1.0 -0.0; abs(-0.0) is 0.0. Casting -0.0 to STRING gives -0.0.

A literal -0.0 without a suffix is DECIMAL(1,1), so CAST(-0.0 AS DOUBLE) is 0.0; -0.0D and CAST('-0.0' AS DOUBLE) are -0.0.

Comparisons, grouping and joins treat the zeros as equal (spark-zero-order).

The reference: One zero (zero_sign: ONE_ZERO, #89): -0.0 is 0.0 everywhere. Spark shows -0.0 where IEEE gives it, so a writer canonicalises where a result’s zeros are observed.

Options: zero_sign.

Rows of the choice tables: fp.arith zero_sign (fp64); fp.arith zero_sign (fp32; evidence probe); fp.neg zero_sign (fp64); fp.neg zero_sign (fp32; evidence probe); fp.div zero_sign (fp64); fp.div zero_sign (fp32; evidence probe).

Constructs: +, -, *, /, unary -, abs.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #89.

Provenance:

zero_sign[0]: 300 rows, a + b, a * b, a / b, -a, abs(a), CAST(a AS STRING), 7042 of 7042 results as the fact says (raised: DIVIDE_BY_ZERO 30).

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json zero_sign[0]

Spark: strings

spark-case: upper, lower: Java’s full Unicode mappings, in the JVM’s default locale

Engine: spark 4.3.0; area: strings; status: no-finding; evidence: probe.

Verdict: char_set: UTF8 with full mappings (ß → SS, final sigma); ASCII fast path

upper and lower map ASCII text byte by byte and anything else with Java’s String.toUpperCase() and toLowerCase(): Unicode’s full mappings, context included. upper('ß') is SS, upper('fi') FI, upper('straße') STRASSE (the length grows from 6 to 7), lower('İ') i̇ (two code points), lower('ΣΑΣ') σας with a final sigma, upper('Dž') DŽ, lower('Ⅻ') ⅻ.

The no-argument toUpperCase() uses the JVM’s default locale, so a JVM started in a Turkish or Azeri locale maps i to İ (a setting outside Spark’s own configuration). The probes ran in an English locale.

The reference: upper, lower with char_set: UTF8, a black box (Rust’s str::to_uppercase, Unicode’s full mappings); Lean evaluates them on ASCII only. ST-10 records that Substrait leaves simple and full mappings open. Spark’s mappings are Unicode’s full ones, as Rust’s are, under a locale without special casing.

Options: char_set, settings.

Rows of the choice tables: str.case char_set (string).

Constructs: upper, lower, ucase, lcase, initcap.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-digests: md5, sha1, sha2, crc32: lowercase hex of the UTF-8 bytes

Engine: spark 4.3.0; area: strings; status: no-finding; evidence: probe.

Verdict: md5(s), sha2(s, 256): hex strings of the UTF-8 bytes; sha2 with a bad length is NULL

md5, sha1 and sha2(s, n) hash the string’s UTF-8 bytes (a string argument is cast to BINARY) and return lowercase hex STRINGs: md5('abc') is 900150983cd24fb0d6963f7d28e17f72, sha2('abc', 256) ba7816bf…15ad, md5('é') the digest of the two bytes c3 a9. sha2 takes 224, 256, 384, 512 or 0 (256); any other length gives NULL in both modes. crc32 returns a BIGINT.

The reference: md5 and sha256 (sverify’s black boxes) are the lowercase hex digest of the UTF-8 bytes. Spark is native with md5(s) and sha2(s, 256).

Options: digests.

Constructs: md5, sha1, sha, sha2, crc32.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

  • 2026-11-05, source-reading, by Spark characterization session (#738): Read at tag v4.3.0 (commit 98bc0212).
  • 2026-11-05, probe, by sverify_dev.probe_spark on homelab (Spark characterization session, #738): sverify_dev.probe_spark runs each query four times on one local session: with spark.sql.ansi.enabled true and false, on the folded path (the optimizer evaluates a query over VALUES on the driver) and on the codegen path (ConvertToLocalRelation and ConstantFolding excluded, so the query runs as a job with whole-stage code generation). probes.json keeps one result per ANSI setting where the two paths agree. Spark 4.3.0 (pyspark 4.3.0), Java 21.0.12, Linux x86_64, local[2], session time zone UTC, spark.sql.timestampType=TIMESTAMP_NTZ.

spark-match-functions: LIKE, contains, startswith, instr, RLIKE, replace are case-sensitive

Engine: spark 4.3.0; area: strings; status: no-finding; evidence: probe.

Verdict: case_sensitivity: CASE_SENSITIVE; ILIKE and (?i) for the other, (?i) ASCII only

'ABC' LIKE 'a%', contains('ABC', 'b'), startswith('ABC', 'a'), endswith('ABC', 'c'), 'ABC' RLIKE 'b' are false and instr('ABC', 'b') is 0: every match function compares code points exactly, and so does = under the default UTF8_BINARY collation. ILIKE folds case. In a regex, (?i) folds ASCII only ('É' RLIKE '(?i)é' is false) unless (?iu) is given.

LIKE escapes with \ by default or ESCAPE; _ matches one code point ('é' LIKE '_'). startswith(s, '') is true.

The reference: starts_with, ends_with, strpos, like, replace with case_sensitivity: CASE_SENSITIVE. Spark is native.

Options: case_sensitivity.

Rows of the choice tables: str.match case_sensitivity (string).

Constructs: LIKE, ILIKE, contains, startswith, endswith, instr, locate, RLIKE, replace, =.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-regex: Regexes are java.util.regex: ASCII classes, $1 references, '' without a match

Engine: spark 4.3.0; area: strings; status: open; evidence: fuzz.

Verdict: Java’s syntax; \\d, \\w ASCII; no match extracts ''; bad patterns raise

Patterns are java.util.regex.Patterns: backreferences and lookaround work; \d and \w are ASCII ('٣' RLIKE '^\d$' and 'é' RLIKE '^\w$' are false); . does not match \n. regexp_extract returns '' when the pattern does not match, or its group did not take part; a group index past the pattern’s raises INVALID_PARAMETER_VALUE.REGEX_GROUP_INDEX. regexp_replace takes $1 for a group (\1 is a literal 1). regexp_count('aaa', 'aa') counts non-overlapping matches (1).

A pattern that does not compile raises in both modes: INVALID_PARAMETER_VALUE.PATTERN on the folded path, and a bare java.util.regex.PatternSyntaxException with no error class on the codegen path (SP-4). A NULL pattern gives NULL.

The reference: regexp_extract, regexp_replace with a constant pattern (#50), RE2’s syntax, which has no backreferences or lookaround; a pattern that does not match extracts ''. Lean evaluates the classes on ASCII only (Text.classesDiffer). Java’s syntax is a superset of RE2’s for the constructs sverify writes; the replacement’s syntax differs ($1).

Options: regex_dialect, case_sensitivity, raise_set.

Constructs: RLIKE, regexp, regexp_like, regexp_extract, regexp_extract_all, regexp_replace, regexp_count, regexp_instr.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: upstream ledger SP-4; issues #50.

Provenance:

regex[0]: 300 rows, s RLIKE (CASE i WHEN 0 THEN '(\\d+)' WHEN 1 THEN '(\\w)(\\w)' WHEN 2 THEN 'a(b)?c' WHEN 3 THEN '(.)\\1' WHEN 4 THEN '^(\\s*)x' WHEN 5 THEN '(é+)' WHEN 6 THEN '([a-c]+)-(\\d)' WHEN 7 THEN '(?<=a)(b)' END), regexp_extract(s, CASE i WHEN 0 THEN '(\\d+)' WHEN 1 THEN '(\\w)(\\w)' WHEN 2 THEN 'a(b)?c' W, 3600 of 3600 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json regex[0]

spark-string-plain: length, reverse, trim, ascii, repeat, levenshtein: code points, spaces only

Engine: spark 4.3.0; area: strings; status: no-finding; evidence: probe.

Verdict: code points throughout; trim strips U+0020 only; native

length and char_length count code points (length('😀a') is 2, a combining accent counts on its own), octet_length UTF-8 bytes. reverse reverses code points, so a combining accent moves to the other side of its letter. trim, btrim, ltrim, rtrim strip the space U+0020 only, never tabs or newlines; trim(BOTH 'x' FROM s) strips the given characters. ascii('é') is 233, the code point; ascii('') 0. repeat('ab', -1) is empty. levenshtein counts code points (levenshtein('héllo', 'hello') is 1).

Strings order and compare by UTF-8 bytes, which is code point order (UTF8String.binaryCompare), under the default UTF8_BINARY collation.

The reference: character_length counts code points; reverse reverses them; btrim(s) trims spaces only; strings order by code point. Spark is native.

Options: char_set.

Constructs: length, char_length, octet_length, reverse, trim, btrim, ltrim, rtrim, ascii, repeat, levenshtein.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-strpos: instr, locate, position: 1-based characters, 0 when absent

Engine: spark 4.3.0; area: strings; status: no-finding; evidence: probe.

Verdict: string_positions: 1-based code points; 0 without a match

instr('héllo', 'l'), locate('l', 'héllo') and position('l' IN 'héllo') are 3: code points from 1. Without a match they are 0. instr(s, '') is 1. locate(t, s, start) starts at start (1-based) and gives 0 for a start below 1. instr(s, t, position, occurrence) takes a 1-based start and the occurrence to find (1 by default), counting overlapping matches (instr('aaaa', 'aa', 1, 3) is 3). A negative position counts from the end and searches backwards: instr('abcabc', 'b', -1) is 5, instr('abcabc', 'b', -3) 2, instr('abcabc', 'b', -1, 2) 2. A position of 0, or one beyond either end (instr('abc', 'b', 10), instr('abc', 'b', -10)), gives 0. An empty needle gives 1 at any position, negative ones included (instr('hello', '', 3) is 1). A NULL position or occurrence gives NULL.

The reference: strpos(s, t) is the 1-based character position of the first match, 0 when there is none (#209). Spark is native.

Options: string_positions, negative_start.

Rows of the choice tables: str.match string_positions (string).

Constructs: instr, locate, position, charindex.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #209.

Provenance:

spark-substring: substring: 1-based characters; a start of 0 is 1; negative counts from the end

Engine: spark 4.3.0; area: strings; status: open; evidence: fuzz.

Verdict: negative_start: WRAP_FROM_END, 1-based code points; start 0 reads as 1

substring(s, pos, len) counts code points from 1. A negative pos counts from the end (substring('héllo', -3, 2) is ll), and a window that starts before the string is cut at its start (substring('héllo', -6, 2) is h, (-10, 3) empty). A pos of 0 reads as 1: substring('héllo', 0, 2) is hé, two characters. A negative or zero len gives the empty string. Neither mode raises.

The reference: substring(s, start, length), 1-based, negative_start: WRAP_FROM_END (#209), the window cut at the string’s ends. A start of 0 covers positions 0 and 1 of a length-2 window, so substring('héllo', 0, 2) is h. Spark agrees but at a start of 0, where it returns one character more; a writer maps a start of 0 (and a negative start that reaches before the string) to the reference’s window.

Options: negative_start, string_positions.

Rows of the choice tables: str.sub negative_start (string); str.sub string_positions (string).

Constructs: substring, substr, substring_index.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #209.

Provenance:

substring[0]: 300 rows, substring(s, p, l), 1200 of 1200 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json substring[0]

spark-substring-range: substring positions and lengths are INT: wider ones raise (ANSI) or wrap

Engine: spark 4.3.0; area: strings; status: open; evidence: fuzz.

Verdict: substring_range: a BIGINT past 32 bits raises CAST_OVERFLOW (ANSI) or wraps

substring’s position and length are INT; a BIGINT argument is cast. Under ANSI 4294967297 (2³² + 1) raises CAST_OVERFLOW; without ANSI it wraps to 1 (substring('hello', 4294967297, 2) is he). Any value from 2³¹ to 2³² raises under ANSI where the reference computes.

Within INT, the end of the window is clamped to the int range: substring('héllo', 2, 2147483647) is éllo.

The reference: substring_range: offsets outside [-2³², 2³²) raise (#336). Spark under ANSI raises from 2³¹ on, a stricter range: an extra raise where the reference returns a string.

Options: substring_range, raise_set.

Rows of the choice tables: str.sub substring_range (string).

Constructs: substring, substr.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #336.

Provenance:

substring_range[0]: 300 rows, substring(s, p, l), 794 of 794 results as the fact says (raised: CAST_OVERFLOW 30).

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json substring_range[0]

Spark: temporal

spark-date-range: Dates and timestamps compute past 9999; date_add wraps at 2³¹ days (SP-2)

Engine: spark 4.3.0; area: temporal; status: open; evidence: fuzz.

Verdict: date_range: no raise at 9999-12-31; day counts wrap; timestamps past 294247 raise a bare ArithmeticException

DATE is a 32-bit day count and TIMESTAMP/TIMESTAMP_NTZ a 64-bit microsecond count, so arithmetic goes past 9999-12-31 without a raise in either mode: DATE'9999-12-31' + 1 is +10000-01-01, TIMESTAMP'9999-12-31 23:59:59' + INTERVAL 1 DAY +10000-01-01 23:59:59, and before year 1 the proleptic Gregorian calendar goes on (DATE'0001-01-01' - 1 is 0000-12-31; make_date(0, 1, 1) is year 0).

date_add(d, n) adds the days as ints without a check, so a sum past 2³¹ - 1 days wraps: date_add(DATE'2024-01-01', 2147483647) is -5877587-06-21, in both modes (SP-2). Timestamps check: timestampadd(YEAR, 300000, …) raises DATETIME_OVERFLOW, but TIMESTAMP_NTZ'+294247-01-10 04:00:54.775807' + INTERVAL 1 DAY raises a bare java.lang.ArithmeticException: long overflow with no error class (SP-3).

Adding months clamps to the month’s last day (DATE'2024-01-31' + INTERVAL 1 MONTH is 2024-02-29). make_date(2024, 2, 30) raises under ANSI and is NULL without it.

The reference: date_range: date arithmetic raises outside 0001-01-01 … 9999-12-31 (#251); adding months keeps the day or takes the month’s last day (Time.addMonths). Spark needs a range guard on every result, and its wrap at 2³¹ days returns a wrong date where the reference raises.

Options: date_range, raise_set.

Rows of the choice tables: time.arith date_range (date, timestamp); time.arith date_range (timestamp_tz; evidence probe).

Constructs: date_add, date_sub, + INTERVAL, add_months, timestampadd, make_date.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: upstream ledger SP-2, SP-3, SP-6; issues #251.

Provenance:

date_range[0]: 300 rows, unix_date(date_add(date_from_unix_date(k), n)), unix_date(date_from_unix_date(k) + 1), 2400 of 2400 results as the fact says; date_range[1]: 300 rows, unix_micros(CAST(timestamp_micros(us) AS TIMESTAMP_NTZ) + make_dt_interval(n, 0, 0, 0)), 960 of 960 results as the fact says (raised: java.lang.ArithmeticException 60).

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json date_range[0], date_range[1]

spark-time-type: TIME is off by default; with it on, TIME + INTERVAL raises past midnight

Engine: spark 4.3.0; area: temporal; status: no-finding; evidence: probe.

Verdict: time_interval_months: TIME unsupported by default; + INTERVAL 1 MONTH a type error

Spark 4.3.0 parses TIME only with spark.sql.timeType.enabled on; by default TIME'12:00:00' raises UNSUPPORTED_TIME_TYPE. With it on, TIME'12:00:00' + INTERVAL 13 HOUR raises DATETIME_OVERFLOW rather than wrapping past midnight, in both modes, and TIME + INTERVAL 1 MONTH is rejected when the query is analysed (DATATYPE_MISMATCH.BINARY_OP_DIFF_TYPES).

The reference: TIME ± INTERVAL is refused (time_interval_months). Spark refuses TIME by default too.

Options: time_interval_months.

Rows of the choice tables: time.arith time_interval_months (time).

Constructs: TIME, + INTERVAL, - INTERVAL.

Settings: {"spark.sql.ansi.enabled": [true, false], "spark.sql.timeType.enabled": [false, true]}.

Provenance:

spark-time-zones: Time zones: the session’s zone for TIMESTAMP_LTZ; a DST gap shifts forward, an overlap takes the earlier offset

Engine: spark 4.3.0; area: temporal; status: no-finding; evidence: probe.

Verdict: LTZ in the session zone; gap → later wall time; overlap → earlier offset; bad zone raises

TIMESTAMP is TIMESTAMP_LTZ (an instant shown in spark.sql.session.timeZone) unless spark.sql.timestampType is TIMESTAMP_NTZ, as sverify sets it. Text and TIMESTAMP_NTZ values become instants in the session zone with java.time’s rules: in America/New_York, 2024-03-10 02:30 (in the spring gap) becomes 03:30 EDT, and 2024-11-03 01:30 (in the autumn overlap) the earlier offset, EDT (05:30Z).

A string with an offset cast to TIMESTAMP_NTZ drops the offset ('2024-01-05T10:00:00Z' and '…+02:00' both give 10:00). from_utc_timestamp, to_utc_timestamp and convert_timezone shift by a zone’s offset; an unknown zone raises INVALID_TIMEZONE in both modes.

The reference: Time zones are outside the model; sverify’s Spark session pins UTC and TIMESTAMP_NTZ (sverify.sql.spark.SPARK_CONF), so no plan depends on them.

Options: time_zones, settings.

Constructs: TIMESTAMP_LTZ, TIMESTAMP_NTZ, from_utc_timestamp, to_utc_timestamp, convert_timezone, spark.sql.session.timeZone.

Settings: {"spark.sql.ansi.enabled": [true, false], "spark.sql.session.timeZone": ["UTC", "America/New_York"], "spark.sql.timestampType": "TIMESTAMP_NTZ"}.

Provenance:

spark-timestamp-precision: Timestamps are microseconds; extra digits are dropped

Engine: spark 4.3.0; area: temporal; status: no-finding; evidence: probe.

Verdict: microseconds, -290308 … +294247; nanoseconds truncated on parse

TIMESTAMP_LTZ and TIMESTAMP_NTZ hold a 64-bit count of microseconds, from -290308-12-21 19:59:06.224192 to +294247-01-10 04:00:54.775807. Parsing 2024-01-01 00:00:00.123456789 keeps .123456 and drops the rest, in a cast and in a literal, without an error in either mode. Behind spark.sql.timestampNanosTypes.enabled, a preview flag that is off in production and on under Spark’s own tests (Utils.isTesting), Spark 4.3.0 also has TIMESTAMP_NTZ(p) and TIMESTAMP_LTZ(p) for p from 7 to 9: a cast to them keeps p digits and floors the rest (.123456789 as timestamp_ntz(7) is .1234567). The cast.sql cases rely on that test default; timestamp-ntz-nanos.sql sets the flag.

The reference: precision_timestamp at 0, 3, 6, 9 (#323); the reference’s timestamps are microseconds. Spark is native at 6; nanosecond timestamps lose digits.

Options: timestamp_precision.

Constructs: TIMESTAMP, TIMESTAMP_NTZ, TIMESTAMP_LTZ, CAST AS TIMESTAMP, unix_micros.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: upstream ledger SP-7; issues #323.

Provenance:

spark-week-parts: ISO weeks; dayofweek from Sunday; date_trunc('week') to Monday; floors before 1970

Engine: spark 4.3.0; area: temporal; status: no-finding; evidence: probe.

Verdict: ISO weekofyear, YEAROFWEEK, DOW_ISO; dayofweek 1 = Sunday; floor before the epoch

weekofyear and extract(WEEK …) are ISO weeks (2021-01-03 is week 53 of 2020), extract(YEAROFWEEK …) the ISO year, extract(DOW_ISO …) 1 = Monday … 7 = Sunday. dayofweek and extract(DOW …) count 1 = Sunday … 7 = Saturday, and weekday 0 = Monday … 6 = Sunday. date_trunc('week', …) and trunc(d, 'week') go back to Monday.

Before 1970 parts and truncations floor: date_trunc('second', 1969-12-31 23:59:59.5) is 23:59:59, extract(SECOND …) 59.500000 (a decimal). datediff counts days; timestampdiff(MONTH, 2024-01-31, 2024-02-29) is 0, whole months only.

date_format and to_date take Java DateTimeFormatter patterns; the week-based letters (Y, w, u, e) raise INCONSISTENT_BEHAVIOR_CROSS_VERSION in both modes. Spark has no date_bin; window(ts, '5 minutes') buckets from the epoch.

The reference: date_part and date_trunc (Time.lean): ISO weeks, Monday-based isodow, flooring before the epoch; strftime is opaque. Spark is native for the ISO parts; dayofweek is a different numbering a writer maps.

Options: week_numbering.

Constructs: weekofyear, dayofweek, weekday, extract, date_part, date_trunc, trunc, dayofyear, date_diff, timestampdiff, date_format.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

Spark: casts

spark-cast-double-to-int: Casts to integers truncate; out of range raises (ANSI) or saturates and wraps

Engine: spark 4.3.0; area: casts; status: no-finding; evidence: probe.

Verdict: float_to_int: TRUNCATE; cast_failure: raise (ANSI), else INT/BIGINT saturate, narrower wrap, NaN 0

A double or decimal cast to an integer truncates toward zero (1.9 → 1, -2.5 → -2), in both modes. Under ANSI a value outside the target’s range, NaN and ±inf raise CAST_OVERFLOW; try_cast gives NULL for them.

Without ANSI a double saturates at INT and BIGINT (CAST(1e20 AS INT) is 2147483647, -inf gives MIN) and gives 0 for NaN; TINYINT and SMALLINT go through INT and then wrap (CAST(130.0 AS TINYINT) is -126, CAST(3e9 AS TINYINT) -1). A decimal or BIGINT cast to a narrower integer wraps (CAST(300 AS TINYINT) is 44).

The reference: float_to_int: TRUNCATE (#209); cast_failure as the cast states, THROW_EXCEPTION for UNSPECIFIED (ST-6). Spark under ANSI is native; try_cast is RETURN_NULL.

Options: float_to_int, cast_failure.

Rows of the choice tables: float_to_int (any); cast_failure (any).

Constructs: CAST AS TINYINT, CAST AS SMALLINT, CAST AS INT, CAST AS BIGINT, try_cast.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #209.

Provenance:

spark-cast-string: Casts from and to strings: trimmed, lenient parsing; Java’s double text

Engine: spark 4.3.0; area: casts; status: open; evidence: fuzz.

Verdict: parse: trims, 1.5 → INT raises (ANSI) or truncates; doubles accept inf, nan, 1.5d, hex; text: 1.0E20

Parsing trims whitespace (spaces, tabs, newlines) and takes a sign. To integers: '1e3', '0x10', '12abc', '', '1_000' fail; '1.5' fails under ANSI and truncates to 1 without it. To doubles: 'nan', 'inf', 'Infinity', '-infinity', '+Inf' (any case), '1.5d', '1.5f', '0x1p3' (Java’s hex floats), '.5', '5.' parse, '-nan' fails; '1e400' is +inf and '1e-400' 0, '-10e-400' -0.0 (a negative below the smallest double keeps its sign). To decimals the value rounds half up ('1.005' → 1.01) and an exponent is allowed. To booleans: t, true, y, yes, 1 and their opposites, any case. To dates: 2024, 2024-01, 2024-1-5 and a trailing time parse; 2024-02-30 fails; +10000-01-01 parses.

A failure raises CAST_INVALID_INPUT under ANSI and is NULL without it; try_cast gives NULL in both.

Formatting: doubles print as Java’s Double.toString (1.0E20, 1.0E-5, 0.001, 1.0E7, 1.23456789E8, -0.0, NaN, Infinity); floats as Float.toString (0.1). Decimals keep their scale (1.50); under ANSI they print plainly (0.0000001000), without it in scientific notation (1.000E-7). Dates print with a sign past 9999, timestamps without trailing zeros (10:00:00.12), arrays as [1, null], structs as {1, null}.

The reference: Casts to and from strings follow the reference’s cast rules; cast_failure as the cast states. Spark’s parsing is more lenient (trimming, hex floats, 1.5d) and its double text is Java’s, so a writer checks the text before the cast and formats doubles itself.

Options: cast_parsing, cast_formatting, cast_failure.

Rows of the choice tables: cast_failure (any).

Constructs: CAST AS STRING, CAST AS INT, CAST AS DOUBLE, CAST AS DECIMAL, CAST AS BOOLEAN, CAST AS DATE, CAST AS TIMESTAMP, date_format, to_date.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

cast_string[0]: 300 rows, CAST(s AS INT), CAST(s AS DOUBLE), CAST(s AS BOOLEAN), try_cast(s AS INT), 3762 of 3762 results as the fact says (raised: CAST_INVALID_INPUT 90); cast_string[1]: 300 rows, CAST(x AS STRING), 1200 of 1200 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json cast_string[0], cast_string[1]

Spark: nulls

spark-concat: concat and || are NULL with a NULL argument; concat_ws skips NULLs

Engine: spark 4.3.0; area: nulls; status: no-finding; evidence: probe.

Verdict: null_handling: ACCEPT_NULLS native; concat_ws for IGNORE_NULLS

concat('a', NULL) and 'a' || NULL are NULL; concat_ws(',', 'a', NULL, 'b') is a,b, and concat_ws(',', NULL) the empty string. concat() with no arguments is the empty string. A NULL separator makes concat_ws NULL: concat_ws(NULL, 10, 20, NULL, 30) is NULL.

The reference: str_concat with null_handling: ACCEPT_NULLS (NULL in, NULL out); IGNORE_NULLS refused. Spark is native, and concat_ws gives IGNORE_NULLS.

Options: null_handling.

Rows of the choice tables: str.concat null_handling (string).

Constructs: concat, ||, concat_ws.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-kleene: AND, OR, NOT are Kleene’s; bool_and, bool_or skip NULLs

Engine: spark 4.3.0; area: nulls; status: no-finding; evidence: probe.

Verdict: three-valued logic; boolean aggregates skip NULLs, NULL when all are

NULL AND false is false, NULL OR true true, NOT NULL NULL. bool_and and bool_or (every, some) skip NULLs and are NULL over NULLs only. 1 IN (2, NULL) and 1 NOT IN (2, NULL) are NULL.

The reference: and, or, not are Kleene’s; bool_and and bool_or skip NULLs as aggregates do. Spark is native.

Options: three_valued_logic.

Constructs: AND, OR, NOT, bool_and, bool_or, every, some, any, IN, NOT IN.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-null-keys: Joins on = skip NULL keys; <=> and IS NOT DISTINCT FROM match them; grouping puts NULLs together

Engine: spark 4.3.0; area: nulls; status: no-finding; evidence: probe.

Verdict: = skips NULL; <=>, IS NOT DISTINCT FROM match; one NULL group

An equi-join on x = y drops NULL keys (1 match out of {1, NULL} × {1, NULL}); x <=> y and x IS NOT DISTINCT FROM y match them (2). GROUP BY puts NULLs in one group.

The reference: Grouping and DISTINCT put NULLs together; an equi-join’s = does not match NULL, and IS NOT DISTINCT FROM keys do. Spark is native and spells the null-safe key <=> or IS NOT DISTINCT FROM.

Options: null_join_keys.

Rows of the choice tables: null_join_keys (any).

Constructs: JOIN ON =, <=>, IS NOT DISTINCT FROM, GROUP BY.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

Spark: nested

spark-explode: explode drops NULL and empty arrays; explode_outer keeps a NULL row

Engine: spark 4.3.0; area: nested; status: no-finding; evidence: probe.

Verdict: unnest_null_empty: no row for NULL or []; OUTER adds one NULL row

LATERAL VIEW explode(a) gives one row per element (a NULL element included) and none for a NULL or empty array; LATERAL VIEW OUTER explode(a) (explode_outer) gives one row with a NULL element for each. Spark 4.3.0 also has ANSI UNNEST in FROM: UNNEST(a) gives no rows for a NULL or empty array, WITH ORDINALITY adds a 1-based bigint column, and UNNEST(a, b) zips several arrays, padding the shorter ones with NULL (a NULL array counts as empty). LEFT JOIN LATERAL UNNEST(a) ON true keeps one row with a NULL element for a NULL or empty array.

The reference: UNNEST: one row per element, none for an empty list or NULL. Spark’s explode is native.

Options: unnest_null_empty.

Constructs: explode, LATERAL VIEW, LATERAL VIEW OUTER, explode_outer, posexplode, inline.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-list-index: element_at is 1-based and counts back from the end; [] is 0-based; out of range raises (ANSI)

Engine: spark 4.3.0; area: nested; status: open; evidence: fuzz.

Verdict: element_at(a, i): 1-based, negative from the end, 0 raises; out of range: raise (ANSI) or NULL

element_at(a, i) counts from 1 and a negative i from the end (element_at([10, 20, 30], -1) is 30). An index of 0 raises INVALID_INDEX_OF_ZERO in both modes. Past either end it raises INVALID_ARRAY_INDEX_IN_ELEMENT_AT under ANSI and is NULL without it; try_element_at gives NULL out of range, but raises INVALID_INDEX_OF_ZERO at 0 too.

a[i] counts from 0 and has no negative indexes: a[5] and a[-1] raise INVALID_ARRAY_INDEX under ANSI and are NULL without it. get(a, i) is 0-based and NULL out of range in both modes.

size(NULL) and cardinality(NULL) are NULL under ANSI and -1 without it (spark.sql.legacy.sizeOfNull follows ANSI); array_size(NULL) is NULL in both.

The reference: list_extract(l, i) (reference-semantics.md, Nested) counts from 1, as DuckDB’s does. element_at matches it for positive indexes; a writer guards 0 and out-of-range indexes.

Options: list_indexing, raise_set, settings.

Constructs: element_at, a[i], get, try_element_at, size, cardinality, array_size.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

list_index[0]: 300 rows, element_at(a, i), a[i], get(a, i), try_element_at(a, i), size(a), array_size(a), 6326 of 6326 results as the fact says (raised: INVALID_ARRAY_INDEX 30, INVALID_ARRAY_INDEX_IN_ELEMENT_AT 28, INVALID_INDEX_OF_ZERO 92).

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json list_index[0]

spark-list-order: Arrays order element-wise with a NULL element lowest, in < and in sorts

Engine: spark 4.3.0; area: nested; status: open; evidence: fuzz.

Verdict: list_null_order: NULL element smallest, [1, NULL] < [1, 2] true; ascending sorts NULL list first

Arrays compare element by element, a shorter prefix first; a NULL element is smaller than any value and equal to another NULL. So array(1, NULL) < array(1, 2) is true (never NULL), array(NULL) < array(0) true, and array(1, NULL) = array(1, NULL) true. Ascending sorts put a NULL array first, then [], [NULL], [1], [1, NULL], [1, 2]; min and max of arrays follow the same order.

Inside an array, sort_array puts NULLs first ascending and last descending; array_sort puts them last.

The reference: list_null_order: NULL where a NULL element decides <, and GREATEST in ORDER BY (#207). Spark orders the NULL element lowest in both, so a writer refuses or rewrites list comparisons where an element may be NULL.

Options: list_null_order.

Rows of the choice tables: list.order list_null_order (list); agg.order list_null_order (list; evidence probe).

Constructs: <, ORDER BY, min, max, greatest, sort_array, array_sort.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #207.

Provenance:

  • 2026-11-05, probe, by sverify_dev.probe_spark on homelab (Spark characterization session, #738): sverify_dev.probe_spark runs each query four times on one local session: with spark.sql.ansi.enabled true and false, on the folded path (the optimizer evaluates a query over VALUES on the driver) and on the codegen path (ConvertToLocalRelation and ConstantFolding excluded, so the query runs as a job with whole-stage code generation). probes.json keeps one result per ANSI setting where the two paths agree. Spark 4.3.0 (pyspark 4.3.0), Java 21.0.12, Linux x86_64, local[2], session time zone UTC, spark.sql.timestampType=TIMESTAMP_NTZ.
  • 2026-11-05, engine-test, by sverify_dev.spark_tests (Spark evidence session): Spark’s SQL query tests at tag v4.3.0: the golden files under results/ run with ANSI on, those under results/nonansi/ with it off.
  • 2026-11-05, fuzz, by sverify_dev.spark_fuzz on homelab (Spark evidence session): python -m sverify_dev.spark_fuzz --rows 300 --seed 738: random rows leaning on the fact’s edges, each construct run with spark.sql.ansi.enabled on and off, on the folded and the codegen path, and compared with a model of this fact (value, result type, error class). 8400 of 8400 results as the fact says.

list_order[0]: 300 rows, a < b, a = b, a > b, greatest(a, b), sort_array(a), sort_array(a, false), array_sort(a), 8400 of 8400 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json list_order[0]

spark-nested-equality: Structs and arrays compare structurally, NULL fields equal

Engine: spark 4.3.0; area: nested; status: no-finding; evidence: probe.

Verdict: = on structs and arrays: structural, NULL fields and elements equal; ordered field by field

named_struct('x', 1, 'y', NULL) = named_struct('x', 1, 'y', NULL) is true, not NULL: struct and array equality treat NULL fields and elements as equal (the ordering’s comparison). Structs order field by field with a NULL field lowest; grouping puts equal structs, NULL fields included, in one group. NaN fields and elements are equal to NaN.

The reference: Equality is structural, and list equality treats NULLs as equal, as grouping does (reference-semantics.md, “Equality for grouping” and Nested). Spark is native.

Options: struct_equality.

Constructs: =, <=>, <, GROUP BY, ORDER BY, named_struct, array.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

Spark: aggregates

spark-avg-double: AVG of doubles: plain sum by the count

Engine: spark 4.3.0; area: aggregates; status: open; evidence: fuzz.

Verdict: plain sum / n

AVG(DOUBLE) keeps a sum and a count and returns Divide(sum, count, LEGACY), NULL for a count of 0. The sum is spark-sum-double’s.

avg of 1e16, 1, -1e16, 1 is 0.25 after a shuffle into one partition and 0.5 into three; of FLOAT it returns DOUBLE.

The reference: AggFn.avg is floatSum / n, NULL without values.

Options: sum_order.

Constructs: avg.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #54, #85.

Provenance:

It exercises avg of doubles as the target’s sum over the count.

aggregates[0]: 300 rows, sum(x), avg(x), max(x), min(x), 2880 of 2880 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json aggregates[0]

spark-avg-int: AVG of integers is a DOUBLE, summed in double

Engine: spark 4.3.0; area: aggregates; status: open; evidence: fuzz.

Verdict: avg_int_result: DOUBLE; the sum is a double, so 64-bit inputs lose digits

avg of any integer type returns DOUBLE, and its running sum is a DOUBLE too: avg(9007199254740993, 9007199254740993) is 9007199254740992.0, and avg of two BIGINT maxima is 9.223372036854776E18 without an overflow. No values give NULL.

The reference: avg of i64 returns i64 in Substrait; the reference follows the declared output type (avg_int_result, #209). Spark’s double result needs a cast back, which may be off where the sum passes 2⁵³; sverify.sql.spark computes the average exactly from a DECIMAL(38,0) sum.

Options: avg_int_result, empty_group_aggregates.

Rows of the choice tables: agg.isum avg_int_result (i64); agg.isum avg_int_result (i8, i16, i32; evidence probe).

Constructs: avg, mean, try_avg.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #209.

Provenance:

int_sums[2]: 300 rows, avg(x), 164 of 164 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json int_sums[2]

spark-corr: CORR: shared Welford state; NULL for one pair or zero variance

Engine: spark 4.3.0; area: aggregates; status: open; evidence: fuzz.

Verdict: ck / sqrt(xMk · yMk); NULL as in the reference; no clamp

One shared Welford state (ck, xMk, yMk); the result is ck / sqrt(xMk * yMk). One pair is NULL (NaN under spark.sql.legacy.statisticalAggregate); xMk == 0 or yMk == 0 is NULL. Not clamped to [-1, 1].

If xMk * yMk underflows to 0 with neither side 0, the division raises under ANSI mode (the 4.x default) and gives NULL otherwise.

The probes: one pair gives NULL (NaN with spark.sql.legacy.statisticalAggregate), a constant side NULL, in both ANSI modes.

The reference: AggFn.corr (corrOf): NULL for fewer than two pairs, or for a side whose values are all equal; else the co-deviation sum by the square root of the product of the two squared-deviation sums. It is not clamped to [-1, 1]. Engines may differ in the last bits (#54) until #85 bounds the error.

Options: stat_algo, stat_degenerate.

Rows of the choice tables: agg.stat stat_algo (fp64); agg.stat stat_algo (i64, decimal; evidence probe); agg.stat stat_degenerate (fp64); agg.stat stat_degenerate (i64, decimal; evidence probe).

Constructs: corr.

Settings: {"spark.sql.ansi.enabled": [true, false], "spark.sql.legacy.statisticalAggregate": [false, true]}.

See: upstream ledger ST-3; issues #54, #85.

Provenance:

covar_corr[1]: 300 rows, corr(x, y), 204 of 204 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json covar_corr[1]

spark-covar: COVAR_*: Welford co-moment, Chan merge

Engine: spark 4.3.0; area: aggregates; status: open; evidence: fuzz.

Verdict: Welford co-moment; Chan merge

ck += dx * (y - newYAvg); partial states merge as ck.l + ck.r + dx * dyN * n1 * n2. No pairs is NULL; the sample covariance of one pair is NULL (NaN under spark.sql.legacy.statisticalAggregate).

The probes: one pair gives NULL for covar_samp and 0.0 for covar_pop (NaN for covar_samp with spark.sql.legacy.statisticalAggregate); a constant side 0.0; BIGINT and decimal inputs are cast to DOUBLE.

The reference: AggFn.cov (covOf) is two-pass: the two means, then the products of the deviations added in list order from +0.0, by n or n - 1. No pairs is NULL, and so is a sample of one pair. Engines may differ in the last bits (#54) until #85 bounds the error.

Options: stat_algo.

Rows of the choice tables: agg.stat stat_algo (fp64); agg.stat stat_algo (i64, decimal; evidence probe).

Constructs: covar_samp, covar_pop.

Settings: {"spark.sql.ansi.enabled": [true, false], "spark.sql.legacy.statisticalAggregate": [false, true]}.

See: issues #54, #85.

Provenance:

covar_corr[0]: 300 rows, covar_samp(x, y), covar_pop(x, y), 408 of 408 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json covar_corr[0]

spark-first-last: first, last, any_value, mode, min_by/max_by take a row that depends on the partitioning

Engine: spark 4.3.0; area: aggregates; status: no-finding; evidence: probe.

Verdict: tie_pick: input order within a partition, partitions in completion order; mode any of the tie

first(x) and last(x) take the first and last row in the order the rows reach the aggregate, NULL included unless ignoreNulls is true (first(x, true)). Over one partition that is input order; after a shuffle it is the order of the partitions’ rows, which Spark documents as non-deterministic (over a 3-partition range(0, 1000) they gave 279 and 635). min_by(v, k) and max_by(v, k) take the row with the least or greatest k, among ties the last one seen in the probes (b and d); they skip NULL keys.

mode(x) returns one of the most frequent values, any of a tie (maxBy over a hash map); mode(x, true) the lowest; mode() WITHIN GROUP (ORDER BY x DESC) the highest. NULLs are skipped.

collect_list, array_agg, string_agg and listagg keep the order the rows arrive in, NULLs skipped (3, 1, 2 in input order; a different order after a shuffle); listagg(...) WITHIN GROUP (ORDER BY ...) sorts. collect_set has no stated order.

The reference: tie_pick: any row of the group (each measure may take its own); tie_order: any order for string_agg and array_agg. Every Spark choice is allowed.

Options: tie_pick, tie_order, parallel_nondeterminism.

Rows of the choice tables: agg.pick tie_pick (any); agg.seq tie_order (any).

Constructs: first, last, any_value, first_value, min_by, max_by, mode, collect_list, array_agg, string_agg, listagg, collect_set.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-nonfinite-aggregates: Aggregates of ±inf and NaN: IEEE results, no raise

Engine: spark 4.3.0; area: aggregates; status: open; evidence: fuzz.

Verdict: sum(inf, -inf) NaN; variance(inf, 1) NaN; sum(1e308, 1e308) +inf; NaN propagates

sum and avg of inf and -inf are NaN; variance and stddev of a set with an infinity are NaN; sum(1e308, 1e308) and avg are +inf, while variance(1e308, 1e308) is 0.0. A NaN input makes sum, avg, variance NaN; min skips it (it sorts last) and max returns it. None of this raises under ANSI.

The reference: nan_creation: raise (#257); a statistical aggregate of ±inf raises (stat_degenerate); a sum past the largest double raises (#575). ST-3 and ST-5 record that Substrait leaves both open. Spark needs guards.

Options: nonfinite_aggregates, nan_creation, float_overflow, stat_degenerate.

Rows of the choice tables: agg.fsum nan_creation (fp64); agg.fsum nan_creation (fp32; evidence probe); agg.fsum float_overflow (fp64); agg.fsum float_overflow (fp32; evidence probe).

Constructs: sum, avg, variance, stddev, min, max, median.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #257, #575.

Provenance:

nonfinite[0]: 300 rows, sum(x), avg(x), max(x), min(x), var_samp(x), 820 of 820 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json nonfinite[0]

spark-quantile: percentile_cont and median interpolate linearly; percentile_disc takes the lower value, as a double

Engine: spark 4.3.0; area: aggregates; status: open; evidence: fuzz.

Verdict: quantile_interp: cont linear at (n - 1)·p; disc the value at ⌈n·p⌉, returned as DOUBLE

median, percentile(x, p) and percentile_cont(p) WITHIN GROUP (ORDER BY x) interpolate linearly at position (n - 1)·p: over 1, 2, 3, 10 the median is 2.5 and p = 0.25 1.75. percentile_disc(p) takes the first value whose cumulative share reaches p (1 at 0.25, 2 at 0.26 over 1, 2, 3, 4). All three return DOUBLE, for integer and decimal inputs too (median of two DECIMAL(10,2) is 1.675 as a double). percentile_approx is approximate and returns the input type.

NULLs are skipped; NaN sorts last (spark-nan-order). The fraction must be a constant. Under spark.sql.legacy.percentileDiscCalculation (off by default) percentile_disc picks other values.

The reference: quantile_disc takes a value of the input, of its type; quantile_cont any double between the neighbours (contAllowed). Spark’s positions match; percentile_disc of integers and decimals needs a cast back to the input type, exact for integers below 2⁵³.

Options: quantile_interp.

Rows of the choice tables: agg.quant quantile_interp (i64); agg.quant quantile_interp (fp64, decimal; evidence probe).

Constructs: median, percentile, percentile_cont, percentile_disc, percentile_approx.

Settings: {"spark.sql.ansi.enabled": [true, false], "spark.sql.legacy.percentileDiscCalculation": false}.

Provenance:

quantile[0]: 300 rows, percentile_cont(0.0) WITHIN GROUP (ORDER BY x), percentile_disc(0.0) WITHIN GROUP (ORDER BY x), percentile_cont(0.25) WITHIN GROUP (ORDER BY x), percentile_disc(0.25) WITHIN GROUP (ORDER BY x), percentile_cont(0.5) WITHIN GROUP (ORDER BY x), percentile_disc(0.5) WITHIN GROUP (ORDER BY x),`, 2244 of 2244 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json quantile[0]

spark-sum-double: SUM of doubles: plain, order by partitioning

Engine: spark 4.3.0; area: aggregates; status: open; evidence: fuzz.

Verdict: plain recursive summation; order depends on partitioning

SUM(DOUBLE) adds with a plain Add and no overflow check; partial sums merge plainly. The order of the additions depends on the partitioning.

The probes show the order at work: 1e16, 1, -1e16, 1 summed in input order on one partition is 0.0; after a shuffle into one partition 1.0, into three partitions 2.0. A FLOAT column sums in DOUBLE (sum returns DOUBLE). No values, or NULLs only, give NULL.

The reference: AggFn.fsum (floatSum) adds the doubles one by one in list order from +0.0; the writers that need that order exactly use Dialect.float_sum. NULL without values.

Options: sum_order, rounding, empty_group_aggregates, parallel_nondeterminism.

Rows of the choice tables: agg.fsum sum_order (fp64); agg.fsum sum_order (fp32; evidence probe); agg.fsum rounding (fp64); agg.fsum rounding (fp32; evidence probe); agg.fsum empty_group_aggregates (fp64); agg.fsum empty_group_aggregates (fp32; evidence probe).

Constructs: sum.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #54, #85.

Provenance:

aggregates[0]: 300 rows, sum(x), avg(x), max(x), min(x), 2880 of 2880 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json aggregates[0]

spark-sum-int: SUM of integers returns BIGINT; 64-bit partial sums raise (ANSI) or wrap

Engine: spark 4.3.0; area: aggregates; status: open; evidence: fuzz.

Verdict: i8…i32 widen to BIGINT; i64: ERROR on a partial sum (ANSI), order-dependent; no values NULL

sum of TINYINT, SMALLINT, INT and BIGINT returns BIGINT, so the narrow widths cannot overflow (two INT maxima sum to 4294967294). A BIGINT sum adds in BIGINT with the + of the mode: under ANSI it raises ARITHMETIC_OVERFLOW as soon as a partial sum passes 64 bits, without ANSI it wraps. Whether a partial sum overflows depends on the order and the partitioning: 2⁶³ - 1, 1, -2 in one partition in that order raises, in the reverse order (or split over two partitions) it gives 2⁶³ - 2.

sum of no rows, or of NULLs only, is NULL; count(x) skips NULLs and is 0 there. try_sum gives NULL on overflow.

The reference: sum of integers returns the input type, with overflow: ERROR on the true total (an inferred precondition checked on the data, #316, #437), and NULL for no values. Spark widens i8…i32 (a cast back to the width raises where the reference does), and for i64 raises on partial sums: sverify.sql.spark adds DECIMAL(38,0) values instead and checks the total.

Options: overflow, empty_group_aggregates, parallel_nondeterminism.

Rows of the choice tables: agg.isum overflow (i32, i64); agg.isum overflow (i8, i16; evidence probe); agg.isum empty_group_aggregates (i32, i64); agg.isum empty_group_aggregates (i8, i16; evidence probe).

Constructs: sum, try_sum, count.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #316, #437.

Provenance:

int_sums[0]: 300 rows, sum(x), try_sum(x), 858 of 858 results as the fact says (raised: ARITHMETIC_OVERFLOW 18); int_sums[1]: 300 rows, sum(x), 164 of 164 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json int_sums[0], int_sums[1]

spark-var-stddev: VAR_* and STDDEV_*: Welford/West, Chan merge

Engine: spark 4.3.0; area: aggregates; status: open; evidence: fuzz.

Verdict: Welford/West; Chan merge; no clamp

All state is DoubleType, the count too. The update is Welford/West: delta = x - avg, deltaN = delta / (n + 1), avg += deltaN, m2 += delta * (delta - deltaN). Partial states merge as m2.l + m2.r + delta * deltaN * n1 * n2, with delta forced to 0 when a side is empty.

No values is NULL; the sample variance of one value is NULL (NaN under spark.sql.legacy.statisticalAggregate). No clamp.

var_samp/variance/stddev_samp/stddev/std take the sample (distribution: SAMPLE), var_pop/stddev_pop the population. Every input type, BIGINT and decimals included, is cast to DOUBLE, and the result is DOUBLE. The probes: one value gives NULL for the sample forms and 0.0 for the population ones (NaN for the sample forms with spark.sql.legacy.statisticalAggregate); 1e9 + {4, 7, 13, 16} gives exactly 30 and 22.5, Welford’s update cancelling no digits there.

The reference: AggFn.var (varOf, SubstraitLean/Aggregate.lean) is two-pass with native doubles in list order: the mean (floatSum / n), then the squared deviations from it, added from +0.0, by n or n - 1, and never below 0. A sample of one value is NULL. Only NULL and the sign are exact; engines may differ in the last bits (#54) until #85 bounds the error.

Options: stat_algo, stat_degenerate, rounding.

Rows of the choice tables: agg.var distribution (fp64); agg.var distribution (i64, decimal; evidence probe); agg.var rounding (fp64); agg.var rounding (i64, decimal; evidence probe); agg.var stat_algo (fp64); agg.var stat_algo (i64, decimal; evidence probe); agg.var stat_degenerate (fp64); agg.var stat_degenerate (i64, decimal; evidence probe).

Constructs: var_samp, var_pop, stddev_samp, stddev_pop, variance, stddev.

Settings: {"spark.sql.ansi.enabled": [true, false], "spark.sql.legacy.statisticalAggregate": [false, true]}.

See: issues #54, #85.

Provenance:

aggregates[1]: 300 rows, var_samp(x), var_pop(x), stddev_samp(x), 1440 of 1440 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json aggregates[1]

Spark: windows

spark-rank: Ranking windows: SQL’s rank, ntile spreads the remainder to the first buckets

Engine: spark 4.3.0; area: windows; status: no-finding; evidence: probe.

Verdict: SQL ranks, NULLs ranked first ascending; ntile front-loads; ties in arrival order

rank, dense_rank, percent_rank and cume_dist follow SQL: NULL keys are ranked, first in ascending order (two NULLs share rank 1). row_number numbers ties in the order the rows arrive, which after a shuffle varies (spark-window-values).

ntile(n) gives the first rows mod n buckets one row more: 7 rows into 3 buckets are 3, 2, 2; ntile(10) over 7 rows numbers them 1 to 7. ntile(0) is rejected when the query is analysed.

The reference: rank, dense_rank, row_number over the window’s order, NULLs placed by the key’s direction; percent_rank, cume_dist, ntile as SQL; ties in any order (tie_order). Spark is native.

Options: ntile_spread, tie_order, default_window_frame.

Rows of the choice tables: win.rank tie_order (any); win.ntile ntile_spread (any); win.ntile tie_order (any).

Constructs: row_number, rank, dense_rank, percent_rank, cume_dist, ntile.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-window-values: Window frames: RANGE … CURRENT ROW with an order, the whole partition without

Engine: spark 4.3.0; area: windows; status: no-finding; evidence: probe.

Verdict: default_window_frame: SQL’s; lag, lead, nth_value as SQL; ties in arrival order

Without a frame, a window with ORDER BY uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so peers are included (sum over keys 1, 2, 2, 3 gives 10, 60, 60, 100, and last_value is the last peer); without ORDER BY the frame is the whole partition. lag, lead take a default; nth_value(x, 2) is NULL until the frame has two rows and takes IGNORE NULLS. A RANGE frame over doubles treats NaN as a peer of NaN only.

Ties in the window order get the order the rows arrive in: row_number() OVER (ORDER BY g) after a 3-way shuffle numbered ids 2, 10, 4, 8, 6, 0 for g = 0.

The reference: lag, lead, first_value and windowed aggregates over the window’s order and frame; ties in any order (tie_order). Spark is native.

Options: default_window_frame, tie_order, parallel_nondeterminism.

Rows of the choice tables: win.value tie_order (any).

Constructs: sum OVER, count OVER, first_value, last_value, lag, lead, nth_value.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

Spark: relations

spark-clustered: DISTRIBUTE BY, SORT BY, CLUSTER BY: equal keys in one partition, sorted within it

Engine: spark 4.3.0; area: relations; status: no-finding; evidence: probe.

Verdict: clustered: DISTRIBUTE BY k puts equal keys together; SORT BY sorts each partition

DISTRIBUTE BY k hash-partitions so equal keys share a partition; SORT BY k sorts within each partition; CLUSTER BY k is both. The output keeps equal keys adjacent with no global order.

The reference: SORT_DIRECTION_CLUSTERED: equal keys adjacent, groups in any order; the parser refuses it for now. CLUSTER BY gives it, as does a full sort.

Options: clustered.

Rows of the choice tables: clustered (any).

Constructs: DISTRIBUTE BY, SORT BY, CLUSTER BY, REPARTITION.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-null-order: NULLs first ascending, last descending

Engine: spark 4.3.0; area: relations; status: no-finding; evidence: probe.

Verdict: null_order: NULLS FIRST for ASC, NULLS LAST for DESC

Without NULLS FIRST or NULLS LAST, an ascending key puts NULLs first and a descending one last. Both clauses are honoured.

The reference: The key’s direction states the NULL placement; SORT_DIRECTION_UNSPECIFIED is NULLS_LAST (D8). Spark is native for every stated direction; its default ascending placement differs from DuckDB’s.

Options: null_order.

Rows of the choice tables: null_order (any).

Constructs: ORDER BY, NULLS FIRST, NULLS LAST, sort_array.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-set-operations: UNION, INTERSECT, EXCEPT, with and without ALL: NULLs equal

Engine: spark 4.3.0; area: relations; status: no-finding; evidence: probe.

Verdict: SQL’s set operations; NULLs (and NaN, ±0.0) equal; no order

UNION deduplicates and UNION ALL keeps every row; INTERSECT and EXCEPT deduplicate; INTERSECT ALL keeps min(m, n) copies and EXCEPT ALL max(m - n, 0). NULLs compare equal, so do NaN and the two zeros. UNION ALL of two small inputs came back in input order; nothing guarantees it.

The reference: UNION ALL is bag union and UNION DISTINCT its distinct; INTERSECT and EXCEPT are semi and anti joins with NULLs equal, then DISTINCT, and the ALL forms number equal rows (#344). Spark is native.

Options: set_operations, null_join_keys.

Constructs: UNION, UNION ALL, INTERSECT, INTERSECT ALL, EXCEPT, EXCEPT ALL, MINUS.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #344.

Provenance:

spark-sort-stability: Sorts are not stable across partitions; ties come in partition order

Engine: spark 4.3.0; area: relations; status: no-finding; evidence: probe.

Verdict: tie_order: ties in an order that changes with the partitioning and between runs

A global sort range-partitions the rows and sorts each partition; equal keys keep the order they arrive in from the shuffle, which depends on the partitioning and on task timing. On one partition the sort keeps input order (b, d before a, c, e). After a 3-way shuffle the same query gave its ties in different orders on the folded and the codegen paths and between two runs of the probes, so a LIMIT on ties picks different rows.

The reference: tie_order: any order of the ties, and any rows of a tie for a fetch. Every behaviour is allowed; sverify’s Spark engine refuses plans that need a table’s row order unless tie_free=True.

Options: tie_order, parallel_nondeterminism.

Rows of the choice tables: tie_order (any).

Constructs: ORDER BY, LIMIT, sort.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

  • 2026-11-05, probe, by sverify_dev.probe_spark on homelab (Spark characterization session, #738): sverify_dev.probe_spark runs each query four times on one local session: with spark.sql.ansi.enabled true and false, on the folded path (the optimizer evaluates a query over VALUES on the driver) and on the codegen path (ConvertToLocalRelation and ConstantFolding excluded, so the query runs as a job with whole-stage code generation). probes.json keeps one result per ANSI setting where the two paths agree. Spark 4.3.0 (pyspark 4.3.0), Java 21.0.12, Linux x86_64, local[2], session time zone UTC, spark.sql.timestampType=TIMESTAMP_NTZ.

Spark: errors

spark-raise-set: Where Spark raises beyond the reference, and where it errors without an error class

Engine: spark 4.3.0; area: errors; status: open; evidence: fuzz.

Verdict: extra raises: INT positions, ANSI string casts, element_at(…, 0), regexes, week patterns; bare Java exceptions (SP-3, SP-4)

Under ANSI Spark raises where the reference computes in these places: a substring position or length from 2³¹ to 2³² (CAST_OVERFLOW, spark-substring-range); an implicit cast of a string in arithmetic or a comparison (1 + '1.5', 1 = '1.0': CAST_INVALID_INPUT, spark-implicit-casts); a[i] out of range. In both modes: element_at(a, 0), an invalid regex or group index, date_format week letters, ntile(0), a DECIMAL(39,…) type, and -x of a 38-digit decimal (SP-1).

Most errors carry an error class and SQLSTATE. Two do not: timestamp arithmetic past the microsecond range raises java.lang.ArithmeticException: long overflow (SP-3), and an invalid constant regex on the codegen path raises java.util.regex.PatternSyntaxException (SP-4). Two more raise without a class in both modes: to_timestamp('294248', 'y'), a year past the microsecond range, raises java.lang.ArithmeticException: long overflow (SP-6), and TIMESTAMP_SECONDS(0.1234567), a decimal with more than six fractional digits, raises java.lang.ArithmeticException: Rounding necessary (SP-7), where a double truncates to microseconds. Planning errors (DATATYPE_MISMATCH, UNSUPPORTED_TIME_TYPE) come when the query is analysed, before any row is read.

Silent wrong results, the opposite case: date_add wraps (SP-2), decimal negation rounds (SP-1), floor(NaN) is 0 under ANSI (SP-5).

The reference: The reference raises only where its options say (D2); an engine’s extra raises are allowed where they happen only on rows the reference would also reject, and otherwise make a plan that verifies fail at run time. A writer avoids each listed form or guards it.

Options: raise_set.

Constructs: substring, element_at, a[i], CAST, +, =, RLIKE, regexp_extract, date_format, make_date, ntile, -x.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: upstream ledger SP-6, SP-7.

Provenance:

It exercises the raising rows: substring positions past 32 bits, element_at at 0 and out of range, a 38-digit decimal negated, floor of ±inf, timestamps past the microsecond range, and n + s with a string.

raise_set[0]: 300 rows, n + s, element_at(a, i), 1812 of 1812 results as the fact says (raised: CAST_INVALID_INPUT 30, INVALID_ARRAY_INDEX_IN_ELEMENT_AT 21, INVALID_INDEX_OF_ZERO 39); substring_range[0]: 300 rows, substring(s, p, l), 794 of 794 results as the fact says (raised: CAST_OVERFLOW 30); list_index[0]: 300 rows, element_at(a, i), a[i], get(a, i), try_element_at(a, i), size(a), array_size(a), 6326 of 6326 results as the fact says (raised: INVALID_ARRAY_INDEX 30, INVALID_ARRAY_INDEX_IN_ELEMENT_AT 28, INVALID_INDEX_OF_ZERO 92); decimal_negate[0]: 300 rows, -x, abs(x), 0 - x, 3536 of 3536 results as the fact says (raised: NUMERIC_VALUE_OUT_OF_RANGE.WITHOUT_SUGGESTION 120); Spark differs from Rel.eval on 394 of 554; round[0]: 300 rows, round(x, 0), bround(x, 0), round(x, 1), bround(x, 1), round(x, 2), bround(x, 2), round(x, -2), bround(x, -2), floor(x), ceil(x), 11956 of 11956 results as the fact says (raised: ARITHMETIC_OVERFLOW 60); Spark differs from Rel.eval on 41 of 1200; date_range[1]: 300 rows, unix_micros(CAST(timestamp_micros(us) AS TIMESTAMP_NTZ) + make_dt_interval(n, 0, 0, 0)), 960 of 960 results as the fact says (raised: java.lang.ArithmeticException 60).

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json raise_set[0], substring_range[0], list_index[0], decimal_negate[0], round[0], date_range[1]

Spark: execution

spark-exec-evaluation-order: CASE, IF, COALESCE are lazy; AND, OR short-circuit left to right; filters can move below projections

Engine: spark 4.3.0; area: execution; status: no-finding; evidence: probe.

Verdict: a guarded branch never raises; b <> 0 AND a div b > 1 is safe, the reverse raises; pushdown only removes raises

CASE WHEN b <> 0 THEN a div b END, if(b = 0, NULL, a div b) and CASE WHEN c RLIKE '^[0-9]+$' THEN CAST(c AS INT) END evaluate the guarded expression only on the rows that reach it, under ANSI too, also when the same subexpression appears twice in a branch or in two CASEs (common subexpression elimination keeps conditional ones lazy). COALESCE stops at the first non-NULL argument.

AND and OR evaluate left to right and short-circuit: WHERE b <> 0 AND a div b > 1 runs, WHERE a div b > 1 AND b <> 0 raises DIVIDE_BY_ZERO under ANSI. Stacked filters stay in order when the optimizer combines them (CombineFilters puts the lower condition first): a filter on CAST(c AS INT) above one on c RLIKE … runs, and so does a join filter above a guard.

The optimizer pushes a filter below a projection, so SELECT * FROM (SELECT a div b AS q, b …) WHERE b <> 0 raises on the folded path (the projection runs first on the driver) and runs on the codegen path (the filter moved below the division). That removes raises and adds none.

The reference: The reference evaluates an expression only on the rows its operator sees: a CASE branch on the rows that reach it, a filter’s condition on the rows below it. Its issue analysis trusts that CASE is lazy and AND/OR are not (ST-12). Spark keeps each guard the reference’s analysis relies on; its AND is lazier than the model assumes.

Options: evaluation_order.

Constructs: CASE, IF, COALESCE, AND, OR, WHERE, JOIN.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-exec-parallel: Results that change with partitioning and timing

Engine: spark 4.3.0; area: execution; status: open; evidence: fuzz.

Verdict: double SUM/AVG, BIGINT sum overflow, first/last, collect_list, sort and window ties

Spark runs each partition’s partial aggregate in a task and merges them in the order the tasks finish, so these depend on the partitioning and can change from run to run: the order of a double sum and avg (spark-sum-double), whether a BIGINT partial sum overflows (spark-sum-int), the row first, last, any_value take and the order collect_list and string_agg keep (spark-first-last), and the order of ties in sorts and windows (spark-sort-stability, spark-window-values). The probes caught a sort’s ties in a different order on two runs.

The reference: The reference compares results as bags, so row order outside a sort is free; sum_order is open and allowed within the plan’s bound; tie_pick and tie_order allow any choice. Every Spark behaviour here is allowed but for the BIGINT sum, which sverify.sql.spark computes in decimals.

Options: parallel_nondeterminism.

Constructs: sum, avg, first, last, collect_list, string_agg, ORDER BY, row_number.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

  • 2026-11-05, probe, by sverify_dev.probe_spark on homelab (Spark characterization session, #738): sverify_dev.probe_spark runs each query four times on one local session: with spark.sql.ansi.enabled true and false, on the folded path (the optimizer evaluates a query over VALUES on the driver) and on the codegen path (ConvertToLocalRelation and ConstantFolding excluded, so the query runs as a job with whole-stage code generation). probes.json keeps one result per ANSI setting where the two paths agree. Spark 4.3.0 (pyspark 4.3.0), Java 21.0.12, Linux x86_64, local[2], session time zone UTC, spark.sql.timestampType=TIMESTAMP_NTZ.
  • 2026-11-05, fuzz, by sverify_dev.spark_fuzz on homelab (Spark evidence session): python -m sverify_dev.spark_fuzz --rows 300 --seed 738: random rows leaning on the fact’s edges, each construct run with spark.sql.ansi.enabled on and off, on the folded and the codegen path, and compared with a model of this fact (value, result type, error class). 3902 of 3902 results as the fact says.

It exercises the sums and averages with spark.sql.leafNodeDefaultParallelism at 1, 2 and 3, each scan slice summed in input order and the partials merged in either order.

aggregates[0]: 300 rows, sum(x), avg(x), max(x), min(x), 2880 of 2880 results as the fact says; int_sums[0]: 300 rows, sum(x), try_sum(x), 858 of 858 results as the fact says (raised: ARITHMETIC_OVERFLOW 18); int_sums[2]: 300 rows, avg(x), 164 of 164 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json aggregates[0], int_sums[0], int_sums[2]

spark-exec-parser-cache: The SQL parser’s DFA cache is unbounded unless Spark manages it

Engine: spark 4.3.0; area: execution; status: shimmed; evidence: probe.

Verdict: A large statement pins hundreds of MB of driver heap until spark.sql.parser.manageParserCaches is on

Spark parses SQL with ANTLR, whose DFA cache is static: it lives as long as the driver JVM and grows with every statement, the more for deeply nested ones. Spark 4.1 added its own cache management behind spark.sql.parser.manageParserCaches (internal, default false): with it on, parserDfaCacheFlushThreshold (a number of DFA states) or parserDfaCacheFlushRatio (a percentage of the driver’s memory, about 9,700 bytes per state) flushes the cache after a parse that exceeds it. Both are -1, off, by default.

With the defaults, parsing the 51 kB statement sverify wrote for the coverage case month end (ts.date().truncate("M") + 1 month - 1 day, #821) took the live heap after a full GC from 469 MB to 863 MB of a 1 GB driver. It stayed there for the rest of the session: later queries failed with [AGGREGATE_OUT_OF_MEMORY] or “Not enough memory to build and broadcast the table”, and the JVM ended in GC thrash. With manageParserCaches=true and parserDfaCacheFlushRatio=10, the same session stayed at about 100 MB, at most 154 MB over about 1,000 queries.

Results do not change: the failure is the query’s, not a wrong answer. It shows only when one JVM parses many statements, as a long-lived session or a single-process test run does.

The reference: The reference has no notion of a parser or of driver memory; a plan’s result does not depend on the statements parsed before it.

Options: settings, size_limits.

Constructs: spark.sql, spark.sql.parser.manageParserCaches, spark.sql.parser.parserDfaCacheFlushRatio, spark.sql.parser.parserDfaCacheFlushThreshold.

Settings: {"spark.driver.memory": ["1g"], "spark.sql.parser.manageParserCaches": [false, true], "spark.sql.parser.parserDfaCacheFlushRatio": [-1, 10]}.

See: issues #783, #821; pull requests #816.

Provenance:

  • 2026-11-05, source-reading, by Spark OOM investigation for the 0.5.0 release (#816): Read at tag v4.3.0 (commit 98bc0212).
  • 2026-11-05, manual-repro, by Spark OOM investigation for the 0.5.0 release (#816), on homelab: Spark 4.3.0 (pyspark 4.3.0), OpenJDK 21.0.12, Linux x86_64 (homelab), local[2], default 1 GB driver. Open: the macOS jobs of the same Wheels run (macos-15, Python 3.13 and 3.15) and a macOS arm64 laptop (OpenJDK 21.0.13) ran the same tests in one process and passed; why the parse pinned less memory there is not known.
    • test python/tests/test_backends.py::test_spark_agrees_with_duckdb Run with the ci profile in one process together with the Spark tests of test_nodes.py and test_sql_spark.py, as the Wheels test-pythons job runs them. Live heap after System.gc() before each query: 333 MB at query 129, 469 MB at 137, then 863 MB from 138 on: query 137 was the month end statement. Lowering spark.sql.ui.retainedExecutions to 10 or spark.sql.codegen.cache.maxEntries to 1, or a Python gc.collect(), left it at 860 MB; manageParserCaches=true with parserDfaCacheFlushRatio=10 kept it near 100 MB.
    • test python/tests/test_sql_spark.py::test_matches_lean Failed with [AGGREGATE_OUT_OF_MEMORY] in every Linux job of the v0.5.0 Wheels run (https://github.com/AlexanderNenninger/substrait-lean/actions/runs/42027728771) after test_spark_agrees_with_duckdb had parsed the statement in the same JVM.
    • in python/src/sverify/spark_engine.py LOCAL_CONF turns the management on for sverify’s local session (#816).

spark-exec-platform: Platform dependence: java.lang.Math intrinsics, the NaN sign, the JVM locale

Engine: spark 4.3.0; area: execution; status: open; evidence: fuzz.

Verdict: last bits of sqrt… atan2 may vary; created NaN negative on x86-64; probes on Linux x86-64, Java 21

The probes ran on Linux x86-64 with OpenJDK 21.0.12. exp, the logarithms and power use StrictMath (fdlibm), the same on every JVM and CPU; the trigonometric functions, cbrt and the hyperbolic ones use java.lang.Math, which a JVM may compute with CPU intrinsics within 1 ulp, so their last bit may differ between x86-64 and aarch64 or between JVMs (spark-elem-rounding). A NaN created by arithmetic has the sign bit set on x86-64 and clear on aarch64 (spark-nan-sign). upper and lower follow the JVM’s default locale (spark-case). Integer and decimal arithmetic, casts and round are the same everywhere. A double’s text depends on the JVM: Double.toString gives the shortest digits that round-trip since JDK 19 and sometimes longer ones before. Spark turns a double into a DECIMAL through that text, so a POWER result stored in a DECIMAL(38,10) is 107511333880051872 on older JVMs and 107511333880051870 on JDK 21, and postgreSQL/numeric.sql keeps a second golden file for JDK 21.

The reference: The reference treats the elementary functions as black boxes with stated laws, so a last-bit difference is allowed; it has one NaN.

Options: platform.

Constructs: sin, cos, tan, asin, acos, atan, atan2, cbrt, NaN, upper, lower.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

It exercises the last bits of the Math and StrictMath functions against the C library’s, and the sign of a created NaN, on Linux x86-64.

elem_rounding[0]: 300 rows, exp(x), sqrt(x), sin(x), cos(x), tan(x), atan(x), cbrt(x), 8400 of 8400 results as the fact says; Spark differs from Rel.eval on 92 of 1500; elem_rounding[1]: 300 rows, ln(x), log10(x), log2(x), 3600 of 3600 results as the fact says; nan_sign[0]: 300 rows, -a, abs(a), a * -1.0D, b - b, 4800 of 4800 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json elem_rounding[0], elem_rounding[1], nan_sign[0]

spark-exec-settings: The session settings that change results

Engine: spark 4.3.0; area: execution; status: open; evidence: fuzz.

Verdict: ansi.enabled, timestampType, session.timeZone, legacy.statisticalAggregate, decimalOperations.allowPrecisionLoss, timeType.enabled, the JVM locale

spark.sql.ansi.enabled (true by default since 4.0) switches overflow, division by zero, casts, indexes and string coercion between raising and NULL or wrapping, and changes type coercion (FLOAT + INT, '1' + 1); it also sets size(NULL) (spark.sql.legacy.sizeOfNull) and the text of decimals cast to strings. spark.sql.timestampType decides what TIMESTAMP means, spark.sql.session.timeZone how TIMESTAMP_LTZ reads and prints. spark.sql.legacy.statisticalAggregate turns the NULLs of degenerate var_samp, covar_samp, corr into NaN. spark.sql.decimalOperations.allowPrecisionLoss decides the result scale past 38 digits. spark.sql.timeType.enabled enables TIME. spark.sql.shuffle.partitions and the cluster’s parallelism change the order of double sums, first, collect_list and ties (spark-exec-parallel). Outside Spark’s configuration, the JVM’s default locale changes upper and lower (spark-case).

sverify’s Spark session sets ANSI on, UTC, TIMESTAMP_NTZ, case-sensitive names and unescaped string literals (sverify.sql.spark.SPARK_CONF); the probes ran with those, and each fact records the settings it was checked under.

The reference: The reference has no settings. A profile’s facts hold under the settings they record (sverify._engine_settings, #444); the Spark row needs ANSI on and the settings of SPARK_CONF.

Options: settings.

Constructs: spark.sql.ansi.enabled, spark.sql.timestampType, spark.sql.session.timeZone, spark.sql.legacy.statisticalAggregate, spark.sql.decimalOperations.allowPrecisionLoss, spark.sql.timeType.enabled, spark.sql.legacy.sizeOfNull, spark.sql.shuffle.partitions, spark.sql.caseSensitive.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #444.

Provenance:

It exercises spark.sql.ansi.enabled on and off throughout; size(NULL) under each; spark.sql.legacy.statisticalAggregate on and off for var_samp.

list_index[0]: 300 rows, element_at(a, i), a[i], get(a, i), try_element_at(a, i), size(a), array_size(a), 6326 of 6326 results as the fact says (raised: INVALID_ARRAY_INDEX 30, INVALID_ARRAY_INDEX_IN_ELEMENT_AT 28, INVALID_INDEX_OF_ZERO 92); aggregates[1]: 300 rows, var_samp(x), var_pop(x), stddev_samp(x), 1440 of 1440 results as the fact says; int_divide[3]: 300 rows, a div b, a / b, 2394 of 2394 results as the fact says (raised: ARITHMETIC_OVERFLOW 2, DIVIDE_BY_ZERO 58); Spark differs from Rel.eval on 0 of 283; fp32[0]: 300 rows, a + b, a * b, a / b, a + i, sqrt(a), 5970 of 5970 results as the fact says (raised: DIVIDE_BY_ZERO 30); raise_set[0]: 300 rows, n + s, element_at(a, i), 1812 of 1812 results as the fact says (raised: CAST_INVALID_INPUT 30, INVALID_ARRAY_INDEX_IN_ELEMENT_AT 21, INVALID_INDEX_OF_ZERO 39).

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json list_index[0], aggregates[1], int_divide[3], fp32[0], raise_set[0]

spark-exec-size-limits: Limits: decimals at 38 digits, strings and arrays below 2 GB

Engine: spark 4.3.0; area: execution; status: open; evidence: fuzz.

Verdict: DECIMAL(39) refused; strings, binaries and arrays under 2³¹ bytes or elements

DECIMAL precision is at most 38; DECIMAL(39,0) raises DECIMAL_PRECISION_EXCEEDS_MAX_PRECISION when the query is analysed, and a value past its precision raises (ANSI) or is NULL (spark-decimal-arith): NUMERIC_VALUE_OUT_OF_RANGE when it fits 38 digits but not the type, NUMERIC_OUT_OF_SUPPORTED_RANGE when its text has more than 38 digits before the point (a long fraction is rounded to the type first). A string or binary is a byte array indexed by int, so it holds less than 2 GB; an array holds at most Integer.MAX_VALUE - 15 elements; a row must fit a task’s memory. Past those, Spark raises (e.g. COLLECTION_SIZE_LIMIT_EXCEEDED).

The reference: The reference caps decimals at 38 digits and raises past them; it has no limit on string, blob or list lengths.

Options: size_limits.

Constructs: DECIMAL, STRING, BINARY, ARRAY, repeat, concat.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

size_limits[0]: 300 rows, CAST(x AS DECIMAL(38,2)), 964 of 964 results as the fact says (raised: NUMERIC_OUT_OF_SUPPORTED_RANGE 8, NUMERIC_VALUE_OUT_OF_RANGE.WITH_SUGGESTION 22).

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json size_limits[0]

spark-exec-streaming-fallback: No streaming fallback: sverify runs Spark batch queries

Engine: spark 4.3.0; area: execution; status: no-finding; evidence: source.

Verdict: not applicable: batch execution only

sverify runs plans as batch SQL on a Spark session (sverify.spark_engine). Spark has no streaming engine that hands operations to another one: Structured Streaming is a separate API with its own incremental execution, and sverify does not use it.

The reference: The reference has no engines; a fact holds for the engine it was probed on. Spark’s batch engine is the only one in question.

Options: streaming_fallback.

Constructs: spark.sql, Structured Streaming.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-try-variants: try_* functions give NULL for their own errors only

Engine: spark 4.3.0; area: execution; status: open; evidence: fuzz.

Verdict: try_cast, try_add, try_divide, try_sum, … : NULL on their errors, in both modes

try_cast, try_add, try_subtract, try_multiply, try_divide, try_mod, try_sum, try_avg, try_element_at, try_to_number, try_to_timestamp and try_to_date return NULL where their raising form raises (overflow, a zero divisor, malformed text, an index out of range), whatever spark.sql.ansi.enabled says. try_avg of BIGINT sums in double and never overflows. Two exceptions raise anyway: try_element_at(a, 0) raises INVALID_INDEX_OF_ZERO in both modes, as element_at does, and try_to_timestamp with a pattern Spark 3.0 changed raises INCONSISTENT_BEHAVIOR_CROSS_VERSION.DATETIME_PATTERN_RECOGNITION (a SparkUpgradeException).

They cover only their own operation: try_cast(regexp_extract('a', '(', 0) AS INT) still raises the pattern’s error, and try_divide(1, 0) + (1 div 0) still raises under ANSI from the div. There is no TRY(expr) that covers a whole expression in SQL.

The reference: Substrait’s cast has failure_behavior: RETURN_NULL; the reference honours it on DuckDB only, and other engines refuse it until #271. try_cast is Spark’s RETURN_NULL.

Options: try_variants.

Constructs: try_cast, try_add, try_subtract, try_multiply, try_divide, try_mod, try_sum, try_avg, try_element_at, try_to_number, try_to_timestamp, try_to_date.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

See: issues #271.

Provenance:

try_variants[0]: 300 rows, try_add(a, b), try_subtract(a, b), try_multiply(a, b), try_mod(a, b), try_divide(a, b), try_cast(s AS INT), 7200 of 7200 results as the fact says.

Spark 4.3.0, Java 21.0.12.1, Linux amd64, local[2], session time zone UTC. - in characterization/spark/fuzz.json try_variants[0]

Spark: frontend

spark-implicit-casts: Implicit casts: ANSI promotes by precedence and parses strings strictly; legacy goes through double

Engine: spark 4.3.0; area: frontend; status: no-finding; evidence: probe.

Verdict: ANSI: 1 + '2' is BIGINT 3, '1.5' + 1 raises; legacy: double; INT + 1.5 is decimal

Under ANSI, types widen by the precedence list: 1 + 1.5 (a decimal literal) is decimal(3,1), INT + DOUBLE double, BIGINT + FLOAT double, TINYINT + SMALLINT smallint, DECIMAL(20,0) + DOUBLE double. A string next to an integer is cast to BIGINT, next to a FLOAT, DOUBLE or decimal to DOUBLE ('1' + CAST(1 AS FLOAT) and 1.1 + '2' are doubles), and a lone string argument of abs or unary - is read as DOUBLE: 1 + '2' is 3 as BIGINT, 1 = '1' true, but '1.5' + 1 and 1 = '1.0' raise CAST_INVALID_INPUT, and 1 + 'x' too. Without ANSI the string goes to DOUBLE (1 + '2' is 3.0, '1.5' + 1 2.5, 1 + 'x' NULL) and FLOAT + INT stays FLOAT.

/ always divides as DOUBLE for integers (typeof(1 / 2) is double) and as a decimal for decimals. Comparing BIGINT with DOUBLE casts to DOUBLE: 9007199254740993L = 9007199254740992.0D is true. DATE = '2024-01-01' casts the string to a date.

The reference: A Substrait call takes the types its plan declares; casts are explicit. The SQL writer casts every operand and literal (sverify.sql.spark), so no implicit cast decides a result.

Options: implicit_casts.

Constructs: +, =, /, coalesce, concat.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-literal-types: Literals: INT, then BIGINT, then DECIMAL; 1.5 is a decimal, 1e0 a double

Engine: spark 4.3.0; area: frontend; status: no-finding; evidence: probe.

Verdict: 1 int; 2147483648 bigint; 1.5 decimal(2,1); 1e0, 1.5D double; suffixes Y S L F D BD

An integer literal is the narrowest of INT, BIGINT and DECIMAL(p,0) that holds it (2147483648 is bigint, 9223372036854775808 decimal(19,0)). A literal with a point is a decimal (1.5 is decimal(2,1), -0.0 decimal(1,1)), one with an exponent a double (1e0). Suffixes pick the type: 1Y tinyint, 1S smallint, 1L bigint, 1.5F float, 1.5D double, 1BD decimal. NULL is void; array(1, 2L) is array<bigint>.

So 2147483647 + 1 is INT arithmetic and raises under ANSI.

The reference: Substrait literals carry their type; the frontends (the SQL lift, the Ibis producer) choose it, and the Spark writer casts each literal to its declared type.

Options: literal_types.

Constructs: literals.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

spark-type-names: Type names: Spark’s (tinyint … bigint, float, double, decimal(p,s), timestamp_ntz)

Engine: spark 4.3.0; area: frontend; status: no-finding; evidence: probe.

Verdict: typeof gives Spark’s names; TIMESTAMP is timestamp (LTZ) or timestamp_ntz by setting

typeof names tinyint, smallint, int, bigint, float, double, decimal(p,s), string, binary, boolean, date, timestamp (with a local time zone), timestamp_ntz, interval day, interval month, array<…>, map<…>, variant, void for NULL. Aliases parse: BYTE, SHORT, INTEGER, LONG, REAL, DEC (decimal(10,0)), NUMERIC. VARCHAR(n) and CHAR(n) in a cast are plain string and keep every character (CAST('abcd' AS VARCHAR(3)) is abcd). TIMESTAMP means timestamp_ntz under spark.sql.timestampType=TIMESTAMP_NTZ.

The reference: typeof folds to DuckDB’s names whichever engine runs the plan (duckdb-typeof-names). The SQL writer names Spark’s types itself (sverify.sql.spark).

Options: type_names.

Constructs: typeof, CAST.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance:

Spark: io

spark-io: I/O: Parquet, ORC, CSV, JSON, XML, text and Avro; Hadoop file systems; catalogs by configuration

Engine: spark 4.3.0; area: io; status: no-finding; evidence: engine_test.

Verdict: data sources by format; s3a://, abfss://, gs:// through Hadoop connectors; spark.sql.catalog.*

Spark reads and writes Parquet, ORC, CSV, JSON, XML, text and binary files with its built-in sources, and Avro with the bundled spark-avro module. Object stores go through Hadoop’s file system connectors (s3a://, abfss://, gs://), which need their jars and credentials in the Hadoop configuration (spark.hadoop.*). Catalogs other than the session’s are plugins named by spark.sql.catalog.<name> (Iceberg’s SparkCatalog, Delta’s DeltaCatalog, Unity Catalog’s), each from its own package. Parquet keeps TIMESTAMP_NTZ and TIMESTAMP_LTZ apart and stores decimals by precision. Written and read back without a header or schema, a CSV column comes back as a string named _c0, and a JSON integer as a bigint; Parquet and ORC keep int.

The reference: I/O is outside what a plan computes; sverify’s io modules read and write the formats, and sverify.spark_engine hands Spark tables as temporary views.

Options: io_formats, object_stores, catalogs.

Constructs: spark.read, DataFrameWriter, CREATE TABLE USING, spark.sql.catalog.

Settings: {"spark.sql.ansi.enabled": [true, false]}.

Provenance: