Details
Major Description
UPDATE statements using NOT IN with a set-operation subquery can affect different rows when the inner set operator is changed from INTERSECT to UNION.
In the repro:
ORIGINAL uses INTERSECT
MUTATED uses UNION
both statements are identical except for that one set operator
Observed result on MariaDB:
ORIGINAL updates 1 row
MUTATED updates 0 rows
-- MariaDB direction-preserving repro for round-002 "over" mismatch.
|
-- This preserves the original bug direction: ORIGINAL affects more rows than MUTATED.
|
-- Original and mutated differ only by INTERSECT -> UNION.
|
|
|
DROP DATABASE IF EXISTS tmp_round002_mariadb_over_min; |
CREATE DATABASE tmp_round002_mariadb_over_min; |
USE tmp_round002_mariadb_over_min; |
|
|
CREATE TABLE t1 ( |
c1 INT PRIMARY KEY, |
c4 INT NULL |
);
|
|
|
CREATE TABLE t2 ( |
c1 INT PRIMARY KEY, |
c2 INT NULL, |
c14 INT NULL |
);
|
|
|
CREATE TABLE t1_seed ( |
c1 INT PRIMARY KEY, |
c4 INT NULL |
);
|
|
|
INSERT INTO t1_seed VALUES (1, 1); |
INSERT INTO t1 SELECT * FROM t1_seed; |
INSERT INTO t2 VALUES (1, 0, NULL); |
|
|
-- ORIGINAL: affects row c1 = 1
|
SELECT 'orig_set' AS variant, q.k |
FROM ( |
SELECT c2 AS k |
FROM t2 |
EXCEPT |
SELECT d.k |
FROM ( |
SELECT c4 AS k, 1 AS tag |
FROM t1 |
INTERSECT |
SELECT c14 AS k, 2 AS tag |
FROM t2 |
) AS d |
) AS q; |
|
|
UPDATE t1 |
SET c4 = 0 |
WHERE NOT c4 IN ( |
SELECT c2 |
FROM t2 |
EXCEPT |
SELECT d.k |
FROM ( |
SELECT c4 AS k, 1 AS tag |
FROM t1 |
INTERSECT |
SELECT c14 AS k, 2 AS tag |
FROM t2 |
) AS d |
);
|
|
|
SELECT 'orig_row_count' AS variant, ROW_COUNT() AS changed_rows; |
SELECT 'orig_changed' AS variant, t1.c1, t1.c4 |
FROM t1 |
JOIN t1_seed USING (c1) |
WHERE NOT (t1.c4 <=> t1_seed.c4) |
ORDER BY t1.c1; |
SELECT 'orig_final' AS variant, c1, c4 FROM t1 ORDER BY c1; |
|
|
TRUNCATE t1; |
INSERT INTO t1 SELECT * FROM t1_seed; |
|
|
-- MUTATED: affects no rows
|
SELECT 'mut_set' AS variant, q.k |
FROM ( |
SELECT c2 AS k |
FROM t2 |
EXCEPT |
SELECT d.k |
FROM ( |
SELECT c4 AS k, 1 AS tag |
FROM t1 |
UNION |
SELECT c14 AS k, 2 AS tag |
FROM t2 |
) AS d |
) AS q; |
|
|
UPDATE t1 |
SET c4 = 0 |
WHERE NOT c4 IN ( |
SELECT c2 |
FROM t2 |
EXCEPT |
SELECT d.k |
FROM ( |
SELECT c4 AS k, 1 AS tag |
FROM t1 |
UNION |
SELECT c14 AS k, 2 AS tag |
FROM t2 |
) AS d |
);
|
|
|
SELECT 'mut_row_count' AS variant, ROW_COUNT() AS changed_rows; |
SELECT 'mut_changed' AS variant, t1.c1, t1.c4 |
FROM t1 |
JOIN t1_seed USING (c1) |
WHERE NOT (t1.c4 <=> t1_seed.c4) |
ORDER BY t1.c1; |
SELECT 'mut_final' AS variant, c1, c4 FROM t1 ORDER BY c1; |
|
|
-- Expected on MariaDB:
|
-- orig_set -> (0)
|
-- orig_row_count -> 1
|
-- orig_changed -> (1, 0)
|
-- mut_set -> (0)
|
-- mut_row_count -> 0
|
-- mut_changed -> empty
|
|
mysql> UPDATE t1
|
-> SET c4 = 0 |
-> WHERE NOT c4 IN (
|
-> SELECT c2
|
-> FROM t2
|
-> EXCEPT
|
-> SELECT d.k
|
-> FROM (
|
-> SELECT c4 AS k, 1 AS tag |
-> FROM t1
|
-> INTERSECT
|
-> SELECT c14 AS k, 2 AS tag |
-> FROM t2
|
-> ) AS d
|
-> );
|
Query OK, 1 row affected (0.01 sec) |
Rows matched: 1 Changed: 1 Warnings: 0 |
mysql> UPDATE t1
|
-> SET c4 = 0 |
-> WHERE NOT c4 IN (
|
-> SELECT c2
|
-> FROM t2
|
-> EXCEPT
|
-> SELECT d.k
|
-> FROM (
|
-> SELECT c4 AS k, 1 AS tag |
-> FROM t1
|
-> UNION
|
-> SELECT c14 AS k, 2 AS tag |
-> FROM t2
|
-> ) AS d
|
-> );
|
Query OK, 0 rows affected (0.00 sec) |
Rows matched: 0 Changed: 0 Warnings: 0 |