Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8.0, 10.6, 10.11, 11.4, 11.8, 10.5(EOL)
-
None
-
git rev-parse HEAD
11a6c1b30a12c448ddfe05e1b818a6a228e90e43
Description
Description:
When executing an UPDATE or DELETE statement using a WHERE condition that includes a subquery with a window function, the number of affected rows is inconsistent with the number of rows returned by SELECT WHERE. Specifically:
SELECT WHERE returns two rows.
UPDATE WHERE modifies only one row.
DELETE WHERE removes only one row.
Steps to Reproduce:
Step 1: Create Tables and Insert Data
CREATE TABLE `t1` (
|
`c1` int |
);
|
|
|
INSERT INTO `t1` VALUES (1),(2); |
|
|
CREATE TABLE `t2` (
|
`c2` int |
);
|
|
|
INSERT INTO `t2` VALUES (1),(2); |
Step 2: Execute SELECT WHERE Query
select
|
*
|
from t1
|
where (((((-45) in (select |
DENSE_RANK() over w0 as c_0
|
from
|
t2 as ref_1
|
window w0 as (partition by false order by t1.c1 asc, ref_1.c2 asc) |
))) is null)) |
Output:
+------+
|
| c1 |
|
+------+
|
| 1 | |
| 2 | |
+------+
|
2 rows in set (0.00 sec) |
Step 3: Execute UPDATE WHERE Query
update t1 set
|
c1 = 3 |
where (((((-45) in (select |
DENSE_RANK() over w0 as c_0
|
from
|
t2 as ref_1
|
window w0 as (partition by false order by t1.c1 asc, ref_1.c2 asc) |
))) is null)) |
Output:
Query OK, 1 row affected (0.01 sec) |
Rows matched: 1 Changed: 1 Warnings: 0 |
Check t1 after update:
select * from t1;
|
Output:
+------+
|
| c1 |
|
+------+
|
| 3 | |
| 2 | |
+------+
|
2 rows in set (0.00 sec) |
Step 4: Rollback update operation and Execute DELETE WHERE Query
delete from t1
|
where (((((-45) in (select |
DENSE_RANK() over w0 as c_0
|
from
|
t2 as ref_1
|
window w0 as (partition by false order by t1.c1 asc, ref_1.c2 asc) |
))) is null)) |
Query OK, 1 row affected (0.01 sec) |
Output:
Query OK, 1 row affected (0.01 sec) |
Check t1 after delete:
select * from t1;
|
Output:
+------+
|
| c1 |
|
+------+
|
| 2 | |
+------+
|
1 row in set (0.00 sec) |
Expected Behavior:
SELECT WHERE, UPDATE WHERE, and DELETE WHERE should operate on the same set of rows.
If SELECT WHERE returns two rows, then UPDATE WHERE should modify both, and DELETE WHERE should remove both.
Actual Behavior:
SELECT WHERE correctly returns two rows.
UPDATE WHERE modifies only one row.
DELETE WHERE removes only one row.
The number of affected rows is inconsistent across different SQL operations.