Description:
The bug occurs when an INTERSECT operation is removed from a nested set-operation sequence within an EXISTS subquery. Although the mutation logically expands the potential result set of the subquery (making the WHERE clause more permissive), the DBMS returns zero affected rows, whereas the original, more restrictive query correctly identifies and deletes rows. This suggests a failure in the optimizer's ability to maintain result set monotonicity when re-evaluating join paths and semi-join strategies after a set-operator reduction.
mysql> SELECT 'original_remaining' AS tag, COUNT(*) AS cnt
-> FROM t3
-> WHERE c1 = 4;
+--------------------+-----+
| tag | cnt |
+--------------------+-----+
| original_remaining | 0 |
+--------------------+-----+
1 row in set (0.01 sec)
mysql> SELECT 'mutated_remaining' AS tag, COUNT(*) AS cnt
-> FROM t3
-> WHERE c1 = 4;
+-------------------+-----+
| tag | cnt |
+-------------------+-----+
| mutated_remaining | 1 |
+-------------------+-----+
1 row in set (0.00 sec)
How to repeat:
DROP DATABASE IF EXISTS databse3_min;
CREATE DATABASE databse3_min;
USE databse3_min;
CREATE TABLE t2 (
c1 INT PRIMARY KEY
);
CREATE TABLE t3 (
c1 INT PRIMARY KEY,
c3 INT NOT NULL,
c10 TEXT NULL,
c12 TEXT NULL,
c14 VARCHAR(32) NULL
);
INSERT INTO t2 (c1) VALUES
(5);
INSERT INTO t3 (c1, c3, c10, c12, c14) VALUES
(1, 4, NULL, 'xrvarcorerztsltsnntwpkptplvatthspfnqwhbjyvnrorrohct', NULL),
(2, 5, 'iqrmeaanyqpblqjbckgbovsuiuoockrglljcyflrxtx', NULL, 'z'),
(3, 1, 'slmvaycgdqthdlzgqav', 'zennjdkvlogdopivxavjsucefuazmfgyqbcugszqzdt', 'y,z,x'),
(4, 5, NULL, 'mtiwlbgtngcrmdazvix', 'y'),
(5, 3, 'gnuebbxrzzzszetlxzroobpdzbqitspd', 'ljhfrjymvngvclaycajmjikuowxizagcyznehwnh', 'x');
SELECT VERSION();
START TRANSACTION;
SAVEPOINT s0;
-- ORIGINAL
DELETE LOW_PRIORITY t3
FROM t3 STRAIGHT_JOIN t2 ON t3.c3 = t2.c1
WHERE t3.c1 = 4
AND t3.c3 = t2.c1
AND EXISTS (
SELECT _sub.c14 AS k, 'rmruvs' AS k2, CONCAT(_sub.c14, 'vjh') AS k3
FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub
INTERSECT
SELECT d_nhwzq.k AS k, UPPER(d_nhwzq.k) AS k2, UPPER(d_nhwzq.k) AS k3
FROM (
SELECT d_gnz.k AS k, UPPER(d_gnz.k) AS k2, 'shyjfmiy' AS k3
FROM (
SELECT _sub.c12 AS k, 'zjq' AS k2, 'vxur' AS k3
FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub
EXCEPT
SELECT _sub.c10 AS k, 'xzapcvcp' AS k2, 'dvdzkf' AS k3
FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub
) AS d_gnz
INTERSECT
SELECT d_pnva.k AS k, d_pnva.k AS k2, UPPER(d_pnva.k) AS k3
FROM (
SELECT _sub.c10 AS k, UPPER(_sub.c10) AS k2, LOWER(_sub.c10) AS k3, LOWER(_sub.c10) AS k4
FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub
UNION ALL
SELECT _sub.c14 AS k, _sub.c14 AS k2, 'oruyqmw' AS k3, COALESCE(_sub.c14, 'vnrkqj') AS k4
FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub
) AS d_pnva
) AS d_nhwzq
);
SELECT 'original_remaining' AS tag, COUNT(*) AS cnt
FROM t3
WHERE c1 = 4;
ROLLBACK TO s0;
-- MUTATED
DELETE LOW_PRIORITY t3
FROM t3 STRAIGHT_JOIN t2 ON t3.c3 = t2.c1
WHERE t3.c1 = 4
AND t3.c3 = t2.c1
AND EXISTS (
SELECT _sub.c14 AS k, 'rmruvs' AS k2, CONCAT(_sub.c14, 'vjh') AS k3
FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub
INTERSECT
SELECT d_nhwzq.k AS k, UPPER(d_nhwzq.k) AS k2, UPPER(d_nhwzq.k) AS k3
FROM (
SELECT d_gnz.k AS k, UPPER(d_gnz.k) AS k2, 'shyjfmiy' AS k3
FROM (
SELECT _sub.c12 AS k, 'zjq' AS k2, 'vxur' AS k3
FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub
EXCEPT
SELECT _sub.c10 AS k, 'xzapcvcp' AS k2, 'dvdzkf' AS k3
FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub
) AS d_gnz
) AS d_nhwzq
);
SELECT 'mutated_remaining' AS tag, COUNT(*) AS cnt
FROM t3
WHERE c1 = 4;
ROLLBACK;
Description: The bug occurs when an INTERSECT operation is removed from a nested set-operation sequence within an EXISTS subquery. Although the mutation logically expands the potential result set of the subquery (making the WHERE clause more permissive), the DBMS returns zero affected rows, whereas the original, more restrictive query correctly identifies and deletes rows. This suggests a failure in the optimizer's ability to maintain result set monotonicity when re-evaluating join paths and semi-join strategies after a set-operator reduction. mysql> SELECT 'original_remaining' AS tag, COUNT(*) AS cnt -> FROM t3 -> WHERE c1 = 4; +--------------------+-----+ | tag | cnt | +--------------------+-----+ | original_remaining | 0 | +--------------------+-----+ 1 row in set (0.01 sec) mysql> SELECT 'mutated_remaining' AS tag, COUNT(*) AS cnt -> FROM t3 -> WHERE c1 = 4; +-------------------+-----+ | tag | cnt | +-------------------+-----+ | mutated_remaining | 1 | +-------------------+-----+ 1 row in set (0.00 sec) How to repeat: DROP DATABASE IF EXISTS databse3_min; CREATE DATABASE databse3_min; USE databse3_min; CREATE TABLE t2 ( c1 INT PRIMARY KEY ); CREATE TABLE t3 ( c1 INT PRIMARY KEY, c3 INT NOT NULL, c10 TEXT NULL, c12 TEXT NULL, c14 VARCHAR(32) NULL ); INSERT INTO t2 (c1) VALUES (5); INSERT INTO t3 (c1, c3, c10, c12, c14) VALUES (1, 4, NULL, 'xrvarcorerztsltsnntwpkptplvatthspfnqwhbjyvnrorrohct', NULL), (2, 5, 'iqrmeaanyqpblqjbckgbovsuiuoockrglljcyflrxtx', NULL, 'z'), (3, 1, 'slmvaycgdqthdlzgqav', 'zennjdkvlogdopivxavjsucefuazmfgyqbcugszqzdt', 'y,z,x'), (4, 5, NULL, 'mtiwlbgtngcrmdazvix', 'y'), (5, 3, 'gnuebbxrzzzszetlxzroobpdzbqitspd', 'ljhfrjymvngvclaycajmjikuowxizagcyznehwnh', 'x'); SELECT VERSION(); START TRANSACTION; SAVEPOINT s0; -- ORIGINAL DELETE LOW_PRIORITY t3 FROM t3 STRAIGHT_JOIN t2 ON t3.c3 = t2.c1 WHERE t3.c1 = 4 AND t3.c3 = t2.c1 AND EXISTS ( SELECT _sub.c14 AS k, 'rmruvs' AS k2, CONCAT(_sub.c14, 'vjh') AS k3 FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub INTERSECT SELECT d_nhwzq.k AS k, UPPER(d_nhwzq.k) AS k2, UPPER(d_nhwzq.k) AS k3 FROM ( SELECT d_gnz.k AS k, UPPER(d_gnz.k) AS k2, 'shyjfmiy' AS k3 FROM ( SELECT _sub.c12 AS k, 'zjq' AS k2, 'vxur' AS k3 FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub EXCEPT SELECT _sub.c10 AS k, 'xzapcvcp' AS k2, 'dvdzkf' AS k3 FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub ) AS d_gnz INTERSECT SELECT d_pnva.k AS k, d_pnva.k AS k2, UPPER(d_pnva.k) AS k3 FROM ( SELECT _sub.c10 AS k, UPPER(_sub.c10) AS k2, LOWER(_sub.c10) AS k3, LOWER(_sub.c10) AS k4 FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub UNION ALL SELECT _sub.c14 AS k, _sub.c14 AS k2, 'oruyqmw' AS k3, COALESCE(_sub.c14, 'vnrkqj') AS k4 FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub ) AS d_pnva ) AS d_nhwzq ); SELECT 'original_remaining' AS tag, COUNT(*) AS cnt FROM t3 WHERE c1 = 4; ROLLBACK TO s0; -- MUTATED DELETE LOW_PRIORITY t3 FROM t3 STRAIGHT_JOIN t2 ON t3.c3 = t2.c1 WHERE t3.c1 = 4 AND t3.c3 = t2.c1 AND EXISTS ( SELECT _sub.c14 AS k, 'rmruvs' AS k2, CONCAT(_sub.c14, 'vjh') AS k3 FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub INTERSECT SELECT d_nhwzq.k AS k, UPPER(d_nhwzq.k) AS k2, UPPER(d_nhwzq.k) AS k3 FROM ( SELECT d_gnz.k AS k, UPPER(d_gnz.k) AS k2, 'shyjfmiy' AS k3 FROM ( SELECT _sub.c12 AS k, 'zjq' AS k2, 'vxur' AS k3 FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub EXCEPT SELECT _sub.c10 AS k, 'xzapcvcp' AS k2, 'dvdzkf' AS k3 FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub ) AS d_gnz ) AS d_nhwzq ); SELECT 'mutated_remaining' AS tag, COUNT(*) AS cnt FROM t3 WHERE c1 = 4; ROLLBACK;