From: Date: March 2 2007 3:23pm
Subject: bk commit into 5.0 tree (tnurnberg:1.2457) BUG#21103
List-Archive: http://lists.mysql.com/commits/21025
X-Bug: 21103
Message-Id: <20070302142320.9FA8C435D0@linux.site>

Below is the list of changes that have just been committed into a local
5.0 repository of tnurnberg. When tnurnberg 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-03-02 15:23:13+01:00, tnurnberg@stripped +9 -0
  Bug #21103: DATE column not compared as DATE
  
  If we compare two items A and B, with B being (a constant) of a
  larger type, then A gets promoted to B's type for comparison if
  it's a constant, function, or CAST() column, but B gets demoted
  to A's type if A is a (not explicitly CAST()) column. This is
  counter-intuitive and not mandated by the standard.
   
  Disabling optimisation where it would be lossy so field value
  will properly get promoted and compared as binary string (rather
  than as integers).

  mysql-test/include/ps_conv.inc@stripped, 2007-03-02 15:23:09+01:00, tnurnberg@stripped +6 -6
    Bug #21103: DATE column not compared as DATE
    
    When comparing a DATE field with a DATETIME constant, we now compare
    as DATETIMEs, not as DATEs.  Fix certain queries to still work.

  mysql-test/r/func_time.result@stripped, 2007-03-02 15:23:10+01:00, tnurnberg@stripped +17 -0
    Bug #21103: DATE column not compared as DATE
    
    When comparing a DATE field with a DATETIME constant, we now compare
    as DATETIMEs, not as DATEs.  Show that everything works as expected.

  mysql-test/r/ps_2myisam.result@stripped, 2007-03-02 15:23:10+01:00, tnurnberg@stripped +6 -6
    Bug #21103: DATE column not compared as DATE
    
    When comparing a DATE field with a DATETIME constant, we now compare
    as DATETIMEs, not as DATEs.  Fix certain queries to still work.

  mysql-test/r/ps_3innodb.result@stripped, 2007-03-02 15:23:10+01:00, tnurnberg@stripped +6 -6
    Bug #21103: DATE column not compared as DATE
    
    When comparing a DATE field with a DATETIME constant, we now compare
    as DATETIMEs, not as DATEs.  Fix certain queries to still work.

  mysql-test/r/ps_4heap.result@stripped, 2007-03-02 15:23:10+01:00, tnurnberg@stripped +6 -6
    Bug #21103: DATE column not compared as DATE
    
    When comparing a DATE field with a DATETIME constant, we now compare
    as DATETIMEs, not as DATEs.  Fix certain queries to still work.

  mysql-test/r/ps_5merge.result@stripped, 2007-03-02 15:23:10+01:00, tnurnberg@stripped +12 -12
    Bug #21103: DATE column not compared as DATE
    
    When comparing a DATE field with a DATETIME constant, we now compare
    as DATETIMEs, not as DATEs.  Fix certain queries to still work.

  mysql-test/r/ps_7ndb.result@stripped, 2007-03-02 15:23:10+01:00, tnurnberg@stripped +6 -6
    Bug #21103: DATE column not compared as DATE
    
    When comparing a DATE field with a DATETIME constant, we now compare
    as DATETIMEs, not as DATEs.  Fix certain queries to still work.

  mysql-test/t/func_time.test@stripped, 2007-03-02 15:23:10+01:00, tnurnberg@stripped +13 -0
    Bug #21103: DATE column not compared as DATE
    
    When comparing a DATE field with a DATETIME constant, we now compare
    as DATETIMEs, not as DATEs.  Show that everything works as expected.

  sql/field.cc@stripped, 2007-03-02 15:23:10+01:00, tnurnberg@stripped +13 -10
    Bug #21103: DATE column not compared as DATE
    
    #0 stores the date only as a 3-byte integer; save_in_field() in
    #1 saves 'this' in field's format (DATE), #2 "converts a constant
    item to an int and replaces the original item" -- consequently,
    this replaces the Item_string "2006-11-06 04:08:36.0" with the
    Item_int_with_ref 20061106.
    
    #0  Field_newdate::store (this=0x8d26880, from=0x8d5e658 "2006-11-06
    04:08:36.0", len=21, cs=0x88022c0) at field.cc:5344
    #1  0x0817e3b0 in Item_string::save_in_field (this=0x8d5e670, field=0x8d26880, no_conversions=true) at item.cc:4340
    #2  0x081b22ae in convert_constant_item (thd=0x8d25240, field=0x8d26880, item=0x8d5e74c) at item_cmpfunc.cc:245
    #3  0x081b8a36 in Item_bool_func2::fix_length_and_dec (this=0x8d5e6f8) at item_cmpfunc.cc:309
    #4  0x081a3427 in Item_func::fix_fields (this=0x8d5e6f8, thd=0x8d25240, ref=0x8d5f5fc) at item_func.cc:190
    #5  0x0825bc2d in setup_conds (thd=0x8d25240, tables=0x8d5e410, leaves=0x8d5e410, conds=0x8d5f5fc) at sql_base.cc:4941
    ...
    
    Disabling optimisation where it would be lossy so field value will
    properly get promoted and compared as binary string (rather than as
    integers).

