UPDATE incorrectly permits a windowed target-table subquery
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 5h
- Merged PRs (30d)
- 108
Description
## What happened
Dolt exhibits this behavior: UPDATE incorrectly permits a target-table subquery containing a window expression.
## Environment
Dolt main commit `59fb843bf6a4b653d7c8b6d997a603b10cf279d9` (`dolt version 2.2.3`).
## How to reproduce
```sql
CREATE TABLE t(i INT PRIMARY KEY, j INT, k INT);
INSERT INTO t VALUES (1,2,3),(2,3,4);
UPDATE t
SET k = 30
WHERE i IN (SELECT FIRST_VALUE(i) OVER () FROM t WHERE j = 2);
SELECT i,j,k FROM t ORDER BY i;
```
## Expected result
MySQL 8.0.43 fresh run:
```text
ERROR 1093 (HY000) at line 3: You can't specify target table 't' for update in FROM clause
```
MySQL rejects this statement with `ERROR 1093 (HY000): You can't specify target table 't' for update in FROM clause`; the target table is read directly by the windowed subquery.
## Actual result
Dolt executes the update and returns:
```text
i j k
1 2 30
2 3 4
```
Contributor guide
No contributing guide indexed for this repository
Research direction
Start by running the SQL reproduction in the issue and comparing Dolt's result with MySQL 8.0.43. Trace the UPDATE and windowed-subquery validation entry points, then add a regression test showing that a target-table windowed subquery is rejected with the expected error behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, mysql, sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 68/100