Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the

NO_AUTO_VALUE_ON_ZERO mode.

In the NO_AUTO_VALUE_ON_ZERO mode the table->auto_increment_field_not_null
variable is used to indicate that a non-NULL value was specified by the user
for an auto_increment column. When an INSERT .. ON DUPLICATE updates the
auto_increment field this variable is set to true and stays unchanged for the
next insert operation. This makes the next inserted row sometimes wrongly have
0 as the value of the auto_increment field.

Now the fill_record() function resets the table->auto_increment_field_not_null
variable before filling the record.
The table->auto_increment_field_not_null variable is also reset by the
open_table() function for a case if we missed some auto_increment_field_not_null
handling bug.
Now the table->auto_increment_field_not_null is reset at the end of the
mysql_load() function.

Reset the table->auto_increment_field_not_null variable after each
write_row() call in the copy_data_between_tables() function.




sql/field_conv.cc:
  Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the 
  NO_AUTO_VALUE_ON_ZERO mode.
  A comment is corrected.
sql/handler.cc:
  Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the 
  NO_AUTO_VALUE_ON_ZERO mode.
  Now the handler::update_auto_increment() function doesn't reset the
  table->auto_increment_field_not_null variable as it is done in the
  fill_record() function.
sql/sql_base.cc:
  Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the 
  NO_AUTO_VALUE_ON_ZERO mode.
  Now the fill_record() function resets the table->auto_increment_field_not_null
  variable before filling the record.
  The table->auto_increment_field_not_null variable is also reset by the
  open_table() function for a case if we missed some auto_increment_field_not_null
  handling bug.
sql/sql_insert.cc:
  Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the
  NO_AUTO_VALUE_ON_ZERO mode.
  Now the the table->auto_increment_field_not_null is reset at the end of the
  mysql_insert() an in the select_insert class destructor.
sql/sql_load.cc:
  Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the 
  NO_AUTO_VALUE_ON_ZERO mode.
  Now the table->auto_increment_field_not_null is reset at the end of the
  mysql_load() function.
sql/sql_table.cc:
  Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the
  NO_AUTO_VALUE_ON_ZERO mode.
  Reset the table->auto_increment_field_not_null variable after each
  write_row() call in the copy_data_between_tables() function.
sql/table.h:
  Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the
  NO_AUTO_VALUE_ON_ZERO mode.
  A comment added.
mysql-test/r/insert_update.result:
  Added the test case for the bug#23233: 0 as LAST_INSERT_ID() after
  INSERT .. ON DUPLICATE in the NO_AUTO_VALUE_ON_ZERO mode.
mysql-test/t/insert_update.test:
  Added the test case for the bug#23233: 0 as LAST_INSERT_ID() after
  INSERT .. ON DUPLICATE in the NO_AUTO_VALUE_ON_ZERO mode.

1 files changed, 1 insertions, 5 deletions

diff --git a/sql/handler.cc b/sql/handler.cc
index 524f47209dc..6cba079e736 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -1598,7 +1598,6 @@ int handler::update_auto_increment()
   ulonglong nr;
   THD *thd= table->in_use;
   struct system_variables *variables= &thd->variables;
-  bool auto_increment_field_not_null;
   DBUG_ENTER("handler::update_auto_increment");
 
   /*
@@ -1606,14 +1605,11 @@ int handler::update_auto_increment()
     row was not inserted
   */
   thd->prev_insert_id= thd->next_insert_id;
-  auto_increment_field_not_null= table->auto_increment_field_not_null;
-  table->auto_increment_field_not_null= FALSE;
 
   if ((nr= table->next_number_field->val_int()) != 0 ||
-      auto_increment_field_not_null &&
+      table->auto_increment_field_not_null &&
       thd->variables.sql_mode & MODE_NO_AUTO_VALUE_ON_ZERO)
   {
-    /* Clear flag for next row */
     /* Mark that we didn't generate a new value **/
     auto_increment_column_changed=0;
     adjust_next_insert_id_after_explicit_value(nr);