Description:
Execute the following three SQL statements under the same conditions, and the affected records are different.You can see that the first two rows retrieved by the query still exist after deletion.
SELECT * FROM t0 WHERE t0.c0 IS NOT NULL ORDER BY t0.c0 LIMIT 2;
UPDATE t0 SET t0.updated = 1 WHERE t0.c0 IS NOT NULL ORDER BY t0.c0 LIMIT 2;
DELETE FROM t0 WHERE t0.c0 IS NOT NULL ORDER BY t0.c0 LIMIT 2;
mysql> SELECT * FROM t0 WHERE t0.c0 IS NOT NULL ORDER BY t0.c0 LIMIT 2;
+------+--------------------------------------+---------+
| c0 | rowId | updated |
+------+--------------------------------------+---------+
| 0 | fdf3cdd4-b716-11f0-9784-fa163eda33b2 | 0 |
| 0 | fdf3ccdf-b716-11f0-9784-fa163eda33b2 | 0 |
+------+--------------------------------------+---------+
2 rows in set (0.00 sec)
mysql> UPDATE t0 SET t0.updated = 1 WHERE t0.c0 IS NOT NULL ORDER BY t0.c0 LIMIT 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> DELETE FROM t0 WHERE t0.c0 IS NOT NULL ORDER BY t0.c0 LIMIT 2;
Query OK, 2 rows affected (0.01 sec)
mysql> SELECT * FROM t0 WHERE t0.c0 IS NOT NULL ORDER BY t0.c0;
+------+--------------------------------------+---------+
| c0 | rowId | updated |
+------+--------------------------------------+---------+
| 0 | fdf3c85f-b716-11f0-9784-fa163eda33b2 | 0 |
| 0 | fdf3ccdf-b716-11f0-9784-fa163eda33b2 | 0 |
| 0 | fdf3cdd4-b716-11f0-9784-fa163eda33b2 | 0 |
| 0 | fdf3cec4-b716-11f0-9784-fa163eda33b2 | 0 |
| 3 | fdf3cb6c-b716-11f0-9784-fa163eda33b2 | 0 |
+------+--------------------------------------+---------+
5 rows in set (0.00 sec)
How to repeat:
DROP TABLE IF EXISTS `t0`;
CREATE TABLE `t0` (
`c0` int DEFAULT NULL,
`rowId` text,
`updated` int DEFAULT '0'
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `t0` VALUES (0,'fdf3c75a-b716-11f0-9784-fa163eda33b2',0),(0,'fdf3c7e3-b716-11f0-9784-fa163eda33b2',0),(0,'fdf3c85f-b716-11f0-9784-fa163eda33b2',0),(NULL,'fdf3c96f-b716-11f0-9784-fa163eda33b2',0),(NULL,'fdf3c9f3-b716-11f0-9784-fa163eda33b2',0),(3,'fdf3cb6c-b716-11f0-9784-fa163eda33b2',0),(0,'fdf3ccdf-b716-11f0-9784-fa163eda33b2',0),(NULL,'fdf3cd5a-b716-11f0-9784-fa163eda33b2',0),(0,'fdf3cdd4-b716-11f0-9784-fa163eda33b2',0),(0,'fdf3cec4-b716-11f0-9784-fa163eda33b2',0);
SELECT * FROM t0 WHERE t0.c0 IS NOT NULL ORDER BY t0.c0 LIMIT 2;
UPDATE t0 SET t0.updated = 1 WHERE t0.c0 IS NOT NULL ORDER BY t0.c0 LIMIT 2;
DELETE FROM t0 WHERE t0.c0 IS NOT NULL ORDER BY t0.c0 LIMIT 2;
Suggested fix:
It seems that the issue is caused by the use of LIMIT 2. The two rows returned by this query are different from the first two rows in the full result set without LIMIT.
SELECT * FROM t0 WHERE t0.c0 IS NOT NULL ORDER BY t0.c0 LIMIT 2;
SELECT * FROM t0 WHERE t0.c0 IS NOT NULL ORDER BY t0.c0;
