mkleen opened a new issue, #26091:
URL: https://github.com/apache/datafusion/issues/26091
### Is your feature request related to a problem or challenge?
## Is your feature request related to a problem or challenge?
PostgreSQL and DuckDB treat every NaN as the same value. Two NaNs are always
equal, and NaN always comes after every other number when sorting. DataFusion
instead treats `-NaN` as a different value from `NaN`, and puts it *before*
every other number when sorting:
```sql
CREATE TABLE t(x DOUBLE) AS VALUES ('NaN'), (-('NaN'::double)), (1.5);
SELECT 'NaN'::double = -('NaN'::double); -- false
SELECT count(DISTINCT x) FROM t; -- 3
SELECT min(x) FROM t; -- NaN (the column holds 1.5)
SELECT x FROM t ORDER BY x; -- NaN, 1.5, NaN
```
| Query | DataFusion | PostgreSQL / DuckDB |
|---|---|---|
| `NaN = -NaN` | false | true |
| `NaN IS DISTINCT FROM -NaN` | true | false |
| `-NaN < 0` | true | false |
| `DISTINCT` / `GROUP BY` / `UNION` | 2 NaN rows | 1 NaN row |
| `x IN ('NaN')` with `x = -NaN` | false | true |
| `ORDER BY x` | `-NaN` first, `NaN` last | all NaNs last |
| `min(x)` over `{1.5, -NaN}` | NaN | 1.5 |
The `min` result is the most serious one. The column holds a real number,
but `min` returns NaN. That is a wrong answer, not just a different count.
The reason is that DataFusion compares, groups and sorts numbers by how they
are stored in memory, and `NaN` and `-NaN` are stored differently. #22835 fixed
the same problem for `0.0` and `-0.0`, which are also stored differently but
mean the same number. It deliberately left NaN alone.
## Describe the solution you'd like
Extend the fix from #22835: before comparing, grouping or sorting numbers,
replace `-0.0` with `0.0` (as today) **and replace every NaN with one standard
NaN**. All other numbers stay as they are. The standard NaN already sorts after
every other number, which is exactly where PostgreSQL puts it.
### Equality and sorting have to change together
Changing only equality is not enough, because it breaks one of the ways
DataFusion joins tables. A sort-merge join first sorts both tables, then walks
through them side by side and matches equal values. If `-NaN` counts as equal
to `NaN` (the largest value) but is still sorted first, the join walks past
rows it should have matched.
### Open questions
- **Data that is already sorted.** Some files declare that they are already
sorted, and they may have been sorted with `-NaN` first. A join that relies on
that order could still miss matches. Should a file's declared sort order also
follow the new NaN rule?
- **Some join types check matches separately.** Sort-merge semi, anti and
mark joins have their own matching code (see #26003 / #26006), which also needs
the new rule.
- **Skipping Parquet data.** DataFusion skips parts of Parquet files based
on their stored minimum and maximum values. This is already wrong when NaN is
present (#15812) and needs checking under the new order.
## Additional context
- PostgreSQL docs: *"PostgreSQL treats NaN values as equal, and greater than
all non-NaN values"*
(https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-FLOAT).
- #22835 "feat: Support IEEE 754 negative zero semantics" (closed #22826,
#22490, #11108) made `0.0` and `-0.0` equal. This proposal does the same for
NaN.
- #25186 "IN LIST: treat signed zeros as equal" deliberately keeps different
NaNs apart. That would need to change under this proposal (#25912 builds on it).
- The Spark-compatible functions already have issues asking for all NaNs to
be treated as equal: #25913 (`xxhash64`, PR #25971) and #25981
(`array_contains`, PR #26011).
- #24432 changed grouped `min` / `max` to the current order, where `-NaN`
comes first.
- #15812 covers skipping Parquet data when NaN is present.
### Describe the solution you'd like
_No response_
### Describe alternatives you've considered
_No response_
### Additional context
_No response_
--
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]
