An aggregate window function inside a view breaks correlated quantified subqueries (`< ANY` / `= ALL` / `IN (SELECT …)`)
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
When a relation is a **view whose body computes an aggregate window function**
(`MAX(col) OVER (PARTITION BY k)`), a query that outer-joins that relation to itself and filters with a
**correlated quantified comparison subquery** returns wrong results. The correlated predicate inside
the subquery stops constraining it, which might be unexpected
### 1. Minimal reproduce step (Required)
```sql
CREATE TABLE k (id BIGINT, name VARCHAR(255), created_at VARCHAR(255), eq_key_1 BIGINT);
INSERT INTO k (id, name, created_at, eq_key_1) VALUES (1, 'a', 'a', 1);
INSERT INTO k (id, name, created_at, eq_key_1) VALUES (NULL, 'b', 'b', 2);
CREATE VIEW t AS SELECT id, name, created_at FROM (
SELECT eq_key_1,
MAX(id) OVER (PARTITION BY eq_key_1) AS id,
MAX(name) OVER (PARTITION BY eq_key_1) AS name,
MAX(created_at) OVER (PARTITION BY eq_key_1) AS created_at
FROM k) AS w;
SELECT t2.id
FROM t AS t2 RIGHT OUTER JOIN t AS t3 ON t2.id >= t3.id
WHERE (t2.id IS NULL) < ANY (SELECT 1 FROM t AS t4 WHERE t3.id IS NULL)
GROUP BY t2.id;
-- Expected: 0 rows. Actual: 1 row, (1).
```
### 2. What did you expect to see? (Required)
0 rows (MySQL also returns 0 rows)
### 3. What did you see instead (Required)
1 row, (1).
### 4. What is your TiDB version? (Required)
tidb `8.0.11-TiDB-v9.0.0-beta.2.pre-2051-g3bea8196a5` @ `3bea8196`
Contributor guide
Research direction
Start by running the provided CREATE TABLE, CREATE VIEW, and SELECT statements on the reported TiDB version, then compare the result with MySQL or the expected zero-row result. Trace the handling of correlated quantified subqueries when the referenced view contains aggregate window functions; done means the query returns zero rows without regressing related subquery and window-function cases.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 50/100