bk commit into 5.0 tree (gkodinov:1.2402) BUG#22344

[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-02-06 11:08:57+02:00, gkodinov@stripped +5 -0
  Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison
   Ignoring error codes from type conversion allows default (wrong) values to
   go unnoticed in the formation of index search conditions.
   Fixed by correctly checking for conversion errors.

  mysql-test/r/select.result@stripped, 2007-02-06 11:08:47+02:00, gkodinov@stripped +28 -0
    Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison
     - test case

  mysql-test/t/select.test@stripped, 2007-02-06 11:08:48+02:00, gkodinov@stripped +23 -0
    Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison
     - test case

  sql/field.h@stripped, 2007-02-06 11:08:48+02:00, gkodinov@stripped +2 -2
    Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison
     - don't ignore coversion errors

  sql/field_conv.cc@stripped, 2007-02-06 11:08:49+02:00, gkodinov@stripped +7 -8
    Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison
     - don't ignore coversion errors

  sql/item.cc@stripped, 2007-02-06 11:08:50+02:00, gkodinov@stripped +6 -5
    Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison
     - don't ignore coversion errors

# 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:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B22344-5.0-opt

--- 1.196/sql/field.h	2007-01-17 12:52:57 +02:00
+++ 1.197/sql/field.h	2007-02-06 11:08:48 +02:00
@@ -29,7 +29,7 @@
 class Send_field;
 class Protocol;
 struct st_cache_field;
-void field_conv(Field *to,Field *from);
+int field_conv(Field *to,Field *from);
 
 inline uint get_enum_pack_length(int elements)
 {
@@ -1242,7 +1242,7 @@ public:
   uint max_packed_col_length(uint max_length);
   void free() { value.free(); }
   inline void clear_temporary() { bzero((char*) &value,sizeof(value)); }
-  friend void field_conv(Field *to,Field *from);
+  friend int field_conv(Field *to,Field *from);
   uint size_of() const { return sizeof(*this); }
   bool has_charset(void) const
   { return charset() == &my_charset_bin ? FALSE : TRUE; }

--- 1.59/sql/field_conv.cc	2006-12-23 21:04:23 +02:00
+++ 1.60/sql/field_conv.cc	2007-02-06 11:08:49 +02:00
@@ -679,7 +679,7 @@ void (*Copy_field::get_copy_func(Field *
 
 /* Simple quick field convert that is called on insert */
 
-void field_conv(Field *to,Field *from)
+int field_conv(Field *to,Field *from)
 {
   if (to->real_type() == from->real_type() &&
       !(to->type() == FIELD_TYPE_BLOB && to->table->copy_blobs))
@@ -707,7 +707,7 @@ void field_conv(Field *to,Field *from)
       if (to->ptr != from->ptr)
 #endif
         memcpy(to->ptr,from->ptr,to->pack_length());
-      return;
+      return 0;
     }
   }
   if (to->type() == FIELD_TYPE_BLOB)
@@ -723,8 +723,7 @@ void field_conv(Field *to,Field *from)
          from->real_type() != MYSQL_TYPE_STRING &&
          from->real_type() != MYSQL_TYPE_VARCHAR))
       blob->value.copy();
-    blob->store(blob->value.ptr(),blob->value.length(),from->charset());
-    return;
+    return
blob->store(blob->value.ptr(),blob->value.length(),from->charset());
   }
   if ((from->result_type() == STRING_RESULT &&
        (to->result_type() == STRING_RESULT ||
@@ -741,15 +740,15 @@ void field_conv(Field *to,Field *from)
       end with \0. Can be replaced with .ptr() when we have our own
       string->double conversion.
     */
-    to->store(result.c_ptr_quick(),result.length(),from->charset());
+    return to->store(result.c_ptr_quick(),result.length(),from->charset());
   }
   else if (from->result_type() == REAL_RESULT)
-    to->store(from->val_real());
+    return to->store(from->val_real());
   else if (from->result_type() == DECIMAL_RESULT)
   {
     my_decimal buff;
-    to->store_decimal(from->val_decimal(&buff));
+    return to->store_decimal(from->val_decimal(&buff));
   }
   else
-    to->store(from->val_int(), test(from->flags & UNSIGNED_FLAG));
+    return to->store(from->val_int(), test(from->flags & UNSIGNED_FLAG));
 }

--- 1.252/sql/item.cc	2007-01-27 03:10:43 +02:00
+++ 1.253/sql/item.cc	2007-02-06 11:08:50 +02:00
@@ -4245,18 +4245,19 @@ void Item_field::save_org_in_field(Field
 
 int Item_field::save_in_field(Field *to, bool no_conversions)
 {
+  int res;
   if (result_field->is_null())
   {
     null_value=1;
-    return set_field_to_null_with_conversions(to, no_conversions);
+    res= set_field_to_null_with_conversions(to, no_conversions);
   }
   else
   {
     to->set_notnull();
-    field_conv(to,result_field);
+    res= field_conv(to,result_field);
     null_value=0;
   }
-  return 0;
+  return res;
 }
 
 
@@ -5284,9 +5285,9 @@ int Item_ref::save_in_field(Field *to, b
       return set_field_to_null_with_conversions(to, no_conversions);
     }
     to->set_notnull();
-    field_conv(to, result_field);
+    res= field_conv(to, result_field);
     null_value= 0;
-    return 0;
+    return res;
   }
   res= (*ref)->save_in_field(to, no_conversions);
   null_value= (*ref)->null_value;

--- 1.147/mysql-test/r/select.result	2007-02-01 05:31:34 +02:00
+++ 1.148/mysql-test/r/select.result	2007-02-06 11:08:47 +02:00
@@ -3728,3 +3728,31 @@ WHERE ID_better=1 AND ID1_with_null IS N
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ref	idx1,idx2	idx2	4	const	1	Using where
 DROP TABLE t1;
+CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
+INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");
+INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;
+ANALYZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	OK
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t1	range	ts	ts	4	NULL	1	Using where
+Warnings:
+Warning	1292	Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+a	ts	a	dt1	dt2
[phone]:00:00	30	2006-01-01 00:00:00	2999-12-31 00:00:00
+Warnings:
+Warning	1292	Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1
+DROP TABLE t1,t2;

--- 1.121/mysql-test/t/select.test	2007-02-01 05:31:34 +02:00
+++ 1.122/mysql-test/t/select.test	2007-02-06 11:08:48 +02:00
@@ -3207,3 +3207,26 @@ EXPLAIN SELECT * FROM t1
         (ID2_with_null=1 OR ID2_with_null=2);
 
 DROP TABLE t1;
+
+#
+# Bug #22344: InnoDB keys act strange on datetime vs timestamp comparison
+#
+CREATE TABLE t1 (a INT, ts TIMESTAMP, KEY ts(ts));
+INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00");
+ANALYZE TABLE t1;
+
+CREATE TABLE t2 (a INT, dt1 DATETIME, dt2 DATETIME, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (30, "2006-01-01 00:00:00", "2999-12-31 00:00:00");
+INSERT INTO t2 SELECT a+1,dt1,dt2 FROM t2;
+ANALYZE TABLE t2;
+
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+  AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+  AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+
+SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30
+  AND t1.ts BETWEEN t2.dt1 AND t2.dt2
+  AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";
+
+DROP TABLE t1,t2;