MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:kgeorge Date:July 10 2006 1:27pm
Subject:bk commit into 4.1 tree (gkodinov:1.2479) BUG#14553
View as plain text  
Below is the list of changes that have just been committed into a local
4.1 repository of kgeorge. When kgeorge 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, 2006-07-10 16:27:03+03:00, gkodinov@stripped +7 -0
  BUG#14553: NULL in WHERE resets LAST_INSERT_ID
  
  To make MySQL compatible with some ODBC applications, you can find
  the AUTO_INCREMENT value for the last inserted row with the following query:
   SELECT * FROM tbl_name WHERE auto_col IS NULL.
  This is done with a special code that replaces 'auto_col IS NULL' with
  'auto_col = LAST_INSERT_ID'.
  However this also resets the LAST_INSERT_ID to 0 as it uses it for a flag
  so as to ensure that only the first SELECT ... WHERE auto_col IS NULL
  after an INSERT has this special behaviour.
  In order to avoid resetting the LAST_INSERT_ID a special flag is introduced
  in the THD class. This flag is used to restrict the second and subsequent
  SELECTs instead of LAST_INSERT_ID.

  mysql-test/r/odbc.result@stripped, 2006-07-10 16:27:01+03:00, gkodinov@stripped +11 -0
    test suite for the bug

  mysql-test/r/rpl_insert_id.result@stripped, 2006-07-10 16:27:01+03:00, gkodinov@stripped +14 -0
    test for the fix in replication

  mysql-test/t/odbc.test@stripped, 2006-07-10 16:27:01+03:00, gkodinov@stripped +10 -0
    test suite for the bug

  mysql-test/t/rpl_insert_id.test@stripped, 2006-07-10 16:27:01+03:00, gkodinov@stripped +19 -1
    test for the fix in replication

  sql/sql_class.cc@stripped, 2006-07-10 16:27:01+03:00, gkodinov@stripped +1 -0
    initialize the flag

  sql/sql_class.h@stripped, 2006-07-10 16:27:01+03:00, gkodinov@stripped +3 -0
    flag's declaration and set code when setting the last_insert_id

  sql/sql_select.cc@stripped, 2006-07-10 16:27:01+03:00, gkodinov@stripped +2 -2
    the special flag is used instead of last_insert_id

# 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:	gkodinov
# Host:	rakia.(none)
# Root:	/home/kgeorge/mysql/4.1/B14553

--- 1.205/sql/sql_class.cc	2006-07-10 16:27:08 +03:00
+++ 1.206/sql/sql_class.cc	2006-07-10 16:27:08 +03:00
@@ -265,6 +265,7 @@
     ulong tmp=sql_rnd_with_mutex();
     randominit(&rand, tmp + (ulong) &rand, tmp + (ulong) ::query_id);
   }
+  substitute_null_with_insert_id = FALSE;
 }
 
 

--- 1.285/sql/sql_class.h	2006-07-10 16:27:08 +03:00
+++ 1.286/sql/sql_class.h	2006-07-10 16:27:08 +03:00
@@ -893,6 +893,8 @@
   bool       last_cuted_field;
   bool	     no_errors, password, is_fatal_error;
   bool	     query_start_used,last_insert_id_used,insert_id_used,rand_used;
+  /* for IS NULL => = last_insert_id() fix in remove_eq_conds() */
+  bool       substitute_null_with_insert_id;
   bool	     time_zone_used;
   bool	     in_lock_tables;
   bool       query_error, bootstrap, cleanup_done;
