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

Offen
  • Stimmt etwas nicht?
#27,335 0 Kommentare 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen

Maintainer antworten meist innerhalb von 1 Tag

Dieses Issue hat noch niemand übernommen.

Bewertung

Schwierigkeit
4/5
Geschätzter Aufwand
3-5 Tage
Anfängerfreundlichkeit
48/100
Issue-Typ
Bug
Klarheit
Größtenteils klar
Aktivitätsstatus
Aktiv
Tech-Stack
rust, sql
Bereich
backend-api-design, databases

Rechercherichtung

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.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Beschreibung

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.

Vorherrschende Sprache
Rust
Sterne
9.6k
Forks
2.6k
Ø Merge
3 T. 4 Std.
Gemergte PRs (30 T.)
345

Entwicklungsumgebung

In Codespaces öffnen

Startet den Dev-Container des Projekts im Browser, mit Ihrem eigenen GitHub-Konto.

Erste Schritte

  1. Lesen Sie das ganze Issue und danach den Beitragsleitfaden des Projekts.
  2. Schreiben Sie ins Issue, dass Sie es übernehmen — das erspart doppelte Arbeit.
  3. Forken Sie das Repository und arbeiten Sie in einem Branch.
  4. Öffnen Sie einen Pull Request, der die Issue-Nummer nennt.

Mehr aus apache/datafusion

Alle Issues in apache/datafusion

Ähnliche Issues

Weitere Issues zu Rust

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.