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

未关闭
  • 哪里不对？
#27,335 0 条评论 0 个 reaction 已指派 0 人 在 GitHub 查看

维护者通常 1 天内回复

还没有人认领这个 Issue。

评估

难度
4/5
预计耗时
3-5 天
新手友好度
48/100
Issue 类型
缺陷
描述清晰度
基本清楚
活跃度
活跃
技术栈
rust, sql
领域
backend-api-design, databases

调研方向

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.

由索引模型根据 Issue 内容生成。

描述

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.

主要语言
Rust
星标
9.6k
派生
2.6k
平均合并
3 天 4 小时
30 天内合并 PR
345

环境准备

在 Codespaces 中打开

在浏览器里用你自己的 GitHub 账号启动这个项目的开发容器。

从这里开始

  1. 先读完整个 Issue，再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库，在一个分支上完成修改。
  4. 提交 Pull Request，并在描述里引用这个 Issue 编号。

apache/datafusion 的其他 Issue

查看 apache/datafusion 的全部 Issue

相似的 Issue

更多 Rust Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。