Yaoxuan Wu created FLINK-40923:
----------------------------------
Summary: `a >= b` on DOUBLE NaN keeps the row in one query but
drops it when the input is first written to a table (reflexive comparison
simplified to TRUE)
Key: FLINK-40923
URL: https://issues.apache.org/jira/browse/FLINK-40923
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 2.3.0
Reporter: Yaoxuan Wu
Running a query in two steps (materialize part 1, then run part 2 on it) gives
a different result than running it as one query:
CREATE TEMPORARY TABLE m (a DOUBLE, b DOUBLE)
WITH ('connector' = 'filesystem', 'path' = 'file:///tmp/m', 'format' = 'csv');
{code:java}
-- (A) part 1: two copies of one value
SELECT x AS a, x AS b
FROM (SELECT CAST(s AS DOUBLE) AS x FROM (VALUES ('NaN'), ('1.5')) AS v(s));
-- (NaN, NaN), (1.5, 1.5)
-- (B) materialize part 1, then run part 2 on it
INSERT INTO m
SELECT x AS a, x AS b
FROM (SELECT CAST(s AS DOUBLE) AS x FROM (VALUES ('NaN'), ('1.5')) AS v(s));
SELECT * FROM m WHERE a >= b;
-- (1.5, 1.5)
-- (C) part 1 + part 2 as one query
SELECT * FROM (
SELECT x AS a, x AS b
FROM (SELECT CAST(s AS DOUBLE) AS x FROM (VALUES ('NaN'), ('1.5')) AS v(s))
) WHERE a >= b;
-- (NaN, NaN), (1.5, 1.5) {code}
*My understanding is that (B) and (C) should return the same rows.*
In (C) the planner sees that `a` and `b` are both `x` and simplifies `a >= b`
to `x IS NOT NULL` (Calcite's `RexSimplify`, cf. CALCITE-4467), so the NaN row
is kept. In (B) the comparison is evaluated at runtime, where `NaN >= NaN` is
FALSE.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