# 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:	tnurnberg
# Host:	sin.intern.azundris.com
# Root:	/home/tnurnberg/21103/50-21103

--- 1.340/sql/field.cc	2007-01-31 06:56:14 +01:00
+++ 1.341/sql/field.cc	2007-03-02 15:23:10 +01:00
@@ -5338,27 +5338,30 @@ void Field_date::sql_type(String &res) c
 int Field_newdate::store(const char *from,uint len,CHARSET_INFO *cs)
 {
   TIME l_time;
-  long tmp;
   int error;
   THD *thd= table ? table->in_use : current_thd;
-  if (str_to_datetime(from, len, &l_time,
-                      (TIME_FUZZY_DATE |
-                       (thd->variables.sql_mode &
-                        (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE |
-                         MODE_INVALID_DATES))),
-                      &error) <= MYSQL_TIMESTAMP_ERROR)
+  enum enum_mysql_timestamp_type ret;
+  if ((ret= str_to_datetime(from, len, &l_time,
+                            (TIME_FUZZY_DATE |
+                             (thd->variables.sql_mode &
+                              (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE |
+                               MODE_INVALID_DATES))),
+                            &error)) <= MYSQL_TIMESTAMP_ERROR)
   {
-    tmp= 0L;
+    int3store(ptr,0L);
     error= 2;
   }
   else
-    tmp= l_time.day + l_time.month*32 + l_time.year*16*32;
+  {
+    int3store(ptr, l_time.day + l_time.month*32 + l_time.year*16*32);
+    if(!error && (ret != MYSQL_TIMESTAMP_DATE))
+      return 2;
+  }
 
   if (error)
     set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED,
                          from, len, MYSQL_TIMESTAMP_DATE, 1);
 
-  int3store(ptr,tmp);
   return error;
 }
 

--- 1.44/mysql-test/r/ps_2myisam.result	2006-10-06 13:15:37 +02:00
+++ 1.45/mysql-test/r/ps_2myisam.result	2007-03-02 15:23:10 +01:00
@@ -3046,25 +3046,25 @@ test_sequence
 -- select .. where date/time column = .. --
 set @arg00= '1991-01-01 01:01:01' ;
 select 'true' as found from t9 
-where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
 c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
 c17= '1991-01-01 01:01:01' ;
 found
 true
 select 'true' as found from t9 
-where c1= 20 and c13= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
 and c17= @arg00 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
   c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
   c17= '1991-01-01 01:01:01'" ;
 execute stmt1 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 found
 true
