summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--mysql-test/r/odbc.result11
-rw-r--r--mysql-test/r/rpl_insert_id.result13
-rw-r--r--mysql-test/t/odbc.test10
-rw-r--r--mysql-test/t/rpl_insert_id.test19
-rw-r--r--sql/sql_class.cc1
-rw-r--r--sql/sql_class.h3
-rw-r--r--sql/sql_select.cc4
7 files changed, 59 insertions, 2 deletions
diff --git a/mysql-test/r/odbc.result b/mysql-test/r/odbc.result
index 2d9d39393b1..5629d3dab33 100644
--- a/mysql-test/r/odbc.result
+++ b/mysql-test/r/odbc.result
@@ -14,3 +14,14 @@ explain select * from t1 where b is null;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
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;
diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result
index b11f1b92020..21eade8f9cf 100644
--- a/mysql-test/r/rpl_insert_id.result
+++ b/mysql-test/r/rpl_insert_id.result
@@ -73,7 +73,20 @@ CREATE TABLE t1 ( a INT UNIQUE );
SET FOREIGN_KEY_CHECKS=0;
INSERT INTO t1 VALUES (1),(1);
ERROR 23000: Duplicate entry '1' for key 1
+drop table if exists t1, t2;
+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;
drop function if exists bug15728;
drop function if exists bug15728_insert;
drop table if exists t1, t2;
diff --git a/mysql-test/t/odbc.test b/mysql-test/t/odbc.test
index d4b6fc35e74..6a754bb32a7 100644
--- a/mysql-test/t/odbc.test
+++ b/mysql-test/t/odbc.test
@@ -21,4 +21,14 @@ select * from t1 where a is null;
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
diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test
index e038829760d..94ce7f95fb4 100644
--- a/mysql-test/t/rpl_insert_id.test
+++ b/mysql-test/t/rpl_insert_id.test
@@ -77,6 +77,25 @@ sync_slave_with_master;
connection master;
drop table t1;
sync_slave_with_master;
+
+#
+# Bug#14553: NULL in WHERE resets LAST_INSERT_ID
+#
+connection master;
+drop table if exists t1,t2;
+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
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 06082a57964..99b609e4267 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -269,6 +269,7 @@ THD::THD()
tablespace_op=FALSE;
ulong tmp=sql_rnd_with_mutex();
randominit(&rand, tmp + (ulong) &rand, tmp + (ulong) ::query_id);
+ substitute_null_with_insert_id = FALSE;
thr_lock_info_init(&lock_info); /* safety: will be reset after start */
thr_lock_owner_init(&main_lock_id, &lock_info);
}
diff --git a/sql/sql_class.h b/sql/sql_class.h
index eb075dd54bb..95d8f06a65e 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -1321,6 +1321,8 @@ public:
bool no_errors, password, is_fatal_error;
bool query_start_used, rand_used, time_zone_used;
bool last_insert_id_used,insert_id_used, clear_next_insert_id;
+ /* for IS NULL => = last_insert_id() fix in remove_eq_conds() */
+ bool substitute_null_with_insert_id;
bool in_lock_tables;
bool query_error, bootstrap, cleanup_done;
bool tmp_table_used;
@@ -1452,6 +1454,7 @@ public:
{
last_insert_id= id_arg;
insert_id_used=1;
+ substitute_null_with_insert_id= TRUE;
}
inline ulonglong insert_id(void)
{
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 41ff9185cfb..36ce2354e6e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7854,7 +7854,7 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
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);
@@ -7873,7 +7873,7 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
*/
cond->fix_fields(thd, &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@uci.edu) */
else if (((field->type() == FIELD_TYPE_DATE) ||