Merge tnurnberg@bk-internal.mysql.com:/home/bk/mysql-5.0-opt

into  mysql.com:/scratch/tnurnberg/31800/50-31800


mysql-test/r/select.result:
  Auto merged
mysql-test/t/select.test:
  Auto merged

1 files changed, 72 insertions, 6 deletions

diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 259de144a51..1547a81a0b3 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -2763,14 +2763,14 @@ create table t1(f1 int, f2 date);
 insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'),
   (4,'2005-10-01'),(5,'2005-12-30');
 # should return all records
-select * from t1 where f2 >= 0;
-select * from t1 where f2 >= '0000-00-00';
+select * from t1 where f2 >= 0            order by f2;
+select * from t1 where f2 >= '0000-00-00' order by f2;
 # should return 4,5
-select * from t1 where f2 >= '2005-09-31';
-select * from t1 where f2 >= '2005-09-3a';
+select * from t1 where f2 >= '2005-09-31' order by f2;
+select * from t1 where f2 >= '2005-09-3a' order by f2;
 # should return 1,2,3
-select * from t1 where f2 <= '2005-09-31';
-select * from t1 where f2 <= '2005-09-3a';
+select * from t1 where f2 <= '2005-09-31' order by f2;
+select * from t1 where f2 <= '2005-09-3a' order by f2;
 drop table t1;
 
 #
@@ -3512,4 +3512,70 @@ ALTER VIEW v1 AS SELECT 1 AS ` `;
 
 DROP VIEW v1;
 
+#
+# Bug#31800: Date comparison fails with timezone and slashes for greater
+#            than comparison
+#
+
+# On DATETIME-like literals with trailing garbage, BETWEEN fudged in a
+# DATETIME comparator, while greater/less-than used bin-string comparisons.
+# Should correctly be compared as DATE or DATETIME, but throw a warning:
+
+select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'
+                                                and '2007/10/20 00:00:00 GMT';
+select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';
+select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';
+
+# We have all we need -- and trailing garbage:
+# (leaving out a leading zero in first example to prove it's a
+# value-comparison, not a string-comparison!)
+select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';
+select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6';
+select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6';
+select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6';
+# no time at all:
+select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6';
+# partial time:
+select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
+# fail, different second part:
+select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';
+# correct syntax, no trailing nonsense -- this one must throw no warning:
+select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56';
+# no warning, but failure (different hour parts):
+select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00';
+# succeed:
+select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00';
+# succeed, but warn for "trailing garbage" (":34"):
+select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00';
+# invalid date (Feb 30) succeeds
+select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34';
+# 0-day for both, just works in default SQL mode.
+select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
+# 0-day, succeed
+select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'
+                                                and '2007/10/20 00:00:00';
+set SQL_MODE=TRADITIONAL;
+# 0-day throws warning in traditional mode, and fails
+select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
+select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';
+# different code-path: get_datetime_value() with 0-day
+select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
+select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01'
+                                                and '2007/10/20';
+set SQL_MODE=DEFAULT;
+select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';
+select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20';
+select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34';
+select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34';
+select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '';
+
+select str_to_date('1','%Y-%m-%d') = '1';
+select str_to_date('1','%Y-%m-%d') = '1';
+select str_to_date('','%Y-%m-%d') = '';
+
+# these three should work!
+select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;
+select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';
+select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;
+
 --echo End of 5.0 tests