bk commit into 5.0 tree (gkodinov:1.2562) BUG#31928

[kgeorge]

Below is the list of changes that have just been committed into a local
5.0 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, 2007-11-07 18:02:12+02:00, gkodinov@stripped +3 -0
  Bug #31928: Search fails on '1000-00-00' date after sql_mode change
  
  When constructing a key image stricter date checking (from sql_mode)
  should not be enabled, because it will reject invalid dates that the
  server would otherwise accept for searching when there's no index.
   
  Fixed by disabling strict date checking when constructing a key image.

  mysql-test/r/type_date.result@stripped, 2007-11-07 18:02:12+02:00, gkodinov@stripped +38
-1
    Bug #31928: test case

  mysql-test/t/type_date.test@stripped, 2007-11-07 18:02:12+02:00, gkodinov@stripped +20 -0
    Bug #31928: test case

  sql/sql_select.h@stripped, 2007-11-07 18:02:12+02:00, gkodinov@stripped +7 -4
    Bug #31928: Disable strict date checking when consructing
    a key image

diff -Nrup a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
--- a/mysql-test/r/type_date.result	2007-10-22 19:32:16 +03:00
+++ b/mysql-test/r/type_date.result	2007-11-07 18:02:12 +02:00
@@ -202,7 +202,6 @@ a
 Warnings:
 Warning	1292	Incorrect date value: '0000-00-00' for column 'a' at row 1
 Warning	1292	Incorrect date value: '0000-00-00' for column 'a' at row 1
-Warning	1292	Incorrect date value: '0000-00-00' for column 'a' at row 1
 SELECT * FROM t2 WHERE a = '0000-00-00';
 a
 0000-00-00
@@ -212,6 +211,44 @@ Warning	1292	Incorrect date value: '0000
 Warning	1292	Incorrect date value: '0000-00-00' for column 'a' at row 1
 INSERT INTO t1 VALUES ('0000-00-00');
 ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1
+SET SQL_MODE=DEFAULT;
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a DATE);
+CREATE TABLE t2 (a DATE);
+CREATE INDEX i ON t1 (a);
+INSERT INTO t1 VALUES ('1000-00-00'),('1000-00-00');
+INSERT INTO t2 VALUES ('1000-00-00'),('1000-00-00');
+SELECT * FROM t1 WHERE a = '1000-00-00';
+a
[phone]
[phone]
+SELECT * FROM t2 WHERE a = '1000-00-00';
+a
[phone]
[phone]
+SET SQL_MODE=TRADITIONAL;
+EXPLAIN SELECT * FROM t1 WHERE a = '1000-00-00';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i	i	4	const	1	Using where; Using index
+Warnings:
+Warning	1292	Incorrect date value: '1000-00-00' for column 'a' at row 1
+Warning	1292	Incorrect date value: '1000-00-00' for column 'a' at row 1
+SELECT * FROM t1 WHERE a = '1000-00-00';
+a
[phone]
[phone]
+Warnings:
+Warning	1292	Incorrect date value: '1000-00-00' for column 'a' at row 1
+Warning	1292	Incorrect date value: '1000-00-00' for column 'a' at row 1
+SELECT * FROM t2 WHERE a = '1000-00-00';
+a
[phone]
[phone]
+Warnings:
+Warning	1292	Incorrect date value: '1000-00-00' for column 'a' at row 1
+Warning	1292	Incorrect date value: '1000-00-00' for column 'a' at row 1
+INSERT INTO t1 VALUES ('1000-00-00');
+ERROR 22007: Incorrect date value: '1000-00-00' for column 'a' at row 1
 SET SQL_MODE=DEFAULT;
 DROP TABLE t1,t2;
 End of 5.0 tests
diff -Nrup a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test
--- a/mysql-test/t/type_date.test	2007-10-22 19:32:16 +03:00
+++ b/mysql-test/t/type_date.test	2007-11-07 18:02:12 +02:00
@@ -190,4 +190,24 @@ INSERT INTO t1 VALUES ('0000-00-00');
 SET SQL_MODE=DEFAULT;
 DROP TABLE t1,t2;
 
+#
+# Bug #31928: Search fails on '1000-00-00' date after sql_mode change
+#
+
+CREATE TABLE t1 (a DATE);
+CREATE TABLE t2 (a DATE);
+CREATE INDEX i ON t1 (a);
+INSERT INTO t1 VALUES ('1000-00-00'),('1000-00-00');
+INSERT INTO t2 VALUES ('1000-00-00'),('1000-00-00');
+SELECT * FROM t1 WHERE a = '1000-00-00';
+SELECT * FROM t2 WHERE a = '1000-00-00';
+SET SQL_MODE=TRADITIONAL;
+EXPLAIN SELECT * FROM t1 WHERE a = '1000-00-00';
+SELECT * FROM t1 WHERE a = '1000-00-00';
+SELECT * FROM t2 WHERE a = '1000-00-00';
+--error ER_TRUNCATED_WRONG_VALUE
+INSERT INTO t1 VALUES ('1000-00-00');
+SET SQL_MODE=DEFAULT;
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests
diff -Nrup a/sql/sql_select.h b/sql/sql_select.h
--- a/sql/sql_select.h	2007-10-10 16:26:00 +03:00
+++ b/sql/sql_select.h	2007-11-07 18:02:12 +02:00
@@ -548,14 +548,17 @@ public:
   enum store_key_result copy()
   {
     enum store_key_result result;
-    enum_check_fields saved_count_cuted_fields= 
-      to_field->table->in_use->count_cuted_fields;
+    THD *thd= to_field->table->in_use;
+    enum_check_fields saved_count_cuted_fields= thd->count_cuted_fields;
+    ulong sql_mode= thd->variables.sql_mode;
+    thd->variables.sql_mode&= ~(MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE);
 
-    to_field->table->in_use->count_cuted_fields= CHECK_FIELD_IGNORE;
+    thd->count_cuted_fields= CHECK_FIELD_IGNORE;
 
     result= copy_inner();
 
-    to_field->table->in_use->count_cuted_fields= saved_count_cuted_fields;
+    thd->count_cuted_fields= saved_count_cuted_fields;
+    thd->variables.sql_mode= sql_mode;
 
     return result;
   }