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)