@@ -988,6 +990,7 @@
   {
     last_insert_id= id_arg;
     insert_id_used=1;
+    substitute_null_with_insert_id= TRUE;
   }
   inline ulonglong insert_id(void)
   {

--- 1.454/sql/sql_select.cc	2006-07-10 16:27:08 +03:00
+++ 1.455/sql/sql_select.cc	2006-07-10 16:27:08 +03:00
@@ -4719,7 +4719,7 @@
       Field *field=((Item_field*) args[0])->field;
       if (field->flags & AUTO_INCREMENT_FLAG && !field->table->maybe_null &&
 	  (thd->options & OPTION_AUTO_IS_NULL) &&
-	  thd->insert_id())
+	  thd->insert_id() && thd->substitute_null_with_insert_id)
       {
 #ifdef HAVE_QUERY_CACHE
 	query_cache_abort(&thd->net);
@@ -4733,7 +4733,7 @@
 	  cond=new_cond;
 	  cond->fix_fields(thd, 0, &cond);
 	}
-	thd->insert_id(0);		// Clear for next request
+	thd->substitute_null_with_insert_id= FALSE;   // Clear for next request
       }
       /* fix to replace 'NULL' dates with '0' (shreeve@stripped) */
       else if (((field->type() == FIELD_TYPE_DATE) ||

--- 1.8/mysql-test/r/odbc.result	2006-07-10 16:27:08 +03:00
+++ 1.9/mysql-test/r/odbc.result	2006-07-10 16:27:08 +03:00
@@ -14,3 +14,14 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 drop table t1;
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
+INSERT INTO t1 VALUES (NULL);
+SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL;
+a	last_insert_id()
+1	1
+SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL;
+a	last_insert_id()
+SELECT sql_no_cache a, last_insert_id() FROM t1;
+a	last_insert_id()
+1	1
+DROP TABLE t1;

--- 1.6/mysql-test/t/odbc.test	2006-07-10 16:27:08 +03:00
+++ 1.7/mysql-test/t/odbc.test	2006-07-10 16:27:08 +03:00
@@ -21,4 +21,14 @@
 explain select * from t1 where b is null;
 drop table t1;
 
+#
+# Bug #14553: NULL in WHERE resets LAST_INSERT_ID
+#
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
+INSERT INTO t1 VALUES (NULL);
+SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL;
+SELECT sql_no_cache a, last_insert_id() FROM t1 WHERE a IS NULL;
+SELECT sql_no_cache a, last_insert_id() FROM t1;
+DROP TABLE t1;
+
 # End of 4.1 tests

--- 1.11/mysql-test/r/rpl_insert_id.result	2006-07-10 16:27:08 +03:00
+++ 1.12/mysql-test/r/rpl_insert_id.result	2006-07-10 16:27:08 +03:00
@@ -73,3 +73,17 @@
 SET FOREIGN_KEY_CHECKS=0;
 INSERT INTO t1 VALUES (1),(1);
 ERROR 23000: Duplicate entry '1' for key 1
+drop table t1;
+create table t1(a int auto_increment, key(a));
+create table t2(a int);
+insert into t1 (a) values (null);
+insert into t2 (a) select a from t1 where a is null;
+insert into t2 (a) select a from t1 where a is null;
+select * from t2;
+a
+1
+select * from t2;
+a
+1
+drop table t1;
+drop table t2;

--- 1.11/mysql-test/t/rpl_insert_id.test	2006-07-10 16:27:08 +03:00
+++ 1.12/mysql-test/t/rpl_insert_id.test	2006-07-10 16:27:08 +03:00
@@ -73,5 +73,23 @@
 --error 1062
 INSERT INTO t1 VALUES (1),(1);
 sync_slave_with_master;
-
+ 
+#
+# Bug#14553: NULL in WHERE resets LAST_INSERT_ID
+#
+connection master;
+drop table t1;
+create table t1(a int auto_increment, key(a));
+create table t2(a int);
+insert into t1 (a) values (null);
+insert into t2 (a) select a from t1 where a is null;
+insert into t2 (a) select a from t1 where a is null;
+select * from t2;
+sync_slave_with_master;
+connection slave;
+select * from t2;
+connection master;
+drop table t1;
+drop table t2;
+sync_slave_with_master;
 # End of 4.1 tests
Thread
bk commit into 4.1 tree (gkodinov:1.2479) BUG#14553kgeorge10 Jul