Description:
On a MySQL-compatible server reporting @@version=9.5.0 and @@version_comment='MySQL Community Server - GPL', INTERSECT behaves inconsistently when the right operand produces only NULL values. In addition, adding DISTINCT to the right operand (which should not change the operand set) changes the INTERSECT result, flipping an EXISTS(...) predicate and causing a DELETE to affect different rows.
In the repro below, both DELETE statements differ only by DISTINCT on the right side of an INTERSECT. The derived table on the right contains NULL in all projected columns because it selects from a single-row table where c5 is NULL (and DATE_ADD(NULL, ...) is NULL). The intersection should therefore be empty in both cases, so EXISTS(...) should be false and both DELETEs should delete 0 rows. Actual behavior: the original deletes 0 rows, but the mutated form deletes 1 row.
mysql> DELETE FROM t3
-> WHERE EXISTS (
-> SELECT 1
-> FROM (
-> SELECT t1.c5 AS k, '2016-06-15 20:06:55' AS k2, '2008-10-29 11:42:47' AS k3
-> FROM t1
-> INTERSECT
-> SELECT s.c5 AS k, s.c5 AS k2, DATE_ADD(s.c5, INTERVAL 16 DAY) AS k3
-> FROM (SELECT c5 FROM t3) AS s
-> ) AS d
-> );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT ROW_COUNT() AS deleted_original;
+------------------+
| deleted_original |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
mysql> DELETE FROM t3
-> WHERE EXISTS (
-> SELECT 1
-> FROM (
-> SELECT t1.c5 AS k, '2016-06-15 20:06:55' AS k2, '2008-10-29 11:42:47' AS k3
-> FROM t1
-> INTERSECT
-> SELECT DISTINCT s.c5 AS k, s.c5 AS k2, DATE_ADD(s.c5, INTERVAL 16 DAY) AS k3
-> FROM (SELECT c5 FROM t3) AS s
-> ) AS d
-> );
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> SELECT ROW_COUNT() AS deleted_mutated;
+-----------------+
| deleted_mutated |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
How to repeat:
DROP DATABASE IF EXISTS dmlscope_b001_dml;
CREATE DATABASE dmlscope_b001_dml;
USE dmlscope_b001_dml;
SELECT @@version, @@version_comment;
CREATE TABLE t1 (c5 DATE NOT NULL);
CREATE TABLE t3 (c1 INT PRIMARY KEY, c5 DATETIME NULL);
INSERT INTO t1 VALUES ('2025-04-17');
INSERT INTO t3 VALUES (1, NULL);
SET autocommit = 0;
-- ORIGINAL: deletes 0 rows
START TRANSACTION;
SELECT 'before_original' AS tag, * FROM t3 ORDER BY c1;
DELETE FROM t3
WHERE EXISTS (
SELECT 1
FROM (
SELECT t1.c5 AS k, '2016-06-15 20:06:55' AS k2, '2008-10-29 11:42:47' AS k3
FROM t1
INTERSECT
SELECT s.c5 AS k, s.c5 AS k2, DATE_ADD(s.c5, INTERVAL 16 DAY) AS k3
FROM (SELECT c5 FROM t3) AS s
) AS d
);
SELECT ROW_COUNT() AS deleted_original;
ROLLBACK;
-- MUTATED: only adds DISTINCT on the INTERSECT right operand, deletes 1 row
START TRANSACTION;
SELECT 'before_mutated' AS tag, * FROM t3 ORDER BY c1;
DELETE FROM t3
WHERE EXISTS (
SELECT 1
FROM (
SELECT t1.c5 AS k, '2016-06-15 20:06:55' AS k2, '2008-10-29 11:42:47' AS k3
FROM t1
INTERSECT
SELECT DISTINCT s.c5 AS k, s.c5 AS k2, DATE_ADD(s.c5, INTERVAL 16 DAY) AS k3
FROM (SELECT c5 FROM t3) AS s
) AS d
);
SELECT ROW_COUNT() AS deleted_mutated;
ROLLBACK;
SET autocommit = 1;
Description: On a MySQL-compatible server reporting @@version=9.5.0 and @@version_comment='MySQL Community Server - GPL', INTERSECT behaves inconsistently when the right operand produces only NULL values. In addition, adding DISTINCT to the right operand (which should not change the operand set) changes the INTERSECT result, flipping an EXISTS(...) predicate and causing a DELETE to affect different rows. In the repro below, both DELETE statements differ only by DISTINCT on the right side of an INTERSECT. The derived table on the right contains NULL in all projected columns because it selects from a single-row table where c5 is NULL (and DATE_ADD(NULL, ...) is NULL). The intersection should therefore be empty in both cases, so EXISTS(...) should be false and both DELETEs should delete 0 rows. Actual behavior: the original deletes 0 rows, but the mutated form deletes 1 row. mysql> DELETE FROM t3 -> WHERE EXISTS ( -> SELECT 1 -> FROM ( -> SELECT t1.c5 AS k, '2016-06-15 20:06:55' AS k2, '2008-10-29 11:42:47' AS k3 -> FROM t1 -> INTERSECT -> SELECT s.c5 AS k, s.c5 AS k2, DATE_ADD(s.c5, INTERVAL 16 DAY) AS k3 -> FROM (SELECT c5 FROM t3) AS s -> ) AS d -> ); Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT ROW_COUNT() AS deleted_original; +------------------+ | deleted_original | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec) mysql> DELETE FROM t3 -> WHERE EXISTS ( -> SELECT 1 -> FROM ( -> SELECT t1.c5 AS k, '2016-06-15 20:06:55' AS k2, '2008-10-29 11:42:47' AS k3 -> FROM t1 -> INTERSECT -> SELECT DISTINCT s.c5 AS k, s.c5 AS k2, DATE_ADD(s.c5, INTERVAL 16 DAY) AS k3 -> FROM (SELECT c5 FROM t3) AS s -> ) AS d -> ); Query OK, 1 row affected (0.01 sec) mysql> mysql> SELECT ROW_COUNT() AS deleted_mutated; +-----------------+ | deleted_mutated | +-----------------+ | 1 | +-----------------+ 1 row in set (0.00 sec) How to repeat: DROP DATABASE IF EXISTS dmlscope_b001_dml; CREATE DATABASE dmlscope_b001_dml; USE dmlscope_b001_dml; SELECT @@version, @@version_comment; CREATE TABLE t1 (c5 DATE NOT NULL); CREATE TABLE t3 (c1 INT PRIMARY KEY, c5 DATETIME NULL); INSERT INTO t1 VALUES ('2025-04-17'); INSERT INTO t3 VALUES (1, NULL); SET autocommit = 0; -- ORIGINAL: deletes 0 rows START TRANSACTION; SELECT 'before_original' AS tag, * FROM t3 ORDER BY c1; DELETE FROM t3 WHERE EXISTS ( SELECT 1 FROM ( SELECT t1.c5 AS k, '2016-06-15 20:06:55' AS k2, '2008-10-29 11:42:47' AS k3 FROM t1 INTERSECT SELECT s.c5 AS k, s.c5 AS k2, DATE_ADD(s.c5, INTERVAL 16 DAY) AS k3 FROM (SELECT c5 FROM t3) AS s ) AS d ); SELECT ROW_COUNT() AS deleted_original; ROLLBACK; -- MUTATED: only adds DISTINCT on the INTERSECT right operand, deletes 1 row START TRANSACTION; SELECT 'before_mutated' AS tag, * FROM t3 ORDER BY c1; DELETE FROM t3 WHERE EXISTS ( SELECT 1 FROM ( SELECT t1.c5 AS k, '2016-06-15 20:06:55' AS k2, '2008-10-29 11:42:47' AS k3 FROM t1 INTERSECT SELECT DISTINCT s.c5 AS k, s.c5 AS k2, DATE_ADD(s.c5, INTERVAL 16 DAY) AS k3 FROM (SELECT c5 FROM t3) AS s ) AS d ); SELECT ROW_COUNT() AS deleted_mutated; ROLLBACK; SET autocommit = 1;