@@ -3078,7 +3078,7 @@ c17= CAST('1991-01-01 01:01:01' as datet
 found
 true
 select 'true' as found from t9 
-where c1= 20 and c13= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
 and c17= @arg00 ;
 found
 true
@@ -3092,7 +3092,7 @@ execute stmt1 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 found
 true

--- 1.47/mysql-test/r/ps_3innodb.result	2006-10-06 13:15:37 +02:00
+++ 1.48/mysql-test/r/ps_3innodb.result	2007-03-02 15:23:10 +01:00
@@ -3029,25 +3029,25 @@ test_sequence
 -- select .. where date/time column = .. --
 set @arg00= '1991-01-01 01:01:01' ;
 select 'true' as found from t9 
-where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
 c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
 c17= '1991-01-01 01:01:01' ;
 found
 true
 select 'true' as found from t9 
-where c1= 20 and c13= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
 and c17= @arg00 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
   c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
   c17= '1991-01-01 01:01:01'" ;
 execute stmt1 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 found
 true
@@ -3061,7 +3061,7 @@ c17= CAST('1991-01-01 01:01:01' as datet
 found
 true
 select 'true' as found from t9 
-where c1= 20 and c13= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
 and c17= @arg00 ;
 found
 true
@@ -3075,7 +3075,7 @@ execute stmt1 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 found
 true

--- 1.43/mysql-test/r/ps_4heap.result	2006-10-06 13:15:37 +02:00
+++ 1.44/mysql-test/r/ps_4heap.result	2007-03-02 15:23:10 +01:00
@@ -3030,25 +3030,25 @@ test_sequence
 -- select .. where date/time column = .. --
 set @arg00= '1991-01-01 01:01:01' ;
 select 'true' as found from t9 
-where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
 c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
 c17= '1991-01-01 01:01:01' ;
 found
 true
 select 'true' as found from t9 
-where c1= 20 and c13= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
 and c17= @arg00 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
   c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
   c17= '1991-01-01 01:01:01'" ;
 execute stmt1 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 found
 true
@@ -3062,7 +3062,7 @@ c17= CAST('1991-01-01 01:01:01' as datet
 found
 true
 select 'true' as found from t9 
-where c1= 20 and c13= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
 and c17= @arg00 ;
 found
 true
@@ -3076,7 +3076,7 @@ execute stmt1 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 found
 true

--- 1.44/mysql-test/r/ps_5merge.result	2006-10-06 13:15:37 +02:00
+++ 1.45/mysql-test/r/ps_5merge.result	2007-03-02 15:23:10 +01:00
@@ -2966,25 +2966,25 @@ test_sequence
 -- select .. where date/time column = .. --
 set @arg00= '1991-01-01 01:01:01' ;
 select 'true' as found from t9 
-where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
 c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
 c17= '1991-01-01 01:01:01' ;
 found
 true
 select 'true' as found from t9 
-where c1= 20 and c13= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
 and c17= @arg00 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
   c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
   c17= '1991-01-01 01:01:01'" ;
 execute stmt1 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 found
 true
@@ -2998,7 +2998,7 @@ c17= CAST('1991-01-01 01:01:01' as datet
 found
 true
 select 'true' as found from t9 
-where c1= 20 and c13= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
 and c17= @arg00 ;
 found
 true
@@ -3012,7 +3012,7 @@ execute stmt1 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 found
 true
@@ -5980,25 +5980,25 @@ test_sequence
 -- select .. where date/time column = .. --
 set @arg00= '1991-01-01 01:01:01' ;
 select 'true' as found from t9 
-where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
 c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
 c17= '1991-01-01 01:01:01' ;
 found
 true
 select 'true' as found from t9 
-where c1= 20 and c13= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
 and c17= @arg00 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
   c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
   c17= '1991-01-01 01:01:01'" ;
 execute stmt1 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 found
 true
@@ -6012,7 +6012,7 @@ c17= CAST('1991-01-01 01:01:01' as datet
 found
 true
 select 'true' as found from t9 
-where c1= 20 and c13= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
 and c17= @arg00 ;
 found
 true
@@ -6026,7 +6026,7 @@ execute stmt1 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 found
 true

--- 1.5/mysql-test/include/ps_conv.inc	2005-08-25 05:05:33 +02:00
+++ 1.6/mysql-test/include/ps_conv.inc	2007-03-02 15:23:09 +01:00
@@ -1152,19 +1152,19 @@ select '-- select .. where date/time col
 ######## SELECT .. WHERE column(date/time/..)=value(CHAR(n)/LONGTEXT) ########
 set @arg00= '1991-01-01 01:01:01' ;
 select 'true' as found from t9 
-where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
   c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
   c17= '1991-01-01 01:01:01' ;
 select 'true' as found from t9 
-where c1= 20 and c13= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
   and c17= @arg00 ;
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
   c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
   c17= '1991-01-01 01:01:01'" ;
 execute stmt1 ;
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 
 
@@ -1177,7 +1177,7 @@ where c1= 20 and c13= CAST('1991-01-01 0
   c16= CAST('1991-01-01 01:01:01' as datetime) and
   c17= CAST('1991-01-01 01:01:01' as datetime) ;
 select 'true' as found from t9 
-where c1= 20 and c13= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
   and c17= @arg00 ;
 prepare stmt1 from "select 'true' as found from t9 
 where c1= 20 and c13= CAST('1991-01-01 01:01:01' as datetime) and
@@ -1187,7 +1187,7 @@ where c1= 20 and c13= CAST('1991-01-01 0
   c17= CAST('1991-01-01 01:01:01' as datetime)" ;
 execute stmt1 ;
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 
 

--- 1.47/mysql-test/r/ps_7ndb.result	2006-10-06 13:15:38 +02:00
+++ 1.48/mysql-test/r/ps_7ndb.result	2007-03-02 15:23:10 +01:00
@@ -3029,25 +3029,25 @@ test_sequence
 -- select .. where date/time column = .. --
 set @arg00= '1991-01-01 01:01:01' ;
 select 'true' as found from t9 
-where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
 c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
 c17= '1991-01-01 01:01:01' ;
 found
 true
 select 'true' as found from t9 
-where c1= 20 and c13= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
 and c17= @arg00 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= '1991-01-01 01:01:01' and c14= '1991-01-01 01:01:01' and
+where c1= 20 and c13= CAST('1991-01-01 01:01:01' AS DATE) and c14= '1991-01-01 01:01:01' and
   c15= '1991-01-01 01:01:01' and c16= '1991-01-01 01:01:01' and
   c17= '1991-01-01 01:01:01'" ;
 execute stmt1 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 found
 true
@@ -3061,7 +3061,7 @@ c17= CAST('1991-01-01 01:01:01' as datet
 found
 true
 select 'true' as found from t9 
-where c1= 20 and c13= @arg00 and c14= @arg00 and c15= @arg00 and c16= @arg00
+where c1= 20 and c13= CAST(@arg00 AS DATE) and c14= @arg00 and c15= @arg00 and c16= @arg00
 and c17= @arg00 ;
 found
 true
@@ -3075,7 +3075,7 @@ execute stmt1 ;
 found
 true
 prepare stmt1 from "select 'true' as found from t9 
-where c1= 20 and c13= ? and c14= ? and c15= ? and c16= ? and c17= ?" ;
+where c1= 20 and c13= CAST(? AS DATE) and c14= ? and c15= ? and c16= ? and c17= ?" ;
 execute stmt1 using @arg00, @arg00, @arg00, @arg00, @arg00 ;
 found
 true

--- 1.80/mysql-test/r/func_time.result	2007-01-30 16:43:28 +01:00
+++ 1.81/mysql-test/r/func_time.result	2007-03-02 15:23:10 +01:00
@@ -1207,6 +1207,23 @@ SET NAMES DEFAULT;
 select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
 str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE
 NULL
+create table t1 (field DATE);
+insert into t1 values ('2006-11-06');
+select * from t1 where field < '2006-11-06 04:08:36.0';
+field
+2006-11-06
+select * from t1 where field = '2006-11-06 04:08:36.0';
+field
+select * from t1 where field = '2006-11-06';
+field
+2006-11-06
+select * from t1 where CAST(field as DATETIME) < '2006-11-06 04:08:36.0';
+field
+2006-11-06
+select * from t1 where CAST(field as DATE) < '2006-11-06 04:08:36.0';
+field
+2006-11-06
+drop table t1;
 CREATE TABLE t1 (a int, t1 time, t2 time, d date, PRIMARY KEY  (a));
 INSERT INTO t1 VALUES (1, '10:00:00', NULL, NULL), 
 (2, '11:00:00', '11:15:00', '1972-02-06');

--- 1.67/mysql-test/t/func_time.test	2007-01-30 16:43:29 +01:00
+++ 1.68/mysql-test/t/func_time.test	2007-03-02 15:23:10 +01:00
@@ -715,6 +715,19 @@ SET NAMES DEFAULT;
 select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
 
 #
+# Bug #21103: DATE column not compared as DATE
+#
+
+create table t1 (field DATE);
+insert into t1 values ('2006-11-06');
+select * from t1 where field < '2006-11-06 04:08:36.0'; 
+select * from t1 where field = '2006-11-06 04:08:36.0'; 
+select * from t1 where field = '2006-11-06'; 
+select * from t1 where CAST(field as DATETIME) < '2006-11-06 04:08:36.0';
+select * from t1 where CAST(field as DATE) < '2006-11-06 04:08:36.0';
+drop table t1;
+
+#
 # Bug #25643: SEC_TO_TIME function problem
 #
 CREATE TABLE t1 (a int, t1 time, t2 time, d date, PRIMARY KEY  (a));