Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.7.2
-
None
-
Ubuntu 22.04
Description
When I use the keyword AUTO_INCREMENT, I find a issue.
As in the following case, when reading via the Limit keyword, the result here is an exception that violates the serializable isolation level. This result is not consistent with any kind of serializable execution.
--- I see
|
DROP TABLE t1, t2; |
CREATE TABLE t1 (c0 INT); |
CREATE TABLE t2 (c0 INT PRIMARY KEY AUTO_INCREMENT, c1 INT); |
INSERT INTO t1(c0) VALUES (1),(2),(3); |
/* init */SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
/* init */SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
/* T1 */BEGIN; |
/* T2 */BEGIN; |
/* T1 */INSERT INTO t2(c1) VALUES(1); |
/* T2 */INSERT INTO t2(c1) VALUES(2), (3); |
/* T2 */COMMIT; |
/* T1 */UPDATE t1 SET c0 = 10 WHERE c0 in (SELECT c1 FROM (SELECT * FROM t2 ORDER BY c0 LIMIT 2) AS t); |
/* T1 */COMMIT; |
/* Finally database state */
|
mysql> SELECT * FROM t1; |
+------+ |
| c0 |
|
+------+ |
| 10 |
|
| 10 |
|
| 3 |
|
+------+ |
3 rows in set (0.00 sec) |
|
|
--- Expected to See
|
/* Finally database state */
|
mysql> SELECT * FROM t1; |
+------+ |
| c0 |
|
+------+ |
| 10 |
|
| 2 |
|
| 3 |
|
+------+ |
3 rows in set (0.00 sec) |
OR
|
mysql> SELECT * FROM t1; |
+------+ |
| c0 |
|
+------+ |
| 1 |
|
| 10 |
|
| 10 |
|
+------+ |
3 rows in set (0.00 sec) |