ameyypawar opened a new issue, #26545:
URL: https://github.com/apache/datafusion/issues/26545
### Describe the bug
A filter on a constant column, such as `k` in `SELECT 'all' AS k, count(*)
...`, is applied below an aggregate without `GROUP BY`, so it no longer removes
the aggregate's row. An aggregate without `GROUP BY` returns one row even for
an empty input, so the query returns that row (`count(*)` = 0, `max(v)` = NULL)
where it should return none.
### To Reproduce
```shell
datafusion-cli -c "
CREATE TABLE t(a INT, v INT) AS VALUES (1, 10), (2, 20), (2, 30);
SELECT c FROM (SELECT 'all' AS k, count(*) AS c FROM t) WHERE k = 'none';
"
```
This currently gives:
```
+---+
| c |
+---+
| 0 |
+---+
```
The same happens with other aggregates, with an empty grouping set
(`ROLLUP`, `CUBE`, `GROUPING SETS (..., ())`), and in a `UNION ALL` that labels
its branches:
| query | DataFusion | expected |
| --- | --- | --- |
| `SELECT m FROM (SELECT 'all' AS k, max(v) AS m FROM t) WHERE k = 'none'` |
one row, `NULL` | no rows |
| `SELECT a, c FROM (SELECT 'all' AS k, a, count(*) AS c FROM t GROUP BY
ROLLUP(a)) WHERE k = 'none'` | one row, `(NULL, 0)` | no rows |
| `SELECT c FROM (SELECT 'one' AS k, count(*) AS c FROM t WHERE a = 1 UNION
ALL SELECT 'all' AS k, count(*) AS c FROM t) WHERE k = 'all'` | `0`, `3` | `3` |
With a true filter (`WHERE k = 'all'`), or with a grouped aggregate (`GROUP
BY a`), the results are right.
### Expected behavior
No rows: the subquery's only row has `k = 'all'`, so `k = 'none'` is false
for it. SQLite 3.51 returns no rows for the `count(*)` and `max(v)` queries,
and `3` for the `UNION ALL` (it has no `ROLLUP`).
### Additional context
`PushDownFilter` moves the filter below the aggregate. In its `Aggregate`
arm, a predicate is pushed when all of its columns are group keys, which holds
for a predicate with no columns. `EXPLAIN VERBOSE` shows `logical_plan after
push_down_filter` with `Filter: Utf8("all") = Utf8("none")` between the
`Aggregate` and the `TableScan`.
#22926 made the physical filter pushdown keep filters above aggregates that
return a row for an empty input. `PushDownFilter` has no such check.
Unlike #26503, #26505 and #26508, no correlated subquery is involved, and
fixing `PushDownFilter` does not change their results.
Found with DataFusion CLI v55.1.0, `main` at d89a85d3e3.
### Willingness to contribute
I plan to contribute a fix for this bug independently
--
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]
