Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 11.4, 10.5(EOL)
Description
INSERT statement that inserts multiple rows can read the values it has just inserted.
create table t10 (a varchar(10), b int); |
insert into t10 values ('original', 0); |
If I try to insert into the table I'm reading from, I get an error:
MariaDB [test]> insert into t10 values ('row1', (select count(*) from t10)), ('row2', (select count(*) from t10));
|
ERROR 1093 (HY000): Table 't10' is specified twice, both as a target for 'INSERT' and as a separate source for data
|
but using a table aliases no longer produces an error (not sure why):
MariaDB [test]> insert into t10 values ('row1', (select count(*) from t10 as T1)), ('row2', (select count(*) from t10 as T2));
|
Query OK, 2 rows affected (0.001 sec)
|
Records: 2 Duplicates: 0 Warnings: 0
|
The result is:
MariaDB [test]> select * from t10;
|
+----------+------+
|
| a | b |
|
+----------+------+
|
| original | 0 |
|
| row1 | 1 |
|
| row2 | 2 |
|
+----------+------+
|
The "row2-2" is there, because we can already read row1 that we have just inserted. In other databases (except MySQL, I tried 8.4), one would see "row-1" because INSERT is executed as if it could not see the rows it has inserted.
Attachments
Issue Links
- relates to
-
-
- Closed
-