Comparing a DOUBLE with a DECIMAL casts the DOUBLE to Decimal128(30, 15), which fails on large values and NaN and truncates small ones

Aperta
  • Qualcosa non va?
#27,335 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
48/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
rust, sql
Ambito
backend-api-design, databases

Direzione di ricerca

Start with datafusion/expr-common/src/type_coercion/binary.rs, especially decimal_coercion and the Float64-to-Decimal128 mapping named in the issue. Read the related coercion tests and the context in #4024 to understand why decimal coercion preserves cases such as TPC-H q6. Done means comparisons and type unions handle large doubles, NaN, infinities, and tiny values as expected while preserving the documented decimal-literal behavior.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

bug
Describe the bug

When a FLOAT or DOUBLE meets a DECIMAL in a comparison, IN, CASE, COALESCE or another type union, DataFusion casts the float to a decimal. decimal_coercion turns Float64 into Decimal128(30, 15) (#L1395, next to a TODO saying it may overflow) and widens from there. Most doubles don't fit that type:

  • values of 1e15 or more overflow, and the query fails;
  • NaN and the infinities can't be cast, and the query fails;
  • values smaller than 1e-15 round to zero, and the query returns wrong results.

Arithmetic isn't affected: double + decimal is a Float64.

To Reproduce

On main (e7c51c7fb), with the default settings:

SELECT CAST('1e20' AS DOUBLE) > CAST(1.5 AS DECIMAL(2, 1));
-- Arrow error: Invalid argument error: 99999999999999996863.366107917975552 is too large to store in a Decimal128 of precision 30. Max is 999999999999999.999999999999999

SELECT CAST('NaN' AS DOUBLE) > CAST(1.5 AS DECIMAL(2, 1));
-- Arrow error: Cast error: Cannot cast to Decimal128(30, 15). Overflowing on NaN

SELECT CAST('1e-20' AS DOUBLE) > CAST(0 AS DECIMAL(2, 1));
-- false

SELECT CASE WHEN true THEN CAST('NaN' AS DOUBLE) ELSE CAST(1.5 AS DECIMAL(2, 1)) END;
-- Arrow error: Cast error: Cannot cast to Decimal128(30, 15). Overflowing on NaN

CREATE TABLE m (d DOUBLE, n DECIMAL(10, 2)) AS VALUES (1e20, 1.5), (1e-20, 0);
SELECT d, n FROM m WHERE d > n;
-- Failed to cast field 'd' from Float64 to Decimal128(30, 15)

With datafusion.sql_parser.parse_float_as_decimal = true a plain literal is enough: SELECT CAST('1e-20' AS DOUBLE) > 0.0 returns false.

Expected behavior

true, true, true, NaN, and both rows of m.

Spark, which the Decimal128(30, 15) mapping comes from (DecimalType.forType), doesn't use it for comparisons: it casts the decimal to DOUBLE (DecimalPrecisionTypeCoercion.scala#L182-L185). DOUBLE is also its common type of a DECIMAL and a FLOAT or DOUBLE in CASE, IF, COALESCE and IN (TypeCoercionHelper.scala#L194-L195). Spark does this in every version from 3.4 to 4.2.

Additional context

Preferring the decimal has a purpose, though (see #4024). In TPC-H q6, l_discount between 0.06 - 0.01 and 0.06 + 0.01 compares a DECIMAL(15, 2) column with float literals, and 0.06 + 0.01 is 0.06999999999999999. Cast to Decimal128(30, 15) it rounds back to 0.07, so the rows where l_discount is 0.07 are kept. Compared as doubles, 0.07 <= 0.06 + 0.01 is false. A fix has to keep that case working. Some options:

  • cast to DOUBLE only when the float side isn't a constant;
  • use DOUBLE when float literals are already decimals, that is with parse_float_as_decimal or the Spark dialect;
  • make the rule configurable.

This will matter more once #27185 is fixed: with the Spark dialect, 1.5 becomes a DECIMAL (#27213), so every WHERE double_col > 0.5 hits it.

Lingua principale
Rust
Stelle
9.6k
Fork
2.6k
Merge medio
3g 4h
PR unite (30g)
345

Preparare l'ambiente

Apri in Codespaces

Avvia il container di sviluppo del progetto nel browser, con il tuo account GitHub.

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di apache/datafusion

Tutte le issue di apache/datafusion

Issue simili

Altre issue su Rust

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.