You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "1fanwang (via GitHub)" <gi...@apache.org> on 2026/09/26 06:05:23 UTC

[I] Window PARTITION BY puts 0.0 and -0.0 in separate partitions [datafusion]

1fanwang opened a new issue, #25766:
URL: https://github.com/apache/datafusion/issues/25766

   ### Describe the bug
   
   `PARTITION BY` in a window function puts `0.0` and `-0.0` in separate partitions. `GROUP BY`, `DISTINCT` and joins treat them as the same value (see `negative_zero.slt`), so window results disagree with the rest of the engine.
   
   ### To Reproduce
   
   ```sql
   > SELECT a, row_number() OVER (PARTITION BY a) AS rn FROM (VALUES (0.0), (-0.0)) t(a);
   +------+----+
   | a    | rn |
   +------+----+
   | -0.0 | 1  |
   | 0.0  | 1  |
   +------+----+
   
   > SELECT count(*) FROM (SELECT a FROM (VALUES (0.0), (-0.0)) t(a) GROUP BY a);
   +----------+
   | count(*) |
   +----------+
   | 1        |
   +----------+
   ```
   
   ### Expected behavior
   
   One partition, so the two rows get row numbers 1 and 2, matching `GROUP BY`.
   
   ### Additional context
   
   This shows up in `INTERSECT ALL` / `EXCEPT ALL` once they are planned with `row_number() OVER (PARTITION BY <all columns>)` (#25742):
   
   ```sql
   SELECT a FROM (VALUES (0.0), (-0.0)) t(a) INTERSECT ALL SELECT a FROM (VALUES (0.0)) s(a); -- 2 rows, expected 1
   SELECT a FROM (VALUES (0.0), (-0.0)) t(a) EXCEPT ALL SELECT a FROM (VALUES (0.0)) s(a);    -- 0 rows, expected 1
   ```
   
   Reported by @kita-renji in https://github.com/apache/datafusion/pull/25742#pullrequestreview-5324718949. I haven't traced it fully, but partition boundaries come from arrow's `partition` kernel (`evaluate_partition_ranges`), which I think compares floats by total order, where `-0.0 != 0.0`.
   


-- 
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: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Window PARTITION BY puts 0.0 and -0.0 in separate partitions [datafusion]

Posted by "ShayanGho (via GitHub)" <gi...@apache.org>.
ShayanGho commented on issue #25766:
URL: https://github.com/apache/datafusion/issues/25766#issuecomment-5852755857

   take


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Window PARTITION BY puts 0.0 and -0.0 in separate partitions [datafusion]

Posted by "1fanwang (via GitHub)" <gi...@apache.org>.
1fanwang commented on issue #25766:
URL: https://github.com/apache/datafusion/issues/25766#issuecomment-5844015808

   #25767 plans INTERSECT ALL and EXCEPT ALL with a grouped count instead of `row_number() OVER (PARTITION BY ..)`. Grouping treats 0.0 and -0.0 as equal, so with that change these return one row each:
   
   ```sql
   SELECT a FROM (VALUES (0.0), (-0.0)) t(a) INTERSECT ALL SELECT a FROM (VALUES (0.0)) s(a);
   SELECT a FROM (VALUES (0.0), (-0.0)) t(a) EXCEPT ALL SELECT a FROM (VALUES (0.0)) s(a);
   ```
   
   I checked 1000 random INTERSECT ALL and EXCEPT ALL queries over small DOUBLE tables with duplicates, NULLs, 0.0 and -0.0 against DuckDB 1.4.5. #25767 had no mismatches and #25742 had 237.
   
   The window behavior itself is unchanged. `SELECT a, row_number() OVER (PARTITION BY a) FROM (VALUES (0.0), (-0.0)) t(a)` still gives both rows row number 1. So the set operations would no longer depend on this issue, but the issue still stands for windows.
   


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org


Re: [I] Window PARTITION BY puts 0.0 and -0.0 in separate partitions [datafusion]

Posted by "ShayanGho (via GitHub)" <gi...@apache.org>.
ShayanGho commented on issue #25766:
URL: https://github.com/apache/datafusion/issues/25766#issuecomment-5852758082

   @1fanwang are you planning to tackle the window partitioning bug too? I’d be interested in working on it if you haven’t started.


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org