Description:
The original query successfully updates 1 row, but after weakening the WHERE clause by introducing an OR condition, the mutated query updates 0 rows.
Compared with BUG:120056,the difference is the case expression.
Crucially, the bug only manifests when the CASE expression is present. If the CASE-based predicate is removed, the mutated query correctly identifies the rows, suggesting an executor-level flaw in handling scalar expression evaluation during scan-method transitions
mysql> -- ORIGINAL
mysql> UPDATE IGNORE t3
-> STRAIGHT_JOIN t1 ON t3.c2 = t1.c1
-> SET t3.c15 = 0,
-> t1.c5 = '2025-05-16'
-> WHERE (t3.c1 = 2 AND t1.c1 = 1 AND t3.c2 = t1.c1)
-> AND (
-> CASE
-> WHEN NOT 98 IS NULL THEN CONCAT_WS(',', t3.c14, 'nsoo', t3.c12)
-> WHEN TAN(t3.c2) >= t3.c3 THEN REPEAT(t3.c12, 272)
-> ELSE LOWER('bome')
-> END <> DATE_ADD(t3.c5, INTERVAL 20 HOUR)
-> );
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 1
mysql> -- MUTATED
mysql> UPDATE IGNORE t3
-> STRAIGHT_JOIN t1 ON t3.c2 = t1.c1
-> SET t3.c15 = 0,
-> t1.c5 = '2025-05-16'
-> WHERE (t3.c1 = 2 AND t1.c1 = 1 OR t3.c2 = t1.c1)
-> AND (
-> CASE
-> WHEN NOT 98 IS NULL THEN CONCAT_WS(',', t3.c14, 'nsoo', t3.c12)
-> WHEN TAN(t3.c2) >= t3.c3 THEN REPEAT(t3.c12, 272)
-> ELSE LOWER('bome')
-> END <> DATE_ADD(t3.c5, INTERVAL '20' HOUR)
-> );
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 2
How to repeat:
DROP DATABASE IF EXISTS repro_round5_dml;
CREATE DATABASE repro_round5_dml;
USE repro_round5_dml;
CREATE TABLE t1 (
c1 INT PRIMARY KEY,
c5 DATE NULL
);
CREATE TABLE t3 (
c1 INT PRIMARY KEY,
c2 INT NOT NULL,
c3 INT NOT NULL,
c5 DATETIME NULL,
c12 VARCHAR(255) NULL,
c14 VARCHAR(255) NULL,
c15 TINYINT NULL,
FOREIGN KEY (c2) REFERENCES t1(c1)
);
INSERT INTO t1 VALUES
(1, '2011-04-21');
INSERT INTO t3 VALUES
(2, 1, 2, '2023-09-21 04:47:52',
'onqjlvmctbscafmmkcxognqnchufanoarpsgavdmmlsllowvkamrlu',
'z', 0),
(3, 1, 1, '2004-12-01 08:40:29',
'vhjcuvxkpenotlpqmcusnhpzabelyektjfaewfkpyhqift',
'z,y,x', 1);
START TRANSACTION;
SAVEPOINT s0;
-- ORIGINAL
UPDATE IGNORE t3
STRAIGHT_JOIN t1 ON t3.c2 = t1.c1
SET t3.c15 = 0,
t1.c5 = '2025-05-16'
WHERE (t3.c1 = 2 AND t1.c1 = 1 AND t3.c2 = t1.c1)
AND (
CASE
WHEN NOT 98 IS NULL THEN CONCAT_WS(',', t3.c14, 'nsoo', t3.c12)
WHEN TAN(t3.c2) >= t3.c3 THEN REPEAT(t3.c12, 272)
ELSE LOWER('bome')
END <> DATE_ADD(t3.c5, INTERVAL 20 HOUR)
);
SELECT 'original' AS tag, t1.c5, t3.c1, t3.c15
FROM t3
JOIN t1 ON t3.c2 = t1.c1
ORDER BY t3.c1;
ROLLBACK TO s0;
-- MUTATED
UPDATE IGNORE t3
STRAIGHT_JOIN t1 ON t3.c2 = t1.c1
SET t3.c15 = 0,
t1.c5 = '2025-05-16'
WHERE (t3.c1 = 2 AND t1.c1 = 1 OR t3.c2 = t1.c1)
AND (
CASE
WHEN NOT 98 IS NULL THEN CONCAT_WS(',', t3.c14, 'nsoo', t3.c12)
WHEN TAN(t3.c2) >= t3.c3 THEN REPEAT(t3.c12, 272)
ELSE LOWER('bome')
END <> DATE_ADD(t3.c5, INTERVAL '20' HOUR)
);
SELECT 'mutated' AS tag, t1.c5, t3.c1, t3.c15
FROM t3
JOIN t1 ON t3.c2 = t1.c1
ORDER BY t3.c1;
ROLLBACK;
Description: The original query successfully updates 1 row, but after weakening the WHERE clause by introducing an OR condition, the mutated query updates 0 rows. Compared with BUG:120056,the difference is the case expression. Crucially, the bug only manifests when the CASE expression is present. If the CASE-based predicate is removed, the mutated query correctly identifies the rows, suggesting an executor-level flaw in handling scalar expression evaluation during scan-method transitions mysql> -- ORIGINAL mysql> UPDATE IGNORE t3 -> STRAIGHT_JOIN t1 ON t3.c2 = t1.c1 -> SET t3.c15 = 0, -> t1.c5 = '2025-05-16' -> WHERE (t3.c1 = 2 AND t1.c1 = 1 AND t3.c2 = t1.c1) -> AND ( -> CASE -> WHEN NOT 98 IS NULL THEN CONCAT_WS(',', t3.c14, 'nsoo', t3.c12) -> WHEN TAN(t3.c2) >= t3.c3 THEN REPEAT(t3.c12, 272) -> ELSE LOWER('bome') -> END <> DATE_ADD(t3.c5, INTERVAL 20 HOUR) -> ); Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 2 Changed: 1 Warnings: 1 mysql> -- MUTATED mysql> UPDATE IGNORE t3 -> STRAIGHT_JOIN t1 ON t3.c2 = t1.c1 -> SET t3.c15 = 0, -> t1.c5 = '2025-05-16' -> WHERE (t3.c1 = 2 AND t1.c1 = 1 OR t3.c2 = t1.c1) -> AND ( -> CASE -> WHEN NOT 98 IS NULL THEN CONCAT_WS(',', t3.c14, 'nsoo', t3.c12) -> WHEN TAN(t3.c2) >= t3.c3 THEN REPEAT(t3.c12, 272) -> ELSE LOWER('bome') -> END <> DATE_ADD(t3.c5, INTERVAL '20' HOUR) -> ); Query OK, 0 rows affected, 2 warnings (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 2 How to repeat: DROP DATABASE IF EXISTS repro_round5_dml; CREATE DATABASE repro_round5_dml; USE repro_round5_dml; CREATE TABLE t1 ( c1 INT PRIMARY KEY, c5 DATE NULL ); CREATE TABLE t3 ( c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT NOT NULL, c5 DATETIME NULL, c12 VARCHAR(255) NULL, c14 VARCHAR(255) NULL, c15 TINYINT NULL, FOREIGN KEY (c2) REFERENCES t1(c1) ); INSERT INTO t1 VALUES (1, '2011-04-21'); INSERT INTO t3 VALUES (2, 1, 2, '2023-09-21 04:47:52', 'onqjlvmctbscafmmkcxognqnchufanoarpsgavdmmlsllowvkamrlu', 'z', 0), (3, 1, 1, '2004-12-01 08:40:29', 'vhjcuvxkpenotlpqmcusnhpzabelyektjfaewfkpyhqift', 'z,y,x', 1); START TRANSACTION; SAVEPOINT s0; -- ORIGINAL UPDATE IGNORE t3 STRAIGHT_JOIN t1 ON t3.c2 = t1.c1 SET t3.c15 = 0, t1.c5 = '2025-05-16' WHERE (t3.c1 = 2 AND t1.c1 = 1 AND t3.c2 = t1.c1) AND ( CASE WHEN NOT 98 IS NULL THEN CONCAT_WS(',', t3.c14, 'nsoo', t3.c12) WHEN TAN(t3.c2) >= t3.c3 THEN REPEAT(t3.c12, 272) ELSE LOWER('bome') END <> DATE_ADD(t3.c5, INTERVAL 20 HOUR) ); SELECT 'original' AS tag, t1.c5, t3.c1, t3.c15 FROM t3 JOIN t1 ON t3.c2 = t1.c1 ORDER BY t3.c1; ROLLBACK TO s0; -- MUTATED UPDATE IGNORE t3 STRAIGHT_JOIN t1 ON t3.c2 = t1.c1 SET t3.c15 = 0, t1.c5 = '2025-05-16' WHERE (t3.c1 = 2 AND t1.c1 = 1 OR t3.c2 = t1.c1) AND ( CASE WHEN NOT 98 IS NULL THEN CONCAT_WS(',', t3.c14, 'nsoo', t3.c12) WHEN TAN(t3.c2) >= t3.c3 THEN REPEAT(t3.c12, 272) ELSE LOWER('bome') END <> DATE_ADD(t3.c5, INTERVAL '20' HOUR) ); SELECT 'mutated' AS tag, t1.c5, t3.c1, t3.c15 FROM t3 JOIN t1 ON t3.c2 = t1.c1 ORDER BY t3.c1; ROLLBACK;