Skip to main content

DuckDB facts

101 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.

integers

duckdb-F1: / and // on integers

Engine: duckdb 2.0.0; area: integers; status: no-finding; evidence: engine_test.

Verdict: frontend

In DuckDB, / on integers is float division and // is integer division; the setting integer_division switches /.

The reference: The lift reads / as DuckDB does: it divides as doubles, and // divides integers.

Options: implicit_casts.

Constructs: /, //.

Settings: {"integer_division": false}.

Provenance:

  • 2026-11-01, replay, by commit 20b02e9: Recorded from the replay’s translation of DuckDB’s SQL to DataFusion’s (sverify_dev.slt.translate).

duckdb-F3: Integer literal types

Engine: duckdb 2.0.0; area: integers; status: no-finding; evidence: engine_test.

Verdict: frontend

DuckDB types an integer literal as the narrowest type that holds it (duckdb-R1).

The reference: 100::TINYINT + 50 is an i8 addition in DuckDB, and an i64 one in DataFusion’s plan.

Options: literal_types.

Constructs: integer literal.

Provenance:

  • 2026-11-01, replay, by commit 20b02e9: Recorded from the replay’s translation of DuckDB’s SQL to DataFusion’s (sverify_dev.slt.translate).

duckdb-R1: Integer overflow of + - * and negation

Engine: duckdb 2.0.0; area: integers; status: deviation; evidence: fuzz.

Verdict: deviation: unbounded, and reported as a warning

DuckDB raises Out of Range Error: Overflow … at the declared width, TINYINT included, which is the reference (native). If a plan asks for SILENT or SATURATE, it needs a shim: compute in HUGEINT and wrap, ((x::HUGEINT + y + 2^(w-1)) % 2^w + 2^w) % 2^w - 2^(w-1), or clamp, LEAST(GREATEST(x::HUGEINT + y, MIN_w), MAX_w), then cast back. HUGEINT holds every i64 product, so both are exact. The written plan must emit the arithmetic at its declared width (S2 in the README).

Frontend (duckdb-F3): DuckDB gives an integer literal the narrowest type that holds it, so 100::TINYINT + 50 is an i8 addition in DuckDB, and an i64 one in DataFusion’s plan.

The docs are silent on integer arithmetic raising at the operands’ declared width: a docs gap.

The reference: Substrait and the reference: add, subtract, multiply, negate and abs on i8/i16/i32/i64 return the same type; divide on integers overflows for MIN / -1. With overflow omitted, the reference raises when the result does not fit the declared type.

Lean, + - * and negation: deviation. Rel.eval’s integers are unbounded, and UnFn.wrap n, which marks arithmetic narrower than 64 bits, is the identity (Expr.lean:134). verify reports the overflow only as a warning (may wrap around, not an error), and the parser accepts only SILENT for + - * (Parse.lean:770). So 9223372036854775800 + 5000 and 100::TINYINT + 50::TINYINT answer 9223372036854780800 and 150, with no error, where the reference raises. The fix: report overflow at the declared width as an error, and accept overflow: ERROR (and the omitted option) rather than only SILENT.

Options: overflow.

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

Constructs: +, -, *, unary -, add, subtract, multiply, negate.

See: about 80 entries of the replay ledger; issues #185.

Provenance:

duckdb-R1-min: abs(MIN) and divide(MIN, -1)

Engine: duckdb 2.0.0; area: integers; status: no-finding; evidence: fuzz.

Verdict: no finding (verify: overflow error)

DuckDB raises Out of Range Error: Overflow … for abs(-2⁶³) and -2⁶³ // -1 (native).

The reference: Substrait and the reference: abs and divide on i8…i64 return the same type; with overflow omitted, abs(MIN) and MIN / -1 raise.

Lean: no finding. verify reports overflow as an error for both.

Options: overflow.

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

Constructs: abs, /, //, divide.

See: issues #185.

Provenance:

duckdb-R15-int-arith: BIGINT + - * and -BIGINT

Engine: duckdb 2.0.0; area: integers; status: no-finding; evidence: probe.

Verdict: matches overflow: ERROR

Raises on overflow at 64 bits, under every setting (duckdb-R1).

The reference: add, subtract, multiply, negate with overflow: ERROR.

Options: overflow.

Rows of the choice tables: int.arith overflow (i64).

Constructs: +, -, *, unary -.

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

See: issues #242.

