CalvinKirs opened a new pull request, #64764:
URL: https://github.com/apache/doris/pull/64764
### What problem does this PR solve?
When a single `LogicalWindow` holds multiple window functions that have the
**same order key** but **different partition keys**, e.g.
```sql
row_number() over (partition by g1 order by ord_key) as rn1,
row_number() over (partition by g2 order by ord_key) as rn2
```
a filter like `rn1 <= 1` was converted into a `partitionTopN` and pushed
**below the whole window node**. The generated `partitionTopN` (partition by
`g1`) prunes the input rows that are **shared by all co-located window
functions**, so `rn2` (partition by `g2`) was then computed over the pruned
rows and produced a wrong result.
`LogicalWindow.getPushDownWindowFuncAndLimit()` already required the **order
keys** of all co-located window functions to be compatible (#56622), but it
never checked the **partition keys**. This PR additionally requires the
partition keys to match the chosen window function; otherwise the
partition-topn optimization is disabled.
### Reproduce
```sql
CREATE TABLE multi_window_cases (
id INT,
g1 VARCHAR(8),
g2 VARCHAR(8),
ord_key INT,
amt INT
)
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id) BUCKETS 1
PROPERTIES ("replication_num" = "1");
INSERT INTO multi_window_cases VALUES
(1,'A','X',1,10),(2,'A','X',2,20),(3,'A','Y',3,30),(4,'B','X',4,40),
(5,'B','Y',5,50),(6,'B','Y',6,60),(7,'C','X',7,70),(8,'C','Z',8,80);
SELECT id, g1, g2, ord_key, rn1, rn2
FROM (
SELECT id, g1, g2, ord_key,
row_number() OVER (PARTITION BY g1 ORDER BY ord_key) AS rn1,
row_number() OVER (PARTITION BY g2 ORDER BY ord_key) AS rn2
FROM multi_window_cases
) q
WHERE rn1 <= 1
ORDER BY id;
```
Wrong result (before), `rn2` should be `1,3,4`:
```
+----+----+----+---------+-----+-----+
| id | g1 | g2 | ord_key | rn1 | rn2 |
+----+----+----+---------+-----+-----+
| 1 | A | X | 1 | 1 | 1 |
| 4 | B | X | 4 | 1 | 2 | <- wrong (should be 3)
| 7 | C | X | 7 | 1 | 3 | <- wrong (should be 4)
+----+----+----+---------+-----+-----+
```
Correct result (after, matches MySQL 8.4):
```
+------+------+------+---------+-----+-----+
| id | g1 | g2 | ord_key | rn1 | rn2 |
+------+------+------+---------+-----+-----+
| 1 | A | X | 1 | 1 | 1 |
| 4 | B | X | 4 | 1 | 3 |
| 7 | C | X | 7 | 1 | 4 |
+------+------+------+---------+-----+-----+
```
### EXPLAIN before (buggy)
A `VPartitionTopN(partition by g1)` is inserted **below both analytic
nodes**, so it prunes rows before `rn2` is computed:
```
8:VSORT order by: id
7:VANALYTIC partition by: g2, order by: ord_key <- computes rn2
| predicates: (rn1 <= 1)
6:VSORT order by: g2, ord_key
4:VANALYTIC partition by: g1, order by: ord_key <- computes rn1
3:VSORT order by: g1, ord_key
1:VPartitionTopN partition by: g1, order by: ord_key <- prunes
input (WRONG)
| partition topn phase: TWO_PHASE_LOCAL_PTOPN
0:VOlapScanNode
```
### EXPLAIN after (fixed)
No `VPartitionTopN`; both window functions are computed over the full input
and `rn1 <= 1` stays as an ordinary predicate above them:
```
7:VSORT order by: id
6:VANALYTIC partition by: g2, order by: ord_key <- computes
rn2 (full input)
| predicates: (rn1 <= 1)
5:VSORT order by: g2, ord_key
3:VANALYTIC partition by: g1, order by: ord_key <- computes rn1
2:VSORT order by: g1, ord_key
0:VOlapScanNode
```
The optimization still applies for the single-window case and for multiple
window functions that share the same partition key.
### Release note
Fix wrong result of multiple window functions
(`row_number`/`rank`/`dense_rank`) with different partition keys when a top-n
filter (e.g. `rn <= k`) is applied.
### Check List (For author)
- [x] Test
- [x] Regression test
(`regression-test/suites/query_p0/partition_topn/check_partitionkey.groovy`)
- [x] Unit test
(`GeneratePartitionTopnFromWindowTest#testMultipleWindowsWithDifferentPartitions`)
- [x] Behavior changed:
- [x] Function behavior changed (returns correct results for the case
above)
--
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]
