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, 52 insertions, 0 deletions

diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test
index b0de66f7fc6..76df4502769 100644
--- a/mysql-test/t/insert_update.test
+++ b/mysql-test/t/insert_update.test
@@ -195,3 +195,55 @@ SELECT LAST_INSERT_ID();
 INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
 SELECT LAST_INSERT_ID();
 DROP TABLE t1;
+
+#
+# Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the
+#            NO_AUTO_VALUE_ON_ZERO mode.
+#
+SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
+CREATE TABLE `t1` (
+  `id` int(11) PRIMARY KEY auto_increment,
+  `f1` varchar(10) NOT NULL UNIQUE
+);
+INSERT IGNORE INTO t1 (f1) VALUES ("test1")
+	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+INSERT IGNORE INTO t1 (f1) VALUES ("test1")
+	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT LAST_INSERT_ID();
+SELECT * FROM t1;
+INSERT IGNORE INTO t1 (f1) VALUES ("test2")
+	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT * FROM t1;
+INSERT IGNORE INTO t1 (f1) VALUES ("test2")
+	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT LAST_INSERT_ID();
+SELECT * FROM t1;
+INSERT IGNORE INTO t1 (f1) VALUES ("test3")
+	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT LAST_INSERT_ID();
+SELECT * FROM t1;
+DROP TABLE t1;
+CREATE TABLE `t1` (
+  `id` int(11) PRIMARY KEY auto_increment,
+  `f1` varchar(10) NOT NULL UNIQUE
+);
+INSERT IGNORE INTO t1 (f1) VALUES ("test1")
+	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT LAST_INSERT_ID();
+SELECT * FROM t1;
+INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4")
+	ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT LAST_INSERT_ID();
+SELECT * FROM t1;
+DROP TABLE t1;
+CREATE TABLE `t1` (
+  `id` int(11) PRIMARY KEY auto_increment,
+  `f1` varchar(10) NOT NULL UNIQUE,
+  tim1 timestamp default '2003-01-01 00:00:00' on update current_timestamp
+);
+INSERT INTO t1 (f1) VALUES ("test1");
+SELECT id, f1 FROM t1;
+REPLACE INTO t1 VALUES (0,"test1",null);
+SELECT id, f1 FROM t1;
+DROP TABLE t1;
+SET SQL_MODE='';