diff options
| -rw-r--r-- | mysql-test/r/func_test.result | 3 | ||||
| -rw-r--r-- | mysql-test/r/func_time.result | 5 | ||||
| -rw-r--r-- | mysql-test/t/func_test.test | 10 | ||||
| -rw-r--r-- | mysql-test/t/func_time.test | 18 |
4 files changed, 23 insertions, 13 deletions
diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result index 2f40740d4aa..586e345ea10 100644 --- a/mysql-test/r/func_test.result +++ b/mysql-test/r/func_test.result @@ -26,9 +26,6 @@ 1 1 1 -1.49 or -1.49 0.6 or 0.6 1 1 -start ctime1 ctime2 -start ctime1 ctime2 -2002-11-04 00:00:00 20021029165106 20021105164731 5 between 0 and 10 between 0 and 1 (5 between 0 and 10) between 0 and 1 0 1 1 and 2 between 2 and 10 2 between 2 and 10 and 1 diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 71fc7e4b90b..a77d9b2cdff 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -220,3 +220,8 @@ to_days("0000-00-00") to_days(d) to_days(dt) to_days(t) to_days(c) NULL NULL NULL NULL NULL extract(MONTH FROM "0000-00-00") extract(MONTH FROM d) extract(MONTH FROM dt) extract(MONTH FROM t) extract(MONTH FROM c) 0 0 0 0 0 +start ctime1 ctime2 +start ctime1 ctime2 +2002-11-04 00:00:00 20021029165106 20021105164731 +start ctime1 ctime2 +2002-11-04 00:00:00 2002-10-29 16:51:06 2002-11-05 16:47:31 diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index ccbb531e2e6..ec44009b1a6 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -15,16 +15,6 @@ select 2 between 1 and 3, "monty" between "max" and "my",2=2 and "monty" between select 'b' between 'a' and 'c', 'B' between 'a' and 'c'; select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0); select -1.49 or -1.49,0.6 or 0.6; -drop table if exists t1,t2; -CREATE TABLE t1 ( start datetime default NULL) TYPE=MyISAM; -INSERT INTO t1 VALUES ('2002-10-21 00:00:00'); -INSERT INTO t1 VALUES ('2002-10-28 00:00:00'); -INSERT INTO t1 VALUES ('2002-11-04 00:00:00'); -CREATE TABLE t2 ( ctime1 timestamp(14) NOT NULL, ctime2 timestamp(14) NOT NULL) TYPE=MyISAM; -INSERT INTO t2 VALUES (20021029165106,20021105164731); -select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2; -select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2; -drop table if exists t1,t2; # # Wrong usage of functions diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index ffb0f8bbf1e..e267339d64c 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -150,3 +150,21 @@ select yearweek("0000-00-00"),yearweek(d),yearweek(dt),yearweek(t),yearweek(c) f select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(t),to_days(c) from t1; select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),extract(MONTH FROM t),extract(MONTH FROM c) from t1; drop table t1; + + +# +# Test problem with TIMESTAMP and BETWEEN +# + +CREATE TABLE t1 ( start datetime default NULL); +INSERT INTO t1 VALUES ('2002-10-21 00:00:00'),('2002-10-28 00:00:00'),('2002-11-04 00:00:00'); +CREATE TABLE t2 ( ctime1 timestamp(14) NOT NULL, ctime2 timestamp(14) NOT NULL); +INSERT INTO t2 VALUES (20021029165106,20021105164731); +CREATE TABLE t3 (ctime1 char(19) NOT NULL, ctime2 char(19) NOT NULL); +INSERT INTO t3 VALUES ("2002-10-29 16:51:06","2002-11-05 16:47:31"); + +# The following statement should be fixed to return a row in 4.1 +select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2; +select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2; +select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2; +drop table t1,t2,t3; |