[I] TopKAggregation drops the NULL group under ORDER BY LIMIT n [datafusion]

["cning112 (via GitHub)" <gi...@apache.org>]

cning112 opened a new issue, #25850:
URL: https://github.com/apache/datafusion/issues/25850

   ### Describe the bug
   With the default physical optimizer rules, a grouped or distinct query that sorts by a single group key and applies a limit drops the NULL group.
   
   ### To Reproduce
   DataFusion 53.1.0:
   
   ```sql
   CREATE TABLE t(x BIGINT); -- rows: -301, 100, 500, NULL
   SELECT DISTINCT x FROM t ORDER BY x LIMIT 10;        -- -301, 100, 500        (NULL missing)
   SELECT x FROM t GROUP BY x ORDER BY x DESC LIMIT 10; -- 500, 100, -301        (NULL missing)
   SELECT DISTINCT x, g FROM t ORDER BY x, g LIMIT 20;  -- NULL rows present (two sort keys)
   ```
   
   Setting `optimizer.enable_topk_aggregation = false` restores the NULL group in both failing queries.
   
   ### Expected behaviour
   The NULL group is returned in its normal sort position (nulls-last by default).
   
   ### Additional context
   Found while cross-checking a SQL engine against DuckDB, where both queries return the NULL group. The rule involved is `TopKAggregation` (`enable_topk_aggregation`).
   


-- 
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] TopKAggregation drops the NULL group under ORDER BY LIMIT n [datafusion]

["HairstonE (via GitHub)" <gi...@apache.org>]

HairstonE commented on issue #25850:
URL: https://github.com/apache/datafusion/issues/25850#issuecomment-5982804169

   This is already fixed on main. The bug was in `GroupedTopKAggregateStream::intern`, which skipped NULL group keys. It is the same bug as #22554. PR #22571 fixed this.
   
   I ran this issue's exact queries against main. Both return the NULL group in the correct position:
   
   ```
   SELECT DISTINCT x FROM t ORDER BY x LIMIT 10;        → -301, 100, 500, NULL
   SELECT x FROM t GROUP BY x ORDER BY x DESC LIMIT 10; → NULL, 500, 100, -301
   ```
   
   The same PR also added regression tests in `aggregates_topk.slt`. An upgrade to 55.0.0 will resolve this. 


-- 
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