From: Date: March 21 2007 8:22pm
Subject: bk commit into 5.0 tree (evgen:1.2477) BUG#23233
List-Archive: http://lists.mysql.com/commits/22529
X-Bug: 23233
Message-Id: <20070321192214.EE53E22DFC9@moonbone.moonbone.local>

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-03-21 22:22:12+03:00, evgen@stripped +3 -0
  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 to wrongly have 0 
  as the value of the auto_increment field.
  
  Now the write_record() function resets the table->auto_increment_field_not_null
  variable after inserting or updating a row.

  mysql-test/r/insert_update.result@stripped, 2007-03-21 22:21:35+03:00, evgen@stripped +24 -0
    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@stripped, 2007-03-21 22:21:34+03:00, evgen@stripped +21 -0
    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.

  sql/sql_insert.cc@stripped, 2007-03-21 22:21:42+03:00, evgen@stripped +1 -0
    Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the 
    NO_AUTO_VALUE_ON_ZERO mode.
    Now the write_record() function resets the table->auto_increment_field_not_null
    variable after inserting or updating a row.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	evgen
# Host:	moonbone.local
# Root:	/mnt/gentoo64/work/23233-bug-5.0-opt-mysql

--- 1.222/sql/sql_insert.cc	2007-03-15 18:33:42 +03:00
+++ 1.223/sql/sql_insert.cc	2007-03-21 22:21:42 +03:00
@@ -1311,6 +1311,7 @@
                                                 TRG_ACTION_AFTER, TRUE));
 
 ok_or_after_trg_err:
+  table->auto_increment_field_not_null= FALSE;
   if (key)
     my_safe_afree(key,table->s->max_unique_length,MAX_KEY_LENGTH);
   if (!table->file->has_transactions())

--- 1.22/mysql-test/r/insert_update.result	2007-03-15 18:33:46 +03:00
+++ 1.23/mysql-test/r/insert_update.result	2007-03-21 22:21:35 +03:00
@@ -247,3 +247,27 @@
 LAST_INSERT_ID()
 1
 DROP TABLE t1;
+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);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+1
+SELECT * FROM t1;
+id	f1
+1	test1
+INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4")
+ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+1
+SELECT * FROM t1;
+id	f1
+1	test1
+2	test4
+SET SQL_MODE='';
+DROP TABLE t1;

--- 1.22/mysql-test/t/insert_update.test	2007-03-15 18:34:02 +03:00
+++ 1.23/mysql-test/t/insert_update.test	2007-03-21 22:21:34 +03:00
@@ -174,3 +174,24 @@
 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);
+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;
+SET SQL_MODE='';
+DROP TABLE t1;
+