Bug #115111 last_insert_id evaluated twice if used nested

• Submitted: 24 May 2024 7:33
• Modified: 24 May 2024 7:41
• Reporter: ximin liang (OCA)
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.4.0, 8.0.37
• OS: Any
• CPU Architecture: Any

[24 May 2024 7:33] ximin liang

Description:
According to official doc, if expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID().
But seems follow sqls generate unexpected result.

How to repeat:
Can be repeated in MySQL 8.4.0:
create table t1 (c1 int primary key auto_increment, c2 int);
insert into t1(c2) values (10); 
insert into t1(c2) values (20);
select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+

select c1, c2 from t1 where c1 < last_insert_id(last_insert_id() + 1);
select last_insert_id();

+------------------+
| last_insert_id() |
+------------------+
|                4 |
+------------------+

This is because val_int() for Item_func_last_insert_id was called during optimize stage(get_mm_tree) and execute stage (FilterIterator::Read)

[24 May 2024 7:35] ximin liang

may be 3 is correct result for final select last_insert_id();

[24 May 2024 7:41] MySQL Verification Team

Hello ximin liang,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

[7 Jun 2024 11:56] huahua xu

Hi liang,

It myebe fix the bugs:

```

diff --git a/sql/item_func.cc b/sql/item_func.cc
index ac6875c..81d5385 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -5693,9 +5693,10 @@ bool Item_func_last_insert_id::do_itemize(Parse_context *pc, Item **res) {
 longlong Item_func_last_insert_id::val_int() {
   THD *thd = current_thd;
   assert(fixed);
-  if (arg_count) {
+  if (arg_count && !const_value_cache) {
     const longlong value = args[0]->val_int();
     null_value = args[0]->null_value;
+    const_value_cache = true;
     /*
       LAST_INSERT_ID(X) must affect the client's mysql_insert_id() as
       documented in the manual. We don't want to touch
@@ -5704,7 +5705,7 @@ longlong Item_func_last_insert_id::val_int() {
       value for this row.
     */
     thd->arg_of_last_insert_id_function = true;
-    thd->first_successful_insert_id_in_prev_stmt = value;
+    thd->first_successful_insert_id_in_prev_stmt = value;
     return value;
   }
   return static_cast<longlong>(
diff --git a/sql/item_func.h b/sql/item_func.h
index e4b7626..188407a 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -2072,6 +2072,10 @@ class Item_func_bit_neg final : public Item_func_bit {
 class Item_func_last_insert_id final : public Item_int_func {
   typedef Item_int_func super;

+ protected:
+  // Tells if last_insert_id(#expression) was called and the result had been cached.
+  bool const_value_cache;
+
  public:
   Item_func_last_insert_id() : Item_int_func() {}
   explicit Item_func_last_insert_id(const POS &pos) : Item_int_func(pos) {}

```