Provenance:

  • 2026-11-02, probe, by sverify_dev.probe (#242, commit d5efa18): sverify_dev.probe runs the function at each signature on edge cases (0, ±1, ±2, 3, ±7, the 64-bit bounds, 0.5, ±2.5, 1e308, ±inf, NaN, NULL), one row at a time from columns, under every combination of error_on_division_by_zero and ieee_floating_point_ops. Lean evaluates the Substrait call on the same rows once per candidate value of its option (substrait-check --outcomes), and the candidates whose outcomes all match are DuckDB’s options. No input is -0.0 (duckdb-R14).

duckdb-R15-int-divide: BIGINT // BIGINT

Engine: duckdb 2.0.0; area: integers; status: no-finding; evidence: probe.

Verdict: matches on_division_by_zero: ERROR

Raises on a zero divisor; gives NULL with error_on_division_by_zero off (duckdb-R2).

The reference: divide with on_division_by_zero: ERROR; NULL matches with error_on_division_by_zero off.

Options: on_division_by_zero, overflow, division_type.

Rows of the choice tables: int.div on_division_by_zero (i64); int.div division_type (i64).

Constructs: //.

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

See: issues #242.

Provenance:

  • 2026-11-02, probe, by sverify_dev.probe (#242, commit d5efa18): sverify_dev.probe runs the function at each signature on edge cases (0, ±1, ±2, 3, ±7, the 64-bit bounds, 0.5, ±2.5, 1e308, ±inf, NaN, NULL), one row at a time from columns, under every combination of error_on_division_by_zero and ieee_floating_point_ops. Lean evaluates the Substrait call on the same rows once per candidate value of its option (substrait-check --outcomes), and the candidates whose outcomes all match are DuckDB’s options. No input is -0.0 (duckdb-R14).

duckdb-R15-int-modulus: BIGINT % BIGINT

Engine: duckdb 2.0.0; area: integers; status: shimmed; evidence: probe.

Verdict: no option matches: MIN % -1 raises

MIN % -1 raises Overflow in division (duckdb-R3); the SQL writes that case out.

Round 4 (probes): at TINYINT and SMALLINT % keeps the type and truncates (-7 % 3 = -1, 7 % -3 = 1, MIN % 1 = 0), and MIN % -1 raises Out of Range Error: Overflow in division of MIN / -1 at all four widths, from table columns.

The docs are silent on the sign of % and on MIN % -1: a docs gap. No upstream test pins MIN % -1 below HUGEINT; it may change between versions.

The reference: modulus: MIN % -1 is 0.

Options: min_mod_neg_one, division_type, raise_set, overflow.

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

Constructs: %.

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

See: issues #242.

Provenance:

duckdb-R15-shift-left: BIGINT << BIGINT

Engine: duckdb 2.0.0; area: integers; status: shimmed; evidence: probe.

Verdict: no option matches

Raises on a negative operand, on bits shifted out, and on shifts below 0 or beyond 63 (but 0 << y is 0 for any y).

The docs are silent on negative operands, shifted-out bits and out-of-range shifts: a docs gap.

The reference: shift_left drops the bits shifted out, and raises on shifts below 0 or beyond 63.

Options: shift_range, raise_set.

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

Constructs: <<, shift_left.

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

See: upstream ledger ST-1; issues #242.

Provenance:

duckdb-R15-shift-right: BIGINT >> BIGINT

Engine: duckdb 2.0.0; area: integers; status: shimmed; evidence: probe.

Verdict: no option matches

0 for shifts below 0 or beyond 63 (-5 >> 64 too).

The docs are silent on out-of-range shifts: a docs gap. No upstream test pins this below HUGEINT (DuckDB’s test puts it in one statement error with <<); it may change between versions.

The reference: shift_right raises on shifts below 0 or beyond 63.

Options: shift_range.

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

Constructs: >>, shift_right.

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

See: upstream ledger ST-1; issues #242.

Provenance:

duckdb-R2: Integer division by zero

Engine: duckdb 2.0.0; area: integers; status: no-finding; evidence: fuzz.

Verdict: no finding (verify: division by zero error)

Native, under error_on_division_by_zero = true, the 2.0 default. With the setting off, DuckDB returns NULL, so it needs a guard, CASE WHEN y = 0 THEN error('division by zero') ELSE x // y END. A plan that asks for NULL needs CASE WHEN y = 0 THEN NULL ELSE x // y END.

Truncation is native: -7 // 2 = -3, in every agreeing entry and fuzz case.

The reference: Substrait and the reference: divide on integers truncates toward zero; with on_division_by_zero omitted, a zero divisor raises.

Lean: no finding. verify reports division by zero as an error, so on those rows the plan may raise, which is the reference. Rel.eval’s NULL there (Expr.lean:195) is not an answer anyone sees. It is the reference value for NULL, should a plan ask for it.

Options: on_division_by_zero, division_type.

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

Constructs: //, divide.

Settings: {"error_on_division_by_zero": true}.

See: about 23 entries of the replay ledger; issues #185.

Provenance:

duckdb-R3: Integer modulus: zero divisor and sign

Engine: duckdb 2.0.0; area: integers; status: open; evidence: fuzz.

Verdict: no finding; division_type open, no diff

Native, except MIN % -1. % raises on zero and truncates (-7 % 2 = -1, 7 % -2 = 1), as Lean does, so division_type makes no difference between the two. But MIN % -1 raises Overflow in division, where the reference gives 0 (found by the probes, duckdb-R15-int-modulus), so the SQL writes that case out.

If the reference picks FLOOR, DuckDB needs a shim, x % y + CASE WHEN x % y <> 0 AND (x % y < 0) <> (y < 0) THEN y ELSE 0 END. With error_on_division_by_zero off, the guard of duckdb-R2.

The docs are silent on the sign of % and on MIN % -1: a docs gap. No upstream test pins MIN % -1 below HUGEINT; it may change between versions.

The reference: Substrait and the reference: modulus on integers raises on a zero divisor (the decided default for division by zero). Its division_type: [TRUNCATE, FLOOR], the remainder’s sign, is open. The parser accepts an explicit TRUNCATE, which is what it models, and refuses FLOOR.

Lean: no finding. verify reports division by zero as an error. The remainder truncates (Int.tmod).

Options: division_type, on_division_by_zero.

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

Constructs: %, modulus.

Settings: {"error_on_division_by_zero": true}.

See: issues #185, #209.

Provenance:

duckdb-int-div-domain: Integer // has no domain error

Engine: duckdb 2.0.0; area: integers; status: no-finding; evidence: engine_test.

Verdict: no finding: only a zero divisor and MIN // -1 fail

// on TINYINT, SMALLINT, INTEGER and BIGINT runs C++ / behind one wrapper, BinaryNumericDivideWrapper, instantiated per width. The wrapper has two failure paths: MIN // -1 raises Overflow in division (duckdb-R1-min) and a zero divisor raises or gives NULL by error_on_division_by_zero (duckdb-R2). No other input fails, so no domain error occurs.

/ of two integers is float division while integer_division is false (the default), which the lift reads as a divide of doubles.

The reference: divide on integers: on_domain_error never applies (docs/core-semantics.md, int.div).

Options: on_domain_error, division_type.

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

Constructs: //, divide.

Settings: {"error_on_division_by_zero": [true, false], "integer_division": false}.

Provenance:

duckdb-int-pow: power has no integer overload

Engine: duckdb 2.0.0; area: integers; status: shimmed; evidence: probe.

Verdict: shimmed: DuckDB’s power is of doubles; sverify writes integer powers as checked products

pow, power, ** and ^ are one function with a single signature, (DOUBLE, DOUBLE) -> DOUBLE. Integer arguments are cast to DOUBLE, so 2 ** 3 is the double 8.0, a BIGINT above 2⁵³ loses digits on the way in, and a result outside 64 bits is a large double or inf. Nothing checks integer overflow.

The body is C’s std::pow. With ieee_floating_point_ops on (the default) it raises nothing: a negative exponent gives a fraction (2 ** -1 = 0.5) and 0 ** -1 gives inf. With it off, 0 ** negative raises zero raised to a negative power is undefined and the rest is unchanged.

So the int.pow rows have no native DuckDB answer: sverify’s SQL never calls POWER for BinFn.ipow. It writes the power out as repeated checked BIGINT multiplication, which raises Overflow in multiplication exactly when a partial product leaves 64 bits (duckdb-R1), and raises on a negative exponent itself.

Round 4 (probes): ** of TINYINT and of SMALLINT is a DOUBLE: 2 ** 7 = 128.0 and 2 ** 15 = 32768.0 leave the argument’s range without an error, 2 ** -1 = 0.5 and 0 ** -1 = inf.

The numeric functions page shows 3 ** 4 as 81 and pow(2, 3) as 8 and never states the DOUBLE result: a docs bug, DD-9.

The reference: power on integers keeps the integer type; a result outside the width raises (overflow: ERROR), and on_domain_error never applies (docs/core-semantics.md). Lean’s BinFn.ipow raises on a negative exponent. The SQL lift reads DuckDB’s ** as the double power (sql_lift/core.py, _pow), as DuckDB computes it.

Options: overflow, on_domain_error.

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

Constructs: power, pow, **, ^.

Settings: {"ieee_floating_point_ops": [true, false]}.

See: upstream ledger DD-9.

Provenance:

decimals

duckdb-decimal-arith: Decimal result types: its own, capped at 18 digits while the operands fit 64 bits; rescaling rounds half away from zero

Engine: duckdb 2.0.0; area: decimals; status: shimmed; evidence: probe.

Verdict: decimal_rescale: TIE_AWAY_FROM_ZERO native; result types its own, compensated

+ and - give DECIMAL(max(p₁−s₁, p₂−s₂) + max(s₁, s₂) + 1, max(s₁, s₂)), at most 38 digits: DECIMAL(18,2) + DECIMAL(18,2) is DECIMAL(19,2), DECIMAL(38,0) + DECIMAL(38,0) stays DECIMAL(38,0) and raises past 38 digits (Overflow in addition of DECIMAL(38)).

* gives DECIMAL(p₁ + p₂, s₁ + s₂), but where both operands fit 64 bits (p ≤ 18) and the sum of the precisions does not, the result is capped at DECIMAL(18, s₁ + s₂) and raises when a value needs more: DECIMAL(10,2) * DECIMAL(10,2) is DECIMAL(18,4), and 9999999999999.99 * 9999999999999.99 (DECIMAL(15,2)) raises Overflow in multiplication of DECIMAL(18). Wider operands give up to DECIMAL(38, s₁ + s₂) (DECIMAL(20,10) * DECIMAL(20,10) is DECIMAL(38,20)); a scale above 38 raises (Needed scale 74 …).

/ of decimals is a DOUBLE (1.00 / 8 is 0.125, 10 / 3 is 3.3333333333333335). sum is DECIMAL(38, s) and raises past 38 digits (Overflow in SUM of DECIMAL); avg is a DOUBLE; min keeps the type; round(x, d) is DECIMAL(p, d).

Every drop of decimal places rounds half away from zero: a cast to a smaller scale (1.25 → 1.3, -1.25 → -1.3, 0.125 → 0.13, ±2.5 → ±3), round (±2.5 → ±3, ±1.25 → ±1.3 at one digit), a cast to an integer (±2.5 → ±3, 3.5 → 4) and a cast of a double to a decimal (0.625 → 0.63, -0.125 → -0.13, 2.675 → 2.68: the double just below 2.675 is scaled in doubles first).

The docs are silent on the result widths, the 18-digit cap of * and the tie rule of round, of a cast to a smaller scale and of a double to a DECIMAL: a docs gap. No upstream test pins the double-to-DECIMAL tie; it may change between versions.

The reference: The reference’s types are the YAML’s: +, - as DuckDB’s; × is DECIMAL(p₁ + p₂ + 1, s₁ + s₂) up to 38 digits, exact; / refused; SUM is DECIMAL(min(38, p + 10), s), AVG a DECIMAL(min(38, p + 4), s + 4) rounded half away from zero (reference-semantics.md, Decimals). A result past its precision raises in both.

decimal_rescale is TIE_AWAY_FROM_ZERO, DuckDB’s own. Decimal to integer truncates in the reference. The translation compensates for the rest (engine-profiles.md, Decimals (#27)): a cast to the plan’s type around each operation, the left operand of × cast to DECIMAL(38, s), TRUNC before a cast to an integer, SUM cast to p + 10 digits, AVG as SUM and COUNT in HUGEINT. Double to decimal is round(x · 10ˢ) in doubles, half away from zero, as DuckDB’s (2.675 → 2.68).

ST-8 records that Substrait leaves this rounding open.

Options: decimal_result_type, decimal_rescale, raise_set, overflow.

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

Constructs: +, -, *, /, CAST, round, sum, avg.

See: upstream ledger ST-8; issues #27.

Provenance:

doubles

duckdb-R12: fp32

Engine: duckdb 2.0.0; area: doubles; status: fixed; evidence: engine_test.

Verdict: fixed (#208): fp32 arithmetic refused until modelled

DuckDB’s FLOAT is 32 bits (native): inf, and 1e308::FLOAT fails to convert.

The reference: Substrait: fp32 and fp64 are distinct types with their own impls.

Lean: one double kind, so 2e38::FLOAT / 0.1 was 2e39. A deviation of width (principle 3). Fixed (#208) by refusing what is not modelled: arithmetic on FLOAT values (+ - * /, sqrt, …) and casts to FLOAT round to 32 bits, so they are refused (“cast them to DOUBLE first”). Reading a FLOAT, comparing it, and widening it to DOUBLE (UnFn.widen) are exact, as is a FLOAT literal, which is the single-precision value of its digits. Modelling binary32 arithmetic is left for later.

Options: fp32.

Rows of the choice tables: fp32 (fp32).

Constructs: FLOAT, +, -, *, /, sqrt, CAST.

See: about 5 entries of the replay ledger; issues #208.

Provenance:

duckdb-R14: -0.0 and 0.0

Engine: duckdb 2.0.0; area: doubles; status: decided; evidence: probe.

Verdict: decided: one zero; DuckDB shim

DuckDB keeps -0.0 through ORDER BY and sorts it as a peer of 0.0: both zeros get the same sort key, so the next key or the input order decides. 1.0 / -0.0 = -inf natively. (Corrected 2026-11-04: this said -0.0 sorts before 0.0; the cited test sorts by v, id, so id broke the tie.)

Shim: sverify’s SQL normalizes -0.0 to 0.0 on every read and after every operation that can make it (Dialect.canon).

Round 4 (probes): ORDER BY v alone keeps the zeros in their input order, both signs kept: -0.0, 0.0, -0.0 and 0.0, -0.0, 0.0 come out as they went in, ascending and descending, for DOUBLE and FLOAT. So they are peers.

The docs are silent on how ORDER BY treats -0.0, NaN and ties: a docs gap. No upstream test pins that the two zeros are peers; it may change between versions.

The reference: Substrait: fp64 is IEEE. The spec says nothing about grouping or ordering -0.0.

Lean: one zero (#89), so 1.0 / -0.0 is inf, not IEEE’s -inf. Decided on #209: one zero, so no Lean change.

Options: zero_sign, nan_zero_grouping.

Rows of the choice tables: fp.div zero_sign (fp64; evidence engine_test).

Constructs: ORDER BY, /, -0.0.

See: upstream ledger ST-4; about 2 entries of the replay ledger; issues #209; pull requests #89.

Provenance:

duckdb-R15-atan2: atan2(0, 0)

Engine: duckdb 2.0.0; area: doubles; status: shimmed; evidence: probe.

Verdict: no option matches: 0, where the reference raises

atan2(0.0, 0.0) is 0. With signed zeros it is C’s std::atan2, which ieee_floating_point_ops does not change: atan2(-0.0::DOUBLE, -0.0::DOUBLE) is -π (-3.141592653589793). sverify’s SQL normalizes -0.0 to 0.0 on every read (duckdb-R14). (Corrected 2026-11-04: this said atan2(0, 0) is 0 for every zero.)

With ieee_floating_point_ops off, a zero divisor of doubles raises, and so do the elementary functions outside their domains (as the reference does), and power(0, negative) raises; the NaN of inf - inf, 0 * inf and inf / inf, atan2(0, 0) and the shifts are unchanged.

The docs are silent on atan2 of zeros: a docs gap. No upstream test pins this; it may change between versions.

The reference: atan2(0, 0) raises.

Options: on_domain_error.

Rows of the choice tables: fp.elem on_domain_error (fp64).

Constructs: atan2.

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

See: upstream ledger ST-2; issues #242.

Provenance:

  • 2026-11-02, probe, by sverify_dev.probe (#242, commit d5efa18): sverify_dev.probe runs the function at each signature on edge cases (0, ±1, ±2, 3, ±7, the 64-bit bounds, 0.5, ±2.5, 1e308, ±inf, NaN, NULL), one row at a time from columns, under every combination of error_on_division_by_zero and ieee_floating_point_ops. Lean evaluates the Substrait call on the same rows once per candidate value of its option (substrait-check --outcomes), and the candidates whose outcomes all match are DuckDB’s options. No input is -0.0 (duckdb-R14).
  • 2026-11-04, source-reading, by characterization pipeline reverse pass
  • 2026-11-04, docs, by characterization pipeline reverse pass
  • 2026-11-04, probe, by characterization pipeline reverse pass: Corrected the description: atan2(0, 0) is 0 for +0, +0 only; atan2(-0.0, -0.0) is -π.
    • SELECT atan2(0.0, 0.0), atan2(-0.0::DOUBLE, -0.0::DOUBLE) gives 0.0, -3.141592653589793

duckdb-R15-double-arith: DOUBLE + - * /

Engine: duckdb 2.0.0; area: doubles; status: shimmed; evidence: fuzz.

Verdict: no option matches: NaN from ±inf; / by zero is IEEE

Operations on ±inf that have no value give NaN (inf - inf, 0 * inf, inf / inf), where the reference raises a domain error (#257); / by zero is IEEE (duckdb-R5).

With ieee_floating_point_ops off, a zero divisor of doubles raises, and so do the elementary functions outside their domains (as the reference does), and power(0, negative) raises; the NaN of inf - inf, 0 * inf and inf / inf, atan2(0, 0) and the shifts are unchanged.

FLOAT behaves the same by the docs (2.0, PostgreSQL Compatibility): 'Infinity'::FLOAT / 'Infinity'::FLOAT and 'Infinity'::FLOAT - 'Infinity'::FLOAT are NaN, and DuckDB follows IEEE 754 for division by zero and operations on infinities. inf / inf is the reference’s domain error of divide (on_domain_error), which DuckDB answers with NaN.

Round 4 (probes): on FLOAT, inf / inf, 0 * inf, inf - inf and 0 / 0 are NaN, 1 / 0 is inf and -1 / 0 is -inf, with a FLOAT result: as on DOUBLE.

The reference: add … divide: no operation creates a NaN (#257); a zero divisor raises unless the plan asks otherwise.

Options: nan_creation, on_division_by_zero, on_domain_error.

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

Constructs: +, -, *, /.

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

See: upstream ledger ST-5; issues #242, #257.

Provenance:

duckdb-R15-double-modulus: DOUBLE % DOUBLE

Engine: duckdb 2.0.0; area: doubles; status: shimmed; evidence: fuzz.

Verdict: no option matches: NaN for a zero divisor or an infinite dividend

NaN for a zero divisor or an infinite dividend (duckdb-R4).

Round 4 (probes): FLOAT % is a FLOAT and truncates like fmod (-7 % 3 = -1, 7 % -3 = 1, -7.5 % 2 = -1.5); 1 % 0 and inf % 1 are NaN.

The numeric functions page says % raises on a zero divisor, which FLOAT and DOUBLE % do not: a docs bug, DD-8. The page is also silent on % of doubles truncating while its fmod floors (-7.5::DOUBLE % 2 is -1.5, fmod(-7.5, 2) 0.5): a docs gap. No upstream test pins the sign of a negative dividend’s % on doubles; it may change between versions.

The reference: modulus of doubles is fmod (duckdb-R4); a zero divisor and an infinite dividend raise.

Options: nan_creation, division_type.

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

Constructs: %.

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

See: upstream ledger DD-8; issues #242.

Provenance:

duckdb-R15-elementary: abs, floor, ceil, exp, atan

Engine: duckdb 2.0.0; area: doubles; status: no-finding; evidence: probe.

Verdict: the reference

Gives the reference’s value on every probed row.

The reference: The functions of functions_arithmetic.yaml and functions_rounding.yaml.

Options: rounding.

Rows of the choice tables: fp.elem rounding (fp64).

Constructs: abs, floor, ceil, exp, atan.

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

See: issues #242.

Provenance:

duckdb-R15-math-domain: power, sqrt, ln, log10, log2, asin, acos, sin, cos, tan

Engine: duckdb 2.0.0; area: doubles; status: shimmed; evidence: probe.

Verdict: no option matches: NaN outside the domain, -inf for a logarithm of 0

NaN outside the domain, -inf for a logarithm of 0.

With ieee_floating_point_ops off, a zero divisor of doubles raises, and so do the elementary functions outside their domains (as the reference does), and power(0, negative) raises; the NaN of inf - inf, 0 * inf and inf / inf, atan2(0, 0) and the shifts are unchanged.

The docs are silent on the domains of ln, log, sqrt, asin and the others (the setting is documented): a docs gap.

The reference: Outside the domain, the reference raises.

Options: on_domain_error, on_log_zero.

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

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

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

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

Provenance:

duckdb-R15-power-neginf: power(-inf, y) for a finite non-integer y: IEEE’s +inf or 0

Engine: duckdb 2.0.0; area: doubles; status: shimmed; evidence: probe.

Verdict: IEEE value where the reference raises

power(-inf, y) for a finite y that is not an integer gives IEEE 754’s value under either setting of ieee_floating_point_ops: +inf for y > 0 (0.5, 1.5) and 0.0 for y < 0 (-0.5). Integer exponents give +inf (2.0) and -inf (3.0), and an infinite exponent +inf or 0.0. A finite negative base to a non-integer power is NaN (duckdb-R15-elementary).

The reference: power(-inf, y) for a finite non-integer y raises a domain error (negInfPow in SubstraitLean/Safety.lean), as a finite negative base does; Polars’s ** 0.5 gives NaN there. Profile.duckdb states DuckDB’s value (duckNegInfPow).

Constructs: power.

Settings: {"ieee_floating_point_ops": [true, false]}.

Provenance:

  • 2026-11-04, probe, by session wonderful-taussig-b9376c, duckdb Python 2.0.0: Polars 2.1.0 on the same inputs: NaN for 0.5 (a square root), inf for 1.5, 0.0 for -0.5.
    • SELECT power('-inf'::DOUBLE, 0.5), power('-inf'::DOUBLE, 1.5), power('-inf'::DOUBLE, -0.5), power('-inf'::DOUBLE, 2.0), power('-inf'::DOUBLE, 3.0) gives inf, inf, 0.0, inf, -inf
    • SET ieee_floating_point_ops = false; SELECT power('-inf'::DOUBLE, 0.5), power('-inf'::DOUBLE, -0.5) gives inf, 0.0

duckdb-R15-round: round(DOUBLE)

Engine: duckdb 2.0.0; area: doubles; status: no-finding; evidence: probe.

Verdict: matches rounding: TIE_AWAY_FROM_ZERO

Rounds half away from zero.

The reference: round with rounding: TIE_AWAY_FROM_ZERO.

Options: rounding.

Rows of the choice tables: fp.round rounding (fp64).

Constructs: round.

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

See: issues #242.

Provenance:

  • 2026-11-02, probe, by sverify_dev.probe (#242, commit d5efa18): sverify_dev.probe runs the function at each signature on edge cases (0, ±1, ±2, 3, ±7, the 64-bit bounds, 0.5, ±2.5, 1e308, ±inf, NaN, NULL), one row at a time from columns, under every combination of error_on_division_by_zero and ieee_floating_point_ops. Lean evaluates the Substrait call on the same rows once per candidate value of its option (substrait-check --outcomes), and the candidates whose outcomes all match are DuckDB’s options. No input is -0.0 (duckdb-R14).

duckdb-R15-round-digits: round(DOUBLE, d) for d from -1000 to 400

Engine: duckdb 2.0.0; area: doubles; status: no-finding; evidence: probe.

Verdict: the reference (DuckDB’s formula since #335)

DuckDB rounds to d digits; below zero it computes round(x / 10^-d) · 10^-d, and 0 where that is not finite.

The reference: round with a digit count: DuckDB’s formula since #335, so the outcomes match on every probed d.

Options: rounding.

Rows of the choice tables: fp.round rounding (fp64).

Constructs: round.

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

See: issues #242, #335; superseded by duckdb-round-digits-2.

Provenance:

duckdb-R4: modulus on doubles

Engine: duckdb 2.0.0; area: doubles; status: fixed; evidence: fuzz.

Verdict: fixed (#203): C’s fmod (no such Substrait function)

DuckDB’s % on doubles is fmod (native): 7.5 % 2 = 1.5, % 0.0 is NaN, or an error with error_on_division_by_zero. sverify raises on a zero divisor and writes out the NaN and infinite cases, so only finite operands reach %.

Changed (#257): an infinite dividend with a non-NaN divisor now raises a domain error instead of giving NaN, since no operation creates a NaN.

Polars’ % floors (the sign of the divisor); numpy’s fmod covers it.

The reference: Substrait: functions_arithmetic.yaml has no fp32/fp64 impl of modulus. DataFusion writes modulus with fp64 arguments anyway.

Lean: "modulus" always parsed to integer imod (Parse.lean:956); mkFloatCall (Parse.lean:1032) had no case for it. On doubles, imod worked on the float codes: 7.5 % 2.0 evaluated to a 300-digit integer, d % 0.0 to NULL. A deviation, and a soundness bug, since the plan was accepted and the result was wrong.

Fixed (#203): a modulus of doubles is C’s fmod (FloatCode.fmod in Float.lean), as DuckDB, DataFusion and Spark compute %: the sign of the dividend (-7.5 % 2.0 = -1.5); NaN for a NaN or an infinite dividend; the dividend for an infinite divisor. It is exact, computed on the codes. A zero divisor is an error, as on integers and as #185 decides for an omitted on_division_by_zero, so verify can prove it absent. Substrait has no such impl; the reference takes the plan DataFusion writes as fmod.

Since #256 it is sverify’s own function sverify_fmod, which Lean writes and the frontends emit; Lean still reads DataFusion’s modulus of doubles as the same function.

Options: on_division_by_zero, nan_creation.

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

Constructs: %, modulus, sverify_fmod.

Settings: {"error_on_division_by_zero": true, "ieee_floating_point_ops": true}.

See: about 30 entries of the replay ledger; issues #203, #256, #257.

Provenance:

duckdb-R5: Double division by zero

Engine: duckdb 2.0.0; area: doubles; status: fixed; evidence: fuzz.

Verdict: fixed (#204): an error unless the plan asks for IEEE, LIMIT or NULL

IEEE, under DuckDB’s default settings (corrected by the probes, duckdb-R15). error_on_division_by_zero is about integers; doubles follow ieee_floating_point_ops, on by default, under which x / 0.0 is ±inf and 0.0 / 0.0 NaN, whatever error_on_division_by_zero says.

So the reference default (ERROR) needs the guard of duckdb-R2, and so does IEEE where the dividend is infinite (inf / inf is NaN in DuckDB, a domain error in the reference). With ieee_floating_point_ops off, a zero divisor raises, which is the reference. NULL and LIMIT need a CASE.

The reference: Substrait and the reference: divide on fp32/fp64 takes on_division_by_zero: [IEEE, LIMIT, NULL, ERROR]. With it omitted, a zero divisor raises, 0.0 / 0.0 included.

Lean: deviation, fixed (#204). Rel.eval gave IEEE values (1.0 / 0.0 = inf, 0.0 / 0.0 = NaN), verify reported nothing for a double divisor, and the parser accepted only IEEE. So 7.5::DOUBLE / 0 answered inf where the reference raises. Now verify reports division by zero as an error when the option is omitted or ERROR. The parser writes IEEE, LIMIT and NULL as a CASE on the divisor (Parse.divByZero): IEEE gives ±inf by the dividend’s sign and NaN for 0 / 0, LIMIT gives ±inf for 0 / 0 too (the one zero is positive), NULL gives NULL, and a NaN dividend stays NaN. inf / inf raises a domain error under every mode (#257), as no operation creates a NaN; only IEEE’s 0 / 0, which the plan asks for, is NaN.

Options: on_division_by_zero.

Rows of the choice tables: fp.div on_division_by_zero (fp64).

Constructs: /, divide.

Settings: {"ieee_floating_point_ops": true}.

See: about 10 entries of the replay ledger; issues #204, #257.

Provenance:

duckdb-double-overflow: Double operations that overflow give ±inf

Engine: duckdb 2.0.0; area: doubles; status: shimmed; evidence: probe.

Verdict: IEEE: ±inf where the reference raises

No double operation raises “Overflow”: every one whose result is too large for a double gives inf or -inf, from finite inputs: 1e308 * 10, 1e308 + 1e308, -1e308 - 1e308, 1e308 / 1e-10, power(10, 400), 10.0 ** 400, exp(1000) (exp(709.0) is finite), sinh(1000), cosh(1000), gamma(200), lgamma(1e308), degrees(1e308), and power(0, -1).

Aggregates too: sum and avg of 1e308 and 1e308 are inf, grouped and over a window; fsum of them is NaN; var_pop of 1e308 and -1e308 is -inf.

A cast of a double outside FLOAT’s range to FLOAT raises a conversion error, the one overflow that raises.

FLOAT arithmetic overflows the same way, in single precision: 3e38::FLOAT + 3e38::FLOAT, * 10 and / 1e-3 are inf, -3e38 - 3e38 is -inf, and the result stays FLOAT. sum and avg of FLOAT accumulate and return a DOUBLE (duckdb-float-sum-order), so sum of 3e38 and 3e38 is the finite 6.0000000109955115e+38; grouped, sum and avg of 1e308 and 1e308 are inf.

The docs are silent on the DOUBLE-to-FLOAT conversion error: a docs gap. No upstream test pins it; it may change between versions.

The reference: An operation on finite doubles whose result is ±inf raises (Issue.infinite, #575): an inferred precondition the written plan checks on the data (CASE WHEN … THEN error('overflow to infinity')), as for a SUM that leaves 64 bits.

Options: float_overflow.

Rows of the choice tables: fp.arith float_overflow (fp32, fp64); fp.div float_overflow (fp32, fp64); agg.fsum float_overflow (fp32, fp64).

Constructs: +, -, *, /, power, exp, sinh, cosh, gamma, degrees, sum, avg, var_pop.

See: upstream ledger ST-9; issues #575.

Provenance:

  • 2026-11-04, probe, by session wonderful-taussig-b9376c (#575), duckdb Python 2.0.0: Run one statement at a time on an in-memory connection with the default settings (ieee_floating_point_ops on).
    • SELECT 1e308::DOUBLE * 10, 1e308::DOUBLE + 1e308::DOUBLE, 1e308::DOUBLE / 1e-10::DOUBLE, power(10::DOUBLE, 400::DOUBLE), exp(1000::DOUBLE), exp(709.0::DOUBLE), sinh(1000::DOUBLE), gamma(200::DOUBLE), degrees(1e308::DOUBLE), power(0::DOUBLE, -1::DOUBLE) gives inf, inf, inf, inf, inf, 8.218407461554972e+307, inf, inf, inf, inf
    • SELECT sum(x), avg(x), fsum(x) FROM (VALUES (1e308::DOUBLE), (1e308::DOUBLE)) t(x) gives inf, inf, nan
    • SELECT var_pop(x) FROM (VALUES (1e308::DOUBLE), (-1e308::DOUBLE)) t(x) gives -inf
    • SELECT 1e308::DOUBLE * 1e308::FLOAT gives Conversion Error: Type DOUBLE with value 1e+308 can't be cast because the value is out of range for the destination type FLOAT
  • 2026-11-04, probe, by characterization pipeline session (float_overflow rows), duckdb Python 2.0.0: One statement at a time on an in-memory connection with the default settings.
    • SELECT 3e38::FLOAT + 3e38::FLOAT, -3e38::FLOAT - 3e38::FLOAT, 3e38::FLOAT * 10::FLOAT, 3e38::FLOAT / 1e-3::FLOAT, typeof(3e38::FLOAT * 10::FLOAT) gives inf, -inf, inf, inf, 'FLOAT'
    • SELECT sum(x), avg(x), typeof(sum(x)), typeof(avg(x)) FROM (VALUES (3e38::FLOAT), (3e38::FLOAT)) t(x) gives 6.0000000109955115e+38, 3.0000000054977558e+38, 'DOUBLE', 'DOUBLE'
    • SELECT g, sum(x), avg(x) FROM (VALUES (1, 1e308::DOUBLE), (1, 1e308::DOUBLE)) t(g, x) GROUP BY g gives 1, inf, inf
    • https://github.com/duckdb/duckdb/blob/v2.0.0/test/sql/overflow/double_float_overflow.test#L8-L46 1e308::DOUBLE + 1e308::DOUBLE, *, / 0.1 → inf; 2e38::FLOAT + 2e38::FLOAT, *, / 0.1 → inf
  • 2026-11-04, source-reading, by characterization pipeline reverse pass
  • 2026-11-04, docs, by characterization pipeline reverse pass
  • 2026-11-04, engine-test, by characterization pipeline reverse pass
  • 2026-11-04, probe, by characterization pipeline reverse pass
    • SELECT 1e308::DOUBLE::FLOAT gives Conversion Error: Type DOUBLE with value 1e+308 can't be cast because the value is out of range for the destination type

duckdb-float-ieee: FLOAT and DOUBLE are IEEE 754 binary32 and binary64

Engine: duckdb 2.0.0; area: doubles; status: no-finding; evidence: engine_test.

Verdict: rounding: TIE_TO_EVEN for + - * /: plain C++ float and double operations

FLOAT (FLOAT4, REAL) is single precision and DOUBLE (FLOAT8) double precision, in practice IEEE 754 “to the extent that the underlying processor, operating system, and compiler support it”. Addition, subtraction and multiplication are approximate on them, so each result is the operands’ IEEE result, rounded to nearest with ties to even, the default IEEE mode. The docs name no other rounding mode and no setting that changes it.

/ of two integers is float division (5 / 2 = 2.5) while integer_division is false (the default); // divides integers. ieee_floating_point_ops (default true) selects IEEE results (NaN, ±inf) over errors or NULL for supported floating-point operations; it does not touch rounding.

The source confirms it (round 2): + - * / on FLOAT and DOUBLE are the C++ operators on float and double, so a FLOAT operation rounds to binary32 once and is not computed in double. The result is the platform’s IEEE arithmetic in its default rounding mode; DuckDB sets no other.

The reference: rounding: TIE_TO_EVEN for + - × / of doubles (Lean’s native Float; docs/core-semantics.md D5). Lean refuses arithmetic on FLOAT values (duckdb-R12), so the fp32 rows matter only once binary32 is modelled.

Options: rounding.

Rows of the choice tables: fp.arith rounding (fp32, fp64; evidence source); fp.div rounding (fp32, fp64; evidence source).

Constructs: +, -, *, /, FLOAT, DOUBLE.

Settings: {"ieee_floating_point_ops": true, "integer_division": false}.

Provenance:

duckdb-float-math-double: Math functions compute in DOUBLE, also for FLOAT

Engine: duckdb 2.0.0; area: doubles; status: open; evidence: engine_test.

Verdict: fp32 inputs widen to DOUBLE and the result is DOUBLE; round(FLOAT) stays FLOAT

sqrt, exp, sin, cos, tan, asin, acos, atan, atan2, power, ln, log, log10 and log2 each have one signature, of DOUBLE to DOUBLE. A FLOAT argument is cast to DOUBLE, which is exact (TryCastWithOverflowCheck(float, double) is a plain conversion), and the result is a DOUBLE: one libm call on doubles, rounded once to binary64. DuckDB has no binary32 version of these functions, so a FLOAT input gets the fp64 answer.

The bodies are C++ <cmath> (std::log, std::log10, std::log2, std::sqrt, …) on doubles, so their rounding is the platform libm’s, which is faithful but not always correctly rounded. log(b, x) is log10(x) / log10(b), two roundings and a division.

Domain and log of zero for FLOAT inputs are those of DOUBLE (duckdb-R15-math-domain): with ieee_floating_point_ops on, NaN outside the domain and -inf for a logarithm of 0; with it off, cannot take logarithm of zero, … of a negative number, ASIN is undefined outside [-1,1], and sin, cos, tan of ±inf raise.

round has FLOAT overloads. round(FLOAT) rounds half away from zero, exact for a float; round(FLOAT, d) widens to double, computes round(x · 10^d) / 10^d there, and converts back to FLOAT, so it rounds twice (to binary64, then binary32). inf and NaN return the input.

The docs are silent on these functions’ signatures and on a FLOAT argument giving a DOUBLE: a docs gap. No upstream test pins the DOUBLE result type of a FLOAT input; it may change between versions.

The reference: The elementary functions and logarithms of fp32 would round to binary32 (rounding: TIE_TO_EVEN); on_domain_error and on_log_zero are ERROR. Lean refuses arithmetic on FLOAT values (duckdb-R12), so a plan casts them to DOUBLE first, which is what DuckDB does itself. For fp64 the reference is Lean’s native Float, also a libm call.

Options: rounding, on_domain_error, on_log_zero.

Rows of the choice tables: fp.elem rounding (fp32; evidence source); fp.elem on_domain_error (fp32); fp.log rounding (fp32, fp64; evidence source); fp.log on_domain_error (fp32); fp.log on_log_zero (fp32); fp.round rounding (fp32; evidence source).

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

Settings: {"ieee_floating_point_ops": [true, false]}.

Provenance:

duckdb-float-signs: Signs of zero and NaN through FLOAT and DOUBLE operations

Engine: duckdb 2.0.0; area: doubles; status: shimmed; evidence: probe.

Verdict: IEEE in arithmetic; one zero in comparisons, hashing and sort keys; FLOAT least/greatest canonicalise

Arithmetic is IEEE on both widths, so it keeps and makes -0.0. +, -, * and / on FLOAT and DOUBLE are the C++ operators on float and double (no promotion of float), unary - is -x (it flips the sign of 0.0 and of a NaN), and % is std::fmod, whose zero takes the dividend’s sign (-4.0 % 2.0 = -0.0). The optimizer’s arithmetic simplification rewrites only integer x + 0, x * 1, …, and for doubles only x / 1, which keeps the sign; so it does not change a zero’s sign.

Comparisons see one zero. =, <, > and >= on floats are C++ ==, <, > after the NaN cases, so -0.0 = 0.0 is true and neither is below the other. Hashing (GROUP BY, joins, DISTINCT) maps -0.0 to +0.0 and every NaN to the quiet NaN, and the sort key encodes both zeros alike, so ORDER BY treats them as peers, and the next key or the input order decides. ORDER BY carries FLOAT and DOUBLE columns in the payload, since the key loses the sign.

least and greatest on DOUBLE keep the first argument among equals (least(-0.0, 0.0) is -0.0, least(0.0, -0.0) is 0.0). On FLOAT there is no direct instantiation: they compare sort keys and decode the winner from its key, so a zero comes out as +0.0 and a NaN as the positive quiet NaN. Builds with DUCKDB_SMALLER_BINARY route DOUBLE the same way.

A NaN’s sign survives arithmetic, negation and the FLOAT-to-DOUBLE cast (a plain conversion), and signbit has a FLOAT overload that reads it (std::signbit). Hashing and the FLOAT least/greatest drop it.

DuckDB’s own test notes that a checkpoint loses the sign of zero in storage (DuckDB issue 25580), unrelated to ORDER BY.

Round 4 (probes): IEEE signs on both widths, from constants and from table columns: -0.0 + -0.0 = -0.0, -0.0 + 0.0 = 0.0 - 0.0 = 1 - 1 = +0.0, -1 * 0 = -0.0, -0.0 * 1 = -0.0, d - d = +0.0 and -d = +0.0 for d = -0.0; % takes the dividend’s sign (-4 % 2 = -0.0, 4 % -2 = +0.0, -0.0 % 1 = -0.0). ORDER BY treats the zeros as peers and keeps their signs (duckdb-R14). least and greatest on DOUBLE keep the first argument among equal zeros; on FLOAT they return +0.0 for any zero winner and the positive NaN for -NaN, also from table columns (least(f, g) with both -0.0): DD-6. least(f, f) is simplified to f and keeps the sign.

The docs are silent on signed zero: a docs gap. No upstream test pins FLOAT least and greatest of zeros (DD-6); it may change between versions.

The reference: zero_sign: ONE_ZERO and nan_kind: ONE_NAN (#89, docs/core-semantics.md); Substrait is IEEE. sverify’s SQL canonicalises -0.0 on every read and after each operation that can make it (Dialect.canon, duckdb-R14), so DuckDB’s IEEE zeros never reach a result. Lean refuses FLOAT arithmetic (duckdb-R12).

Options: zero_sign, nan_kind.

Rows of the choice tables: fp.arith zero_sign (fp32, fp64); fp.neg zero_sign (fp32, fp64); fp.mod zero_sign (fp32, fp64); cmp zero_sign (fp32, fp64); nan_kind (fp32, fp64); fp.div zero_sign (fp32, fp64; evidence engine_test).

Constructs: +, -, *, /, %, =, <, least, greatest, GROUP BY, ORDER BY, signbit.

Settings: {"ieee_floating_point_ops": true}.

See: upstream ledger DD-6.

Provenance:

duckdb-nan-equality: NaN equals NaN, of either sign: in =, IN, grouping, DISTINCT, joins and set operations

Engine: duckdb 2.0.0; area: doubles; status: no-finding; evidence: probe.

Verdict: nan_equality: NAN_IS_NAN natively

'nan' = 'nan' is true, <> false, and 'nan' = -'nan' true: DuckDB compares doubles with NaN as one value above +inf, so the sign of a NaN never splits it. IN, IS DISTINCT FROM, list_contains and list_position find NaN; FLOAT behaves the same.

GROUP BY, count(DISTINCT …), equi-joins and INTERSECT put nan and -nan in one group and match them; -0.0 and 0.0 group and join together too (R14).

The reference: NAN_IS_NAN (#89, an override of IEEE equality) for =, grouping, DISTINCT and joins, with one NaN (nan_kind: ONE_NAN); docs/core-semantics.md lists DuckDB as NAN_IS_NAN. ST-4 records the gap in Substrait.

Options: nan_equality, nan_zero_grouping.

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

Constructs: =, <>, IN, IS DISTINCT FROM, GROUP BY, DISTINCT, JOIN, INTERSECT, list_contains.

See: upstream ledger ST-4; issues #89.

Provenance:

duckdb-nan-order: NaN compares greater than every other float

Engine: duckdb 2.0.0; area: doubles; status: no-finding; evidence: fuzz.

Verdict: nan_order: GREATEST natively (docs)

DuckDB departs from IEEE 754 in one point: NaN compares equal to NaN and greater than any other floating-point number, +inf included ('nan' > sqrt(2) is true). So FLOAT and DOUBLE have a total order, and greatest and least treat NaN as the largest value.

The docs state this for both floating-point types. They say nothing about the sign of a NaN in comparisons (duckdb-nan-equality: nan = -nan is true).

min, max and quantile_cont take the values in this order (“in the specified order”); the docs do not restate NaN’s place for each aggregate. duckdb-quantile-disc probes agg.order for fp64.

Round 4 (probes): median and quantile_cont sort NaN above every number: of 1, 2, NaN, 3 the median is 2.5, quantile_cont(x, 0.75) and quantile_cont(x, 1.0) are NaN and quantile_disc(x, 0.75) is 3.0; of 1, NaN the median is NaN.

The reference: nan_order: GREATEST, NaN above +inf, for sorting, MIN, MAX, <, greatest and least (docs/core-semantics.md). Substrait’s comparisons are IEEE (NaN < x false); ST-4 records the gap.

Options: nan_order.

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

Constructs: <, >, greatest, least, min, max, quantile_cont, median.

See: issues #89.

Provenance:

duckdb-round-digits-2: round(DOUBLE, d): an overflowing result keeps the input

Engine: duckdb 2.0.0; area: doubles; status: no-finding; evidence: probe.

Verdict: the reference (DuckDB 2.0’s formula, #335)

DuckDB rounds to d digits: round(x · 10^d) / 10^d for d ≥ 0 and round(x / 10^-d) · 10^-d below zero, and gives x itself where that is not finite (round(1.5e308, -308) is 1.5e308). Below zero, when 10^-d is infinite, every finite x gives 0 (round(1.5e308, -309) is 0). DuckDB 1.4.1 gave 0 wherever a negative d left no finite result, which duckdb-R15-round-digits recorded.

The reference: FloatCode.roundTo (SubstraitLean/Float.lean) follows DuckDB 2.0’s formula, and the Polars and SQL writers write it.

Options: rounding.

Rows of the choice tables: fp.round rounding (fp64).

Constructs: round.

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

See: issues #242, #335; supersedes duckdb-R15-round-digits.

Provenance:

duckdb-signbit: signbit reads the sign of -0.0 and of NaN

Engine: duckdb 2.0.0; area: doubles; status: decided; evidence: probe.

Verdict: recorded: signbit is a black box Lean evaluates away from zero and NaN

signbit(x) is the IEEE sign bit of the double x: TRUE for '-0.0'::DOUBLE, -(0.0::DOUBLE) and -'nan'::DOUBLE, FALSE for +0.0 and 'nan'::DOUBLE. The literal -0.0 is a DECIMAL, which converts to +0.0, so signbit(-0.0) is FALSE. An integer argument is converted to a double.

The docs are silent on the type of the literal -0.0, a DECIMAL that loses the sign: a docs gap. No upstream test pins signbit(-0.0) of the literal; it may change between versions.

The reference: The model’s double codes have one zero and one NaN (SubstraitLean/Float.lean), so they do not determine signbit there. signbit is a black box (#328) whose only law is nonNull; Lean’s evaluator gives c < 0 for every other code and refuses to print rows at zero and NaN.

Options: nan_kind, zero_sign.

Rows of the choice tables: fp.neg zero_sign (fp64); nan_kind (fp64).

Constructs: signbit.

See: upstream ledger ST-4; issues #328.

Provenance:

duckdb-ulp-arith: + - * / on DOUBLE and FLOAT round once

Engine: duckdb 2.0.0; area: doubles; status: no-finding; evidence: probe.

Verdict: correctly rounded (≤ 0.5 ulp) in each type, also a division by a literal

+, -, * and / on DOUBLE are the C++ operators on double, on FLOAT the operators on float: one IEEE operation, rounded once to nearest, ties to even, in the operands’ own type. A FLOAT operation stays in binary32 and returns FLOAT. A division by a literal is the same division.

Bounds in ulps of the result type: add, sub, mul, div ≤ 0.5 for fp64 and fp32 (correctly rounded); x / c for a literal c ≤ 0.5. Observed on macOS arm64, 3 000 inputs each: max 0.5000 and 0 results off the correctly rounded value, for every operation and type. These are hardware operations, so the bound holds on every platform; the Linux run should give the same.

The reference: add, subtract, multiply and divide on doubles round once, rounding: TIE_TO_EVEN (Lean’s native Float). sverify’s ε for them is 0.5 ulp (#85).

Options: rounding, fp32.

Rows of the choice tables: fp.arith rounding (fp32, fp64); fp.div rounding (fp32, fp64).

Constructs: +, -, *, /.

Settings: {"platform": "macOS arm64"}.

Provenance:

  • 2026-11-04, source-reading, by characterization pipeline round 5 (ulp bounds)
  • 2026-11-04, probe, by characterization pipeline round 5 (ulp bounds): Probe script ulp_probe.py (session scratchpad, not in the repository), run from python/ as uv run --with mpmath python ulp_probe.py --n 3000 --seed 1 --out ulp.json: about 3 000 inputs per operation and type, random and hard cases, against mpmath at 256 bits. ‘bad’ counts results that differ from the correctly rounded value; the max is in ulps of the result type at the exact value’s binade. DuckDB runs on a table loaded from Parquet, Polars on a LazyFrame with collect(engine=‘streaming’), so the column kernels run. DuckDB 2.0.0 and Polars 2.1.0 (Python wheels), macOS 26.6 arm64, Python 3.12.11, mpmath 1.3.0.
    • SELECT a + b, a - b, a * b, a / b FROM t -- t(a DOUBLE, b DOUBLE), 3 000 rows: random signs and exponents in [-60, 60], every fifth pair nearly equal gives max 0.5000, 0.5000, 0.4994, 0.4999 ulp; 0 of 3 000 off the correctly rounded value for each
    • SELECT a + b, a - b, a * b, a / b FROM t -- t(a FLOAT, b FLOAT), the same pairs rounded to binary32 gives max 0.5000, 0.5000, 0.5000, 0.5000 ulp of binary32; 0 of 3 000 off; result type FLOAT
    • SELECT a / CAST(c AS DOUBLE) FROM t -- c in 49, 3, 10, 0.1, 7.3; also with FLOAT gives max 0.4898, 0.3333, 0.4000, 0.5000, 0.4997 ulp (fp64), the same for fp32; 0 of 3 000 off

duckdb-ulp-libm: Elementary functions and logarithms: the platform libm on doubles

Engine: duckdb 2.0.0; area: doubles; status: open; evidence: probe.

Verdict: sqrt correctly rounded; macOS: tan 2.4 ulp, log(b, x) 1.4 ulp, the rest < 1 ulp; Linux: glibc’s bounds

Each function is one <cmath> call on double (std::sqrt, std::exp, std::log, std::log10, std::log2, std::pow, std::sin, …): the platform’s libm, so every bound but sqrt’s is platform-dependent. log(b, x) is log10(x) / log10(b): two libm calls and a division. pow has no shortcuts. A FLOAT argument widens exactly to DOUBLE and the result is DOUBLE (duckdb-float-math-double), so an fp32 input gets the fp64 libm answer.

Max error in ulps of the DOUBLE result, measured on macOS arm64 (3 000 inputs each, fp64 inputs / FLOAT inputs widened): sqrt 0.5 / 0.5 (correctly rounded on every platform); exp 0.502 / 0.504; ln 0.500 / 0.500; log10 0.500 / 0.498; log2 0.502 / 0.498; log(b, x) 1.42 / 1.44; pow 0.504 / 0.503 (also with integer exponents 2 to 9: ≤ 0.505); sin 0.77 / 0.62; cos 0.83 / 0.77; tan 2.30 / 2.40; asin 0.61 / 0.61; acos 0.89 / 0.89; atan 0.72 / 0.64; atan2 0.56 / 0.51.

tan exceeds 1 ulp on macOS: 1 252 of 3 000 results miss the correctly rounded value, at moderate arguments too, e.g. tan(1.0391439199447632) = 1.7002787929535204 where the correctly rounded value is 1.7002787929535208 (1.9 ulp). log(b, x) misses on a quarter of the inputs. ln, log10 and log2 were correctly rounded on every probed input but log2 once (0.502); exp, pow, atan2 within 0.51.

Cast back to FLOAT, every FLOAT-input result above was within 0.5 ulp of binary32 (0 off the correctly rounded binary32 value for each function): the extra rounding from DOUBLE to FLOAT can only matter when the DOUBLE lies within its own error of a binary32 midpoint.

Linux x86_64 (glibc, version unknown; the bounds are the glibc 2.39 manual’s): exp, ln, sin, cos, asin, acos, atan, pow 1 ulp; log10, log2 2 ulp; atan2, tan, sqrt no known error; log(b, x) then about 2 · 2 + 0.5 ulp by its formula. To be measured on homelab.

Linux x86_64 (glibc 2.39), same 3000 inputs per function: most functions are within about 0.5 ulp where macOS is not (sin 0.50 against 0.77, acos 0.50 against 0.89, tan at moderate arguments 0.51 against 2.4), but cos and tan of DOUBLE reach 7.95 and 14.36 ulp at x = 5.319372648326541e+255, near a zero of cos (glibc gives cos = -4.68716592425462e-19, the exact value is -4.6871659242546276e-19; macOS is within 0.05 ulp there, checked with mpmath at 3000 bits). log10 reaches 1.35 ulp on Linux (0.50 on macOS) and log(b, x) 1.47 to 1.56. So no libm-backed function has a platform-independent bound below glibc’s: take the larger of the two columns, and more for huge arguments of cos and tan.

The docs are silent on the math functions’ accuracy: a docs gap. No upstream test pins this, and none can, since DuckDB’s sqllogictests compare floating results within 1 %; it may change between versions.

The reference: The reference computes these with Lean’s Float, which calls the same platform libm, and fixes rounding: TIE_TO_EVEN; the core leaves the last bits to each engine (#54). sverify’s ε for a function is the engine’s bound on the platform it runs on (#85), so the ε table needs a column per platform.

Options: rounding, fp32.

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

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

Settings: {"ieee_floating_point_ops": true, "platform": ["macOS arm64", "Linux x86_64"]}.

Provenance:

Hard cases included: ln, log10, log2 at 1 ± k·2^-52 and 1 ± 1e-6; sin, cos, tan at multiples of π/2 ± 1e-6, at |x| up to 1e308 (1e22, 6381956970095103·2^797); asin, acos near 0.5 and ±1; exp near 0 and up to ±700. - SELECT sqrt(x), exp(x), ln(x), log10(x), log2(x), sin(x), cos(x), tan(x), asin(x), acos(x), atan(x) FROM t -- t(x DOUBLE), one function per run on its own inputs gives max ulp (results off the correctly rounded value of 3 000): sqrt 0.49998 (0), exp 0.5015 (5), ln 0.5000 (0), log10 0.4999 (0), log2 0.5017 (1), sin 0.7687 (108), cos 0.8319 (118), tan 2.3017 (1 252), asin 0.6068 (121), acos 0.8857 (274), atan 0.7217 (13) - SELECT atan2(a, b), pow(a, b), log(a, b) FROM t -- t(a DOUBLE, b DOUBLE) gives atan2 0.5561 (10), pow 0.5044 (4), log(b, x) 1.4218 (741 of 3 000) at log(7.5, 6.433942787503329e-50) - SELECT pow(x, n) FROM t -- n = 2 … 9, x in ±[0.5, 2] gives max 0.4994 to 0.5053 ulp; at most 5 of 3 000 off - the same functions on t(x FLOAT) gives DOUBLE results: sqrt 0.5, exp 0.5036, ln 0.4999, log10 0.4977, log2 0.4984, sin 0.6240, cos 0.7690, tan 2.4028 (958 of 2 323), asin 0.6122, acos 0.8898, atan 0.6414, atan2 0.5104, pow 0.5030, log(b, x) 1.4368; CAST(… AS FLOAT) of each ≤ 0.5000 ulp of binary32, 0 off - SELECT tan(CAST(1.0391439199447632 AS DOUBLE)) gives 1.7002787929535204 (mpmath: 1.70027879295352078…, correctly rounded 1.7002787929535208); the same from Python's math.tan and libSystem's tan via ctypes - 2026-11-04, probe, by characterization pipeline platform run (homelab, Linux x86_64, glibc 2.39-0ubuntu8.9; scratchpad ulp_probe.py –n 3000 –seed 1, the same inputs as the macOS run): Same seed and inputs as the macOS measurement. - ulp_probe.py output on homelab: cos fp64 max 7.954598 ulp, tan fp64 max 14.360645 ulp at 5.319372648326541e+255; sin 0.501170, asin 0.501869, acos 0.501590, atan 0.499671, atan2 0.499917, log10 1.347590 (DuckDB), log(b, x) 1.470205 (DuckDB), 2.132601 (Polars) - python3 -c 'import math; x=5.319372648326541e+255; print(repr(math.cos(x)), repr(math.tan(x)))' gives -4.68716592425462e-19 -2.1334853857537075e+18 - 2026-11-04, docs, by characterization pipeline reverse pass - https://duckdb.org/docs/2.0/sql/data_types/numeric.html IEEE 754 to the extent the processor, OS and compiler support it; no accuracy stated for math functions - 2026-11-04, engine-test, by characterization pipeline reverse pass - https://github.com/duckdb/duckdb/blob/v2.0.0/test/sqlite/result_helper.cpp#L543-L594 float results compared approximately (1 %): no upstream test can pin ulps

duckdb-ulp-round: round(x, d): one rounding after the decimal pick

Engine: duckdb 2.0.0; area: doubles; status: no-finding; evidence: probe.

Verdict: result ≤ 0.5 ulp from the decimal it picks; the pick follows x·10^d, off the exact decimal on near-ties

round(x, d) for d ≥ 0 computes std::round(x · 10^d) / 10^d in double (10^d from std::pow(10, d), exact up to 10^22), and a FLOAT input widens to double and casts the quotient back to FLOAT. The product rounds once, then std::round picks an integer n (ties away from zero), and the division rounds n / 10^d once, correctly.

Bounds: the result is within 0.5 ulp of n / 10^d for fp64 (observed max 0.4998 for d up to 12), and for FLOAT within 0.5 ulp of binary32 in the probes (two roundings, double then float). The pick n is the exact decimal rounding of x except where x · 10^d rounds onto or across a tie: on random decimals with d to d + 4 digits, 0.6 to 4.6 % of the fp64 inputs (9 to 46 of 1 000), and on decimal-text ties such as 0.865 or 4.05, about half. Off by one unit in the d-th digit there. FLOAT inputs had no wrong pick (0 of 3 000 per d), since a binary32 value times 10^d is exact enough in double.

No libm beyond pow(10, d) and the hardware round, so the bound is platform-independent where pow(10, d) is exact (d ≤ 22).

The reference: The reference’s round with digits is DuckDB’s formula (#335, duckdb-R15-round-digits), TIE_AWAY_FROM_ZERO, so the pick matches; the error to the exact decimal is the formula’s, and only the final division’s 0.5 ulp is rounding error in the #85 sense.

Options: rounding, fp32.

Rows of the choice tables: fp.round rounding (fp32, fp64).

Constructs: round.

Settings: {"platform": "macOS arm64"}.

Provenance:

  • 2026-11-04, source-reading, by characterization pipeline round 5 (ulp bounds)
  • 2026-11-04, probe, by characterization pipeline round 5 (ulp bounds): Probe script ulp_probe.py (session scratchpad, not in the repository), run from python/ as uv run --with mpmath python ulp_probe.py --n 3000 --seed 1 --out ulp.json: about 3 000 inputs per operation and type, random and hard cases, against mpmath at 256 bits. ‘bad’ counts results that differ from the correctly rounded value; the max is in ulps of the result type at the exact value’s binade. DuckDB runs on a table loaded from Parquet, Polars on a LazyFrame with collect(engine=‘streaming’), so the column kernels run. DuckDB 2.0.0 and Polars 2.1.0 (Python wheels), macOS 26.6 arm64, Python 3.12.11, mpmath 1.3.0.
    • SELECT round(x, d) FROM t -- d in 0, 1, 2, 3, 5, 8, 12; t(x DOUBLE): 1 000 random decimals with d … d+4 digits, 1 000 decimal-text ties (k·10^-d ± 0.5·10^-d), 1 000 k/10^d + 5/10^(d+1) gives final rounding ≤ 0.4000, 0.4800, 0.4960, 0.4995, 0.4999, 0.4998 ulp (d = 1 … 12), 0 at d = 0; wrong pick 0, 703, 816, 862, 837, 785, 994 of 3 000 (0, 9, 15, 12, 9, 7, 46 of the 1 000 random decimals), e.g. round(4.05, 1) = 4.1 (the double is below 4.05)
    • the same on t(x FLOAT) gives ≤ 0.5 ulp of binary32 from the exact decimal rounding; 0 wrong picks for every d

strings

duckdb-F4: Collations, macros and identifiers

Engine: duckdb 2.0.0; area: strings; status: no-finding; evidence: engine_test.

Verdict: frontend

DuckDB has collations (NOCASE, NOACCENT), macros that shadow functions, and case-insensitive identifiers.

The reference: The replay skips queries under a collation or macro, and follows the identifiers.

Options: case_sensitivity, type_names.

Constructs: COLLATE, CREATE MACRO.

Provenance:

  • 2026-11-01, replay, by commit 20b02e9: Recorded from the replay’s translation of DuckDB’s SQL to DataFusion’s (sverify_dev.slt.translate).

duckdb-R13: substring with a negative start

Engine: duckdb 2.0.0; area: strings; status: fixed; evidence: engine_test.

Verdict: fixed (#209): WRAP_FROM_END

WRAP_FROM_END is native, but sverify’s SQL normalizes the start for every engine (CASE WHEN a < 0 THEN a + length + 1, then the LEFT_OF_BEGINNING normalization it already had).

DataFusion’s own substring is LEFT_OF_BEGINNING, so a wrapping one whose start is not a constant ≥ 0 is refused there.

substring counts code points from 1 (ONE_BASED_CHARS): DuckDB’s tests take substring(s, 1, 1) and substring(s, 2, 1) of emoji strings, substring('A3🦤u🦆f', -3, 3) = u🦆f and substring('🦤🦆f', -3, 2) = 🦤🦆, and the replay agrees with the reference on each.

The reference: Substrait: negative_start: [WRAP_FROM_END, LEFT_OF_BEGINNING, ERROR].

Lean: was LEFT_OF_BEGINNING, as DataFusion does (substring('hello' FROM -2 FOR 2) = ''). Fixed (#209): the reference is WRAP_FROM_END when the plan omits the option (Text.substrWrap: 'lo'; a start that wraps past the beginning, or 0, is left of it, so substring('hello', -10, 7) = 'he', as in DuckDB). A plan that sets LEFT_OF_BEGINNING gets that (sverify’s Ibis producer sets it for the starts it computes); ERROR is refused.

Options: negative_start, string_positions.

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

Constructs: substring.

See: about 10 entries of the replay ledger; issues #209.

Provenance:

duckdb-R15-digests: md5, sha256 of VARCHAR

Engine: duckdb 2.0.0; area: strings; status: no-finding; evidence: probe.

Verdict: the reference

Gives the reference’s value on every probed row.

The reference: md5, sha256.

Options: digests.

Constructs: md5, sha256.

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

See: issues #242, #50.

Provenance:

  • 2026-11-02, probe, by sverify_dev.probe (#50, commit f1c86f3): sverify_dev.probe runs the function at each signature on edge cases (0, ±1, ±2, 3, ±7, the 64-bit bounds, 0.5, ±2.5, 1e308, ±inf, NaN, NULL), one row at a time from columns, under every combination of error_on_division_by_zero and ieee_floating_point_ops. Lean evaluates the Substrait call on the same rows once per candidate value of its option (substrait-check --outcomes), and the candidates whose outcomes all match are DuckDB’s options. No input is -0.0 (duckdb-R14).

duckdb-R15-regexp: regexp_extract, regexp_replace of constant patterns, on ASCII text

Engine: duckdb 2.0.0; area: strings; status: no-finding; evidence: probe.

Verdict: the reference (RE2’s meaning, #50)

With and without 'g', DuckDB gives the reference’s value on every probed row.

The reference: regexp_extract, regexp_replace: RE2’s meaning (#50).

Options: regex_dialect.

Constructs: regexp_extract, regexp_replace.

Settings: {"error_on_division_by_zero": [true, false], "ieee_floating_point_ops": [true, false]}.

See: issues #242, #50.

Provenance:

  • 2026-11-02, probe, by sverify_dev.probe (#50, commit f1c86f3): sverify_dev.probe runs the function at each signature on edge cases (0, ±1, ±2, 3, ±7, the 64-bit bounds, 0.5, ±2.5, 1e308, ±inf, NaN, NULL), one row at a time from columns, under every combination of error_on_division_by_zero and ieee_floating_point_ops. Lean evaluates the Substrait call on the same rows once per candidate value of its option (substrait-check --outcomes), and the candidates whose outcomes all match are DuckDB’s options. No input is -0.0 (duckdb-R14).

duckdb-case-mapping: upper, lower: Unicode’s simple one-to-one case mappings

Engine: duckdb 2.0.0; area: strings; status: shimmed; evidence: probe.

Verdict: char_set: UTF8 with simple mappings; sverify guards non-ASCII text

upper and lower map each code point on its own with utf8proc’s simple case mappings, so the length never changes: upper('ß') is ẞ (U+1E9E), upper('straße') is STRAẞE, upper('fi') stays fi, lower('İ') is i (one character), and lower('ΣΑΣ') is σασ, with no final sigma. Accented Latin and Greek letters map as expected (éÿ → ÉŸ, ÀΩ → àω); dž uppercases to DŽ.

The docs are silent on simple against full case mappings: a docs gap. No upstream test pins ß, fi or İ; it may change between versions.

The reference: upper, lower with char_set: UTF8 are black boxes defined as Rust’s str::to_uppercase and to_lowercase: full mappings, which can change the length (ß → SS, fi → FI, İ → i̇) and use a final sigma (σας). On ASCII text the two agree.

The written SQL raises on non-ASCII text for every engine (ascii_only, #70), so DuckDB never returns its mapping there. ST-10 records that Substrait’s UTF8 does not say which Unicode mapping.

Options: char_set.

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

Constructs: upper, lower, ucase, lcase.

See: upstream ledger ST-10; issues #70.

Provenance:

duckdb-printf: printf and format: strict, raise on a format that does not fit

Engine: duckdb 2.0.0; area: strings; status: decided; evidence: probe.

Verdict: recorded: printf and format are black boxes that may raise

printf(fmt, …) (C’s format strings) and format(fmt, …) ({} fields) are NULL when any argument is NULL, the format included.

Both raise an Invalid Input Error when a field does not fit its value (printf('%d', 'x'), format('{:d}', 'x')) or has no value (printf('%d'), format('{} {}', 1)). A format without fields ignores its values: printf('abc', 1) is abc.

printf formats a signed integer as a BIGINT whatever its width: printf('%x', -1::TINYINT) is ffffffffffffffff, while format('{:x}', -1) is -1. A boolean is true/false under %s and 1 under %d.

The docs are silent on NULL arguments, errors, extra arguments and the BIGINT widening: a docs gap. No upstream test pins the widening of a negative TINYINT under %x; it may change between versions.

The reference: printf and format of a format and one value are black boxes (#328): strict, may raise (a domain error in verify), and a string where they do not. The value is an integer, string or boolean (BlackBox.Ty.atom): a double’s code and a date are integers in the model, which the function could not tell apart. More values are refused.

Options: cast_formatting, raise_set.

Constructs: printf, format.

See: issues #328.

Provenance:

duckdb-string-match-case: LIKE, starts_with, strpos, replace: case-sensitive

Engine: duckdb 2.0.0; area: strings; status: no-finding; evidence: probe.

Verdict: CASE_SENSITIVE under the default BINARY collation

LIKE and like_escape match case-sensitively; ILIKE and ilike_escape case-insensitively, “according to the active locale”. Strings compare by their binary UTF-8 contents under the default BINARY collation, so 'hello' = 'hElLO' is FALSE; the NOCASE collation, per expression, per column or as default_collation, compares regardless of case.

contains and suffix (ends_with) state that collations are not supported. For starts_with, instr (strpos, position) and replace the docs say nothing about case or collations; their case sensitivity rests on the binary default. Whether default_collation = NOCASE reaches LIKE or these functions is for round 2 (source).

Round 2 (source): LIKE (~~) matches bytes (LikeOperatorFunction). LIKE, NOT LIKE, like_escape, ILIKE, starts_with (^@), contains and instr (strpos, position) push combinable collations (PUSH_COMBINABLE_COLLATIONS), so an argument typed VARCHAR COLLATE NOCASE wraps both sides in lower and the match ignores case; the docs’ note that contains supports no collation does not match this. prefix, suffix (ends_with) and replace keep the default handling, which pushes nothing, so they compare bytes under any collation. The push reads only a collation carried by an argument’s type and returns early when there is none, so by this reading default_collation alone does not reach these functions; a probe should confirm it.

Round 3 (engine tests): DuckDB’s tests pin strpos('HELLO', 'el') = 0, LIKE matching case-sensitively without a collation, replace keeping the other case, and contains, starts_with, strpos and LIKE ignoring case for a NOCASE argument. No test runs them under default_collation alone.

Round 4 (probes): under SET default_collation = 'nocase', 'Hello' = 'hello' is true (literals and a column), ORDER BY sorts A, a, b, B and count(DISTINCT …) of a, A, b, B is 2. LIKE 'h%', contains(v, 'ELL'), starts_with(v, 'he'), strpos(v, 'EL') (0), ends_with, prefix and replace still match bytes, case-sensitively, and ILIKE ignores case as always. So the setting changes equality, sorting and grouping, and leaves LIKE and the string functions case-sensitive.

Positions (round 5, replay): strpos, instr and position count code points from 1 and give 0 without a match (ONE_BASED_CHARS): instr(s, 'ñ') is 5 and instr(s, '🦆 end') 20 in test_instr_utf8.test, instr(s, 'h') is 0 where s has no h; the replay agrees with the reference on each.

The reference: case_sensitivity: the reference is CASE_SENSITIVE (docs/core-semantics.md); ilike is a function of its own (str.opaque). F4 records that the replay skips queries under a collation.

Options: case_sensitivity, string_positions.

Rows of the choice tables: str.match case_sensitivity (string); str.match string_positions (string; evidence engine_test).

Constructs: LIKE, like_escape, ILIKE, starts_with, prefix, ends_with, suffix, contains, strpos, instr, position, replace.

Settings: {"default_collation": ["BINARY (the default)", "nocase"]}.

Provenance:

duckdb-substring-range: substring raises for an offset or a length outside 32 bits

Engine: duckdb 2.0.0; area: strings; status: fixed; evidence: probe.

Verdict: fixed (#336): the reference raises too

DuckDB’s substring(s, start, length) raises “Substring offset outside of supported range” for a start outside [-2³², 2³²), and “Substring length outside of supported range” for a length of 2³² or more. With a NULL string, offset or length it gives NULL.

The reference: Was: no such error; Rel.eval returned rows, and verify reported nothing (#336).

Now: the same range is an error in the reference (Text.substrInRange, Issue.domain), reported by verify unless the offset’s bounds rule it out. The size hypothesis for strings is 2³² characters (Text.sizeCap). DuckDB raises itself; the written plan guards the offset its wrap-around arithmetic shifts.

Options: substring_range.

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

Constructs: substring.

See: issues #336.

Provenance:

  • 2026-11-03, probe, by session quick-fixes (#336), duckdb 2.0.0 Python package
    • SELECT substring('hello', 4294967295, 2), substring('hello', -4294967296, 2), substring('hello', 1, 4294967295) gives '', '', 'hello'
    • SELECT substring('hello', 4294967296, 2) gives Out of Range Error: Substring offset outside of supported range
    • SELECT substring('hello', -4294967297, 2) gives Out of Range Error: Substring offset outside of supported range
    • SELECT substring('hello', 1, 4294967296) gives Out of Range Error: Substring length outside of supported range
    • SELECT substring(NULL::VARCHAR, -4294967297, 2), substring('abc', NULL::BIGINT, 4294967296) gives NULL, NULL
  • 2026-11-03, replay, by sverify_dev.slt (#240), reported in #336

temporal

duckdb-R10: Casts between date and timestamp literals

Engine: duckdb 2.0.0; area: temporal; status: fixed; evidence: engine_test.

Verdict: fixed (#206)

DuckDB’s conversions are the standard ones (native).

The reference: Substrait: a date is a day, and a precision_timestamp a point in time. A cast from date to timestamp is that day at midnight.

Lean: a date or timestamp literal was an integer in the model (argKind, Parse.lean:833), so CAST(DATE '2020-05-05' AS TIMESTAMP) took the .int → .time branch (Parse.lean:1248) and read 18387 days as 18387 µs. (TIMESTAMP …)::DATE kept the microsecond count as days. Then ts >= DATE '1993-08-15' compared microseconds with days, and COALESCE(date, timestamp) mixed both codes. A deviation and a soundness bug.

Fixed (#206): date and timestamp constants keep their kinds; a cast from a date is that day at midnight, a cast from a timestamp floors to its day, and a comparison of a date with a timestamp that the plan does not cast is refused. A cast of a date column to a timestamp is still refused, as before.

Options: cast_parsing, timestamp_precision.

Constructs: CAST, cast, COALESCE, >=.

See: about 60 entries of the replay ledger; issues #206.

Provenance:

duckdb-date-range: Dates past 9999 and before year 1: computed, up to DuckDB’s own limits

Engine: duckdb 2.0.0; area: temporal; status: shimmed; evidence: probe.

Verdict: computes past 0001-01-01 … 9999-12-31; the written plan guards

Date arithmetic is not limited to 0001-01-01 … 9999-12-31: DATE '9999-12-31' + 1 is 10000-01-01, DATE '0001-01-01' - 1 is 0001-12-31 (BC) (year 0), and an interval of months or a second past a timestamp’s last one carry on. year(DATE '0001-01-01' - 1) is 0.

DuckDB’s dates run from 5877642-06-25 BC to 5881580-07-10; a day past either raises Out of Range Error: Date out of range. DATE + INTERVAL gives a timestamp, whose range is narrower (to 294247-01-10): past it, Conversion Error: Date and time not in timestamp range.

TIMESTAMPTZ + INTERVAL (and -) is the ICU extension’s: it sets an ICU calendar (proleptic Gregorian, in the TimeZone setting) to the instant, adds months and days, then hours, minutes, seconds and milliseconds (lowest first when any part is negative), and reads the instant back. Nothing checks 0001-01-01 … 9999-12-31; it raises (Conversion Error: ICU date overflows timestamp range) only when the result leaves the 64-bit microsecond range, and an infinite timestamp passes through. Round 2 reading, not probed.

The docs are silent on the DATE range and the Date out of range error: a docs gap. No upstream test pins DATE '9999-12-31' + 1; it may change between versions.

The reference: date_range: date arithmetic whose operand or result leaves 0001-01-01 … 9999-12-31 raises (#251), reported by verify. The written SQL checks the result against the reference’s dates and raises “outside the reference’s dates” (sql/core/_base.py), so DuckDB’s wider range is never returned; with constant operands the check is made when the plan is written.

Options: date_range, raise_set.

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

Constructs: DATE + INTEGER, DATE + INTERVAL, TIMESTAMP + INTERVAL, TIMESTAMPTZ + INTERVAL, date_diff, year.

See: issues #251.

Provenance:

duckdb-time-plus-interval: TIME ± INTERVAL wraps around midnight; no date_trunc of a TIME

Engine: duckdb 2.0.0; area: temporal; status: no-finding; evidence: probe.

Verdict: no-finding: the reference wraps too; months refused

TIME + INTERVAL wraps around midnight in both directions: the result is the time plus the interval’s microseconds, modulo a day. TIME '23:30' + INTERVAL 1 HOUR = 00:30, TIME '00:30' - INTERVAL 1 HOUR = 23:30, and a negative interval subtracts.

The interval’s days and months are dropped: TIME '10:00' + INTERVAL 1 DAY and + INTERVAL 1 MONTH are 10:00; + INTERVAL '1 day 2 hours' is 12:00. Its microseconds may exceed a day: + INTERVAL 25 HOUR is 11:00.

DuckDB has no date_trunc of a TIME (a binder error). sverify’s SQL truncates it on 1970-01-01 and casts back: CAST(date_trunc(u, DATE '1970-01-01' + t) AS TIME) (DuckDBDialect.time_trunc).

TIME - INTERVAL negates the interval first, and raises where its microseconds are -2^63: TIME '12:00:00' - INTERVAL '-4611686018427387904 microseconds -4611686018427387904 microseconds' is Out of Range Error: Interval micros value out of range (DuckDB’s types/test_interval_negation_overflow.test). TIME + the same interval wraps.

The reference: A time of day plus a constant interval of days or less is Time.timeOfDay (the microseconds modulo a day, Euclidean) of the sum, as DuckDB computes. An interval with months is refused (temporalArith): DuckDB drops them, which the model does not follow.

Truncating a time of day to an hour, minute, second, millisecond or microsecond is Time.trunc, a floor of the microseconds, which stays within the day. Coarser units are refused.

The SQL lift refuses TIME - INTERVAL with -2^63 microseconds, where DuckDB raises, and an interval beyond 64-bit microseconds. Plans from the Ibis producer reduce the interval modulo a day, so the written SQL never negates such an interval.

Options: time_interval_months.

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

Constructs: TIME + INTERVAL, TIME - INTERVAL, date_trunc.

See: upstream ledger IB-2; issues #507.

Provenance:

  • 2026-11-04, probe, by Claude session for #507 (duckdb 2.0.0, Python)
    • SELECT TIME '23:30' + INTERVAL 1 HOUR gives 00:30:00
    • SELECT TIME '00:30' - INTERVAL 1 HOUR gives 23:30:00
    • SELECT TIME '00:30' + INTERVAL (-1) HOUR gives 23:30:00
    • SELECT TIME '00:30' + INTERVAL '-90 minutes' gives 23:00:00
    • SELECT TIME '10:00' + INTERVAL (-49) HOUR gives 09:00:00
    • SELECT TIME '23:59:59.999999' + INTERVAL 1 MICROSECOND gives 00:00:00
    • SELECT TIME '10:00' + INTERVAL 1 DAY gives 10:00:00
    • SELECT TIME '10:00' + INTERVAL 1 MONTH gives 10:00:00
    • SELECT TIME '10:00' + INTERVAL 25 HOUR gives 11:00:00
    • SELECT TIME '10:00' + INTERVAL '1 day 2 hours' gives 12:00:00
    • SELECT TIME '10:00' + INTERVAL 9223372036 SECOND gives 09:47:16
    • SELECT date_trunc('hour', TIME '10:37:12.5') gives Binder Error: No function matches the given name and argument types 'date_trunc(STRING_LITERAL, TIME)'
    • SELECT CAST(date_trunc('hour', DATE '1970-01-01' + TIME '10:37:12.5') AS TIME) gives 10:00:00
    • SELECT TIME '12:00:00' - INTERVAL '-4611686018427387904 microseconds -4611686018427387904 microseconds' gives Out of Range Error: Interval micros value out of range
    • SELECT TIME '12:00:00' + INTERVAL '-4611686018427387904 microseconds -4611686018427387904 microseconds' gives 07:59:05.224192

duckdb-time-zones: Time zones: ICU, the session’s TimeZone; a DST gap moves forward, an overlap takes standard time

Engine: duckdb 2.0.0; area: temporal; status: open; evidence: probe.

Verdict: time zones outside the model; DuckDB’s are ICU’s

TIMESTAMP WITH TIME ZONE is an instant (microseconds, UTC); the ICU extension, built into the Python wheel, reads and prints it in the session’s TimeZone setting, whose default is the host’s zone (Europe/Berlin on the probing laptop), so a query’s text output depends on the host unless the setting is set.

A local time in a DST gap moves forward by the gap (2024-03-31 02:30 in Berlin is 03:30:00+02); one in an overlap takes the later, standard-time instant (2024-10-27 02:30 is 02:30:00+01). AT TIME ZONE turns a TIMESTAMP into such an instant and a TIMESTAMPTZ into a local TIMESTAMP; both instants of the overlap give 02:30:00.

Calendar arithmetic follows the zone: + INTERVAL 1 DAY across the spring change keeps the wall clock (12:00:00+02), + INTERVAL 24 HOUR does not (13:00:00+02); date_trunc('day', …), date_part and a cast to DATE use the local date; date_diff('hour', …) over the change counts 23. An unknown zone raises (Not implemented Error: Unknown TimeZone).

The docs are silent on DST gaps and overlaps: a docs gap.

The reference: Time-zone-aware timestamps are a kind the model does not compute with (reference-semantics.md, Temporal): plans with them are refused, and the reference’s timestamp has no zone. The written SQL never reads TimeZone.

Options: time_zones.

Constructs: TIMESTAMPTZ, AT TIME ZONE, TimeZone, date_trunc, date_diff, + INTERVAL.

Settings: {"TimeZone": ["UTC", "Europe/Berlin"]}.

Provenance:

duckdb-yearweek: yearweek subtracts the ISO week in the years up to 0, and raises in DuckDB’s first year

Engine: duckdb 2.0.0; area: temporal; status: decided; evidence: probe.

Verdict: decided: yearweek is a black box, NULL where DuckDB raises

yearweek(d) is the ISO year of d times 100 plus its ISO week where the ISO year is above 0, and the ISO year times 100 minus the week where it is 0 or below: the day before 0001-01-01 (ISO year 0, week 52) gives -52, and DuckDB’s DATE '-0010-01-05' gives -1001. A timestamp is read as its date.

yearweek, weekday and dayofweek of an infinite date are NULL. weekday(d) is dayofweek(d), Sunday 0.

yearweek, week and isoyear raise a Conversion Error (Date out of range: -5877641-1-1) on DuckDB’s dates before 5877641-01-04 BC (day -2147483453), whose ISO year starts before DuckDB’s first date, 5877642-06-25 BC. year, weekday and dayofweek do not.

The docs are silent on the sign rule for ISO years at or below 0 and on the raise near the minimum date: a docs gap. No upstream test pins that raise; it may change between versions.

The reference: yearweek is a black box (#328), strict, with no nonNull law because of the infinite dates, and bounded by DuckDB’s dates: -587764053 to 588158053. Before 5877641-01-04 BC it is NULL (BlackBox.yearweekMin), as date_part is NULL outside chrono’s dates, and its SQL guards DuckDB’s call there. Lean’s evaluator (BlackBox.yearweek) computes DuckDB’s value, signs included. weekday lifts to date_part('dow', d), exact.

Options: week_numbering.

Constructs: yearweek, weekday.

See: issues #328.

Provenance:

casts

duckdb-R8: A cast to a narrower integer that does not fit

Engine: duckdb 2.0.0; area: casts; status: no-finding; evidence: engine_test.

Verdict: no finding (verify: cast out of range error)

Native. CAST raises (Conversion Error); RETURN_NULL is TRY_CAST, also native.

The reference: Substrait and the reference: the plan’s cast says failureBehavior: THROW_EXCEPTION, so the cast raises.

Lean: no finding. verify reports cast out of range as an error, and the checker refuses a constant one (CAST(200 AS TINYINT)). Rel.eval’s NULL there (Expr.lean:133) is the value for RETURN_NULL. A cast to BIGINT is not checked, which matters only for values that are already outside i64 (duckdb-R6).

Options: cast_failure.

Rows of the choice tables: cast_failure (any).

Constructs: CAST, TRY_CAST, cast.

See: upstream ledger ST-6; about 10 entries of the replay ledger; issues #185.

Provenance:

duckdb-R9: Casts of doubles and decimals to integers: half to even and half away from zero

Engine: duckdb 2.0.0; area: casts; status: decided; evidence: probe.

Verdict: decided: truncate; DuckDB shim

DuckDB rounds casts of FLOAT and DOUBLE to integers half to even (CAST(1.5::DOUBLE AS INTEGER) = 2, CAST(0.5::DOUBLE AS INTEGER) = 0, CAST(126.5::DOUBLE AS TINYINT) = 126; float_integer_cast.test), and casts of DECIMAL half away from zero (CAST(0.55 AS INTEGER) = 1, CAST(-0.55 AS BIGINT) = -1, CAST(2.5 AS INTEGER) = 3). avg(d)::INTEGER rounds too, half to even, since avg is a DOUBLE. (Corrected 2026-11-04: this said half away from zero for doubles as well; the earlier examples were DECIMAL literals.)

Shim: sverify’s SQL writes CAST(trunc(x) AS BIGINT) (DuckDBDialect.float_to_int).

The reference: Substrait: the cast’s rounding is not specified.

Lean: truncates (ftoi), as DataFusion does. Decided on #209: the reference truncates toward zero, so no Lean change.

Options: float_to_int.

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

Constructs: CAST, cast.

See: upstream ledger ST-6; about 28 entries of the replay ledger; issues #209.

Provenance:

duckdb-typeof-names: typeof: DuckDB’s names of the types

Engine: duckdb 2.0.0; area: casts; status: decided; evidence: probe.

Verdict: decided: fold typeof of a column to DuckDB’s name

DuckDB’s typeof gives the type’s SQL name in upper case: TINYINT, SMALLINT, INTEGER, BIGINT, HUGEINT, UTINYINT, USMALLINT, UINTEGER, UBIGINT, FLOAT, DOUBLE, DECIMAL(p,s), VARCHAR, BOOLEAN, DATE, TIME, TIMESTAMP, TIMESTAMP_S, TIMESTAMP_MS, TIMESTAMP_NS, TIMESTAMP WITH TIME ZONE, INTERVAL, BLOB, UUID; a list is its element’s name and [] (BIGINT[], VARCHAR[][]), a struct STRUCT(a BIGINT, b VARCHAR) with its field names quoted where they need it (STRUCT("x y" INTEGER)), a map MAP(VARCHAR, BIGINT). A DECIMAL without precision is DECIMAL(18,3).

An expression’s type is DuckDB’s own inference: typeof(1) is INTEGER, typeof(1.5) is DECIMAL(2,1), and an INTEGER plus an INTEGER is an INTEGER. typeof(NULL) is "NULL", quotes included.

The docs are silent on the spelling of nested types and of "NULL": a docs gap.

The reference: The type of a column is fixed when the plan is checked, so the Ibis producer folds typeof(column) to DuckDB’s name as a string constant (duckdb_type_name, ibis_substrait/casts.py). Lists of these types are folded; structs and maps are refused, and so is typeof of an expression, whose type is the engine’s inference. The plan holds DuckDB’s name whichever engine runs it, so every engine gives the same string (decided by the coordinating session for #508).

Options: type_names.

Constructs: typeof.

See: issues #508.

Provenance:

duckdb-ulp-casts: Integer and decimal to DOUBLE and FLOAT: correctly rounded but from HUGEINT

Engine: duckdb 2.0.0; area: casts; status: open; evidence: probe.

Verdict: BIGINT and DECIMAL ≤ 0.5 ulp; HUGEINT and DECIMAL(38, 0) < 1 ulp (0.625 seen)

Integers up to 64 bits convert with static_cast<double> or <float>, one hardware rounding to nearest even: ≤ 0.5 ulp, also above 2^53 (observed 0 of 3 000 off, BIGINT to DOUBLE and to FLOAT).

A DECIMAL with a nonzero scale converts with one division when the unscaled value is below 2^53 and 10^scale is exact, otherwise through fast_float’s decimal algorithm: correctly rounded, ≤ 0.5 ulp, to DOUBLE and to FLOAT (observed 0 off for DECIMAL(18, 6), (18, 17), (38, 10), (38, 30), (38, 2)).

HUGEINT, and a DECIMAL(38, 0) whose scale 0 takes the integer path, convert as double(lower) + double(upper) · 2^64, and for upper = -1 as -double(2^64 - 1 - lower) - 1: two roundings, so not always the nearest double. Bound by the formula: < 1 ulp; observed max 0.625 ulp, 5 of 3 000 HUGEINT and 4 of 1 687 DECIMAL(38, 0) values off, e.g. CAST(-55927220547740261 AS DOUBLE) is -55927220547740256 where the nearest double is -55927220547740264, and -9223372036854776833 gives -2^63 where the nearest is -2^63 - 2048. HUGEINT to FLOAT goes through that double, a third rounding (0 off in the probe).

All of these are integer and hardware operations: platform-independent.

The docs are silent on how integer and DECIMAL casts to floating point round: a docs gap. No upstream test pins the HUGEINT double rounding; it may change between versions.

The reference: Casts of integers and decimals to doubles round once to nearest, ties to even (Lean’s Float.ofInt, Float.ofScientific). A HUGEINT reaches a plan only from a sum or an explicit cast; sverify’s ε for that cast would be 1 ulp.

Options: fp32.

Constructs: CAST(BIGINT AS DOUBLE), CAST(BIGINT AS FLOAT), CAST(HUGEINT AS DOUBLE), CAST(HUGEINT AS FLOAT), CAST(DECIMAL AS DOUBLE), CAST(DECIMAL AS FLOAT).

Settings: {"platform": "macOS arm64"}.

Provenance:

DuckDB’s casts run on a table materialised first: a CAST right on read_csv is pushed into the scan and parses the text, which an earlier run measured by mistake. - CREATE TABLE h AS SELECT row_number() OVER () AS i, x FROM read_csv('big.csv', columns={'x': 'HUGEINT'}); SELECT CAST(x AS DOUBLE) FROM h ORDER BY i -- 1 500 values in ±2^126, 750 in ±2^64, 750 in [-2^64, -1] gives max 0.625 ulp, 5 of 3 000 off the nearest double; CAST(x AS FLOAT) 0 off - SELECT CAST(-55927220547740261::HUGEINT AS DOUBLE), CAST(-9223372036854776833::HUGEINT AS DOUBLE), CAST(-55927220547740261::DECIMAL(38,0) AS DOUBLE) gives -5.592722054774026e+16, -9.223372036854776e+18, -5.592722054774026e+16 (Python's float() gives -5.5927220547740264e+16 and -9.223372036854778e+18) - SELECT CAST(x AS DOUBLE), CAST(x AS FLOAT) FROM t -- t(x BIGINT): 1 500 random int64, 750 above 2^53, 750 near ties gives max 0.5000 / 0.49999 ulp; 0 of 3 000 off - SELECT CAST(x AS DOUBLE), CAST(x AS FLOAT) FROM d -- DECIMAL(18,6), (18,17), (38,10), (38,30), (38,2): 1 500 full-width values and 1 500 up to 10^10 unscaled gives max ≤ 0.49997 (fp64), ≤ 0.5000 (fp32) ulp; 0 off for each - 2026-11-04, engine-test, by characterization pipeline reverse pass - https://github.com/duckdb/duckdb/blob/v2.0.0/test/sql/cast/decimal_float_cast.test#L21-L40 DECIMAL to DOUBLE and FLOAT agree with the correctly rounded string casts (issue #22837) - https://github.com/duckdb/duckdb/blob/v2.0.0/test/sql/optimizer/expression/test_float_identity.test#L110-L125 BIGINT 2^53+1 converts to 2^53 (DOUBLE); INTEGER 2^24+1 to 2^24 (FLOAT): ties to even - 2026-11-04, docs, by characterization pipeline reverse pass - https://duckdb.org/docs/2.0/sql/data_types/typecasting.html silent on how integer and DECIMAL casts to floating point round

nulls

duckdb-kleene: Three-valued AND, OR, NOT, IN; bool_and, bool_or skip NULLs

Engine: duckdb 2.0.0; area: nulls; status: no-finding; evidence: probe.

Verdict: Kleene logic natively

NULL AND FALSE is false, NULL OR TRUE true, NULL AND TRUE, NULL OR FALSE, NOT NULL and NULL = NULL NULL. 1 IN (1, NULL) is true, NULL IN (1, NULL) and 2 NOT IN (1, NULL) NULL.

bool_and and bool_or skip NULLs: over (TRUE, NULL) both are true, over (FALSE, NULL) both false; over NULLs only or no rows both are NULL.

The reference: and, or, not and comparisons are Kleene’s; aggregates skip NULLs and are NULL over no values (reference-semantics.md, NULL and Aggregates).

Options: three_valued_logic.

Constructs: AND, OR, NOT, IN, NOT IN, bool_and, bool_or.

Provenance:

duckdb-null-concat: || propagates NULL; concat and concat_ws skip it; greatest, least skip it

Engine: duckdb 2.0.0; area: nulls; status: no-finding; evidence: probe.

Verdict: null_handling: ACCEPT_NULLS for ||; concat is IGNORE_NULLS

'a' || NULL is NULL, and so is [1] || NULL. concat('a', NULL, 'b') is 'ab' and concat(NULL, NULL) is ''; concat_ws(',', 'a', NULL, 'b') is 'a,b', and NULL with a NULL separator; list_concat([1], NULL) is [1].

Other scalar functions give NULL for a NULL argument (upper, length, substring, replace), but greatest(1, NULL, 3) is 3 and least(NULL, 2) is 2: they leave NULLs out and are NULL only when every argument is.

The reference: str_concat with null_handling: ACCEPT_NULLS, NULL in, NULL out; IGNORE_NULLS refused. sverify writes str_concat as DuckDB’s ||, natively.

The SQL lift reads DuckDB’s concat(…) as str_concat of coalesce(a, '') for each argument, and greatest, least with their NULLs left out (sql_lift/strings.py, sql_lift/core.py).

Options: null_handling.

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

Constructs: ||, concat, concat_ws, list_concat, greatest, least.

Provenance:

duckdb-null-keys: NULL keys group together and do not match in = joins; IS NOT DISTINCT FROM matches them

Engine: duckdb 2.0.0; area: nulls; status: no-finding; evidence: probe.

Verdict: NULLs group together; IS NOT DISTINCT FROM joins them; no <=>

An equi-join on = does not match NULL keys; one on IS NOT DISTINCT FROM does. A full join keeps each NULL row unmatched. x IN (SELECT NULL) and x NOT IN (2, NULL) are NULL. GROUP BY puts the NULLs in one group.

The spelling is IS NOT DISTINCT FROM; MySQL’s <=> is not a DuckDB operator (Binder Error).

The reference: Grouping and DISTINCT put NULLs together; an equi-join’s = does not match NULL, and keys compared with IS NOT DISTINCT FROM do (reference-semantics.md, NULL).

Options: null_join_keys.

Rows of the choice tables: null_join_keys (any).

Constructs: JOIN, FULL JOIN, IS NOT DISTINCT FROM, IN (subquery), GROUP BY.

Provenance:

nested

duckdb-R11: Ordering comparisons of lists

Engine: duckdb 2.0.0; area: nested; status: shimmed; evidence: engine_test.

Verdict: fixed (#207): lexicographic; DuckDB shim

DuckDB orders lists lexicographically natively, but a NULL element sorts above every value, so [1, NULL] < [1, 3] is FALSE.

Shim: it finds the first position whose elements are distinct (list_position over IS DISTINCT FROM) and gives NULL when one of them is NULL. DataFusion sorts NULL first and Polars has no list order: both refuse.

The reference: Substrait: lt(x, y) (and gt, lte, gte) takes any1, and “if either/both of x and y are null, null is returned”; it does not define an order on lists.

Lean: [1, 2] < [1, 3] was NULL on every row, a deviation. Fixed (#207), with the order decided on #209: lexicographic, element by element (Value.listLt3). A proper prefix sorts first, two NULL elements at a position are equal (as in list equality), and a NULL element against a value makes the result NULL: [1, NULL] < [2] is TRUE, [1, NULL] < [1, 3] is NULL, [NULL, 1] < [NULL, 2] is TRUE. Lists of lists are refused. The written plan spells the comparison list_lt, so every engine knows it compares lists.

Options: list_null_order.

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

Constructs: <, >, <=, >=, lt, gt, lte, gte, list_lt.

See: about 22 entries of the replay ledger; issues #207, #209.

Provenance:

duckdb-list-index: List indexing: 1-based, negative from the end, NULL out of range and at 0

Engine: duckdb 2.0.0; area: nested; status: no-finding; evidence: probe.

Verdict: 1-based; the written SQL adds 1 to Substrait’s 0-based offset

l[i] (list_extract, list_element) counts from 1; a negative index counts from the end ([10, 20, 30][-1] is 30, [-3] is 10). Index 0, an index past either end ([4], [-4], 2⁶³ − 1), a NULL index and a NULL list give NULL, with no error.

Slices l[a:b] are 1-based and inclusive ([2:3] is [20, 30], [-2:] the last two); [0:1] is [10] and an empty range is [].

The reference: Substrait’s ListElement offset is 0-based, negative from the end, NULL out of range in either direction, without wrapping (Value.listElement, #94). DuckDB’s SQL writes l[i + 1] for an offset i ≥ 0 and l[i] for a negative one, which agree on every offset (docs/nested-values.md). list_extract(l, i) read from DuckDB SQL is l[i], modelled exactly with a constant index (#327).

Options: list_indexing.

Constructs: list_extract, list_element, array_extract, l[i], l[a:b].

See: issues #94, #327.

Provenance:

duckdb-list-sum: list_sum of integers is a HUGEINT, NULL without values

Engine: duckdb 2.0.0; area: nested; status: decided; evidence: probe.

Verdict: decided: a BIGINT in the model, which raises past its range

list_sum(l) of a list of integers (of any width, or of booleans) is a HUGEINT: the sum of the non-NULL elements, NULL for an empty list, a list of NULLs or a NULL list. It does not overflow at 64 bits: list_sum([9223372036854775807, 1]) is 9223372036854775808. Of doubles it is a DOUBLE (list_sum(['inf', '-inf']) is NaN).

The docs are silent on list_sum’s result type, overflow and NULL handling: a docs gap.

The reference: list_sum of integers is a black box (#327) Lean evaluates exactly. Its result is a BIGINT, as SUM’s is (#437), so a sum outside the BIGINT range raises a domain error, and the SQL casts DuckDB’s HUGEINT to a BIGINT. list_sum of doubles is refused.

Options: empty_group_aggregates.

Constructs: list_sum.

See: issues #327.

Provenance:

duckdb-list-vectors: Vector functions of lists: NULL elements raise, a zero vector gives NaN

Engine: duckdb 2.0.0; area: nested; status: decided; evidence: probe.

Verdict: decided: black boxes with bounds that allow NaN

list_cosine_similarity, list_cosine_distance, list_distance, list_inner_product and list_negative_inner_product of two DOUBLE[] raise an Invalid Input Error where a list holds a NULL (left argument can not contain NULL values) or the two differ in length (list dimensions must be equal). A NULL list gives NULL.

For two empty lists the cosine similarity and distance are NULL, the distance and the inner products 0.0. Where a list is zero, or holds NaN or an infinity, the cosine similarity and distance are NaN. Otherwise the similarity stays in [-1, 1] and the distance in [0, 2]: CosineSimilarityOp clamps a finite similarity to [-1, 1] and rescales by the largest magnitude on overflow or underflow, and the distance is 1 − the similarity. 400000 random pairs, and a against a and -a, gave exactly 1.0, 0.0, -1.0 and 2.0 at the ends. (Corrected 2026-11-04: the range rested on random samples; the source’s clamp guarantees it.)

The array_* functions (array_cosine_similarity, array_distance, array_inner_product, …) take fixed-size ARRAYs only: on two DOUBLE[] they fail to bind. array_cross_product takes DOUBLE[3], to which a list converts, and raises a Conversion Error for another length; its result is a DOUBLE[3].

The docs are silent on NULL elements, unequal lengths, empty lists, NaN and the range: a docs gap.

The reference: Each is a black box (#327), strict, that may raise. Laws: the similarity at least -1.0 (NaN is above every number in the model’s codes, so <= 1 is not a law), the cosine distance at least +0.0, the distance not NULL and at least +0.0, the inner products and the cross product not NULL. The model has no fixed-size ARRAY, so a list stands for one and the array_* names lift to the list_* functions; the cross product is written as a DOUBLE[].

Options: raise_set.

Constructs: list_cosine_similarity, list_cosine_distance, list_distance, list_inner_product, list_negative_inner_product, array_cross_product, array_cosine_similarity.

See: issues #327.

Provenance:

  • 2026-11-04, probe, by Claude session for #327 (duckdb 2.0.0, Python)
    • SELECT list_cosine_similarity([NULL,1.0]::DOUBLE[], [1.0,1.0]::DOUBLE[]) gives Invalid Input Error: list_cosine_similarity: left argument can not contain NULL values
    • SELECT list_cosine_similarity([1.0,2]::DOUBLE[], [2.0]::DOUBLE[]) gives Invalid Input Error: list_cosine_similarity: list dimensions must be equal, got left length '2' and right length '1'
    • SELECT list_cosine_similarity([0.0,0]::DOUBLE[], [2.0,1]::DOUBLE[]), list_cosine_similarity(['nan'::DOUBLE,1.0], [1.0,1.0]::DOUBLE[]), list_cosine_similarity(['inf'::DOUBLE,1.0], [1.0,1.0]::DOUBLE[]) gives NaN, NaN, NaN
    • SELECT list_cosine_similarity([]::DOUBLE[], []::DOUBLE[]), list_cosine_distance([]::DOUBLE[], []::DOUBLE[]), list_distance([]::DOUBLE[], []::DOUBLE[]), list_inner_product([]::DOUBLE[], []::DOUBLE[]) gives NULL, NULL, 0.0, 0.0
    • SELECT max(list_cosine_similarity(a, a)), min(list_cosine_distance(a, a)), min(list_cosine_similarity(a, [-x for x in a])), max(list_cosine_distance(a, [-x for x in a])) FROM (SELECT [random()*2-1, random()*1e5, random()-0.5, random()*1e-3] a FROM range(200000)) gives 1.0, 0.0, -1.0, 2.0
    • SELECT array_cosine_similarity([1.0,2.0]::DOUBLE[], [2.0,4.0]::DOUBLE[]) gives Binder Error: No function matches the given name and argument types 'array_cosine_similarity(DOUBLE[], DOUBLE[])'
    • SELECT array_cosine_similarity([1.0,2]::DOUBLE[2], [2.0,4]::DOUBLE[2]), array_cosine_similarity([0.0,0]::DOUBLE[2], [2.0,4]::DOUBLE[2]) gives 1.0, NaN
    • SELECT array_cross_product([1.0,0,0]::DOUBLE[], [0.0,1,0]::DOUBLE[]), typeof(array_cross_product([1.0,0,0]::DOUBLE[], [0.0,1,0]::DOUBLE[])) gives [0.0, 0.0, 1.0], DOUBLE[3]
    • SELECT array_cross_product([1.0,0]::DOUBLE[], [0.0,1]::DOUBLE[]) gives Conversion Error: Cannot cast list with length 2 to array with length 3
    • test python/tests/test_black_box.py::test_vectors_keep_their_laws_on_random_data
  • 2026-11-04, source-reading, by characterization pipeline reverse pass: Corrected the description: the [-1, 1] range of the cosine similarity is the source’s clamp, no longer only random samples.
  • 2026-11-04, engine-test, by characterization pipeline reverse pass
  • 2026-11-04, docs, by characterization pipeline reverse pass

duckdb-struct-equality: Structs: NULL fields equal in = and grouping; in order a NULL field is the greatest, also in <

Engine: duckdb 2.0.0; area: nested; status: open; evidence: probe.

Verdict: structural equality, NULL = NULL inside; < two-valued with NULL greatest

{'a': 1, 'b': NULL} = {'a': 1, 'b': NULL} is true, and so is [1, NULL] = [1, NULL]: a NULL inside a struct or list equals NULL. A NULL struct or list compared with anything is NULL. Grouping and joins on struct keys put equal structs, NULL fields included, together.

Order is field by field, a NULL field after every value, in comparisons as in ORDER BY: {a: 1, b: NULL} < {a: 1, b: 2} is false and > true; ascending order puts {a: NULL, b: 0} last and descending first.

The docs are silent on a NULL struct compared with anything giving NULL: a docs gap.

The reference: Equality is structural, and NULLs inside lists and structs are equal, as in grouping (reference-semantics.md, NULL and Nested). Lists order element by element, three-valued in < (a NULL element that decides gives NULL) and NULL greatest in ORDER BY (#207); DuckDB’s lists are two-valued in < (R11, shimmed with list_lt). Structs are the lists of their fields, so the same split would hold.

DuckDB’s SQL refuses struct columns and fields for now (docs/nested-values.md), so no written plan compares structs.

Options: struct_equality.

Constructs: =, <>, <, >, ORDER BY, GROUP BY, JOIN.

See: issues #207.

Provenance:

duckdb-unnest: UNNEST drops a NULL list and an empty one; parallel UNNESTs pad with NULL

Engine: duckdb 2.0.0; area: nested; status: no-finding; evidence: probe.

Verdict: no row for [] or NULL; [NULL] gives a NULL row

unnest(l) in the select list and a lateral unnest(l) give one row per element and no row for an empty or NULL list; [NULL] gives one row with NULL. A LEFT JOIN unnest(l) ON TRUE keeps those rows with NULL. Two unnests in one select list run in parallel, the shorter padded with NULL (unnest([1, 2]), unnest([7]) gives (1, 7), (2, NULL)), and an empty one pads entirely.

The reference: UNNEST gives one row per element and none for an empty list or NULL (sverify’s own extension, reference-semantics.md, Nested). DuckDB’s SQL is a lateral UNNEST(l) WITH ORDINALITY (docs/nested-values.md).

Options: unnest_null_empty.

Constructs: UNNEST.

Provenance:

aggregates

duckdb-R6: Integer sum

Engine: duckdb 2.0.0; area: aggregates; status: deviation; evidence: probe.

Verdict: deviation: unbounded, and reported as a warning

Shim. SUM(BIGINT) returns HUGEINT and never overflows at 64 bits, so DuckDB alone answers where the reference raises. CAST(SUM(x) AS BIGINT) raises past i64. sverify’s written plan already guards this and raises (test_sum.test:68).

avg of integers returns DOUBLE in DuckDB (duckdb-avg-int). sverify’s written SQL computes an i64 average as the exact HUGEINT sum // the count (int_avg in python/src/sverify/sql/duckdb.py). DataFusion’s avg is a double, so it refuses an integer one.

Round 4 (probes): sum of TINYINT and SMALLINT is a HUGEINT, so 127 + 126 = 253 and 32767 + 32766 = 65533 without an error.

The docs are silent on sum’s result type, a HUGEINT for integers, and on overflow: a docs gap.

The reference: Substrait and the reference: sum of i8…i64 returns i64?; with overflow omitted, a sum outside i64 raises.

Lean: deviation. The sum is unbounded, and verify reports it as a warning (may wrap around, sumWraps); the parser accepts only SILENT. SUM(b) of values summing past 2⁶³ answers 4611686018427388403500, and SUM(b)::BIGINT the same, since a cast to BIGINT is not checked.

avg of integers (decided on #209, fixed): it follows the plan’s declared output type. With fp64 (DataFusion’s plans), it is the mean as a double; with i64 (Substrait’s own impl), it is the exact mean truncated toward zero (AggFn.iavg), which always fits.

Options: overflow, avg_int_result.

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

Constructs: sum, avg.

See: about 5 entries of the replay ledger; issues #185, #209.

Provenance:

duckdb-R7: sum/avg of a double constant

Engine: duckdb 2.0.0; area: aggregates; status: fixed; evidence: probe.

Verdict: fixed (#205)

DuckDB’s sums of doubles are IEEE (native): inf, -inf, nan.

Order class: a constant argument reaches sum and avg as a constant vector, and DuckDB adds it as one product per vector (input * count, at most 2048 rows), so sum(0.1) over 10 rows is 1.0 and avg(0.1) 0.1, where adding the ten values in turn gives 0.9999999999999999 and 0.09999999999999999. The product’s error is at most u·k|x|, inside the sequential bound γ_{n−1} Σ|x_i| (Higham §4.2) and #85’s. Lean’s floatSum adds the constant n times, so this path refines neither SEQUENTIAL nor, until a lemma puts fl(k·x) among the tree sums of k copies, ANY (duckdb-sum-double).

The reference: Substrait: sum(fp64) returns fp64?.

Lean: SUM(CAST('inf' AS DOUBLE)) evaluated to a 500-digit integer, and avg(42) OVER () to inf. The aggregate took a constant argument for an integer and added float codes; over a double column it was right. A deviation and a soundness bug. Fixed (#205): a constant argument has its declared type, so sum(fp64) adds doubles.

Options: nonfinite_aggregates, sum_order.

Rows of the choice tables: agg.fsum sum_order (fp64).

Constructs: sum, avg.

See: about 3 entries of the replay ledger; issues #205.

Provenance:

duckdb-arg-min-nulls: ARG_MIN/ARG_MAX: rows with a NULL on either side are skipped

Engine: duckdb 2.0.0; area: aggregates; status: no-finding; evidence: probe.

Verdict: FIRST(x ORDER BY y) over the rows where neither is NULL; ties are the engine’s

ARG_MIN(x, y) gives x at the row with the smallest y, over the rows where neither x nor y is NULL (ARG_MIN_NULL keeps a NULL x). No such rows give NULL. Doubles compare in DuckDB’s order, NaN above +inf. Of rows that tie on y it keeps the one it met first, so ties depend on the order it reads the rows. ARG_MAX is the same with the largest y.

The reference: The parser reads arg_min(x, y) as FIRST(x ORDER BY y) with the filter y IS NOT NULL (AggFn.pick, #53), and arg_max with ORDER BY y DESC; FIRST skips a NULL x. Ties are the engine’s: Rel.windowsTotal reports the result as determined only when the group’s and the order’s columns contain a key.

Options: tie_pick, nan_order.

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

Constructs: arg_min, arg_max, min_by, max_by.

See: upstream ledger IB-3, ST-4; issues #53.

Provenance:

  • 2026-11-04, source-reading, by session for #53 (exact quantiles, mode, arg_min), duckdb 2.0.0 Python package: Read at tag v2.0.0.
  • 2026-11-04, probe, by session for #53 (exact quantiles, mode, arg_min), duckdb 2.0.0 Python package
    • SELECT arg_min(a, b), arg_max(a, b) FROM (VALUES (1, NULL), (NULL, 0), (3, 5), (4, 7)) t(a, b) gives 3, 4
    • SELECT arg_min_null(a, b) FROM (VALUES (NULL, 0), (3, 5)) t(a, b) gives NULL
    • SELECT arg_min(a, b), arg_max(a, b) FROM (VALUES ('x', 'nan'::DOUBLE), ('y', 1.0)) t(a, b) gives y, x
    • SELECT arg_min(a, b), arg_max(a, b) FROM (VALUES (1, 5), (2, 5)) t(a, b) gives 1, 1
    • test python/tests/test_aggregate.py::test_arg_min_is_determined_by_a_key_and_mode_by_its_value

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

Engine: duckdb 2.0.0; area: aggregates; status: open; evidence: probe.

Verdict: plain sum / n

AVG(DOUBLE) is the plain sequential sum (duckdb-sum-double) divided by the count; favg uses the Kahan sum (duckdb-kahan-sum).

Order class: (a) the sum of duckdb-sum-double (sequential per state, partials merged in finishing order, a constant run as one product), then one division by the count (state.value / state.count, the count exact as a double). (b) γ_{h+1} Σ|x_i| / n on the mean, h as for the sum (at most n − 1), so at most γ_n Σ|x_i| / n (Higham §4.2 for the sum, one more rounding for the quotient); within #85’s avgTolScaled, which allows γ_{n+2}. (c) The source below; avg(0.1) over 10 constant rows is 0.1, where the sequential sum gives 0.09999999999999999. (d) AggFn.avg is the sum_order choice divided by n, so this refines SEQUENTIAL at one thread without constant vectors and ANY otherwise, with the same open point for a constant run’s product.

The docs are silent on the algorithm and on the constant run’s product: a docs gap. No upstream test pins the constant run’s product or the dependence on thread order; it may change between versions.

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

Options: sum_order, rounding.

Rows of the choice tables: agg.fsum sum_order (fp64); agg.fsum rounding (fp64).

Constructs: avg.

See: issues #54, #85.

Provenance:

duckdb-avg-int: AVG of integers: a DOUBLE from the exact sum

Engine: duckdb 2.0.0; area: aggregates; status: open; evidence: probe.

Verdict: DOUBLE; exact integer sum, one division (in long double for INTEGER and BIGINT)

avg has one overload per integer width it names, SMALLINT, INTEGER, BIGINT and HUGEINT, and each returns DOUBLE; there is no integer result. TINYINT has none: the binder takes the overload with the cheapest implicit cast, and a cast to BIGINT costs 101 against 110 for SMALLINT, so avg(TINYINT) runs the BIGINT one.

SMALLINT: the sum is an int64_t (RegularAdd, unchecked, out of reach below 2^48 rows) and the result double(sum) / double(count). INTEGER and BIGINT: the sum is an exact HUGEINT (AddToHugeint) and the result long double(sum) / long double(count), rounded to DOUBLE. long double is 80-bit on x86-64 Linux and the same as double on arm64 macOS and MSVC, so the last bit of a large mean can depend on the platform.

No values gives NULL. The width dispatch is one template instantiation per width (GetAverageAggregate), which is what lets this reading cover i8 to i64.

Round 4 (probes): avg of TINYINT and SMALLINT is a DOUBLE (126.5, 32766.5). avg of 2^63 - 1, 2^63 - 2, 2^63 - 3 is 9.223372036854776e+18 and of 2^53 + 1, 2^53 + 2 is 9007199254740994.0 on any platform. The last bit depends on long double: avg of 2^53 + 1, 0, 0 is 3002399751580330.5 on macOS arm64, where the sum is rounded to a double before the division; the exact mean 3002399751580331 is what an 80-bit long double gives (homelab, x86-64 Linux, to check).

The docs are silent on avg’s DOUBLE result, the long double division and the overload TINYINT takes: a docs gap. No upstream test pins the platform-dependent last bit; it may change between versions.

The reference: avg_int_result follows the plan’s declared output type (#209). With fp64 the reference averages the integers’ conversions to doubles (parseMeasure), so it rounds each value and then the sum, where DuckDB rounds the exact sum once; both are within #85’s bound.

With i64 the reference is the exact mean truncated toward zero (AggFn.iavg); sverify’s DuckDB SQL writes it as CAST(SUM(v) // COUNT(v) AS BIGINT) (sql/duckdb.py, int_avg), on the exact HUGEINT sum, so DuckDB’s AVG is not used there.

Options: avg_int_result, empty_group_aggregates.

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

Constructs: avg, mean.

See: issues #209.

Provenance:

duckdb-corr: CORR: three separate states; NaN for one pair or zero variance

Engine: duckdb 2.0.0; area: aggregates; status: decided; evidence: probe.

Verdict: cov / (std_x · std_y); NaN where the reference is NULL; no clamp

CORR keeps three independent states, a covariance and two Welford variances, and returns cov / (std_x * std_y), NaN when std_x * std_y == 0. One pair or a zero variance gives NaN; no pairs gives NULL. Not clamped to [-1, 1]: with the separate states, |corr| can exceed 1 by a few ulps, more so than with a shared state. The square roots are taken separately, so the product of the variances does not overflow or underflow.

Probed: of no pairs (rows with a NULL on either side skipped) it is NULL; of one pair, of a side whose values are all equal, or where a variance underflows to 0 or overflows to inf, it is NaN, as is any NaN or ±inf input. Where the reference is NULL with at least one pair, DuckDB’s is NaN; elsewhere they differ only in rounding (#85).

The docs are silent on NaN against NULL and on the missing clamp to [-1, 1]: a docs gap. No upstream test pins |corr| above 1; it may change between versions.

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.

Where the reference is NULL (one pair, a constant side), DuckDB gives NaN; Dialect.covar leaves the NULL cases to its caller.

The SQL lift (semantics="duckdb", the lift profile’s corr_degenerate="NAN") writes DuckDB’s CORR as the reference’s with a count of pairs, NaN where it is NULL and the count is positive (#433). With semantics="reference" it is the reference’s.

Options: stat_algo, stat_degenerate.

Rows of the choice tables: agg.stat stat_algo (fp64); agg.stat stat_degenerate (fp64).

Constructs: corr.

See: upstream ledger ST-3; issues #54, #85, #433; pull requests #511.

Provenance:

duckdb-covar: COVAR_* of doubles: Welford co-moment, Chan merge

Engine: duckdb 2.0.0; area: aggregates; status: open; evidence: engine_test.

Verdict: Welford co-moment; Chan merge

The co-moment is updated online (Schubert and Gertz 2018, eq. 4.3): C += dx * (y - meany_new), where dx uses the old mean of x. Partial states merge with means (n_s * mean_s + n_t * mean_t) / n and C = C_s + C_t + dx * dy * n_s * n_t / n.

No pairs is NULL; the sample covariance of fewer than two pairs is NULL.

The docs are silent on the algorithm and on NULL below two pairs: a docs gap.

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).

Constructs: covar_samp, covar_pop.

See: issues #54, #85.

Provenance:

duckdb-float-sum-order: SUM and AVG of floats depend on the order of the rows; IEEE special values

Engine: duckdb 2.0.0; area: aggregates; status: open; evidence: probe.

Verdict: order-dependent plain sum; fsum/favg Kahan; NaN from +inf and -inf

“The floating-point versions of” sum “are affected by ordering”; so are avg, fsum and favg, the last two with Kahan summation. The order in which rows reach a whole-table or grouped aggregate is not guaranteed unless the aggregate states an ORDER BY (Order Preservation). Both FLOAT and DOUBLE are named: the docs do not say whether a FLOAT sum accumulates in FLOAT or DOUBLE, nor its result type (round 2, source).

FLOAT and DOUBLE follow IEEE 754 for infinities and NaN “on machines with the required CPU/FPU support”, NaN’s order and equality aside; so a sum meeting +inf and -inf is NaN. The docs state this for the types, not for SUM itself; duckdb-nonfinite-aggregates probes fsum of [inf, 1].

Round 2 (source): sum and avg have no FLOAT overload, and FLOAT’s only numeric implicit cast is to DOUBLE, so a FLOAT sum or average converts each value exactly to DOUBLE, accumulates in DOUBLE and returns DOUBLE. fsum and favg take DOUBLE only. sum adds with a plain state.value += input, and partial states merge with target += source; avg divides that sum by the count. fsum is Kahan’s compensated sum, and its partial states merge by adding the other state’s sum and then its negated error, both compensated. All of it is IEEE double arithmetic under the default rounding (to nearest, ties to even), with no check for non-finite values, so +inf and -inf make NaN.

Round 3 (engine tests): sum(n ORDER BY abs(n)) and fsum give 9007199254740994 for doubles whose sum in insertion order is 9007199254740992, and a FLOAT sum is a DOUBLE (3.999999872114277e+38 for 2e38 + 2e38). A skipped test records that a double SUM past DBL_MAX gives inf without an error.

Round 4 (probes): a double sum of the same 1M values gives one result at 1 thread and a different one in most runs at 2 or 8 threads, all within a few units in the 13th digit; fsum gives the same result every time. A FLOAT sum behaves the same (it is a DOUBLE sum of the cast values). Sequential rounding is visible: sum of 1e16, 1, 1 is 1e16, fsum 1.0000000000000002e16. A FLOAT sum of 1e8, 1, -1e8 is the DOUBLE 1.0, so it accumulates in DOUBLE. +inf and -inf sum to NaN for both widths.

Order class: FLOAT values convert exactly to DOUBLE, so a FLOAT sum or avg has the class of the double one (duckdb-sum-double, duckdb-avg-double): sequential per state, partials merged in finishing order, a constant run as one product; bound γ_{n−1} Σ|x_i| with u = 2⁻⁵³ on the widened values (Higham §4.2), and the result is a DOUBLE, rounded once per step. fsum/favg are Kahan (duckdb-kahan-sum). For the Lean side: SEQUENTIAL at one thread without constant vectors, ANY otherwise, read on the fp64 codes of the fp32 inputs; the round-4 thread runs (one result at 1 thread, several at 8) are the evidence of the tree shape.

The reference: sum_order: any order and grouping, within #85’s bound (FloatSums); DuckDB’s order-dependent sum is one of them.

nan_creation: the reference raises on a SUM of +inf and -inf (#257); an error-side option, so DuckDB’s NaN is never reached on a plan verified not to raise.

Options: sum_order, nan_creation, rounding.

Rows of the choice tables: agg.fsum sum_order (fp32, fp64); agg.fsum rounding (fp32, fp64); agg.fsum nan_creation (fp32, fp64).

Constructs: sum, avg, fsum, favg.

See: issues #54, #85, #257.

Provenance:

duckdb-kahan-sum: kahan_sum, fsum, sumkahan, favg: classic Kahan

Engine: duckdb 2.0.0; area: aggregates; status: open; evidence: engine_test.

Verdict: classic Kahan (not Neumaier); NaN with an infinite input

kahan_sum (also fsum, sumkahan) is classic Kahan summation, not Neumaier’s. Merging a partial state Kahan-adds other.value, then -other.err. favg divides the Kahan sum by the count.

An infinite input makes the compensation inf - inf, so fsum and favg of [inf, 1] are NaN, where sum is inf (duckdb-nonfinite-aggregates).

Order class: (a) Kahan’s compensated summation per state, in the order values reach it; a constant run enters as one term fl(k·x) (KahanAdd::AddConstant, its product not compensated); partial states merge by two compensated steps (the other’s sum, then its negated error), in the order threads finish. favg returns value/count − err/count. (b) Kahan: |ŝ − s| ≤ (2u + O(nu²)) Σ|x_i| (Higham §4.3); the merges are compensated steps over the partials’ sums and errors, which keeps that form with n counting every step (not proved here); each constant run adds up to u·k|x|, so at most (3u + O(nu²)) Σ|x_i| in all, within #85’s bound for n ≥ 3. (c) The source below; duckdb-float-sum-order round 4 (fsum gave one result at 1, 2 and 8 threads where sum varied), test_kahan_sum.test (9007199254740994). (d) No order the Lean side names: a Kahan result need not be any tree sum. Four doubles −554628673.5322614, −5523426119.001462, 2.685058088631258, 34728.84084343375 give −6078020061.007821 by Kahan, while every tree sum of them is −6078020061.007822 or −6078020061.007823. Refining it needs a behaviour such as COMPENSATED or a bound-only order (any double within #85’s bound). kahan_sum, fsum and favg are DuckDB’s own functions, so no agg.fsum row rests on this fact.

The docs are silent on the NaN of an infinite input: a docs gap. No upstream test pins it; it may change between versions.

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, nonfinite_aggregates.

Constructs: kahan_sum, fsum, sumkahan, favg.

See: issues #54, #85.

Provenance:

The counterexample is a scratch Python search over IEEE doubles (Kahan’s loop against every bracketing of every order of the four values), outside the repository. - https://github.com/duckdb/duckdb/blob/v2.0.0/extension/core_functions/include/core_functions/aggregate/sum_helpers.hpp#L68-L73 KahanAddInternal - https://github.com/duckdb/duckdb/blob/v2.0.0/extension/core_functions/include/core_functions/aggregate/sum_helpers.hpp#L95-L104 KahanSumState::Combine - https://github.com/duckdb/duckdb/blob/v2.0.0/extension/core_functions/include/core_functions/aggregate/sum_helpers.hpp#L143-L153 KahanAdd: AddConstant adds input * count as one term - https://github.com/duckdb/duckdb/blob/v2.0.0/extension/core_functions/aggregate/algebraic/avg.cpp#L40-L53 KahanAvgState::Combine - https://github.com/duckdb/duckdb/blob/v2.0.0/extension/core_functions/aggregate/algebraic/avg.cpp#L200-L209 favg: value/count − err/count - https://epubs.siam.org/doi/10.1137/1.9780898718027.ch4 Higham, Accuracy and Stability of Numerical Algorithms (2nd ed., SIAM 2002), ch. 4 Summation: §4.2 recursive, pairwise and any-order bounds; §4.3 compensated summation - 2026-11-04, docs, by characterization pipeline reverse pass - https://duckdb.org/docs/2.0/sql/functions/aggregates fsum, favg: Kahan Sum, affected by ordering; silent on the NaN of an infinite input - 2026-11-04, engine-test, by characterization pipeline reverse pass - https://github.com/duckdb/duckdb/blob/v2.0.0/test/sql/aggregate/aggregates/test_kahan_sum.test#L5-L25 fsum, sumKahan, kahan_sum of 9007199254740992, 1, 1, 0 → 9007199254740994 - https://github.com/duckdb/duckdb/blob/v2.0.0/test/sql/aggregate/aggregates/test_kahan_avg.test#L5-L15 favg of the same → 2251799813685248.5 - https://github.com/duckdb/duckdb/blob/v2.0.0/test/sql/aggregate/aggregates/test_kahan_combine.test#L1-L50 state combines keep the compensation’s sign: fsum(2^53, 1, 1) → 9007199254740994, favg → 3002399751580331.5; grouped and exported states; no test of an infinite input

duckdb-minmax-zero-sign: MIN and MAX of floats: -0.0 and 0.0 tie, and the last one read wins

Engine: duckdb 2.0.0; area: aggregates; status: shimmed; evidence: probe.

Verdict: the zeros compare equal; the sign of the result depends on the order of the rows

min and max of FLOAT and DOUBLE are the float and double instantiations of one template (NumericMinMaxBase). Each update is Min::Operation(state, input), which for floats is MinFloatingPoint: a NaN is skipped, otherwise right > left ? left : right. When the state and the input compare equal, as -0.0 and 0.0 do, the input replaces the state. MaxFloatingPoint is left > right ? left : right (a NaN wins), with the same rule for ties. Merging two partial states applies the same operation, the other state as the input.

So MIN over 0.0 and -0.0 returns the zero read last, which depends on the order rows reach the aggregate and on how the parallel states merge. The sign is kept. A sort also treats the zeros as equal: Radix::EncodeDouble and EncodeFloat give both zeros the same key.

The group’s quantile_disc and median were not read for this row.

Round 4 (probes): min and max of the rows 0.0, -0.0 are both -0.0, and of -0.0, 0.0 both +0.0, for DOUBLE and FLOAT: the zero read last wins. Over 1M rows the result was the last zero in table order at 1 and at 4 threads, in every run (10 to 20 each), so the merge order the source allows to vary did not show.

The docs are silent on which of two equal zeros min and max return: a docs gap. No upstream test pins this; it may change between versions.

The reference: zero_sign: one zero (ONE_ZERO, #89). sverify’s SQL makes -0.0 into 0.0 on every read and after every operation that can make it (Dialect.canon, duckdb-R14), so MIN and MAX only meet 0.0.

Options: zero_sign.

Rows of the choice tables: agg.order zero_sign (fp32, fp64).

Constructs: min, max.

See: issues #89.

Provenance:

duckdb-mode-ties: MODE: ties go to the value seen first, in the order DuckDB reads the rows

Engine: duckdb 2.0.0; area: aggregates; status: decided; evidence: probe.

Verdict: ties are the engine’s without ORDER BY; with one, the first in that order

MODE(x) counts each non-NULL value and keeps, of the most frequent, the one whose first row came first (first_row). Without an ORDER BY that is the order in which threads read and merge the rows, so ties depend on parallelism: on 3,000,000 rows alternating 5 and 3, starting with 5, MODE(x) gave 3. With MODE(x ORDER BY …) the rows arrive sorted, and the first of the tied values in that order wins: MODE(x ORDER BY x) is the smallest, ORDER BY x DESC the largest. mode() WITHIN GROUP (ORDER BY x) is the same. NULLs are skipped; no values give NULL. -0.0 and 0.0 count as one value.

The docs are silent on the first-occurrence tie rule: a docs gap.

The reference: AggFn.mode (modeOf, #53): the most frequent value, and of the values that tie, the first in the measure’s sort order (rows that tie on it in list order). The parser refuses MODE without an order; Ibis’s mode() becomes MODE(x ORDER BY x). Rel.windowsTotal reports a mode as the engine’s choice unless the sort includes its argument’s column or the group’s and the order’s columns contain a key.

Options: tie_pick, tie_order.

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

Constructs: mode.

See: issues #53.

Provenance:

duckdb-moments: kurtosis and skewness raise where a moment is out of range

Engine: duckdb 2.0.0; area: aggregates; status: decided; evidence: probe.

Verdict: decided: black-box aggregates that may raise

kurtosis, kurtosis_pop and skewness of DOUBLEs (integers convert) raise an Out of Range Error (Kurtosis is out of range!, SKEW is out of range!) for a group with an infinity or NaN, or a value whose fourth power (third, for skewness) overflows: from about 1e77 for the kurtoses (1e76 passes, 1e77 raises) and later for skewness (1e100 passes, 1e300 raises).

kurtosis (of a sample) is NULL for fewer than 4 values, skewness for fewer than 3, kurtosis_pop for fewer than 2, and all three for values that are all equal. kurtosis_pop of 2 values can be -2.0 (1.0 and 2.0), of 3 values -1.5. (Corrected 2026-11-04: this said kurtosis_pop is NULL for fewer than 3 values.)

sem does not raise: it is NaN for an infinity or NaN, inf where the variance overflows, and 0.0 for one value or equal values.

The docs are silent on the raises, the NULL thresholds and zero variance: a docs gap. No upstream test pins the raise on a NaN or infinite input; it may change between versions.

The reference: Each is a black box (#326). Aggregates in the table may raise now (Aggregate.raises): verify reports a domain error for the three moments, on the groups rawAggRaises decides, as it does for a variance of an infinity (AggFn.makesNan). sem is not NULL over values and at least +0.0.

Options: raise_set, nonfinite_aggregates.

Constructs: kurtosis, kurtosis_pop, skewness, sem.

See: issues #326.

Provenance:

duckdb-nonfinite-aggregates: ±inf in variance, correlation and Kahan sums

Engine: duckdb 2.0.0; area: aggregates; status: open; evidence: probe.

Verdict: NaN, no error (since 2.0)

Any ±inf input makes var_*, stddev_* and corr NaN: the update computes inf - inf. fsum and favg of [inf, 1] are NaN, while sum and avg are inf.

One value: var_samp is NULL, var_pop 0, corr(x, x) NaN.

The docs are silent on NaN and infinity in every aggregate: a docs gap. No upstream test pins fsum and favg of [inf, 1]; it may change between versions.

The reference: AggFn.var and AggFn.corr of the same rows; the reference’s handling of ±inf in these aggregates is part of #85.

Options: nonfinite_aggregates, nan_creation, stat_degenerate.

Rows of the choice tables: agg.var stat_degenerate (fp64); agg.stat stat_degenerate (fp64).

Constructs: var_samp, stddev_samp, corr, sum, fsum, favg, avg.

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

Provenance:

duckdb-quantile-cont-interpolation: QUANTILE_CONT: lo·(1−d) + hi·d in doubles, NaN between -inf and +inf

Engine: duckdb 2.0.0; area: aggregates; status: shimmed; evidence: probe.

Verdict: between the neighbours up to rounding; NaN between -inf and +inf; the written SQL clamps

QUANTILE_CONT(x, q) of n values computes RN = (n − 1)·q in doubles, takes the values lo and hi at positions ⌊RN⌋ and ⌈RN⌉ in order, and gives lo where they are one position, else, for a DOUBLE, lo·(1 − d) + hi·d with d = RN − ⌊RN⌋. Integers are cast to DOUBLE first and the result is a DOUBLE; DATE gives a TIMESTAMP. FLOAT and DECIMAL take DuckDB’s generic interpolation, lo + (hi − lo)·d in doubles, cast back to the argument’s type with a static_cast: FLOAT stays FLOAT, with another formula than DOUBLE’s (and hi − lo can overflow), and a DECIMAL result truncates toward zero, where DuckDB’s DECIMAL casts round half away from zero: median of 1.5 and 1.6 as DECIMAL(2,1) is 1.5, quantile_cont of -0.05 and 0.00 as DECIMAL(4,2) at 0.5 is -0.02, and DuckDB’s own test gives 24.7 for 24.75. (Corrected 2026-11-04: this said each type is interpolated in its own type by DOUBLE’s formula.) MEDIAN of a number is QUANTILE_CONT(x, 0.5).

The interpolation rounds: of neighbours that are equal it can give a double an ulp away from them, so the result can lie just outside [lo, hi]. Between -inf and +inf it is NaN (-inf·(1 − d) + inf·d); next to a NaN it is NaN. Large neighbours do not overflow (-1.5e308 and 1.5e308 give 0.0), since each is scaled before the sum. Since RN is a double, a fraction such as 0.1 may land just off a whole position; the result then differs from the exact one by rounding only.

The docs are silent on the formula per type and on the DECIMAL truncation: a docs gap. No upstream test pins the overshoot between equal neighbours, ±inf neighbours or large neighbours; it may change between versions.

The reference: AggFn.quantile true num scale (contValue, #53): for the exact q = num / 10^scale, the doubles at positions ⌊(n−1)·q⌋ and ⌈(n−1)·q⌉ in order; the one value where they coincide, else DuckDB’s lo·(1 − d) + hi·d kept between lo and hi (interpolate). The reference allows any double between the two neighbours (contAllowed, AggFn.Allowed), so contracts know the result lies within the argument’s range. Integers are converted to doubles first; DECIMAL, DATE and TIMESTAMP arguments are refused.

DuckDB differs from the reference only where its rounding leaves [lo, hi], and between -inf and +inf: there DuckDB gives NaN and the reference raises (a NaN created from numbers, AggFn.makesNan, D21; before, the reference kept +inf, #561). The DuckDB writer clamps its QUANTILE_CONT to [MIN(x), MAX(x)] and raises where the interpolation is NaN and no value is.

Options: quantile_interp.

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

Constructs: quantile_cont, median.

See: issues #53, #85.

Provenance:

duckdb-quantile-disc: QUANTILE_DISC: the value at position ⌈n·q⌉, exact for a decimal fraction

Engine: duckdb 2.0.0; area: aggregates; status: no-finding; evidence: probe.

Verdict: the reference’s rule; a DOUBLE fraction rounds; negative fractions count from the top

QUANTILE_DISC(x, q) takes the value at 0-based position max(1, n − ⌊n − n·q⌋) − 1 of the n non-NULL values in order, which is ⌈n·q⌉ − 1 and the first value for n·q ≤ 1. For a DECIMAL fraction (a literal such as 0.25) it computes this in integers, exactly; for a DOUBLE fraction in doubles. Doubles are in DuckDB’s order, NaN above +inf. The result has the argument’s type. No values give NULL.

QUANTILE is QUANTILE_DISC. MEDIAN of a value that is not a number is QUANTILE_DISC(x, 0.5) (of two strings, the first). A fraction outside [-1, 1] is a binder error; a negative one counts from the largest value (the sort is descending). A list of fractions gives a list.

The reference: AggFn.quantile false num scale (SubstraitLean/Aggregate.lean, #53): for the exact decimal q = num / 10^scale in [0, 1], the value at position ⌈n·q⌉ (at least the first) of the non-NULL values in ORDER BY order (sortVals, discIndex); NULL without values. The frontends write the fraction as a DECIMAL literal, so DuckDB’s is the reference’s.

Negative fractions, DOUBLE fractions and lists of fractions are refused by the parser and the frontends.

Options: quantile_interp, nan_order.

Rows of the choice tables: agg.order nan_order (fp64); agg.order nan_order (fp32; evidence engine_test).

Constructs: quantile_disc, quantile, median.

See: upstream ledger ST-4; issues #53.

Provenance:

  • 2026-11-04, source-reading, by session for #53 (exact quantiles, mode, arg_min), duckdb 2.0.0 Python package: Read at tag v2.0.0.
  • 2026-11-04, probe, by session for #53 (exact quantiles, mode, arg_min), duckdb 2.0.0 Python package
    • SELECT quantile_disc(x, 0.25), quantile_disc(x, 0.5), quantile_disc(x, 0), quantile_disc(x, 1), quantile_disc(x, 0.3) FROM (VALUES (10), (20), (30), (40)) t(x) gives 10, 20, 10, 40, 20
    • SELECT quantile_disc(x, 0.1), quantile_disc(x, 0.1::DOUBLE), quantile_disc(x, 0.7), quantile_disc(x, 0.7::DOUBLE) FROM range(10) t(x) gives 0, 0, 6, 6
    • SELECT median(x) FROM (VALUES ('a'), ('b')) t(x) gives a
    • SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY x DESC), quantile(x, 0.25) FROM range(10) t(x) gives 7, 2
    • SELECT quantile_cont(x, 1.5) FROM (VALUES (1)) t(x) gives Binder Error: QUANTILE can only take parameters in the range [-1, 1]
    • SELECT median(x), quantile_disc(x, 0.5) FROM (SELECT 1 x WHERE false) gives NULL, NULL
    • test python/tests/test_aggregate.py::test_quantiles_mode_and_arg_min_agree_with_duckdb
  • 2026-11-04, engine-test, by characterization pipeline round 3 (engine tests; aggregates, windows, relations, temporal, nested, strings, execution): Read at tag v2.0.0 (commit d54cbbe3); the tests were not run. For n = 101 values and q = 0.9 the position is ⌈90.9⌉ = 91, which is 91.0 only when the two NaNs sort last. This settles nan_order for FLOAT as well.

duckdb-stat-arg-types: Statistical aggregates of integers and decimals: cast to DOUBLE first

Engine: duckdb 2.0.0; area: aggregates; status: open; evidence: probe.

Verdict: one DOUBLE overload each; BIGINT and DECIMAL arguments are cast, then the doubles’ algorithm

var_samp, var_pop, stddev_samp, stddev_pop (and the aliases variance, stddev) each have a single overload, DOUBLE to DOUBLE; covar_samp, covar_pop and corr a single (DOUBLE, DOUBLE) one. A BIGINT or DECIMAL argument binds through the implicit cast to DOUBLE. BIGINT to DOUBLE is the C++ conversion, round to nearest, exact up to 2^53. DECIMAL to DOUBLE is correctly rounded: one division by an exact power of ten when the operands are exact, otherwise fast_float’s decimal-to-binary conversion.

stat_algo: from the cast on, the algorithm is the doubles’ one: Welford with Chan’s merge (duckdb-var-stddev), the online co-moment (duckdb-covar), three states for corr (duckdb-corr).

rounding: every step is native IEEE double arithmetic, + - * /, one fma in the merge and sqrt, under the default floating-point environment (round to nearest, ties to even). No setting selects another mode.

stat_degenerate: a cast BIGINT or DECIMAL(38) is finite and below 1e38 in magnitude, so the squared deviations stay far below DBL_MAX and no ±inf reaches the update. NaN comes only from corr’s std_x * std_y == 0 (one pair, a constant side), as for doubles. var_samp and stddev_samp of one value are NULL, var_pop and stddev_pop of one value 0, no values NULL.

Round 4 (probes): var_samp of BIGINT and of DECIMAL is a DOUBLE (7/3 and 1.5833… for 1, 2, 4 and 1.5, 2.5, 4.0). BIGINTs above 2^53 lose digits in the cast: var_samp of 2^53 + 1, 2^53 + 2, 2^53 + 3 is 6.0 and var_pop 4.0, where the exact values are 1 and 2/3. Degenerate inputs are the same for BIGINT and DECIMAL: one value gives var_samp, stddev_samp and covar_samp NULL, var_pop and covar_pop 0 and corr(x, x) NaN; a constant side gives corr NaN and the (co)variances 0; no values give NULL throughout.

The docs are silent on the argument and result types and on the cast to DOUBLE: a docs gap.

The reference: rounding: the reference’s doubles are native Float (TIE_TO_EVEN).

stat_algo: the generic properties (#85). variance and std_dev of integers reach AggFn.var through .un .itof (parseMeasure), the conversion DuckDB’s cast makes; parseMeasure takes them of doubles and integers only, so a decimal argument is refused.

stat_degenerate: NULL for corr’s degenerate cases and raise for ±inf (docs/core-semantics.md). DuckDB’s NaN for corr is handled as for doubles (duckdb-corr); ±inf cannot arise from these types.

Options: rounding, stat_algo, stat_degenerate.

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

Constructs: var_samp, var_pop, stddev_samp, stddev_pop, variance, stddev, covar_samp, covar_pop, corr.

See: issues #54, #85.

Provenance:

duckdb-statistical-aggregates: product, mad, histogram, weighted_avg and bit_count: types and edge cases

Engine: duckdb 2.0.0; area: aggregates; status: decided; evidence: probe.

Verdict: decided: product and mad are black boxes; histogram and weighted_avg are refused; bit_count of a BIGINT only

product, kahan_sum, favg, sem, kurtosis, kurtosis_pop and skewness take DOUBLE only, so an integer converts. product is NULL without values and does not raise: inf past the range, NaN with a NaN.

mad takes DOUBLE, FLOAT and DECIMAL (a DECIMAL result), and dates, times and timestamps (an INTERVAL result); a BIGINT converts to DOUBLE. Of NaN it is NaN; an infinity does not change it when the median is finite.

histogram(x) is a MAP(x's type, UBIGINT) of the counts, NULLs skipped. weighted_avg(x, w) (wavg) is a DOUBLE, skipping rows where x or w is NULL; It is the macro SUM(x * w) / SUM(w) over the rows with a non-NULL x, so where the weights sum to 0 it divides by 0: NaN where SUM(x * w) is 0 too (every weight 0), otherwise ±inf with the sign of SUM(x * w) (-inf for (1.0, 1.0), (2.0, -1.0), inf for (2.0, 1.0), (1.0, -1.0)). (Corrected 2026-11-04: this said inf for weights summing to 0 from both signs.)

bit_count(x) counts the 1 bits of x at its own width, as a TINYINT: bit_count(-1::INTEGER) is 32, bit_count(-1::BIGINT) 64.

The docs are silent on product’s overflow, zero weights in weighted_avg, histogram’s count type and bit_count’s width and result type: a docs gap. No upstream test pins product past the double range or with NaN, or mad with NaN or infinities; it may change between versions.

The reference: product and mad of doubles (integers converted) are black boxes (#326), not NULL over values; mad is at least +0.0. mad of a DECIMAL or a temporal type is refused. histogram (the model has no MAP) and weighted_avg (black-box aggregates take one argument) are refused, naming #326. bit_count is a black box of a BIGINT, in [0, 64], that Lean evaluates; narrower integers are refused, as their count depends on the width.

Options: empty_group_aggregates.

Constructs: product, mad, histogram, weighted_avg, bit_count.

See: issues #326.

Provenance:

duckdb-sum-double: SUM of doubles: plain sequential

Engine: duckdb 2.0.0; area: aggregates; status: open; evidence: probe.

Verdict: plain recursive summation

SUM(DOUBLE) adds each value to the state in turn (state.value += input), and partial states merge by plain addition. The order of the values depends on the parallel plan.

Order class: (a) each state adds its values in turn, in the order they reach it; partial states combine by one addition each, in the order threads finish (ungrouped: each thread’s state into the global one under a lock; grouped: per-thread hash tables, and abandoned or spilled partitions, merged partition by partition). With more than one thread, or a spilled hash table, the sum is a tree of unknown shape whose leaves are sequential runs. A constant vector (a constant argument, or a constant-compressed segment of a stored table) adds fl(k·x) for its k ≤ 2048 rows in one multiplication (RepeatedStateValue::Multiply). (b) With u = 2⁻⁵³ and γ_k = ku/(1 − ku): sequential |ŝ − s| ≤ γ_{n−1} Σ|x_i|, to first order (n − 1)u Σ|x_i| (Higham §4.2); any tree γ_h Σ|x_i|, h the most additions on one value’s path to the root, at most n − 1 (§4.2); with P partials merged in a chain h ≤ m − 1 + P, m the largest partial’s count. A constant run’s product adds at most u·k|x| for its k values, so every path stays within γ_{n−1} Σ|x_i| and within #85’s sumTolScaled. (c) The source below; duckdb-float-sum-order’s round-4 thread runs (one result at 1 thread, several at 2 and 8); the constant-argument probe below (sum(0.1) over 10 rows is 1.0, the same values from a table 0.9999999999999999). (d) At one thread, with no constant vector and no spilled partial, SEQUENTIAL. Otherwise the tree shape of ANY (FloatSums.split over seq leaves), so sum_order’s extra entry (SEQUENTIAL, citing this fact) holds at threads = 1 only. A constant run’s fl(k·x) is no sequential sum (1.0 against 0.9999999999999999 for ten 0.1); a search found it among the tree sums of k copies for each k ≤ 40 over 300 random x, but its membership in FloatSums is unproved: the Lean side needs that lemma, or a behaviour that allows a product step.

SUM and AVG of no rows, or of NULLs only, are NULL for DOUBLE, FLOAT and integer arguments (sum-empty): the clustered sum’s state starts unset and finalizes to NULL.

The docs are silent on the algorithm and on the constant run’s product: a docs gap. No upstream test pins the constant run’s product or the dependence on thread order; it may change between versions.

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.

Rows of the choice tables: agg.fsum sum_order (fp64); agg.fsum rounding (fp64); agg.fsum empty_group_aggregates (fp32, fp64); agg.isum empty_group_aggregates (i8); agg.isum empty_group_aggregates (i16, i32, i64; evidence source).

Constructs: sum.

See: issues #54, #85.

Provenance:

The tree-membership search is a scratch Python script over IEEE doubles, outside the repository. - https://github.com/duckdb/duckdb/blob/v2.0.0/extension/core_functions/include/core_functions/aggregate/sum_helpers.hpp#L23-L65 RepeatedStateValue: a constant run adds input * static_cast<double>(count) in one step - https://github.com/duckdb/duckdb/blob/v2.0.0/extension/core_functions/include/core_functions/aggregate/sum_helpers.hpp#L107-L117 RegularAdd: state.value += input; AddConstant goes to AddRepeated - https://github.com/duckdb/duckdb/blob/v2.0.0/extension/core_functions/include/core_functions/aggregate/sum_helpers.hpp#L250-L267 BaseSumOperation: Operation per value, ConstantOperation per constant run - https://github.com/duckdb/duckdb/blob/v2.0.0/src/include/duckdb/common/vector_operations/aggregate_executor.hpp#L721-L737 UnaryScatter: a constant input over one state calls ConstantOperation with the vector’s count - https://github.com/duckdb/duckdb/blob/v2.0.0/src/include/duckdb/common/vector_operations/aggregate_executor.hpp#L782-L792 UnaryUpdate (ungrouped): a CONSTANT_VECTOR input calls ConstantOperation - https://github.com/duckdb/duckdb/blob/v2.0.0/src/storage/compression/numeric_constant.cpp#L228-L233 a constant-compressed segment scans as a CONSTANT_VECTOR - https://github.com/duckdb/duckdb/blob/v2.0.0/src/execution/operator/aggregate/physical_ungrouped_aggregate.cpp#L134-L152 ungrouped: each thread’s state combined into the global one under a lock, as threads finish - https://github.com/duckdb/duckdb/blob/v2.0.0/src/execution/radix_partitioned_hashtable.cpp#L709-L790 grouped: per-thread hash tables; abandoned and uncombined partition data appended as threads finish - https://github.com/duckdb/duckdb/blob/v2.0.0/src/execution/aggregate_hashtable.cpp#L1205-L1225 GroupedAggregateHashTable::Combine: partial states combined chunk by chunk (CombineStates) - https://github.com/duckdb/duckdb/blob/v2.0.0/extension/core_functions/aggregate/distributive/sum.cpp#L206-L212 NumericSumOperation (double sum): RegularAdd, clustered local state copied in and out - https://epubs.siam.org/doi/10.1137/1.9780898718027.ch4 Higham, Accuracy and Stability of Numerical Algorithms (2nd ed., SIAM 2002), ch. 4 Summation: §4.2 recursive, pairwise and any-order bounds; §4.3 compensated summation - 2026-11-04, probe, by characterization pipeline round 5 (summation order classes): DuckDB 2.0.0 (Python, macOS arm64), a scratch script at threads = 1. - SET threads = 1; SELECT sum(0.1::DOUBLE)::VARCHAR, avg(0.1::DOUBLE)::VARCHAR, fsum(0.1::DOUBLE)::VARCHAR FROM range(10); CREATE TABLE t AS SELECT 0.1::DOUBLE v FROM range(10); SELECT sum(v)::VARCHAR, fsum(v)::VARCHAR FROM t; SELECT sum(v ORDER BY i)::VARCHAR FROM (SELECT 0.1::DOUBLE v, i FROM range(10) r(i)) gives constant argument: sum 1.0, avg 0.1, fsum 1.0; table column: sum 0.9999999999999999, fsum 1.0; sum(v ORDER BY i) 0.9999999999999999. 0.1 * 10 is 1.0 in double, the ten additions in turn 0.9999999999999999 - 2026-11-04, probe, by characterization pipeline round 5 (empty groups): The probe covers DOUBLE, FLOAT and TINYINT (bound to the BIGINT overload); SMALLINT, INTEGER and BIGINT share the finalize, read in source. - probe sum-empty in characterization/duckdb/probes.json - https://github.com/duckdb/duckdb/blob/v2.0.0/extension/core_functions/aggregate/distributive/sum.cpp#L84-L91 ClusteredSumOperation::Finalize: if (!state.is_set) ReturnNull(), used by the double and integer SUM - 2026-11-04, docs, by characterization pipeline reverse pass - https://duckdb.org/docs/2.0/sql/functions/aggregates sum: floating-point versions affected by ordering; NULL on empty groups; silent on the constant-run product - 2026-11-04, engine-test, by characterization pipeline reverse pass - https://github.com/duckdb/duckdb/blob/v2.0.0/test/sql/aggregate/aggregates/test_kahan_combine.test#L8-L17 sum(9007199254740992.0, 1.0) → 9007199254740992: plain sequential double sum - https://github.com/duckdb/duckdb/blob/v2.0.0/test/sql/aggregate/aggregates/test_sum.test#L82-L92 sum(n ORDER BY ABS(n)) of 2^53, 1, 1, 0 → 9007199254740994; no test of the constant-run product

duckdb-var-distribution: variance and stddev are the sample’s

Engine: duckdb 2.0.0; area: aggregates; status: no-finding; evidence: probe.

Verdict: variance is var_samp, stddev is stddev_samp

DuckDB names both distributions: var_samp and stddev_samp divide by count(x) - 1 (Bessel’s correction), var_pop and stddev_pop by count(x). variance is an alias of var_samp and stddev of stddev_samp.

The docs give these per function, without listing the argument types; the types here rest on the functions taking any numeric argument. The docs’ formulas ((sum(x^2) - sum(x)^2 / count(x)) / (count(x) - 1)) define the result; the algorithm is Welford’s (duckdb-var-stddev).

Round 2 (source): variance is an alias of var_samp and stddev of stddev_samp in the function list. Each of the four has one DOUBLE overload, so BIGINT and DECIMAL arguments are cast to DOUBLE first (duckdb-stat-arg-types); the distribution is the same for every argument type. var_samp divides by count - 1 and is NULL for one value; var_pop divides by count and is 0 for one value.

Round 4 (probes): for BIGINT 1, 2, 4 var_samp is 7/3 and var_pop 14/9; for DECIMAL 1.5, 2.5, 4.0 var_samp is 1.5833… and stddev_samp its root. One value gives var_samp NULL and var_pop 0 for both types.

The reference: Substrait’s variance and std_dev take distribution (SAMPLE, POPULATION); sverify reads an omitted one as SAMPLE (SubstraitLean/Options.lean, Parse.lean), and the lift writes VAR_SAMP/VAR_POP (sql_lift/_base.py, _distribution). DuckDB’s defaults agree.

Rows of the choice tables: agg.var distribution (i64, decimal); agg.var distribution (fp64; evidence engine_test).

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

Provenance:

duckdb-var-stddev: VAR_* and STDDEV_* of doubles: Welford, Chan merge

Engine: duckdb 2.0.0; area: aggregates; status: open; evidence: engine_test.

Verdict: Welford; Chan merge with fma; no clamp

Welford’s online update: mean_differential = (x - mean) / count, new_mean = mean + mean_differential, dsquared += (x - new_mean) * (x - mean). Partial states merge by Chan et al.: delta = s.mean - t.mean, mean = fma(n_s / n, delta, t.mean), M2 = M2_s + M2_t + delta * delta * n_s * n_t / n.

No values is NULL; the sample variance of one value is NULL; the population variance of one value is 0. No clamp and no finiteness check: 1.5.6 raised OutOfRange (“VARSAMP is out of range!”) on non-finite values, 2.0 gives NaN (duckdb-nonfinite-aggregates).

The docs are silent on the algorithm and on one value: a docs gap.

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.

Rows of the choice tables: agg.var stat_algo (fp64).

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

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

Provenance:

windows

duckdb-ntile: NTILE: the SQL standard’s buckets, the larger first

Engine: duckdb 2.0.0; area: windows; status: no-finding; evidence: probe.

Verdict: ntile_spread: the SQL standard’s

Of n rows in k buckets, the first n % k buckets hold ⌊n / k⌋ + 1 rows: 10 rows in 3 are 4, 3, 3; in 4 are 3, 3, 2, 2; in 6 are 2, 2, 2, 2, 1, 1. With more buckets than rows each row gets its own (ntile(12) of 10 rows is 1 … 10). Ties and NULLs in the order take buckets by their position.

ntile(NULL) is NULL; ntile(0) and a negative count raise (Invalid Input Error: Argument for ntile must be greater than zero).

The reference: The SQL standard’s buckets (ntileOf, reference-semantics.md, Windows): native on DuckDB. DataFusion spreads the larger buckets out (DF-11).

Options: ntile_spread, raise_set.

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

Constructs: ntile.

Provenance:

duckdb-window-arg-order: Ranking functions with an ORDER BY of their own rank within the frame

Engine: duckdb 2.0.0; area: windows; status: open; evidence: engine_test.

Verdict: lead: the SQL lift drops the argument ordering and the frame of rank, percent_rank, cume_dist

DuckDB accepts an ORDER BY inside a ranking function’s call, rank(ORDER BY x) OVER w. The function then orders the rows of its frame by x, independently of w’s own ORDER BY, and the frame applies: rank(ORDER BY id) OVER (ORDER BY ch ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING), an empty frame, gives 1 on every row, percent_rank and cume_dist 0.0, ntile(2 ORDER BY id) NULL. Peers of the argument ordering share a value even when w orders them by a further key.

Without the argument ORDER BY, the ranking functions ignore the frame and rank by the window’s order (duckdb-window-frame).

The reference: Substrait’s rank, percent_rank, cume_dist and ntile rank by the window’s sort and take no ordering of their own, and the reference evaluates them over the partition, whatever the frame (SubstraitLean, docs/reference-semantics.md, windows). DuckDB’s argument-ordered, framed form has no Substrait counterpart, so the frontend should refuse it. At v2.0.0’s tests the SQL lift reads rank(ORDER BY id) OVER (ORDER BY ch ROWS …) as rank() OVER (ORDER BY ch): the replay ledger records Lean and the written plan both giving 1, 2, 2 where DuckDB gives 1, 1, 1. The lift’s window (python/src/sverify/sql_lift/_base.py, the _RANKING branch) reads no argument of a ranking function other than ntile’s count, and ntile(2 ORDER BY id) is refused only because the count is not a plain constant.

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

Provenance:

duckdb-window-frame: Default frame: RANGE up to the current row’s peers with an order, the whole partition without; NULLs last in ranks

Engine: duckdb 2.0.0; area: windows; status: no-finding; evidence: probe.

Verdict: SQL’s default frame; ranks put NULLs last both ways

With an ORDER BY and no frame, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: a running aggregate includes the current row’s peers (sum is 60 on both rows with i = 2), and last_value is the last peer’s value. ROWS counts rows instead. Without an ORDER BY the frame is the whole partition.

rank, dense_rank, percent_rank and cume_dist give peers the same value; NULL order keys rank after the others, ascending and descending (default_null_order is NULLS LAST, F2). Without an order every row is a peer: rank 1, percent_rank 0, cume_dist 1. lag, lead and nth_value give NULL (or the default) outside the partition, nth_value within the default frame.

The reference: The reference’s windows follow SQL’s default frame, with NULLs placed by the key’s direction and NULLS_LAST for UNSPECIFIED (D8); ties in a window’s order are open (tie_order).

Options: default_window_frame, null_order.

Rows of the choice tables: null_order (any).

Constructs: OVER, sum, count, first_value, last_value, rank, dense_rank, percent_rank, cume_dist, lag, lead, nth_value.

Provenance:

duckdb-window-sum-order: Windowed SUM/AVG of doubles: a segment tree of fanout 16, or per-thread partials

Engine: duckdb 2.0.0; area: windows; status: open; evidence: probe.

Verdict: a tree of sequential nodes (fanout 16) for framed windows; per-thread sequential partials for a whole-partition frame

A windowed sum or avg of doubles picks its aggregator in order: DISTINCT a merge sort tree; a frame that is the whole partition (OVER (), or unbounded on both sides, or CURRENT ROW in RANGE mode without ORDER BY) WindowConstantAggregator; otherwise WindowSegmentTree (sum has no custom window callback). The constant aggregator adds each thread’s rows of a partition in turn to a local state and combines the local states into the partition’s state. The segment tree sums each run of 16 rows in turn into a leaf state, and each level combines 16 states of the level below in turn; a frame’s state takes the ragged leaf values on its left, then whole nodes level by level, then the ragged right parts, each added in turn. All of it is the aggregate’s += and combine (duckdb-sum-double), in DOUBLE.

Order class: (a) a tree whose nodes add their children in turn: fanout 16 for framed windows, with the frame’s own node adding up to 15 leaf values and 30 nodes per level; per-thread sequential partials combined in finishing order for whole-partition frames. (b) Any tree: |ŝ − s| ≤ γ_h Σ|x_i| with h the most additions on one value’s path (Higham §4.2); h ≤ n − 1 always, and for a framed window of n rows h grows with 45·⌈log₁₆ n⌉ (15 in each node on the way up, up to 30 in the frame’s node per level), an estimate from the code’s shape. avg divides once more. Within #85’s bounds. (c) The source below; a probe at one thread gives 0.9999999999999999 for a running sum and an OVER () sum of ten 0.1, the same as adding them in turn. (d) ANY (FloatSums): a framed window’s sum is a tree sum of the frame’s values, which SEQUENTIAL does not cover for frames over 16 rows. A whole-partition frame at one thread is SEQUENTIAL.

The docs are silent on the summation order of windowed aggregates: a docs gap. No upstream test pins the double rounding; it may change between versions.

The reference: sum_order over the frame: any order and grouping within #85’s bound (FloatSums), as for SUM of doubles.

Options: sum_order, rounding.

Rows of the choice tables: agg.fsum sum_order (fp64); agg.fsum rounding (fp64).

Constructs: sum OVER, avg OVER.

See: issues #54, #85.

Provenance:

relations

duckdb-F2: NULLs in ORDER BY

Engine: duckdb 2.0.0; area: relations; status: no-finding; evidence: engine_test.

Verdict: frontend

DuckDB puts NULLs last in both directions; DataFusion puts them first for DESC.

The reference: The replay writes the order out and follows SET default_null_order.

Options: null_order.

Rows of the choice tables: null_order (any).

Constructs: ORDER BY.

Settings: {"default_null_order": "nulls_last"}.

See: upstream ledger ST-7.

Provenance:

  • 2026-11-01, replay, by commit 20b02e9: Recorded from the replay’s translation of DuckDB’s SQL to DataFusion’s (sverify_dev.slt.translate).

duckdb-clustered: No clustering-only sort: an ORDER BY on the keys

Engine: duckdb 2.0.0; area: relations; status: no-finding; evidence: probe.

Verdict: clustered: an ORDER BY on the keys

DuckDB’s SQL has no direction that only brings equal keys together (ORDER BY 1 CLUSTERED is a syntax error). A sort on the keys clusters them, at the cost of a sort.

The reference: SORT_DIRECTION_CLUSTERED allows any order with equal keys adjacent (docs/core-semantics.md, clustered); a sorted order is one of them, so an ORDER BY on the keys refines it.

Options: clustered.

Rows of the choice tables: clustered (any).

Constructs: ORDER BY.

Provenance:

  • 2026-11-04, probe, by sverify_dev.probe observations (duckdb 2.0.0, Python, macOS arm64; session duckdb-characterization-gaps): python -m sverify_dev.probe --observations runs each query on a fresh connection under TimeZone = 'UTC' (or its own setup) and records the rows as they come; tests/test_probe.py::test_observations_are_current checks they are current.

duckdb-set-operations: Set operations: bag UNION ALL; INTERSECT, EXCEPT with NULLs and NaNs equal; ALL keeps min(m, n) and m − n

Engine: duckdb 2.0.0; area: relations; status: no-finding; evidence: probe.

Verdict: native, as the reference’s

UNION ALL keeps every row; UNION, INTERSECT and EXCEPT remove duplicates, with NULL equal to NULL and NaN equal to NaN of either sign. INTERSECT ALL keeps min(m, n) copies of a row (three 1s and two give two), EXCEPT ALL max(m − n, 0) (three and two give one; one NULL of two is left).

No set operation promises an order. The column types unify as in other expressions (1 UNION 1.5 is DECIMAL(11,1)).

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, so m and n copies leave min(m, n) and max(m − n, 0) (#344). Grouping keys use NAN_IS_NAN.

Options: set_operations, null_join_keys.

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

See: issues #344.

Provenance:

duckdb-sort-ties: Ties of a sort and the input order of aggregates are not guaranteed

Engine: duckdb 2.0.0; area: relations; status: no-finding; evidence: probe.

Verdict: tie_order: any order (ORDER BY may not be stable)

“ORDER BY may not use a stable algorithm”: rows that tie on every key come out in any order. JOIN, UNION, GROUP BY and whole-table aggregation do not preserve the order of rows either; FROM a single table, WHERE, SELECT, LIMIT, OFFSET, UNION ALL and a window with an empty OVER do (Order Preservation).

list (array_agg) and string_agg are order-sensitive and “can have non-deterministic results without ordering”; an ORDER BY in the call sorts the values first, and the docs give that sort no stability either, so ties on its keys take any order.

For windows (row_number, ntile, lag, …) the docs only say that without an ORDER BY the results “are not well-defined”; the order of peers is for round 2.

Round 2 (source): every sort, ORDER BY, a window’s partition and order, and an aggregate’s ORDER BY, goes through the same Sort. Each thread sorts its rows’ keys with vergesort, which falls back to ska_sort, an in-place radix sort; neither is stable, and the key holds only the sort columns. The threads’ sorted runs are then merged (K-way Merge Path). Rows that tie on every key come out in an order that depends on the sort and on how rows were split between threads.

Windows: row_number is the row’s position in its sorted partition, and ntile is computed from that position, so both depend on the order of peers. rank, dense_rank, percent_rank and cume_dist step at the start of each peer group and give every peer the same value, so they do not. lag, lead, first_value, last_value and nth_value read rows by their position in the frame, so a peer’s value depends on the order of the peers.

Round 4 (probes): over 1M rows with 10 distinct keys, ORDER BY k, row_number, ntile and lag over ORDER BY k, and list(i ORDER BY k) return the peers in a different order in each of five runs at 4 threads. At 1 thread the result repeats, and still is not the input order. A top-N (ORDER BY k LIMIT 5) gave the same rows every time.

The reference: tie_order: any order of the ties (docs/core-semantics.md); the reference fixes nothing, and a plan that needs one order is refused or made tie_free. DuckDB’s is one of them.

Options: tie_order.

Rows of the choice tables: tie_order (any); agg.seq tie_order (any); win.rank tie_order (any); win.ntile tie_order (any); win.value tie_order (any).

Constructs: ORDER BY, list, array_agg, string_agg, row_number, rank, ntile, lag, lead, first_value, last_value, nth_value.

Provenance:

execution

duckdb-exec-filter-pushdown: Filter pushdown: a raising filter crosses a join, and runs on one input even when the other is empty

Engine: duckdb 2.0.0; area: execution; status: open; evidence: probe.

Verdict: raising filters kept above projections; pushed into join inputs, outside the reference’s loose model only when the other input is empty (probed: it raises)

Undocumented. In the optimizer at v2.0.0, a filter that can raise (CanThrow) is not pushed through a projection: it stays on top (pushdown_projection.cpp), and filter pullup keeps fallible predicates below a projection. Only internal barrier expressions (__internal_barrier, for secure views) are held above every operator that can remove rows (FilterPushdown::Rewrite).

An inner join is turned into a cross product whose conditions and the filters above it are pushed to the side they reference, with no CanThrow test (pushdown_inner_join.cpp, pushdown_cross_product.cpp); a left join pushes its left-side filters the same way (pushdown_left_join.cpp), and the join order optimizer moves filters to the lowest join that binds them. A filter over a filter merges both conjunct lists, the upper one first.

The reference’s issue analysis already allows most of this: filter and join conditions are evaluated on Rel.loose, the input with filters removed and inner joins widened to cross joins (SubstraitLean/Safety.lean), so a WHERE over an inner or left join covers every row of each input, and a merged filter covers the rows the lower filter would remove. The gap left is an empty other input: the loose cross product is then empty and the reference reports no issue, while DuckDB evaluates the pushed filter on every row of the side it was pushed into. So SELECT … FROM a JOIN b ON a.k = b.k WHERE 10 // a.x > 1 with b empty appears to raise on a.x = 0. This is from reading the code: a probe must confirm it (the hash join may skip the probe side when the build side is empty).

The other way round, DuckDB can raise less than the reference: a LIMIT stops the scan, so WHERE s::INT = 10 LIMIT 1 does not raise on the unconvertible rows after the first match (DuckDB’s test). That keeps the no-error guarantee.

Round 3 (engine tests): test_barrier.test pins it: with join_filter_pushdown off, SELECT … FROM secrets JOIN allowed ON … WHERE s::INT = 42 raises on the secret row the join removes, and __internal_barrier keeps the filter above the join. A LIMIT above a raising scan filter stops the scan before the bad rows (test_throwing_filter_pushdown.test).

Round 4 (probes): with b empty, SELECT * FROM a JOIN b ON a.k = b.k WHERE 10 // a.x > 1 raises Division by zero on a’s row with x = 0; so do the cast a.s::INTEGER > 1 and b LEFT JOIN a with the filter on a (the filter makes the join inner). EXPLAIN shows the filter in a’s scan. With b = {2}, the row k = 1, x = 0 is removed by the dynamic join filter in the scan before the division runs, and nothing raises; with join_filter_pushdown disabled it raises, on a row the reference’s loose model also evaluates. So the empty other input is the case outside the reference.

No upstream test pins a raising filter pushed below a join whose other input is empty; it may change between versions.

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, a join condition on the pairs it forms. Its issue analysis (Rel.HasIssue, SubstraitLean/Safety.lean; docs/how-it-works.md) trusts the engine’s evaluation order: CASE is lazy, AND/OR are not, and filter and join conditions may run before the filters below them. A plan verify reports error-free must run without error, so an engine that evaluates a raising expression on rows outside that model breaks the guarantee. sverify’s DuckDB SQL writes a filter over a join as a WHERE over the join, which the optimizer is then free to push.

Options: evaluation_order.

Constructs: WHERE, JOIN, LIMIT.

See: upstream ledger ST-12.

Provenance:

duckdb-exec-platform: Platform dependence: IEEE where the hardware supports it

Engine: duckdb 2.0.0; area: execution; status: open; evidence: probe.

Verdict: elementary functions call the C++ standard library, so the platform’s libm; no bundled math library

The numeric types page says FLOAT and DOUBLE follow IEEE 754 to the extent the processor, operating system and compiler support it, that values out of the double range may cause errors on some platforms but usually become zero or infinity, and that the special-value semantics hold on machines with the required CPU and FPU support.

Source at v2.0.0: the scalar math functions in extension/core_functions/scalar/math/numeric.cpp call the C++ standard library: std::exp, std::pow, std::log, std::log10, std::log2, std::sin, std::cos, std::tan, std::asin, std::atan2, std::cbrt, the hyperbolic functions, and std::pow(10, d) inside round(x, d). DuckDB bundles no portable math library (none under third_party/), so the last bits of these functions are the platform’s libm: macOS arm64 for the probes, glibc on Linux x86_64 in CI. sqrt, +, -, *, / and fmod are correctly rounded by IEEE 754 and do not depend on it.

Next rounds: run the double probes (characterization/duckdb/probes.json) on homelab (Linux x86_64) and diff them with the macOS arm64 ledger.

Round 4 (probes): a second platform dependence beside libm: the integer avg divides in long double (duckdb-avg-int), so avg of 2^53 + 1, 0, 0 is 3002399751580330.5 on macOS arm64 and should be 3002399751580331 on x86-64 Linux. The homelab run should also compare the 1M-row var_samp repro of duckdb-stat-arg-types, since a compiler may contract Welford’s update into fma on one platform only.

Platform: on Linux x86_64 a NaN that an operation creates is negative, on macOS arm64 positive. DuckDB 2.0.0 gives signbit(sin('inf'::DOUBLE)) and signbit('inf'::DOUBLE - 'inf'::DOUBLE) true and prints -nan on Linux, false and nan on macOS; Polars 2.1.0’s sin(inf) has the bits fff8000000000000 on Linux and 7ff8000000000000 on macOS, on both engines. The sign comes from the CPU’s default NaN (x86’s is negative) and libm. The reference has one NaN, so only nan_kind and anything that shows a NaN’s sign (signbit, text) depend on the platform.

No upstream test pins this, and none can, since DuckDB’s sqllogictests compare floating results within 1 %; it may change between versions.

The reference: The reference treats sin, exp, pow and the other elementary functions as black boxes with stated laws (docs/reference-semantics.md), so a last-bit difference is allowed; NaN payloads and signs are not observable values for it (duckdb-R14 for -0.0).

Options: platform.

Constructs: FLOAT, DOUBLE, sin, pow, exp, ln.

Settings: {"platform": ["macOS arm64", "Linux x86_64"]}.

Provenance:

duckdb-exec-row-order: Row order and order-sensitive aggregates under parallel execution

Engine: duckdb 2.0.0; area: execution; status: open; evidence: probe.

Verdict: insertion order kept for scans, WHERE, LIMIT, UNION ALL; not for joins, GROUP BY, aggregates

The order preservation page guarantees the input order for a single-table FROM, SELECT, WHERE, LIMIT, OFFSET, UNION ALL, COPY and an empty OVER (), and for the CSV, JSON and Parquet readers, while preserve_insertion_order is true. Joins, UNION, GROUP BY, whole-table aggregates, USING SAMPLE and scalar subqueries guarantee no order. With the setting off, any query without ORDER BY may reorder rows.

The aggregates page marks the double versions of sum, fsum and favg, and first, last, any_value, list and string_agg, as affected by ordering, with non-deterministic results without an ORDER BY in the aggregate. Neither page names threads; the dependence on the thread count is implied by the parallel hash aggregate and goes to a probe (sum of doubles at threads 1 and N; existing facts duckdb-sum-double and duckdb-mode-ties).

Round 2 (source): sum of doubles is a plain running sum (NumericSumOperation with RegularAdd, state.value += input), and combining two states adds their sums. A grouped aggregate builds one hash table per thread, appends each thread’s partitions to the global data in the order the threads finish (under a lock), and combines each partition’s chunks in that order; an ungrouped aggregate combines each thread’s state into the global one as the thread finishes. So with more than one thread the order of the double additions follows scheduling, and the last bits of a double SUM may change between runs. fsum/kahan_sum use Kahan summation, which narrows the difference. A probe at threads 1 and N goes to the next round.

Round 4 (probes): at 2 and 8 threads a double sum over 1M rows changes its last digits between runs and connections, and a sort with ties (ORDER BY k with 10 distinct keys over 1M rows) returns the peers in a different order in every run at 4 threads; at 1 thread both repeat (duckdb-float-sum-order, duckdb-sort-ties).

The reference: The reference compares results as bags (List.Perm), so row order outside ORDER BY is free. sum_order is an open option every route allows within the plan’s bound; first/any_value without ordering and tie_order allow every order.

Options: parallel_nondeterminism.

Constructs: ORDER BY, GROUP BY, JOIN, SUM, first, list, string_agg, any_value.

Settings: {"preserve_insertion_order": true, "threads": "the host's core count"}.

Provenance:

duckdb-exec-settings: Settings that change results

Engine: duckdb 2.0.0; area: execution; status: open; evidence: probe.

Verdict: eleven documented settings change values or order; five are checked

The configuration reference lists these settings, with their defaults, as changing what a query computes:

  • default_null_order (NULLS_LAST) and default_order (ASCENDING): sort keys without a placement or direction (duckdb-F2).
  • integer_division (false): whether / of integers divides as integers (duckdb-F1).
  • error_on_division_by_zero (true) and ieee_floating_point_ops (true): division by zero raises or gives NULL; double edge cases give NaN and ±inf or errors and NULL (duckdb-R2, R4, R5, R15-*).
  • default_collation (empty): the collation of every string comparison and sort without a COLLATE. sverify’s SQL writes no collation and assumes byte order (duckdb-F4).
  • scalar_subquery_error_on_multiple_rows (true, deprecated): off, a scalar subquery with several rows returns an arbitrary one.
  • preserve_insertion_order (true): off, rows without ORDER BY may come in any order (duckdb-exec-row-order).
  • regex_match_operator_semantics (partial): whether the regex match operators match the whole string.
  • dialect_compatibility_mode (NONE): emulates another engine’s dialect, for example spark; the page does not say what it changes.
  • old_implicit_casting (false, deprecated) and legacy_disable_null_type (false): implicit casts and the NULL type at bind time.
  • TimeZone and Calendar (the host’s): ICU’s time-zone-aware functions (duckdb-time-zones).

Frontend-only, for SQL that sverify does not write: order_by_non_integer_literal, preserve_identifier_case, lambda_syntax, table_function_identifier_conversion, search_path.

Round 2 (source): regex_match_operator_semantics changes only which function the parser writes for the operators ~, !~, ~* and !~*: regexp_matches (partial, the default) or regexp_full_match (full). The functions themselves do not read it, so sverify’s SQL, which writes regexp_matches(…) as a function, is unaffected; setting it logs a deprecation warning. dialect_compatibility_mode has two values, NONE and SPARK; in v2.0.0 the binder reads it only to treat GROUP BY all and ORDER BY all as GROUP BY ALL and ORDER BY ALL when no column is named all. Neither changes a value sverify’s SQL computes.

What dialect_compatibility_mode and regex_match_operator_semantics change, and whether sverify’s SQL reaches it (it writes regexp_matches, a function, for the match operator), goes to source in a later round.

Round 3 (engine tests): default_null_order also decides where list_sort, list_reverse_sort and an aggregate’s ORDER BY (FIRST, LAST) put NULL (default_null_order_extended.test). error_on_division_by_zero is deprecated at v2.0.0 and warns when set.

Round 4 (probes): default_collation = 'nocase' makes =, ORDER BY and DISTINCT ignore case and leaves LIKE, contains, starts_with, strpos, ends_with, prefix and replace case-sensitive (duckdb-string-match-case). The optimizer setting disabled_optimizers = 'join_filter_pushdown' changes whether a raising filter under a join raises (duckdb-exec-filter-pushdown).

The reference: The reference has no settings: each choice is an option of the plan (null_order, division_type, on_division_by_zero, nan_creation), and a profile’s facts hold under the settings they record (sverify._engine_settings, #444).

Options: settings.

Constructs: SET.

Settings: {"Calendar": "the host's", "TimeZone": "the host's", "default_collation": "", "default_null_order": "nulls_last", "default_order": "ascending", "dialect_compatibility_mode": "none", "error_on_division_by_zero": true, "ieee_floating_point_ops": true, "integer_division": false, "old_implicit_casting": false, "order_by_non_integer_literal": false, "preserve_insertion_order": true, "regex_match_operator_semantics": "partial", "scalar_subquery_error_on_multiple_rows": true}.

Provenance:

duckdb-exec-short-circuit: Short-circuit: CASE and COALESCE lazy, AND/OR lazy only in a filter

Engine: duckdb 2.0.0; area: execution; status: open; evidence: fuzz.

Verdict: CASE, COALESCE lazy at run time, but a raising constant WHEN raises while planning (DD-7); AND/OR eager in a projection, sequential in a filter with the order kept when a child can raise

The documentation is silent: the CASE page says the matching branch is evaluated and returned, and the logical operators page says AND and OR are commutative. Neither says whether other operands run.

The executor at v2.0.0: CASE evaluates each WHEN on the rows still undecided and each THEN only on the rows its WHEN selects, ELSE on the rest (execute_case.cpp). COALESCE evaluates each argument only on the rows still NULL (execute_operator.cpp). So a raising branch or argument runs only on the rows that reach it.

AND and OR run two ways. As a value (in a SELECT list, or inside a CASE result), Execute evaluates every child on every row and combines them, so SELECT x <> 0 AND 10 // x > 1 raises on x = 0. As a condition (WHERE, a CASE WHEN, a join filter), Select evaluates the children one after the other, each on the rows the earlier ones left undecided. The adaptive filter permutes the children at run time by observed cost, but not when one of them can raise (Expression::CanThrow) or holds a barrier, and the optimizer’s cost ordering (ExpressionHeuristics) also leaves such a conjunction in its written order. DuckDB’s own test checks that WHERE id <> 0 AND s::INT = 10 does not raise on the rows id <> 0 removes, also when the filter is pushed into a dictionary-compressed segment or a Parquet dictionary.

Constant folding does not raise: an expression whose folding fails is left in place (constant_folding.cpp), so CASE WHEN false THEN 1 // 0 END is evaluated at run time, lazily.

To settle at the next rounds: probes of SELECT a AND b with a raising b, of a WHERE that writes the raising conjunct first, and of which functions CanThrow misses (a function marked as not raising that does raise would be permuted).

Round 2 (source): common subexpression elimination respects the lazy forms: inside a CASE or a conjunction it counts only the first child as a new candidate, so a subexpression of a THEN is hoisted only when it also occurs where it is evaluated unconditionally (cse_optimizer.cpp). One eager path remains: the CASE simplification rule evaluates every foldable WHEN at plan time with EvaluateScalar, which throws, where constant folding uses TryEvaluateScalar and leaves a failing expression alone. So CASE WHEN x > 0 THEN 1 WHEN 1 // 0 = 1 THEN 2 END appears to raise while planning, even on an empty table or when every row takes the first branch. From reading only: a probe must confirm it (ST-12 covers the operand order of AND/OR, #604).

Round 3 (engine tests): DuckDB’s tests pin lazy CASE (also an AND in a WHEN) and COALESCE, a failing constant left unfolded, and NOT (false AND error('…')) raising: an AND evaluated as a value runs every operand. No test covers a WHEN that raises at plan time.

Round 4 (probes): the CASE simplification raises while planning, as round 2 read. CASE WHEN x > 0 THEN 1 WHEN 1 // 0 = 1 THEN 2 END raises Division by zero over a table whose one row takes the first branch, over an empty table and under EXPLAIN; a constant WHEN 'a'::INTEGER = 1 raises its conversion error the same way. A raising ELSE that no row reaches does not raise. This is DD-7, and it breaks the reference’s lazy CASE. AND and OR as values raise on x = 0 ((x <> 0) AND (10 // x > 1) over 0 and 5), except when statistics decide the guard: over a one-row table holding 0 the projection is folded to false and nothing raises. As a WHERE they do not raise, in either written order when the guard is a column compared with a constant (x <> 0 is pushed into the scan and runs first), and in the written order otherwise: WHERE (10 // x > 1) AND (x * x <> 0) raises. An AND in a CASE WHEN is lazy. An aggregate’s FILTER (WHERE (x <> 0) AND (10 // x > 1)) raises: the filter clause evaluates its conjunction as a value.

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, a join condition on the pairs it forms. Its issue analysis (Rel.HasIssue, SubstraitLean/Safety.lean; docs/how-it-works.md) trusts the engine’s evaluation order: CASE is lazy, AND/OR are not, and filter and join conditions may run before the filters below them. A plan verify reports error-free must run without error, so an engine that evaluates a raising expression on rows outside that model breaks the guarantee. Lean evaluates CASE lazily and and/or on both operands (Kleene), the same model.

Since D21 (docs/core-semantics.md, #609) the reference itself raises on a constant subexpression that raises, in any CASE branch and on an empty table too (Rel.constIssues), so DuckDB’s plan-time fold (DD-7) no longer breaks a verified plan’s no-error guarantee: such a plan is not verified error-free.

Options: evaluation_order.

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

Settings: {"error_on_division_by_zero": true}.

See: upstream ledger DD-7, ST-12; issues #604.

Provenance:

duckdb-exec-size-limits: Size limits: decimal width 38; strings and blobs 4 GiB - 1; vectors 128 GB

Engine: duckdb 2.0.0; area: execution; status: open; evidence: engine_test.

Verdict: decimal width over 38, a string or blob over 2^32 - 1 bytes and a vector over 128 GB raise OutOfRange

Decimals: a width must be 1 to 38; addition, subtraction and multiplication raise when the result needs a width over 38, and a declared value out of bounds is rejected by the binder (numeric types page).

Strings: the text page says a declared length VARCHAR(n) has no effect, and names no maximum. Lists, arrays, structs and maps: the list page names no maximum length or depth. The parser’s max_expression_depth (default 1000) limits nesting of expressions; raising it risks a stack overflow (configuration page).

Source at v2.0.0: a string or blob (string_t) has a 32-bit length, MAX_STRING_SIZE = 2^32 - 1 bytes. Every string built in a vector’s heap (StringVector::EmptyString, AddBlob) goes through StringHeap::CreateEmptyStringInHeap, which raises an OutOfRangeException past it (“Cannot create a string of size …”), so repeat, concat and the like raise rather than wrap. Lists and maps (list_entry_t) have 64-bit offsets and lengths; their child vector grows through the standard vector buffer, which raises an OutOfRangeException past 128 GB (MAX_VECTOR_SIZE = 2^37 bytes) for one vector. An out-of-range error is an execution error, so TRY turns it into NULL (duckdb-exec-try).

The reference: The reference caps decimals at 38 digits and raises past them (docs/reference-semantics.md, decimal arithmetic). It has no limit on string, blob or list lengths.

Options: size_limits.

Constructs: DECIMAL, VARCHAR, BLOB, LIST, max_expression_depth.

Settings: {"max_expression_depth": 1000}.

Provenance:

duckdb-exec-try: TRY_CAST and TRY(…): which errors become NULL

Engine: duckdb 2.0.0; area: execution; status: open; evidence: engine_test.

Verdict: TRY nulls invalid-input, out-of-range and conversion errors row by row; TRY_CAST nulls conversion failures, except casts of extension types

TRY_CAST returns NULL where the cast fails, and the cast page says it never raises. The planner’s CastCanThrow qualifies this at v2.0.0: a cast to or from an aliased type (one an extension implements, such as JSON) can raise even as TRY_CAST, for example JSON to a MAP whose key fails to cast.

TRY(expr) turns errors caused by a row into NULL for that row (docs: TRY('abc'::INTEGER), TRY(1 // 0)). It cannot wrap a volatile function, an aggregate or a scalar subquery; that is refused at bind time. In the executor it runs the child on the whole vector; on an error of type INVALID_INPUT, OUT_OF_RANGE or CONVERSION it runs it again row by row and gives NULL for each row that raises; other error types propagate (Exception::IsExecutionError). So TRY(error('x')), an invalid-input error, also gives NULL, and an internal or out-of-memory error still raises.

Round 2 (source): TRY_CAST is not exception handling. The cast function passes an error-message slot to the cast kernels only for a try-cast (cast.cpp); a kernel that fails a row reports it through HandleCastError::AssignError, which throws a ConversionException when there is no slot and otherwise records the message and leaves the row NULL. So TRY_CAST nulls exactly the failures a kernel reports, and any other exception still raises: an out-of-memory or string-size OutOfRange error, and the kernels of extension types (the CastCanThrow case above). The direct throws left in src/function/cast/ are bind-time (struct and union shape errors) or guarded by parameters.strict. TRY nulls division by zero (an InvalidInputException, arithmetic.cpp) and overflow (OutOfRangeException); DuckDB’s test of nested CASE and COALESCE under TRY (test/sql/error/test_try_nested_conditional.test) checks the row-by-row retry.

No upstream test pins a raising TRY_CAST of an extension type; it may change between versions.

The reference: Substrait’s cast has failure_behavior: RETURN_NULL; the reference honours it on DuckDB for a double to an integer and an integer to a narrower integer, and refuses TRY_CAST to a decimal, of a decimal to an integer and of strings (docs/reference-semantics.md, #58). Substrait has no TRY of a general expression.

Options: try_variants.

Constructs: TRY_CAST, TRY.

Provenance:

io

duckdb-IO1: unity_catalog: ATTACH ignores a named secret unless it names it

Engine: duckdb 2.0.0; area: io; status: workaround; evidence: probe.

Verdict: use an unnamed secret, or ATTACH … (SECRET name)

ATTACH '<catalog>' AS x (TYPE unity_catalog) uses only the default (unnamed) unity_catalog secret. With a named secret (CREATE SECRET uc (TYPE unity_catalog, ...)) and no SECRET option, it does not fail on the missing secret: it requests the API with an empty endpoint, /api/2.1/unity-catalog/... with no host, and fails with Could not resolve hostname.

Workaround: create the secret without a name, or name it in the ATTACH: (TYPE unity_catalog, SECRET uc). The endpoint is the server’s root (http://host:port, with or without a trailing slash): one with the API path gets the path twice (404), and one without a scheme is refused (Unsupported URL scheme).

Options: catalogs, object_stores.

Constructs: CREATE SECRET (TYPE unity_catalog), ATTACH ... (TYPE unity_catalog).

Provenance:

  • 2026-11-03, manual-repro, by homelab catalog session (AlexanderNenninger/homelab#9): DuckDB 2.0.0 in Python, extensions unity_catalog cc59382f52, delta 9dcfb65b99, httpfs 77392a7207, against Unity Catalog OSS 0.7.0 with unitycatalog/unitycatalog#1959 on MinIO: A first reading blamed the missing trailing slash; the same session’s check of each combination showed the secret’s name was the cause.
    • CREATE SECRET uc (TYPE unity_catalog, TOKEN 'none', ENDPOINT 'http://unitycatalog:8080/', AWS_REGION 'us-east-1'); ATTACH 'homelab' AS lake (TYPE unity_catalog) gives IO Error: Could not resolve hostname error for HTTP GET to '/api/2.1/unity-catalog/schemas?catalog_name=homelab'
    • the same named secret; ATTACH 'homelab' AS lake (TYPE unity_catalog, SECRET uc) gives attached; schemas ci, cycling, metrics
    • CREATE SECRET (TYPE unity_catalog, ..., ENDPOINT 'http://unitycatalog:8080'); ATTACH 'homelab' AS lake (TYPE unity_catalog) gives attached; the same with ENDPOINT 'http://unitycatalog:8080/'
    • ... ENDPOINT 'http://unitycatalog:8080/api/2.1/unity-catalog' ... gives Request to 'http://unitycatalog:8080/api/2.1/unity-catalog/api/2.1/unity-catalog/schemas?catalog_name=homelab' failed (HTTP 404)
    • ... ENDPOINT 'unitycatalog:8080' ... gives Failed to initialize curl URL: Unsupported URL scheme

duckdb-IO2: unity_catalog: the vended endpoint_url is ignored

Engine: duckdb 2.0.0; area: io; status: workaround; evidence: probe.

Verdict: set s3_endpoint, s3_url_style, s3_use_ssl

For each table it reads, the unity_catalog extension asks the catalog for temporary credentials and creates an internal S3 secret with them (__internal_uc_<table id>, scoped to the table’s location). It does not take the endpoint_url the catalog vends with them (an S3-compatible store such as MinIO), so the reads go to AWS and fail with InvalidAccessKeyId.

Workaround: DuckDB’s global S3 settings, which the internal secret falls back to: SET s3_endpoint='host:port', SET s3_url_style='path', SET s3_use_ssl=false for plain HTTP, SET s3_region=....

Options: catalogs, object_stores.

Constructs: ATTACH ... (TYPE unity_catalog), temporary-table-credentials.

Settings: {"s3_endpoint": "minio:9000", "s3_region": "us-east-1", "s3_url_style": "path", "s3_use_ssl": false}.

Provenance:

  • 2026-11-03, manual-repro, by homelab catalog session (AlexanderNenninger/homelab#9): DuckDB 2.0.0 in Python, extensions unity_catalog cc59382f52, delta 9dcfb65b99, httpfs 77392a7207, against Unity Catalog OSS 0.7.0 with unitycatalog/unitycatalog#1959 on MinIO
    • ATTACH 'homelab' AS lake (TYPE unity_catalog); SELECT count(*) FROM lake.ci.job_commits gives HTTP 403 listing 's3://metrics/ci/job_commits/_delta_log/': InvalidAccessKeyId: The AWS Access Key Id you provided does not exist in our records (an AWS response; the catalog vended MinIO keys with endpoint_url http://minio:9000)
    • SELECT name, type, scope FROM duckdb_secrets() gives ('__internal_uc_c94a42d7-…', 's3', ['s3://metrics/ci/job_commits'])
    • SET s3_endpoint='minio:9000'; SET s3_url_style='path'; SET s3_use_ssl=false; SET s3_region='us-east-1'; then the same SELECT gives the reads reach MinIO (and stop at duckdb-IO3)

duckdb-IO3: delta: access denied with table-scoped vended keys on MinIO

Engine: duckdb 2.0.0; area: io; status: open; evidence: probe.

Verdict: open

With the workarounds of duckdb-IO1 and duckdb-IO2, a Delta table of the catalog fails in the delta extension (Delta Kernel) with ObjectStoreError (8): ... lacked the necessary privileges, on tables with and without _delta_log/_last_checkpoint. The keys are MinIO STS keys whose session policy the catalog scopes to the table’s prefix (GetObject on the prefix, ListBucket with an s3:prefix condition).

delta-rs 1.6.6 reads the same tables with keys vended the same way (both through uc:// and with the keys passed in storage_options), so the keys allow reading the table; which request Delta Kernel makes outside them is not isolated.

Unexplained: the failing call is in the delta extension (Delta Kernel’s object store), which DuckDB ships out of tree, so DuckDB’s v2.0.0 source does not explain it, and which request falls outside the session policy is not isolated.

Options: object_stores, io_formats.

Constructs: ATTACH ... (TYPE unity_catalog), delta_scan.

Settings: {"s3_endpoint": "minio:9000", "s3_url_style": "path", "s3_use_ssl": false}.

Provenance:

  • 2026-11-03, manual-repro, by homelab catalog session (AlexanderNenninger/homelab#9): DuckDB 2.0.0 in Python, extensions unity_catalog cc59382f52, delta 9dcfb65b99, httpfs 77392a7207, against Unity Catalog OSS 0.7.0 with unitycatalog/unitycatalog#1959 on MinIO: delta-rs 1.6.6 (DeltaTable('uc://homelab.ci.job_commits', ...) and with the resolver’s vended keys) read version 8 of the same table in the same container.
    • SELECT count(*) FROM lake.metrics.host_minute gives DeltaKernel ObjectStoreError (8): Error interacting with object store: The operation lacked the necessary privileges to complete for path host_minute/ (the table has a _last_checkpoint)
    • SELECT count(*) FROM lake.ci.job_commits gives the same error for path ci/job_commits/_delta_log/_last_checkpoint (the table has no _last_checkpoint)
  • 2026-11-04, source-reading, by characterization pipeline reverse pass: Unexplained: the failing request is not in DuckDB’s tree, and which S3 request falls outside the session policy is not isolated. Docs not assessed; no upstream test.