feat: Treat all NaNs as the same value (-NaN = NaN) #26091

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:

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

The min result is the most serious one. The column holds a real number, but min returns NaN.

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

Same as in #22835, before comparing, grouping or sorting numbers, 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.

Additional context

• PostgreSQL docs: https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-FLOAT.
• feat: Support IEEE 754 negative zero semantics #22835
• Spark xxhash64 hashes the raw bits of a NaN instead of the canonical NaN #25913
• Grouped floating-point MIN and MAX return order-dependent results for NaNs and signed zeros #24432
• Pruning of floating point Parquet columns is incorrect when NaN is present #15812