kosiew opened a new issue, #17557:
URL: https://github.com/apache/datafusion/issues/17557
### Describe the bug
Comparisons with `NaN` values behave inconsistently between the DataFusion
Python API and `datafusion-cli`.
When a table contains `a = NaN` and `b = 1.0`, expressions such as `(a < b)`
and `(a > b)` produce results that differ between the two interfaces instead of
returning SQL-standard `NULL` values (or otherwise being consistent across
interfaces).
### To Reproduce
## Python (DataFusion API)
```python
import numpy as np
import pyarrow as pa
import datafusion as dfn
from datafusion import col
table = pa.table({"a": [np.nan], "b": [1.0]})
ctx = dfn.SessionContext()
df = ctx.from_arrow(table)
result = df.select(
(col("a") < col("b")).alias("less"),
(col("a") > col("b")).alias("greater"),
)
print(result.to_pandas())
```
## datafusion-cli (SQL)
Use the CLI and create a `NaN` value (one way is using `sqrt(-1.0)`):
```sql
-- start the CLI
-- datafusion-cli
WITH t AS (
SELECT sqrt(CAST(-1.0 AS DOUBLE)) AS a, CAST(1.0 AS DOUBLE) AS b
)
SELECT
(a < b) AS less,
(a > b) AS greater
FROM t;
```
```
SELECT
(a < b) AS less,
(a > b) AS greater
FROM t;
+-------+---------+
| less | greater |
+-------+---------+
| false | true |
+-------+---------+
1 row(s) fetched.
```
Alternatively (VALUES form):
```sql
SELECT
(a < b) AS less,
(a > b) AS greater
FROM (
VALUES (sqrt(CAST(-1.0 AS DOUBLE)), CAST(1.0 AS DOUBLE))
) AS t(a, b);
```
### Expected behavior
Both the Python API and the `datafusion-cli` should yield identical results
and follow standard SQL semantics for comparisons with `NaN`:
* `(NaN < 1.0)` → false
* `(NaN > 1.0)` → false
Results should be deterministic and consistent across interfaces.
### Additional context
https://github.com/apache/datafusion-python/issues/1233
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
