apache / apache/datafusion

`ARRAY_AGG .. ORDER BY` fails on `NULL` typed columns

Open
#18,344 6 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
Rust
Stars
9.3k
Forks
2.4k
Avg merge
3d 7h
Merged PRs (30d)
344

Description

### Describe the bug

This appears to have regressed somewhere between v48 and v49.

### To Reproduce

```
DataFusion CLI v50.3.0
> create table source as
select null as col
union all
select null
union all
select null
;

0 row(s) fetched.
Elapsed 0.009 seconds.

> select
array_agg(distinct col) within group (order by col)
from source
group by all
;

Arrow error: Invalid argument error: The data type type Null has no natural order
```

### Expected behavior

```
DataFusion CLI v48.0.1
> create table source as
select null as col
union all
select null
union all
select null
;

0 row(s) fetched.
Elapsed 0.011 seconds.

> select
array_agg(distinct col) within group (order by col)
from source
group by all
;

+--------------------------------+
| array_agg(DISTINCT source.col) |
+--------------------------------+
| [NULL] |
+--------------------------------+
1 row(s) fetched.
Elapsed 0.019 seconds.
```

### Additional context

The exact semantics of a `Null` typed column is perhaps debatable, but if this behavior is intentional, we should probably catch this at planning time instead of letting it err at execution time.

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the query in DataFusion CLI v50.3.0 and compare it with v48.0.1. Trace the ARRAY_AGG DISTINCT ... WITHIN GROUP (ORDER BY ...) planning and execution paths for a NULL-typed column. Done means the query returns [NULL] as before, or the invalid ordering is rejected clearly during planning rather than execution.

Written by the indexing model from the issue text.

Assessment

Tech stack
rust, sql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.