jayzhan211 opened a new issue, #25998:
URL: https://github.com/apache/datafusion/issues/25998
### Describe the bug
`ORDER BY <list or struct column> LIMIT n` can return the wrong rows when
the sort key values contain NULL elements. The same query without `LIMIT` sorts
correctly.
### To Reproduce
```sql
SET datafusion.execution.target_partitions = 1;
create table t (l int[]) as values (make_array(1, NULL));
insert into t values (make_array(1, 2));
select l from t order by l limit 1;
-- [1, NULL] (wrong)
select l from t order by l;
-- [1, 2]
-- [1, NULL] (correct)
create table s as
select named_struct('a', 1, 'b', NULL::int) x
union all
select named_struct('a', 1, 'b', 2);
select x from s order by x limit 1;
-- {a: 1, b: NULL} (wrong)
```
`ORDER BY l DESC LIMIT 1` is also wrong (with the rows inserted in the
opposite order), as are multi-column orderings such as `ORDER BY a, l LIMIT 1`
with ties on `a`, or `ORDER BY l, b LIMIT 1`.
### Expected behavior
`select l from t order by l limit 1` returns `[1, 2]`, and `select x from s
order by x limit 1` returns `{a: 1, b: 2}`, i.e. the first row of the full
`ORDER BY` result.
### Additional context
The TopK operator builds its dynamic filter as `col < threshold` (or `>` for
`DESC`) plus `col = threshold` terms. For nested types these comparisons go
through `compare_op_for_nested`, which always uses `SortOptions::default()`, so
NULL elements inside a list/struct are ordered differently from the sort itself
(which follows the query's `NULLS FIRST/LAST`). With the threshold `[1, NULL]`,
`[1, 2] < [1, NULL]` evaluates to false and the correct row is filtered out.
TopK applies this filter to its own input as well as pushing it down, so
setting `datafusion.optimizer.enable_topk_dynamic_filter_pushdown = false` (or
`enable_dynamic_filter_pushdown = false`) does not avoid the bug.
#25957 is the same root cause in `PiecewiseMergeJoinExec`.
--
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]
