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

Ouverte
  • Quelque chose ne va pas ?
#27,335 0 commentaires 0 réactions 0 personnes assignées Voir sur GitHub

Les mainteneurs répondent en général sous 1 jour

Personne n'a encore pris cette issue.

Évaluation

Difficulté
4/5
Temps estimé
3-5 jours
Accessibilité débutants
48/100
Type d'issue
Bug
Clarté
Plutôt claire
Activité
Active
Stack technique
rust, sql
Domaine
backend-api-design, databases

Piste de recherche

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.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Description

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.

Langage dominant
Rust
Étoiles
9.6k
Forks
2.6k
Merge moyen
3 j 4 h
PR mergées (30 j)
345

Préparer son environnement

Ouvrir dans Codespaces

Lance le conteneur de développement du projet dans votre navigateur, avec votre propre compte GitHub.

Par où commencer

  1. Lisez l'issue en entier, puis le guide de contribution du projet.
  2. Signalez en commentaire que vous la prenez — cela évite que deux personnes fassent le même travail.
  3. Forkez le dépôt et travaillez sur une branche.
  4. Ouvrez une pull request qui référence le numéro de l'issue.

Autres issues de apache/datafusion

Toutes les issues de apache/datafusion

Issues similaires

Plus d'issues Rust

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.