[MDEV-16847] bulk insert: LAST_INSERT_ID() returns wrong value Created: 2018-07-30  Updated: 2018-12-13  Resolved: 2018-12-13

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert, Prepared Statements
Affects Version/s: 10.2.13
Fix Version/s: 10.2.20

Type: Bug Priority: Critical
Reporter: Georg Richter Assignee: Oleksandr Byelkin
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

This might be related to MDEV-16593:

After bulk insert the function LAST_INSERT_ID() returns wrong value.

  def test_conpy_15(self):
 
    cursor=self.connection.cursor()
 
    cursor.execute("CREATE OR REPLACE TABLE t1 (a int not null auto_increment primary key, b varchar(20))");
 
    self.assertEqual(cursor.lastrowid, 0)
 
    cursor.execute("INSERT INTO t1 VALUES (null, 'foo')")
 
    self.assertEqual(cursor.lastrowid, 1)
 
    cursor.execute("SELECT LAST_INSERT_ID()")
 
    row= cursor.fetchone()
 
    self.assertEqual(row[0], 1)
 
    vals= [(3, "bar"), (4, "this")]
 
    cursor.executemany("INSERT INTO t1 VALUES (?,?)", vals)
 
    self.assertEqual(cursor.lastrowid, 4)
 
    cursor.execute("SELECT LAST_INSERT_ID()")
 
    row= cursor.fetchone()
 
    # row[0] is 1 instead of 4 !!!
 
    self.assertEqual(row[0], 4)
 
    del cursor



 Comments   
Comment by Oleksandr Byelkin [ 2018-09-07 ]

usual test suite by Georg;

MariaDB [test]> create or replace table t1 (a int not null auto_increment primary key);
 
Query OK, 0 rows affected (0.06 sec)
 
 
 
MariaDB [test]> prepare my from "insert into t1 values (?),(?)";
 
Query OK, 0 rows affected (0.00 sec)
 
Statement prepared
 
 
 
MariaDB [test]> set @a:=NULL,@b:=NULL;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> execute my using @a,@b;
 
Query OK, 2 rows affected (0.01 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select last_insert_id();
 
+------------------+
 
| last_insert_id() |
 
+------------------+
 
|                1 |
 
+------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select * from t1;
 
+---+
 
| a |
 
+---+
 
| 1 |
 
| 2 |
 
+---+
 
2 rows in set (0.00 sec)

Comment by Oleksandr Byelkin [ 2018-12-13 ]

As example above shows it has nothing to do with bulk...

Comment by Oleksandr Byelkin [ 2018-12-13 ]

It even has nothing to do with prepare:

insert into t1 values (NULL),(NULL);
 
select last_insert_id();
 
last_insert_id()
 
1
 
select * from t1;
 
a
 
1
 
2

so the question is why it behave so strange

Comment by Oleksandr Byelkin [ 2018-12-13 ]

I think this behaviour is documented here:
https://mariadb.com/kb/en/library/last_insert_id/

The currently executing statement does not affect the value of LAST_INSERT_ID(). Suppose that you generate an AUTO_INCREMENT value with one statement, and then refer to LAST_INSERT_ID() in a multiple-row INSERT statement that inserts rows into a table with its own AUTO_INCREMENT column. The value of LAST_INSERT_ID() will remain stable in the second statement; its value for the second and later rows is not affected by the earlier row insertions. (However, if you mix references to LAST_INSERT_ID() and LAST_INSERT_ID(expr), the effect is undefined.)

Generated at Thu Feb 08 08:32:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.