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#14553 | kgeorge | 10 Jul |
