Details
Major Description
A logical inconsistency was found in the optimizer when handling nested set operations within an EXISTS clause.
Original Query (Deletes 1 row):
The subquery effectively computes (A EXCEPT B) INTERSECT (A EXCEPT B).
Mutated Query (Deletes 0 rows):
The subquery computes (A EXCEPT B) INTERSECT A.
mysql> -- ORIGINAL: 删除 1 行 |
mysql> DELETE FROM t3
|
-> WHERE EXISTS (
|
-> SELECT x.k, COALESCE(x.k, '2021-01-01 00:00:00') |
-> FROM (
|
-> SELECT t1.c5 AS k, 1 AS pad FROM t1 |
-> EXCEPT
|
-> SELECT t2.c13 AS k, 2 AS pad FROM t2 |
-> ) AS x
|
-> INTERSECT
|
-> SELECT y.k, y.k
|
-> FROM (
|
-> SELECT t1.c5 AS k, 1 AS pad FROM t1 |
-> EXCEPT
|
-> SELECT t2.c13 AS k, 2 AS pad FROM t2 |
-> ) AS y
|
-> );
|
Query OK, 1 row affected (0.01 sec) |
|
|
mysql>
|
mysql> ROLLBACK;
|
Query OK, 0 rows affected (0.00 sec) |
|
|
mysql>
|
mysql> START TRANSACTION;
|
Query OK, 0 rows affected (0.00 sec) |
|
|
mysql>
|
mysql> -- MUTATED: 删除 0 行 |
mysql> DELETE FROM t3
|
-> WHERE EXISTS (
|
-> SELECT x.k, COALESCE(x.k, '2021-01-01 00:00:00') |
-> FROM (
|
-> SELECT t1.c5 AS k, 1 AS pad FROM t1 |
-> EXCEPT
|
-> SELECT t2.c13 AS k, 2 AS pad FROM t2 |
-> ) AS x
|
-> INTERSECT
|
-> SELECT y.k, y.k
|
-> FROM (
|
-> SELECT t1.c5 AS k, 1 AS pad FROM t1 |
-> ) AS y
|
-> );
|
Query OK, 0 rows affected (0.00 sec) |
How to repeat
```
DROP DATABASE IF EXISTS repro_delete; |
CREATE DATABASE repro_delete; |
USE repro_delete; |
|
|
CREATE TABLE t1 ( |
c5 DATE NOT NULL |
);
|
|
|
CREATE TABLE t2 ( |
c13 DATETIME NULL |
);
|
|
|
CREATE TABLE t3 ( |
id INT PRIMARY KEY |
);
|
|
|
INSERT INTO t1 VALUES ('2024-01-01'); |
INSERT INTO t3 VALUES (1); |
-- t2 保持空表
|
|
|
START TRANSACTION; |
|
|
-- ORIGINAL: 删除 1 行
|
DELETE FROM t3 |
WHERE EXISTS ( |
SELECT x.k, COALESCE(x.k, '2021-01-01 00:00:00') |
FROM ( |
SELECT t1.c5 AS k, 1 AS pad FROM t1 |
EXCEPT |
SELECT t2.c13 AS k, 2 AS pad FROM t2 |
) AS x |
INTERSECT |
SELECT y.k, y.k |
FROM ( |
SELECT t1.c5 AS k, 1 AS pad FROM t1 |
EXCEPT |
SELECT t2.c13 AS k, 2 AS pad FROM t2 |
) AS y |
);
|
|
|
ROLLBACK; |
|
|
START TRANSACTION; |
|
|
-- MUTATED: 删除 0 行
|
DELETE FROM t3 |
WHERE EXISTS ( |
SELECT x.k, COALESCE(x.k, '2021-01-01 00:00:00') |
FROM ( |
SELECT t1.c5 AS k, 1 AS pad FROM t1 |
EXCEPT |
SELECT t2.c13 AS k, 2 AS pad FROM t2 |
) AS x |
INTERSECT |
SELECT y.k, y.k |
FROM ( |
SELECT t1.c5 AS k, 1 AS pad FROM t1 |
) AS y |
);
|
|
|
ROLLBACK; |
```