Merge mysql.com:/home/tnurnberg/21103/50-21103

into  mysql.com:/home/tnurnberg/21103/51-21103


mysql-test/r/func_time.result:
  Auto merged
mysql-test/r/ps_2myisam.result:
  Auto merged
mysql-test/r/ps_3innodb.result:
  Auto merged
mysql-test/r/ps_4heap.result:
  Auto merged
mysql-test/r/ps_5merge.result:
  Auto merged
mysql-test/r/ps_7ndb.result:
  Auto merged
mysql-test/t/func_time.test:
  Auto merged
sql/field.cc:
  Auto merged

8 files changed, 72 insertions, 42 deletions

diff --git a/mysql-test/include/ps_conv.inc b/mysql-test/include/ps_conv.inc
index 0dd819f6e62..09290d760ce 100644
--- a/mysql-test/include/ps_conv.inc
+++ b/mysql-test/include/ps_conv.inc
@@ -1152,19 +1152,19 @@ select '-- select .. where date/time column = .. --' as test_sequence ;
 ######## 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 01:01:01' as datetime) and
   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 01:01:01' as datetime) and
   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 ;
 
 
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index d5888d13a06..827c631e353 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -1183,6 +1183,23 @@ set time_zone= @@global.time_zone;
 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');
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index 2bfd6d31ac9..af7a762c5a8 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -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 datetime) ;
 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
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index 607a0426bd7..8c0b9f1e19b 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -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 datetime) ;
 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
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index f4eec0c610c..7ad6e3ea722 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -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 datetime) ;
 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
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index 38e4626d59c..1e2c27e6e4f 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -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 datetime) ;
 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 datetime) ;
 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
diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result
index 432a07df9d0..e43f6b4ed6c 100644
--- a/mysql-test/r/ps_7ndb.result
+++ b/mysql-test/r/ps_7ndb.result
@@ -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 datetime) ;
 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
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 9e5a3012934..ccb4c2cd0e2 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -694,6 +694,19 @@ set time_zone= @@global.time_zone;
 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));