GROUP BY constant with aggregation function and with empty input magically summons a row
- Dominant language
- Rust
- Stars
- 9.3k
- Forks
- 2.4k
- Avg merge
- 3d 7h
- Merged PRs (30d)
- 344
Description
### Describe the bug
Hello, it seems that there is inconsistent behaviour for select queries with group by on constant when there are no input rows.
When I have no aggregation functions then result of such query is empty, but as soon as I add some aggregation function I get single row result.
### To Reproduce
Run following query in datafusion-cli
```
> select 'wat' as key, count(1) as count from (select * from unnest(array[]::text[])) as b(field) group by key;
+-----+-------+
| key | count |
+-----+-------+
| wat | 0 |
+-----+-------+
1 row(s) fetched.
Elapsed 0.004 seconds.
```
### Expected behavior
Empty result
### Additional context
Query with no aggregation functions gives empty result
```
DataFusion CLI v46.0.1
> select 'wat' as key from (select * from unnest(array[]::text[])) as b(field) group by key;
+-----+
| key |
+-----+
+-----+
0 row(s) fetched.
Elapsed 0.004 seconds.
```
Postgres gives empty results in both cases
```
psql (17.4 (Homebrew))
Type "help" for help.
postgres=# select 'wat' as key from (select * from unnest(array[]::text[])) as b(field) group by key;
key
-----
(0 rows)
postgres=# select 'wat' as key, count(1) as count from (select * from unnest(array[]::text[])) as b(field) group by key;
key | count
-----+-------
(0 rows)
```
Contributor guide
Research direction
Start by reproducing the two SQL queries in datafusion-cli v46.0.1 and compare their results with PostgreSQL. Trace how empty input, a constant GROUP BY key, and COUNT aggregation are handled; done means the aggregation query returns zero rows for this case, with regression coverage for the behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100