Description:
The mutation changes a strict equality filter (t3.c1 = 3) to a more permissive range filter (t3.c1 <= 3), which logically should result in a superset of the original affected rows. However, the DBMS returns zero affected rows in the mutated version. This bug likely stems from the optimizer's handling of Common Table Expressions (CTEs) when combined with multi-table JOINs in a DML context. The change in predicate selectivity probably triggered an incorrect execution plan transition (e.g., from Index Lookup to a flawed Table Scan or incorrect CTE Inlining), leading to a complete loss of row visibility during the update process.
mysql> -- ORIGINAL
mysql> WITH cte_kul AS (
-> SELECT k AS c1, k2 AS c2
-> FROM (
-> SELECT d_fbxgmp.k AS k, COALESCE(d_fbxgmp.k, 712) AS k2
-> FROM (
-> SELECT _sub.c9 AS k, 592 AS k2, COALESCE(_sub.c9, 510) AS k3, 646 AS k4
-> FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub
-> ) d_fbxgmp
-> UNION
-> SELECT d_lfa.k AS k, d_lfa.k + 2 AS k2
-> FROM (
-> SELECT d_ujw.k AS k, d_ujw.k + 5 AS k2
-> FROM (
-> SELECT t2.c2 AS k, ABS(t2.c2) AS k2, t2.c2 + 4 AS k3, ABS(t2.c2) AS k4
-> FROM t2
-> ) d_ujw
-> UNION ALL
-> SELECT _sub.c4 AS k, 51 AS k2
-> FROM (SELECT * FROM (SELECT * FROM t1) AS _sub_inner) AS _sub
-> ) d_lfa
-> ) AS cte_sub
-> )
-> UPDATE IGNORE t3
-> INNER JOIN t1 ON t3.c2 = t1.c1
-> SET t3.c12 = 'qqxrmjulreexvmyjnkeenksvd',
-> t1.c6 = 'qmlw'
-> WHERE ((t3.c1 = 3 AND t1.c1 = 1 AND t3.c2 = t1.c1)
-> AND (t3.c5 > CASE
-> WHEN LOWER(t3.c10) <> REPEAT(t3.c10, 674) THEN t3.c12
-> END))
-> AND t3.c1 IN (SELECT c2 FROM cte_kul);
Query OK, 2 rows affected, 1 warning (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 1
mysql> -- MUTATED
mysql> WITH cte_kul AS (
-> SELECT k AS c1, k2 AS c2
-> FROM (
-> SELECT d_fbxgmp.k AS k, COALESCE(d_fbxgmp.k, 712) AS k2
-> FROM (
-> SELECT _sub.c9 AS k, 592 AS k2, COALESCE(_sub.c9, 510) AS k3, 646 AS k4
-> FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub
-> ) AS d_fbxgmp
-> UNION
-> SELECT d_lfa.k AS k, d_lfa.k + 2 AS k2
-> FROM (
-> SELECT d_ujw.k AS k, d_ujw.k + 5 AS k2
-> FROM (
-> SELECT t2.c2 AS k, ABS(t2.c2) AS k2, t2.c2 + 4 AS k3, ABS(t2.c2) AS k4
-> FROM t2
-> ) AS d_ujw
-> UNION ALL
-> SELECT _sub.c4 AS k, 51 AS k2
-> FROM (SELECT * FROM (SELECT * FROM t1) AS _sub_inner) AS _sub
-> ) AS d_lfa
-> ) AS cte_sub
-> )
-> UPDATE IGNORE t3
-> INNER JOIN t1 ON t3.c2 = t1.c1
-> SET t3.c12 = 'qqxrmjulreexvmyjnkeenksvd',
-> t1.c6 = 'qmlw'
-> WHERE ((t3.c1 <= 3 AND t1.c1 = 1 AND t3.c2 = t1.c1)
-> AND (t3.c5 > CASE
-> WHEN LOWER(t3.c10) <> REPEAT(t3.c10, 674) THEN t3.c12
-> END))
-> AND t3.c1 IN (SELECT c2 FROM cte_kul);
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 1
How to repeat:
DROP DATABASE IF EXISTS databse15_min;
CREATE DATABASE databse15_min;
USE databse15_min;
CREATE TABLE t1 (
c1 INT NOT NULL,
c4 INT NULL,
c6 VARCHAR(10) NOT NULL,
PRIMARY KEY (c1)
);
CREATE TABLE t2 (
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY (c1)
);
CREATE TABLE t3 (
c1 INT NOT NULL,
c2 INT NOT NULL,
c5 DATETIME NULL,
c9 BIGINT NULL,
c10 LONGTEXT NULL,
c12 TINYTEXT NULL,
PRIMARY KEY (c1),
FOREIGN KEY (c2) REFERENCES t1(c1)
);
INSERT INTO t1 (c1, c4, c6) VALUES
(1, 563, 'kzrqnojyqb'),
(2, NULL, 'xr'),
(3, 787, 'gprppumnj'),
(4, 515, 'ioqs'),
(5, 774, 'xhhrryya'),
(6, 830, 'jgkjbcr');
INSERT INTO t2 (c1, c2) VALUES
(1, 6),
(2, 1),
(3, 6),
(4, 6),
(5, 5),
(6, 6);
INSERT INTO t3 (c1, c2, c5, c9, c10, c12) VALUES
(1, 1, NULL, 151, 'hnpermvusqlhrbio', 'hjuyebweepqdwktkngxxxrwukdywmoxpga'),
(2, 4, '2021-03-31 03:46:20', NULL, NULL, 'cczopjpmhhldbvypyehpwyjfiogbsxcyvdrzlukefsluez'),
(3, 1, '2005-08-03 07:16:02', 187, 'pqzqwpureslinclqlszkpssnvanpcwikvkwnxjjitrvgi', 'dziksvdhzwqbhzkfhfizybiqxfoyuvshqjg'),
(4, 6, '2002-04-11 04:50:03', NULL, 'njfaeopkruqwkvjdbxvzdkhmumtoxfqecsvuuucnwgoqjykwx', 'wvpvfftqadhtbadqjghegwvpdxafqkgkgpiudvvepg'),
(5, 6, '2007-03-22 13:17:36', NULL, NULL, 'mpezwvpodoyzwqemxqipsmuwbzximmarjilfiufygdjp'),
(6, 3, '2022-02-23 11:57:14', 999, 'asxiecegyr', 'ohmykokvt');
SELECT VERSION();
START TRANSACTION;
SAVEPOINT s0;
-- ORIGINAL
WITH cte_kul AS (
SELECT k AS c1, k2 AS c2
FROM (
SELECT d_fbxgmp.k AS k, COALESCE(d_fbxgmp.k, 712) AS k2
FROM (
SELECT _sub.c9 AS k, 592 AS k2, COALESCE(_sub.c9, 510) AS k3, 646 AS k4
FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub
) d_fbxgmp
UNION
SELECT d_lfa.k AS k, d_lfa.k + 2 AS k2
FROM (
SELECT d_ujw.k AS k, d_ujw.k + 5 AS k2
FROM (
SELECT t2.c2 AS k, ABS(t2.c2) AS k2, t2.c2 + 4 AS k3, ABS(t2.c2) AS k4
FROM t2
) d_ujw
UNION ALL
SELECT _sub.c4 AS k, 51 AS k2
FROM (SELECT * FROM (SELECT * FROM t1) AS _sub_inner) AS _sub
) d_lfa
) AS cte_sub
)
UPDATE IGNORE t3
INNER JOIN t1 ON t3.c2 = t1.c1
SET t3.c12 = 'qqxrmjulreexvmyjnkeenksvd',
t1.c6 = 'qmlw'
WHERE ((t3.c1 = 3 AND t1.c1 = 1 AND t3.c2 = t1.c1)
AND (t3.c5 > CASE
WHEN LOWER(t3.c10) <> REPEAT(t3.c10, 674) THEN t3.c12
END))
AND t3.c1 IN (SELECT c2 FROM cte_kul);
SHOW WARNINGS;
SELECT 'original' AS tag, t1.c1, t1.c6, t3.c1, t3.c2, t3.c12
FROM t3 JOIN t1 ON t3.c2 = t1.c1
WHERE t3.c1 = 3;
ROLLBACK TO s0;
-- MUTATED
WITH cte_kul AS (
SELECT k AS c1, k2 AS c2
FROM (
SELECT d_fbxgmp.k AS k, COALESCE(d_fbxgmp.k, 712) AS k2
FROM (
SELECT _sub.c9 AS k, 592 AS k2, COALESCE(_sub.c9, 510) AS k3, 646 AS k4
FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub
) AS d_fbxgmp
UNION
SELECT d_lfa.k AS k, d_lfa.k + 2 AS k2
FROM (
SELECT d_ujw.k AS k, d_ujw.k + 5 AS k2
FROM (
SELECT t2.c2 AS k, ABS(t2.c2) AS k2, t2.c2 + 4 AS k3, ABS(t2.c2) AS k4
FROM t2
) AS d_ujw
UNION ALL
SELECT _sub.c4 AS k, 51 AS k2
FROM (SELECT * FROM (SELECT * FROM t1) AS _sub_inner) AS _sub
) AS d_lfa
) AS cte_sub
)
UPDATE IGNORE t3
INNER JOIN t1 ON t3.c2 = t1.c1
SET t3.c12 = 'qqxrmjulreexvmyjnkeenksvd',
t1.c6 = 'qmlw'
WHERE ((t3.c1 <= 3 AND t1.c1 = 1 AND t3.c2 = t1.c1)
AND (t3.c5 > CASE
WHEN LOWER(t3.c10) <> REPEAT(t3.c10, 674) THEN t3.c12
END))
AND t3.c1 IN (SELECT c2 FROM cte_kul);
SHOW WARNINGS;
SELECT 'mutated' AS tag, t1.c1, t1.c6, t3.c1, t3.c2, t3.c12
FROM t3 JOIN t1 ON t3.c2 = t1.c1
WHERE t3.c1 = 3;
ROLLBACK;
Description: The mutation changes a strict equality filter (t3.c1 = 3) to a more permissive range filter (t3.c1 <= 3), which logically should result in a superset of the original affected rows. However, the DBMS returns zero affected rows in the mutated version. This bug likely stems from the optimizer's handling of Common Table Expressions (CTEs) when combined with multi-table JOINs in a DML context. The change in predicate selectivity probably triggered an incorrect execution plan transition (e.g., from Index Lookup to a flawed Table Scan or incorrect CTE Inlining), leading to a complete loss of row visibility during the update process. mysql> -- ORIGINAL mysql> WITH cte_kul AS ( -> SELECT k AS c1, k2 AS c2 -> FROM ( -> SELECT d_fbxgmp.k AS k, COALESCE(d_fbxgmp.k, 712) AS k2 -> FROM ( -> SELECT _sub.c9 AS k, 592 AS k2, COALESCE(_sub.c9, 510) AS k3, 646 AS k4 -> FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub -> ) d_fbxgmp -> UNION -> SELECT d_lfa.k AS k, d_lfa.k + 2 AS k2 -> FROM ( -> SELECT d_ujw.k AS k, d_ujw.k + 5 AS k2 -> FROM ( -> SELECT t2.c2 AS k, ABS(t2.c2) AS k2, t2.c2 + 4 AS k3, ABS(t2.c2) AS k4 -> FROM t2 -> ) d_ujw -> UNION ALL -> SELECT _sub.c4 AS k, 51 AS k2 -> FROM (SELECT * FROM (SELECT * FROM t1) AS _sub_inner) AS _sub -> ) d_lfa -> ) AS cte_sub -> ) -> UPDATE IGNORE t3 -> INNER JOIN t1 ON t3.c2 = t1.c1 -> SET t3.c12 = 'qqxrmjulreexvmyjnkeenksvd', -> t1.c6 = 'qmlw' -> WHERE ((t3.c1 = 3 AND t1.c1 = 1 AND t3.c2 = t1.c1) -> AND (t3.c5 > CASE -> WHEN LOWER(t3.c10) <> REPEAT(t3.c10, 674) THEN t3.c12 -> END)) -> AND t3.c1 IN (SELECT c2 FROM cte_kul); Query OK, 2 rows affected, 1 warning (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 1 mysql> -- MUTATED mysql> WITH cte_kul AS ( -> SELECT k AS c1, k2 AS c2 -> FROM ( -> SELECT d_fbxgmp.k AS k, COALESCE(d_fbxgmp.k, 712) AS k2 -> FROM ( -> SELECT _sub.c9 AS k, 592 AS k2, COALESCE(_sub.c9, 510) AS k3, 646 AS k4 -> FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub -> ) AS d_fbxgmp -> UNION -> SELECT d_lfa.k AS k, d_lfa.k + 2 AS k2 -> FROM ( -> SELECT d_ujw.k AS k, d_ujw.k + 5 AS k2 -> FROM ( -> SELECT t2.c2 AS k, ABS(t2.c2) AS k2, t2.c2 + 4 AS k3, ABS(t2.c2) AS k4 -> FROM t2 -> ) AS d_ujw -> UNION ALL -> SELECT _sub.c4 AS k, 51 AS k2 -> FROM (SELECT * FROM (SELECT * FROM t1) AS _sub_inner) AS _sub -> ) AS d_lfa -> ) AS cte_sub -> ) -> UPDATE IGNORE t3 -> INNER JOIN t1 ON t3.c2 = t1.c1 -> SET t3.c12 = 'qqxrmjulreexvmyjnkeenksvd', -> t1.c6 = 'qmlw' -> WHERE ((t3.c1 <= 3 AND t1.c1 = 1 AND t3.c2 = t1.c1) -> AND (t3.c5 > CASE -> WHEN LOWER(t3.c10) <> REPEAT(t3.c10, 674) THEN t3.c12 -> END)) -> AND t3.c1 IN (SELECT c2 FROM cte_kul); Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 1 How to repeat: DROP DATABASE IF EXISTS databse15_min; CREATE DATABASE databse15_min; USE databse15_min; CREATE TABLE t1 ( c1 INT NOT NULL, c4 INT NULL, c6 VARCHAR(10) NOT NULL, PRIMARY KEY (c1) ); CREATE TABLE t2 ( c1 INT NOT NULL, c2 INT NOT NULL, PRIMARY KEY (c1) ); CREATE TABLE t3 ( c1 INT NOT NULL, c2 INT NOT NULL, c5 DATETIME NULL, c9 BIGINT NULL, c10 LONGTEXT NULL, c12 TINYTEXT NULL, PRIMARY KEY (c1), FOREIGN KEY (c2) REFERENCES t1(c1) ); INSERT INTO t1 (c1, c4, c6) VALUES (1, 563, 'kzrqnojyqb'), (2, NULL, 'xr'), (3, 787, 'gprppumnj'), (4, 515, 'ioqs'), (5, 774, 'xhhrryya'), (6, 830, 'jgkjbcr'); INSERT INTO t2 (c1, c2) VALUES (1, 6), (2, 1), (3, 6), (4, 6), (5, 5), (6, 6); INSERT INTO t3 (c1, c2, c5, c9, c10, c12) VALUES (1, 1, NULL, 151, 'hnpermvusqlhrbio', 'hjuyebweepqdwktkngxxxrwukdywmoxpga'), (2, 4, '2021-03-31 03:46:20', NULL, NULL, 'cczopjpmhhldbvypyehpwyjfiogbsxcyvdrzlukefsluez'), (3, 1, '2005-08-03 07:16:02', 187, 'pqzqwpureslinclqlszkpssnvanpcwikvkwnxjjitrvgi', 'dziksvdhzwqbhzkfhfizybiqxfoyuvshqjg'), (4, 6, '2002-04-11 04:50:03', NULL, 'njfaeopkruqwkvjdbxvzdkhmumtoxfqecsvuuucnwgoqjykwx', 'wvpvfftqadhtbadqjghegwvpdxafqkgkgpiudvvepg'), (5, 6, '2007-03-22 13:17:36', NULL, NULL, 'mpezwvpodoyzwqemxqipsmuwbzximmarjilfiufygdjp'), (6, 3, '2022-02-23 11:57:14', 999, 'asxiecegyr', 'ohmykokvt'); SELECT VERSION(); START TRANSACTION; SAVEPOINT s0; -- ORIGINAL WITH cte_kul AS ( SELECT k AS c1, k2 AS c2 FROM ( SELECT d_fbxgmp.k AS k, COALESCE(d_fbxgmp.k, 712) AS k2 FROM ( SELECT _sub.c9 AS k, 592 AS k2, COALESCE(_sub.c9, 510) AS k3, 646 AS k4 FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub ) d_fbxgmp UNION SELECT d_lfa.k AS k, d_lfa.k + 2 AS k2 FROM ( SELECT d_ujw.k AS k, d_ujw.k + 5 AS k2 FROM ( SELECT t2.c2 AS k, ABS(t2.c2) AS k2, t2.c2 + 4 AS k3, ABS(t2.c2) AS k4 FROM t2 ) d_ujw UNION ALL SELECT _sub.c4 AS k, 51 AS k2 FROM (SELECT * FROM (SELECT * FROM t1) AS _sub_inner) AS _sub ) d_lfa ) AS cte_sub ) UPDATE IGNORE t3 INNER JOIN t1 ON t3.c2 = t1.c1 SET t3.c12 = 'qqxrmjulreexvmyjnkeenksvd', t1.c6 = 'qmlw' WHERE ((t3.c1 = 3 AND t1.c1 = 1 AND t3.c2 = t1.c1) AND (t3.c5 > CASE WHEN LOWER(t3.c10) <> REPEAT(t3.c10, 674) THEN t3.c12 END)) AND t3.c1 IN (SELECT c2 FROM cte_kul); SHOW WARNINGS; SELECT 'original' AS tag, t1.c1, t1.c6, t3.c1, t3.c2, t3.c12 FROM t3 JOIN t1 ON t3.c2 = t1.c1 WHERE t3.c1 = 3; ROLLBACK TO s0; -- MUTATED WITH cte_kul AS ( SELECT k AS c1, k2 AS c2 FROM ( SELECT d_fbxgmp.k AS k, COALESCE(d_fbxgmp.k, 712) AS k2 FROM ( SELECT _sub.c9 AS k, 592 AS k2, COALESCE(_sub.c9, 510) AS k3, 646 AS k4 FROM (SELECT * FROM (SELECT * FROM t3) AS _sub_inner) AS _sub ) AS d_fbxgmp UNION SELECT d_lfa.k AS k, d_lfa.k + 2 AS k2 FROM ( SELECT d_ujw.k AS k, d_ujw.k + 5 AS k2 FROM ( SELECT t2.c2 AS k, ABS(t2.c2) AS k2, t2.c2 + 4 AS k3, ABS(t2.c2) AS k4 FROM t2 ) AS d_ujw UNION ALL SELECT _sub.c4 AS k, 51 AS k2 FROM (SELECT * FROM (SELECT * FROM t1) AS _sub_inner) AS _sub ) AS d_lfa ) AS cte_sub ) UPDATE IGNORE t3 INNER JOIN t1 ON t3.c2 = t1.c1 SET t3.c12 = 'qqxrmjulreexvmyjnkeenksvd', t1.c6 = 'qmlw' WHERE ((t3.c1 <= 3 AND t1.c1 = 1 AND t3.c2 = t1.c1) AND (t3.c5 > CASE WHEN LOWER(t3.c10) <> REPEAT(t3.c10, 674) THEN t3.c12 END)) AND t3.c1 IN (SELECT c2 FROM cte_kul); SHOW WARNINGS; SELECT 'mutated' AS tag, t1.c1, t1.c6, t3.c1, t3.c2, t3.c12 FROM t3 JOIN t1 ON t3.c2 = t1.c1 WHERE t3.c1 = 3; ROLLBACK;