bk commit into 5.0 tree (evgen:1.2477) BUG#23233

[eugene]

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-28 18:51:21+04:00, evgen@stripped +9 -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 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.

  mysql-test/r/insert_update.result@stripped, 2007-03-28 17:27:45+04:00, evgen@stripped
+78 -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-28 17:27:43+04:00, evgen@stripped
+52 -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/field_conv.cc@stripped, 2007-03-28 18:50:38+04:00, evgen@stripped +1 -1
    A comment is corrected.

  sql/handler.cc@stripped, 2007-03-28 18:50:14+04:00, evgen@stripped +1 -5
    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@stripped, 2007-03-28 18:48:14+04:00, evgen@stripped +47 -5
    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 for a case if we missed some
    auto_increment_field_not_null handling bug.
    fill_record() functions now reset the table->auto_increment_field_not_null
    variable before filling a record.

  sql/sql_insert.cc@stripped, 2007-03-28 18:45:25+04:00, evgen@stripped +2 -0
    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@stripped, 2007-03-28 18:46:39+04:00, evgen@stripped +1 -2
    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@stripped, 2007-03-28 17:32:41+04:00, evgen@stripped +2 -0
    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.

  sql/table.h@stripped, 2007-03-28 17:27:46+04:00, evgen@stripped +5 -0
    Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the 
    NO_AUTO_VALUE_ON_ZERO mode.
    A comment added.

# 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.61/sql/field_conv.cc	2007-02-08 14:34:20 +03:00
+++ 1.62/sql/field_conv.cc	2007-03-28 18:50:38 +04:00
@@ -173,7 +173,7 @@
   if (field == field->table->next_number_field)
   {
     field->table->auto_increment_field_not_null= FALSE;
-    return 0;					// field is set in handler.cc
+    return 0;				  // field is set in fill_record()
   }
   if (current_thd->count_cuted_fields == CHECK_FIELD_WARN)
   {

--- 1.228/sql/handler.cc	2007-02-21 14:04:59 +03:00
+++ 1.229/sql/handler.cc	2007-03-28 18:50:14 +04:00
@@ -1598,7 +1598,6 @@
   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 @@
     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);

--- 1.370/sql/sql_base.cc	2007-03-08 20:29:58 +03:00
+++ 1.371/sql/sql_base.cc	2007-03-28 18:48:14 +04:00
@@ -1640,6 +1640,9 @@
   table->used_keys= table->s->keys_for_keyread;
   table->fulltext_searched= 0;
   table->file->ft_handler= 0;
+  /* Catch wrong handling of the auto_increment_field_not_null. */
+  DBUG_ASSERT(!table->auto_increment_field_not_null);
+  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;
@@ -5283,27 +5286,47 @@
   List_iterator_fast<Item> f(fields),v(values);
   Item *value, *fld;
   Item_field *field;
+  TABLE *table= 0;
   DBUG_ENTER("fill_record");
 
+  /*
+    Reset the table->auto_increment_field_not_null as it is valid for
+    only one row.
+  */
+  if (fields.elements)
+  {
+    /*
+      On INSERT or UPDATE fields are checked to be from the same table,
+      thus we safely can take table from the first field.
+    */
+    field= (Item_field*)f++;
+    table= field->filed_for_view_update()->field->table;
+    table->auto_increment_field_not_null= FALSE;
+    f.rewind();
+  }
   while ((fld= f++))
   {
     if (!(field= fld->filed_for_view_update()))
     {
       my_error(ER_NONUPDATEABLE_COLUMN, MYF(0), fld->name);
-      DBUG_RETURN(TRUE);
+      goto err;
     }
     value=v++;
     Field *rfield= field->field;
-    TABLE *table= rfield->table;
+    table= rfield->table;
     if (rfield == table->next_number_field)
       table->auto_increment_field_not_null= TRUE;
     if ((value->save_in_field(rfield, 0) < 0) && !ignore_errors)
     {
       my_message(ER_UNKNOWN_ERROR, ER(ER_UNKNOWN_ERROR), MYF(0));
-      DBUG_RETURN(TRUE);
+      goto err;
     }
   }
   DBUG_RETURN(thd->net.report_error);
+err:
+  if (table)
+    table->auto_increment_field_not_null= FALSE;
+  DBUG_RETURN(TRUE);
 }
 
 
