From: Date: March 19 2007 4:24pm
Subject: bk commit into 5.0 tree (evgen:1.2477) BUG#23233
List-Archive: http://lists.mysql.com/commits/22278
X-Bug: 23233
Message-Id: <20070319152453.F0B0322D159@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-19 18:24:50+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 the LAST_INSERT_ID value is set. But the
  open_table() function doesn't initialize it. When an INSERT .. ON DUPLICATE
  query that updates the auto_increment field is issued it sets the
  table->auto_increment_field_not_null variable to TRUE. This makes each
  subsequent query treat the LAST_INSERT_ID value as already set while it isn't
  and using 0 as the LAST_INSERT_ID value.
  
  Now the open_table() function initializes the 
  table->auto_increment_field_not_null variable at the table opening.

  mysql-test/r/insert_update.result@stripped, 2007-03-19 18:23:04+03:00, evgen@stripped +51 -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-19 18:22:43+03:00, evgen@stripped +32 -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_base.cc@stripped, 2007-03-19 18:23:17+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 open_table() function initializes the 
    table->auto_increment_field_not_null variable.

# 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.370/sql/sql_base.cc	2007-03-08 20:29:58 +03:00
+++ 1.371/sql/sql_base.cc	2007-03-19 18:23:17 +03:00
@@ -1640,6 +1640,7 @@
   table->used_keys= table->s->keys_for_keyread;
   table->fulltext_searched= 0;
   table->file->ft_handler= 0;
+  table->auto_increment_field_not_null= FALSE;
   if (table->timestamp_field)
     table->timestamp_field_type= table->timestamp_field->get_auto_set_type();
   table->pos_in_table_list= table_list;

--- 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-19 18:23:04 +03:00
@@ -247,3 +247,54 @@
 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")
+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 ("test2")
+ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+2
+SELECT * FROM t1;
+id	f1
+1	test1
+2	test2
+INSERT IGNORE INTO t1 (f1) VALUES ("test2")
+ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+2
+SELECT * FROM t1;
+id	f1
+1	test1
+2	test2
+INSERT IGNORE INTO t1 (f1) VALUES ("test3")
+ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+3
+SELECT * FROM t1;
+id	f1
+1	test1
+2	test2
+3	test3
+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-19 18:22:43 +03:00
@@ -174,3 +174,35 @@
 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")
+	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 LAST_INSERT_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;
+SET SQL_MODE='';
+DROP TABLE t1;