[
https://issues.apache.org/jira/browse/FLINK-40923?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18124285#comment-18124285
]
Yahya Kisana commented on FLINK-40923:
--------------------------------------
While investigating this, noticed alot of NaN comparison is inconsistent
Filed a ticket here: https://issues.apache.org/jira/browse/FLINK-40927
Have a PR out for the issue in current ticket though.
> `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
> Priority: Major
> Labels: pull-request-available
>
> 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)