@@ -5362,19 +5385,38 @@
 {
   List_iterator_fast<Item> v(values);
   Item *value;
+  TABLE *table= 0;
   DBUG_ENTER("fill_record");
 
   Field *field;
+  /*
+    Reset the table->auto_increment_field_not_null as it is valid for
+    only one row.
+  */
+  if (*ptr)
+  {
+    /*
+      On INSERT or UPDATE fields are checked to be from the same table,
+      thus we safely can take table from the first field.
+    */
+    table= (*ptr)->table;
+    table->auto_increment_field_not_null= FALSE;
+  }
   while ((field = *ptr++))
   {
     value=v++;
-    TABLE *table= field->table;
+    table= field->table;
     if (field == table->next_number_field)
       table->auto_increment_field_not_null= TRUE;
     if (value->save_in_field(field, 0) == -1)
-      DBUG_RETURN(TRUE);
+      goto err;
   }
   DBUG_RETURN(thd->net.report_error);
+
+err:
+  if (table)
+    table->auto_increment_field_not_null= FALSE;
+  DBUG_RETURN(TRUE);
 }
 
 

--- 1.222/sql/sql_insert.cc	2007-03-15 18:33:42 +03:00
+++ 1.223/sql/sql_insert.cc	2007-03-28 18:45:25 +04:00
@@ -761,6 +761,7 @@
   table->next_number_field=0;
   thd->count_cuted_fields= CHECK_FIELD_IGNORE;
   thd->next_insert_id=0;			// Reset this if wrongly used
+  table->auto_increment_field_not_null= FALSE;
   if (duplic != DUP_ERROR || ignore)
     table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
   if (duplic == DUP_REPLACE &&
@@ -2549,6 +2550,7 @@
   if (table)
   {
     table->next_number_field=0;
+    table->auto_increment_field_not_null= FALSE;
     table->file->reset();
   }
   thd->count_cuted_fields= CHECK_FIELD_IGNORE;

--- 1.109/sql/sql_load.cc	2007-03-08 20:29:58 +03:00
+++ 1.110/sql/sql_load.cc	2007-03-28 18:46:39 +04:00
@@ -493,6 +493,7 @@
     mysql_unlock_tables(thd, thd->lock);
     thd->lock=0;
   }
+  table->auto_increment_field_not_null= FALSE;
   thd->abort_on_warning= 0;
   DBUG_RETURN(error);
 }
@@ -589,8 +590,6 @@
       {
 	uint length;
 	byte save_chr;
-        if (field == table->next_number_field)
-          table->auto_increment_field_not_null= TRUE;
 	if ((length=(uint) (read_info.row_end-pos)) >
 	    field->field_length)
 	  length=field->field_length;

--- 1.333/sql/sql_table.cc	2007-02-21 14:05:03 +03:00
+++ 1.334/sql/sql_table.cc	2007-03-28 17:32:41 +04:00
@@ -4064,6 +4064,7 @@
 	   error != HA_ERR_FOUND_DUPP_UNIQUE))
       {
 	to->file->print_error(error,MYF(0));
+        to->auto_increment_field_not_null= FALSE;
 	break;
       }
       to->file->restore_auto_increment();
@@ -4071,6 +4072,7 @@
     }
     else
       found_count++;
+    to->auto_increment_field_not_null= FALSE;
   }
   end_read_record(&info);
   free_io_cache(from);

--- 1.139/sql/table.h	2007-03-06 21:19:14 +03:00
+++ 1.140/sql/table.h	2007-03-28 17:27:46 +04:00
@@ -273,6 +273,11 @@
   my_bool no_cache;
   /* To signal that we should reset query_id for tables and cols */
   my_bool clear_query_id;
+  /*
+    To indicate that a non-null value of the auto_increment field
+    was provided by the user or retrieved from the current record.
+    Used only in the MODE_NO_AUTO_VALUE_ON_ZERO mode.
+  */
   my_bool auto_increment_field_not_null;
   my_bool insert_or_update;             /* Can be used by the handler */
   my_bool alias_name_used;		/* true if table_name is alias */

--- 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-28 17:27:45 +04:00
@@ -247,3 +247,81 @@
 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);
+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 * 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
+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();
+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
+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;
+id	f1
+1	test1
+REPLACE INTO t1 VALUES (0,"test1",null);
+SELECT id, f1 FROM t1;
+id	f1
+0	test1
+DROP TABLE t1;
+SET SQL_MODE='';

--- 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-28 17:27:43 +04:00
@@ -174,3 +174,55 @@
 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='';