Yaoxuan Wu created FLINK-41600:
----------------------------------

             Summary: Comparing DECIMAL with DOUBLE using <, >, BETWEEN uses 
Double.compare: 0.00 > -0.0 is TRUE while 0.00 = -0.0 is TRUE, and 0.00 < NaN 
is TRUE
                 Key: FLINK-41600
                 URL: https://issues.apache.org/jira/browse/FLINK-41600
             Project: Flink
          Issue Type: Bug
          Components: Table SQL / Runtime
    Affects Versions: 2.2.1, 2.3.0, 2.0.1
            Reporter: Yaoxuan Wu


Comparing a DECIMAL with a DOUBLE using {{<}}, {{<=}}, {{>}}, {{>=}} or BETWEEN 
uses a total order instead of SQL / IEEE semantics: -0.0 is smaller than 0.00, 
and NaN is greater than every value. The result contradicts {{=}} on the same 
values. It also differs from the same comparison after {{CAST(d AS DOUBLE)}}, 
and from the same comparison against a literal.

{code:sql}
-- t(k INT, d DECIMAL(10, 2), z DOUBLE, n DOUBLE) with one row (1, 0.00, -0.0, 
NaN)
SELECT d = z, d > z, d >= z, d <= z, CAST(d AS DOUBLE) > z FROM t;
-- expected: TRUE, FALSE, TRUE, TRUE, FALSE
-- actual:   TRUE, TRUE,  TRUE, FALSE, FALSE      (d = z and d > z are both 
TRUE)

SELECT k FROM t WHERE d BETWEEN z AND z;             -- expected 1, actual: no 
rows
SELECT k FROM t WHERE d > z;                         -- expected no rows, 
actual: 1
SELECT k FROM t WHERE d > -0.0E0;                    -- no rows (correct with a 
literal bound)

SELECT d < n, CAST(d AS DOUBLE) < n, k < n FROM t;   -- expected FALSE, FALSE, 
FALSE; actual TRUE, FALSE, FALSE
SELECT k FROM t WHERE d < n;                         -- expected no rows, 
actual: 1
{code}

The table above is built in PyFlink as {{from_elements([(1, Decimal('0.00'), 
-0.0, 'NaN')])}} with {{CAST(s AS DOUBLE)}} for {{n}}. Any source with these 
values behaves the same.

* Batch and streaming.
* Same on 2.0.1, 2.3.0 and master (2.4-SNAPSHOT, 2f01434 of 2026-11-19).
* DOUBLE vs DOUBLE, and INT / BIGINT vs DOUBLE, use IEEE semantics (0.0 = -0.0, 
every comparison with NaN is FALSE), so the result depends on whether the other 
operand is a DECIMAL.

Cause: the generated code compares a DECIMAL with a DOUBLE through 
{{DecimalDataUtils.compare(DecimalData, double)}} (and the mirrored 
{{compare(double, DecimalData)}}), which is {{Double.compare(d.doubleValue(), 
x)}}. {{Double.compare}} is a total order (-0.0 < 0.0, NaN greater than 
everything and equal to itself). With a literal bound, the planner casts or 
folds the literal first, so the IEEE result comes out.

A possible fix: compare {{d.doubleValue()}} with {{x}} using the primitive 
operators, or cast the DECIMAL operand to DOUBLE as is done for INT / BIGINT.

Related: FLINK-40927 (NaN semantics across planner and runtime) and FLINK-40945 
(-0.0 and 0.0 as different keys); this is a different code path (DECIMAL vs 
DOUBLE ordering comparisons).




--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

Reply via email to