Repository navigation
Commit c4f5a9e
fix: do not push filters on volatile group keys below Aggregate (apache#25416)
## Which issue does this PR close?
- Closes apache#25415.
## Rationale for this change
A filter on a volatile `GROUP BY` key returns rows that do not satisfy
the filter:
```sql
CREATE TABLE v AS SELECT value AS a FROM generate_series(1, 10000);
SELECT k, c FROM (SELECT random() < 0.5 AS k, count(*) AS c FROM v GROUP BY random() < 0.5) WHERE k;
```
```
+-------+------+
| k | c |
+-------+------+
| false | 2525 | <-- does not satisfy `WHERE k`
| true | 2494 |
+-------+------+
```
`PushDownFilter` moves the predicate below the aggregate and replaces
`k` with `random() < 0.5`. The filter then calculates `random()` again,
and it gets a different value than the one that the aggregate uses for
grouping. PostgreSQL keeps the filter above the aggregate and returns
only the `true` row.
## What changes are included in this PR?
In the `LogicalPlan::Aggregate` branch of `PushDownFilter`, a predicate
is pushed below the aggregate only if it references non-volatile group
keys. Predicates on volatile group keys stay above the aggregate. The
`Projection` branch already does the same for volatile expressions.
## What is the testing strategy for this PR?
- Unit test
`test_filter_on_volatile_group_key_not_pushed_below_aggregate`: a
non-volatile key predicate is still pushed, and the volatile key
predicate stays above. The test fails without the fix.
- `push_down_filter_regression.slt`: `EXPLAIN` plus three result checks
(`WHERE k`, `WHERE NOT k` with `GROUP BY k`, and `WHERE k OR NOT k`,
which must count all 10000 rows).
## Are there any user-facing changes?
Queries that filter on a volatile grouping key now return correct
results. Plans for non-volatile keys do not change.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
---------
Co-authored-by: Claude Opus 5 <[email protected]>
Co-authored-by: Nuno Faria <[email protected]>1 parent 3ab72e6 commit c4f5a9e
2 files changed
Lines changed: 72 additions & 1 deletion
File tree
- datafusion
- optimizer/src
- sqllogictest/test_files
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
997 | 997 | | |
998 | 998 | | |
999 | 999 | | |
1000 | | - | |
| 1000 | + | |
| 1001 | + | |
| 1002 | + | |
| 1003 | + | |
| 1004 | + | |
| 1005 | + | |
| 1006 | + | |
| 1007 | + | |
| 1008 | + | |
| 1009 | + | |
1001 | 1010 | | |
1002 | 1011 | | |
1003 | 1012 | | |
| |||
4091 | 4100 | | |
4092 | 4101 | | |
4093 | 4102 | | |
| 4103 | + | |
| 4104 | + | |
| 4105 | + | |
| 4106 | + | |
| 4107 | + | |
| 4108 | + | |
| 4109 | + | |
| 4110 | + | |
| 4111 | + | |
| 4112 | + | |
| 4113 | + | |
| 4114 | + | |
| 4115 | + | |
| 4116 | + | |
| 4117 | + | |
| 4118 | + | |
| 4119 | + | |
| 4120 | + | |
| 4121 | + | |
| 4122 | + | |
| 4123 | + | |
| 4124 | + | |
| 4125 | + | |
| 4126 | + | |
| 4127 | + | |
| 4128 | + | |
| 4129 | + | |
| 4130 | + | |
4094 | 4131 | | |
4095 | 4132 | | |
4096 | 4133 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
640 | 640 | | |
641 | 641 | | |
642 | 642 | | |
| 643 | + | |
| 644 | + | |
| 645 | + | |
| 646 | + | |
| 647 | + | |
| 648 | + | |
| 649 | + | |
| 650 | + | |
| 651 | + | |
| 652 | + | |
| 653 | + | |
| 654 | + | |
| 655 | + | |
| 656 | + | |
| 657 | + | |
| 658 | + | |
| 659 | + | |
| 660 | + | |
| 661 | + | |
| 662 | + | |
| 663 | + | |
| 664 | + | |
| 665 | + | |
| 666 | + | |
| 667 | + | |
| 668 | + | |
| 669 | + | |
| 670 | + | |
| 671 | + | |
| 672 | + | |
| 673 | + | |
| 674 | + | |
| 675 | + | |
| 676 | + | |
0 commit comments