Description:
In this case:
t2.c2 = SIN(888)
should match no rows, because SIN(888) is approximately 0.8775464294662774 while all values in t2.c2 are integers 1 or 2.
However, MySQL incorrectly returns one row when using the indexed predicate, which then causes an EXISTS condition to become true and updates all rows of another table.
Since:
SIN(888) = 0.8775464294662774
and all values in t2.c2 are 1 or 2:
t2.c2 = SIN(888) should match no rows
t2.c2 <= SIN(888) should also match no rows
How to repeat:
DROP DATABASE IF EXISTS repro120;
CREATE DATABASE repro120;
USE repro120;
CREATE TABLE t1 (
c1 INT PRIMARY KEY,
c5 DATE
);
CREATE TABLE t2 (
c1 INT PRIMARY KEY,
c2 INT,
KEY c2 (c2)
);
INSERT INTO t1 VALUES
(1, '2020-01-01'),
(2, '2020-01-01'),
(3, '2020-01-01'),
(4, '2020-01-01');
INSERT INTO t2 VALUES
(1, 1),
(2, 2),
(3, 2),
(4, 2);
UPDATE LOW_PRIORITY IGNORE t1
SET t1.c5 = DATE_ADD(t1.c5, INTERVAL 2 DAY)
WHERE EXISTS (
SELECT t2.c1
FROM t2
WHERE t2.c2 = SIN(888)
);
SELECT * FROM t1 ORDER BY c1;
DROP DATABASE IF EXISTS repro120;
CREATE DATABASE repro120;
USE repro120;
CREATE TABLE t1 (
c1 INT PRIMARY KEY,
c5 DATE
);
CREATE TABLE t2 (
c1 INT PRIMARY KEY,
c2 INT,
KEY c2 (c2)
);
INSERT INTO t1 VALUES
(1, '2020-01-01'),
(2, '2020-01-01'),
(3, '2020-01-01'),
(4, '2020-01-01');
INSERT INTO t2 VALUES
(1, 1),
(2, 2),
(3, 2),
(4, 2);
UPDATE LOW_PRIORITY IGNORE t1
SET t1.c5 = DATE_ADD(t1.c5, INTERVAL 2 DAY)
WHERE EXISTS (
SELECT t2.c1
FROM t2
WHERE t2.c2 <= SIN(888)
);
SELECT * FROM t1 ORDER BY c1;
