dolthub / dolthub/dolt

UPDATE incorrectly permits a windowed target-table subquery

Open
#11,516 0 comments 0 reactions 0 assignees View on GitHub
bug correctness customer issue good repro sql
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.