apache / apache/datafusion

GROUP BY constant with aggregation function and with empty input magically summons a row

Open
#15,734 4 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

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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.