diff options
Diffstat (limited to 'mysql-test/main/type_datetime.test')
| -rw-r--r-- | mysql-test/main/type_datetime.test | 851 |
1 files changed, 851 insertions, 0 deletions
diff --git a/mysql-test/main/type_datetime.test b/mysql-test/main/type_datetime.test new file mode 100644 index 00000000000..dafa9f9456e --- /dev/null +++ b/mysql-test/main/type_datetime.test @@ -0,0 +1,851 @@ +# +# testing different DATETIME ranges +# + +--disable_warnings +drop table if exists t1; +--enable_warnings + +create table t1 (t datetime); +insert into t1 values (101),(691231),(700101),(991231),(10000101),(99991231),(101000000),(691231000000),(700101000000),(991231235959),(10000101000000),(99991231235959),(20030100000000),(20030000000000); +select * from t1; +delete from t1 where t > 0; +optimize table t1; +check table t1; +delete from t1; +insert into t1 values("000101"),("691231"),("700101"),("991231"),("00000101"),("00010101"),("99991231"),("00101000000"),("691231000000"),("700101000000"),("991231235959"),("10000101000000"),("99991231235959"),("20030100000000"),("20030000000000"); + +# Strange dates +insert into t1 values ("2003-003-03"); + +# Bug #7308: ISO-8601 date format not handled correctly +insert into t1 values ("20030102T131415"),("2001-01-01T01:01:01"), ("2001-1-1T1:01:01"); +select * from t1; + +# Test some wrong dates +truncate table t1; +insert ignore into t1 values("2003-0303 12:13:14"); +select * from t1; +drop table t1; + +# +# Test insert of now() and curtime() +# + +CREATE TABLE t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, b date, c time, d datetime); +insert into t1 (b,c,d) values(now(),curtime(),now()); +select date_format(a,"%Y-%m-%d")=b,right(a+0,6)=c+0,a=d+0 from t1; +drop table t1; + +# +# Test of datetime and not null +# + +CREATE TABLE t1 (a datetime not null); +insert into t1 values (0); +select * from t1 where a is null; +drop table t1; + +# +# Test with bug when propagating DATETIME to integer and WHERE optimization +# + +create table t1 (id int, dt datetime); +insert into t1 values (1,"2001-08-14 00:00:00"),(2,"2001-08-15 00:00:00"),(3,"2001-08-16 00:00:00"),(4,"2003-09-15 01:20:30"); +select * from t1 where dt='2001-08-14 00:00:00' and dt = if(id=1,'2001-08-14 00:00:00','1999-08-15'); +create index dt on t1 (dt); +select * from t1 where dt > 20021020; +select * from t1 ignore index (dt) where dt > 20021020; +drop table t1; + +# +# Test of datetime optimization +# + +CREATE TABLE `t1` ( + `date` datetime NOT NULL default '0000-00-00 00:00:00', + `numfacture` int(6) unsigned NOT NULL default '0', + `expedition` datetime NOT NULL default '0000-00-00 00:00:00', + PRIMARY KEY (`numfacture`), + KEY `date` (`date`), + KEY `expedition` (`expedition`) +) ENGINE=MyISAM; + +INSERT INTO t1 (expedition) VALUES ('0001-00-00 00:00:00'); +SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00'; +INSERT INTO t1 (numfacture,expedition) VALUES ('1212','0001-00-00 00:00:00'); +SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00'; +EXPLAIN SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00'; +drop table t1; +create table t1 (a datetime not null, b datetime not null); +insert into t1 values (now(), now()); +insert into t1 values (now(), now()); +select * from t1 where a is null or b is null; +drop table t1; + +# +# Let us check if we properly treat wrong datetimes and produce proper +# warnings (for both strings and numbers) +# +create table t1 (t datetime); +insert ignore into t1 values (20030102030460),(20030102036301),(20030102240401), + (20030132030401),(20031302030401),(100001202030401); +select * from t1; +delete from t1; +insert ignore into t1 values + ("2003-01-02 03:04:60"),("2003-01-02 03:63:01"),("2003-01-02 24:04:01"), + ("2003-01-32 03:04:01"),("2003-13-02 03:04:01"), ("10000-12-02 03:04:00"); +select * from t1; +delete from t1; +insert ignore into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer"); +select * from t1 order by t; +drop table t1; + +# +# Test for bug #7297 "Two digit year should be interpreted correctly even +# with zero month and day" +# +create table t1 (dt datetime); +# These dates should be treated as dates in 21st century +insert into t1 values ("12-00-00"), ("00-00-00 01:00:00"); +# Zero dates are still special :/ +insert into t1 values ("00-00-00"), ("00-00-00 00:00:00"); +select * from t1; +drop table t1; + +# +# Bug #16546 DATETIME+0 not always coerced the same way +# +select cast('2006-12-05 22:10:10' as datetime) + 0; + + +# End of 4.1 tests + +# +# Bug#21475: Wrongly applied constant propagation leads to a false comparison. +# +CREATE TABLE t1(a DATETIME NOT NULL); +INSERT INTO t1 VALUES ('20060606155555'); +SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555"); +PREPARE s FROM 'SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555")'; +EXECUTE s; +DROP PREPARE s; +DROP TABLE t1; + + +# +# Bug 19491 (CAST DATE AS DECIMAL returns incorrect result +# +SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)); +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME(6)) AS DECIMAL(20,6)); +SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME(6)) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); +SELECT CAST(CAST('10:11:12.098700' AS TIME(6)) AS DECIMAL(20,6)); + + +# +# Test of storing datetime into date fields +# + +set @org_mode=@@sql_mode; +create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03'); +show create table t1; +insert into t1 values (); +insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38'); +set @@sql_mode='ansi,traditional'; +insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38'); +insert into t1 set dt='2007-03-23 13:49:38',da=dt; +# Test error handling +--error ER_TRUNCATED_WRONG_VALUE +insert into t1 values ('2007-03-32','2007-03-23 13:49:38'); +select * from t1; +drop table t1; +--error ER_INVALID_DEFAULT +create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03'); +--error ER_INVALID_DEFAULT +create table t1 (t time default '916:00:00 a'); +set @@sql_mode= @org_mode; + + +# +# Bug#27590: Wrong DATE/DATETIME comparison. +# +## The following sub test will fail (difference to expected result) if the +## select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1; +## runs exact at midnight ('00:00:00'). +## ( Bug#29290 type_datetime.test failure in 5.1 ) +## Therefore we sleep a bit if we are too close to midnight. +## The complete test itself needs around 1 second. +## Therefore a time_distance to midnight of 5 seconds should be sufficient. +if (`SELECT CURTIME() > SEC_TO_TIME(24 * 3600 - 5)`) +{ + # We are here when CURTIME() is between '23:59:56' and '23:59:59'. + # So a sleep time of 5 seconds brings us between '00:00:01' and '00:00:04'. + --real_sleep 5 +} +create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); +insert into t1(f1) values(curdate()); +select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1; +delete from t1; +insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01'); +insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01'); +insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01'); +insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00'); +insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01'); +select f1, f3 from t1 where f1 >= '2001-02-05 00:00:00' and f3 <= '2001-04-15'; +select f1, f3 from t1 where f1 >= '2001-2-5 0:0:0' and f2 <= '2001-4-15'; +select f1, f2 from t1 where if(1, f1, 0) >= f2; +select 1 from dual where cast('2001-1-1 2:3:4' as date) = cast('2001-01-01' as datetime); +select f1, f2, f1 > f2, f1 = f2, f1 < f2 from t1; +drop table t1; + +# +# Bug#16377: Wrong DATE/DATETIME comparison in BETWEEN function. +# +create table t1 (f1 date, f2 datetime, f3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); +insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01'); +insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01'); +insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01'); +insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00'); +insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01'); +select f2 from t1 where f2 between '2001-2-5' and '01-04-14'; +select f1, f2, f3 from t1 where f1 between f2 and f3; +select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and + cast(f3 as date); +select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01'; +select f2, f3 from t1 where '01-03-10' between f2 and f3; +select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15"; +SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE(); +drop table t1; + +# +# Bug#28133: Wrong DATE/DATETIME comparison in IN() function. +# +create table t1 (f1 date); +insert into t1 values('01-01-01'),('01-01-02'),('01-01-03'); +select * from t1 where f1 in ('01-01-01','2001-01-02','2001-01-03 00:00:00'); +create table t2(f2 datetime); +insert into t2 values('01-01-01 00:00:00'),('01-02-03 12:34:56'),('02-04-06 11:22:33'); +select * from t2 where f2 in ('01-01-01','01-02-03 12:34:56','01-02-03'); +select * from t1,t2 where '01-01-02' in (f1, cast(f2 as date)); +select * from t1,t2 where '01-01-01' in (f1, '01-02-03'); +select * from t1,t2 where if(1,'01-02-03 12:34:56','') in (f1, f2); +create table t3(f3 varchar(20)); +insert into t3 select * from t2; +select * from t2,t3 where f2 in (f3,'03-04-05'); +select f1,f2,f3 from t1,t2,t3 where (f1,'1') in ((f2,'1'),(f3,'1')); +select f1 from t1 where ('1',f1) in (('1','01-01-01'),('1','2001-1-1 0:0:0'),('1','02-02-02')); +drop table t1,t2,t3; + +# +# Bug#27759: Wrong DATE/DATETIME comparison in LEAST()/GREATEST() functions. +# +select least(cast('01-01-01' as date), '01-01-02'); +select greatest(cast('01-01-01' as date), '01-01-02'); +select least(cast('01-01-01' as date), '01-01-02') + 0; +select greatest(cast('01-01-01' as date), '01-01-02') + 0; +select least(cast('01-01-01' as datetime), '01-01-02') + 0; +select cast(least(cast('01-01-01' as datetime), '01-01-02') as signed); +select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2)); +--disable_warnings +DROP PROCEDURE IF EXISTS test27759 ; +--enable_warnings +DELIMITER |; +CREATE PROCEDURE test27759() +BEGIN +declare v_a date default '2007-4-10'; +declare v_b date default '2007-4-11'; +declare v_c datetime default '2004-4-9 0:0:0'; +select v_a as a,v_b as b, + least( v_a, v_b ) as a_then_b, + least( v_b, v_a ) as b_then_a, + least( v_c, v_a ) as c_then_a; +END;| +DELIMITER ;| +call test27759(); +drop procedure test27759; + +# +# Bug#28208: Wrong result of a non-const STRING function with a const +# DATETIME function. +# +create table t1 (f1 date); +insert into t1 values (curdate()); +select left(f1,10) = curdate() from t1; +drop table t1; + +# +# Bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function +# is involved. +# +create table t1(f1 date); +insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02'); +set @bug28261=''; +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +select if(@bug28261 = f1, '', @bug28261:= f1) from t1; +drop table t1; + +# +# Bug#28778: Wrong result of BETWEEN when comparing a DATETIME field with an +# integer constants. +# +create table t1(f1 datetime); +insert into t1 values('2001-01-01'),('2002-02-02'); +select * from t1 where f1 between 20020101 and 20070101000000; +select * from t1 where f1 between 2002010 and 20070101000000; +select * from t1 where f1 between 20020101 and 2007010100000; +drop table t1; + +--echo # +--echo # Bug#27216: functions with parameters of different date types may +--echo # return wrong type of the result. +--echo # +create table t1 (f1 date, f2 datetime, f3 varchar(20)); +create table t2 as select coalesce(f1,f1) as f4 from t1; +desc t2; +create table t3 as select coalesce(f1,f2) as f4 from t1; +desc t3; +create table t4 as select coalesce(f2,f2) as f4 from t1; +desc t4; +create table t5 as select coalesce(f1,f3) as f4 from t1; +desc t5; +create table t6 as select coalesce(f2,f3) as f4 from t1; +desc t6; +create table t7 as select coalesce(makedate(1997,1),f2) as f4 from t1; +desc t7; +create table t8 as select coalesce(cast('01-01-01' as datetime),f2) as f4 + from t1; +desc t8; +create table t9 as select case when 1 then cast('01-01-01' as date) + when 0 then cast('01-01-01' as date) end as f4 from t1; +desc t9; +create table t10 as select case when 1 then cast('01-01-01' as datetime) + when 0 then cast('01-01-01' as datetime) end as f4 from t1; +desc t10; +create table t11 as select if(1, cast('01-01-01' as datetime), + cast('01-01-01' as date)) as f4 from t1; +desc t11; +create table t12 as select least(cast('01-01-01' as datetime), + cast('01-01-01' as date)) as f4 from t1; +desc t12; +create table t13 as select ifnull(cast('01-01-01' as datetime), + cast('01-01-01' as date)) as f4 from t1; +desc t13; +drop tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13; +--echo ################################################################### +# +# Bug #31253: crash comparing datetime to double +# Should return 1st row only. Crashes if NULL propagation fails. +# +create table t1 (f1 time); +insert into t1 set f1 = '45:44:44'; +insert into t1 set f1 = '15:44:44'; +select * from t1 where (convert(f1,datetime)) != 1; +drop table t1; + +# +# Bug #31249: problem with convert(..., datetime) +# +create table t1 (a tinyint); +insert into t1 values (), (), (); +select sum(a) from t1 group by convert(a, datetime); +drop table t1; + +# +# Bug #32694: NOT NULL table field in a subquery produces invalid results +# +create table t1 (id int(10) not null, cur_date datetime not null); +create table t2 (id int(10) not null, cur_date date not null); +insert into t1 (id, cur_date) values (1, '2007-04-25 18:30:22'); +insert into t2 (id, cur_date) values (1, '2007-04-25'); + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='semijoin_with_cache=off'; + +explain extended +select * from t1 +where id in (select id from t1 as x1 where (t1.cur_date is null)); +select * from t1 +where id in (select id from t1 as x1 where (t1.cur_date is null)); + +explain extended +select * from t2 +where id in (select id from t2 as x1 where (t2.cur_date is null)); +select * from t2 +where id in (select id from t2 as x1 where (t2.cur_date is null)); + +insert into t1 (id, cur_date) values (2, '2007-04-26 18:30:22'); +insert into t2 (id, cur_date) values (2, '2007-04-26'); + +explain extended +select * from t1 +where id in (select id from t1 as x1 where (t1.cur_date is null)); +select * from t1 +where id in (select id from t1 as x1 where (t1.cur_date is null)); + +explain extended +select * from t2 +where id in (select id from t2 as x1 where (t2.cur_date is null)); +select * from t2 +where id in (select id from t2 as x1 where (t2.cur_date is null)); + +SET optimizer_switch=@save_optimizer_switch; + +drop table t1,t2; + + +# +# Bug #37526: asymertic operator <=> in trigger +# +SELECT + CAST('NULL' AS DATE) <=> CAST('2008-01-01' AS DATE) n1, + CAST('2008-01-01' AS DATE) <=> CAST('NULL' AS DATE) n2, + CAST('NULL' AS DATE) <=> CAST('NULL' AS DATE) n3, + CAST('NULL' AS DATE) <> CAST('2008-01-01' AS DATE) n4, + CAST('2008-01-01' AS DATE) <> CAST('NULL' AS DATE) n5, + CAST('NULL' AS DATE) <> CAST('NULL' AS DATE) n6, + CAST('NULL' AS DATE) < CAST('2008-01-01' AS DATE) n7, + CAST('2008-01-01' AS DATE) < CAST('NULL' AS DATE) n8, + CAST('NULL' AS DATE) < CAST('NULL' AS DATE) n9; + + +--echo End of 5.0 tests +# +# Test of storing datetime into date fields +# + +set @org_mode=@@sql_mode; +create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03'); +show create table t1; +insert into t1 values (); +insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38'); +set @@sql_mode='ansi,traditional'; +insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38'); +insert into t1 set dt='2007-03-23 13:49:38',da=dt; +# Test error handling +--error 1292 +insert into t1 values ('2007-03-32','2007-03-23 13:49:38'); +select * from t1; +drop table t1; +--error 1067 +create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03'); +--error 1067 +create table t1 (t time default '916:00:00 a'); +set @@sql_mode= @org_mode; + +# +# Bug #42146 - DATETIME fractional seconds parse error +# +# show we trucate microseconds from the right -- special case: leftmost is 0 +SELECT CAST(CAST('2006-08-10 10:11:12.0123450' AS DATETIME(6)) AS DECIMAL(30,7)); + +# show that we ignore leading zeroes for all other fields +SELECT CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.0123450' AS DATETIME(6)) AS DECIMAL(30,7)); +# once more with feeling (but no warnings) +SELECT CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.012345' AS DATETIME(6)) AS DECIMAL(30,7)); + +# +# Bug #38435 - LONG Microseconds cause MySQL to fail a CAST to DATETIME or DATE +# +# show we truncate microseconds from the right +SELECT CAST(CAST('2008-07-29T10:42:51.1234567' AS DateTime(6)) AS DECIMAL(30,7)); + +--echo # +--echo # Bug#59173: Failure to handle DATE(TIME) values where Year, Month or +--echo # Day is ZERO +--echo # +CREATE TABLE t1 (dt1 DATETIME); +INSERT INTO t1 (dt1) VALUES ('0000-00-01 00:00:01'); +DELETE FROM t1 WHERE dt1 = '0000-00-01 00:00:01'; +--echo # Should be empty +SELECT * FROM t1; +DROP TABLE t1; + +--echo End of 5.1 tests + +# +# MDEV-4281 Assertion `maybe_null && item->null_value' fails in make_sortkey on CASE with different return types, GROUP_CONCAT, GROUP BY +# +SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30'); +create table t1 (d date, t time) engine=myisam; +insert into t1 values ('2000-12-03','22:55:23'),('2008-05-03','10:19:31'); +select case when d = '2012-12-12' then d else t end as cond, group_concat( d ) from t1 group by cond; +drop table t1; +SET timestamp=DEFAULT; + +--echo # +--echo # Semantics of the condition <non-nullable datetime field> IS NULL +--echo # when the field belongs to an inner table of an outer join +--echo # + +create table t1 (a int, b date not null); +insert t1 values (1, 0), (2, '1999-01-02'); +create table t2 (c int); +insert t2 values (1),(3); + +select * from t2 left join t1 on t1.a=t2.c where t1.a is null; +select * from t2 left join t1 on t1.a=t2.c where t1.b is null; + +drop table t1,t2; + +--echo # +--echo # MDEV-4634 Crash in CONVERT_TZ +--echo # +SELECT CONVERT_TZ(GREATEST(TIMESTAMP('2021-00-00'),TIMESTAMP('2022-00-00')),'+00:00','+7:5'); + + +--echo # +--echo # MDEV-5041 Inserting a TIME with hour>24 into a DATETIME column produces a wrong value +--echo # +SET @@timestamp=UNIX_TIMESTAMP('2013-08-19 20:30:00'); +SELECT CAST(TIME('-800:20:30') AS DATETIME); +SELECT CAST(TIME('800:20:30') AS DATETIME); +SELECT CAST(TIME('33 08:20:30') AS DATETIME); +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES (TIME('800:20:30')); +INSERT INTO t1 VALUES (TIME('33 08:20:30')); +SET SQL_MODE=NO_ZERO_IN_DATE; +INSERT INTO t1 VALUES (TIME('48:20:30')); +SET SQL_MODE=DEFAULT; +SELECT * FROM t1; +DROP TABLE t1; +DELIMITER |; +CREATE PROCEDURE test5041() +BEGIN + DECLARE t TIME; + DECLARE dt DATETIME; + SET t= TIME('800:20:30'); + SET dt= t; + SELECT dt; +END;| +DELIMITER ;| +call test5041(); +drop procedure test5041; +SET @@timestamp=DEFAULT; + +--echo # +--echo # MDEV-6097 Inconsistent results for CAST(int,decimal,double AS DATETIME) +--echo # +SELECT + CAST(010203101112 AS DATETIME(1)) AS c1, + CAST(010203101112.2 AS DATETIME(1)) AS c2, + CAST(010203101112.2+0e0 AS DATETIME(1)) AS c3; + +--echo End of 5.3 tests + +--echo # +--echo # Start of 5.5 tests +--echo # + +--echo # +--echo # Bug#52849 datetime index not work +--echo # +CREATE TABLE t1 (Id INT, AtTime DATETIME, KEY AtTime (AtTime)); +SET NAMES CP850; +INSERT INTO t1 VALUES (1,'2010-04-12 22:30:12'), (2,'2010-04-12 22:30:12'), (3,'2010-04-12 22:30:12'); +EXPLAIN EXTENDED SELECT * FROM t1 FORCE INDEX(attime) WHERE AtTime = '2010-02-22 18:40:07'; +DROP TABLE t1; +SET NAMES latin1; + +--echo # +--echo # Bug#56271: Wrong comparison result with STR_TO_DATE function +--echo # +CREATE TABLE t1 ( + `year` int(4) NOT NULL, + `month` int(2) NOT NULL +); + +INSERT INTO t1 VALUES (2010,3),(2010,4),(2009,8),(2008,9); + +SELECT * +FROM t1 +WHERE STR_TO_DATE(CONCAT_WS('/01/',`month`,`year`), '%m/%d/%Y') >= +STR_TO_DATE('1/1/2010', '%m/%d/%Y'); + +create table t2(f1 datetime primary key); +insert into t2 select STR_TO_DATE(CONCAT_WS('/01/',`month`,`year`), '%m/%d/%Y') from t1; +select * from t2 where f1=STR_TO_DATE('4/1/2010', '%m/%d/%Y'); +--echo t2 should be const +explain select * from t2 where f1=STR_TO_DATE('4/1/2010', '%m/%d/%Y'); + +DROP TABLE t1,t2; + +--echo # +--echo # Bug#57095: Wrongly chosen expression cache type led to a wrong +--echo # result. +--echo # +CREATE TABLE t1 (`b` datetime ); +INSERT INTO t1 VALUES ('2010-01-01 00:00:00'), ('2010-01-01 00:00:00'); +SELECT * FROM t1 WHERE b <= coalesce(NULL, now()); +DROP TABLE t1; +--echo # + +--echo # +--echo # BUG#12561818: RERUN OF STORED FUNCTION GIVES ERROR 1172: +--echo # RESULT CONSISTED OF MORE THAN ONE ROW +--echo # + +CREATE TABLE t1 (a DATE NOT NULL, b INT); +INSERT INTO t1 VALUES ('0000-00-00',1), ('1999-05-10',2); + +CREATE TABLE t2 (a DATETIME NOT NULL, b INT); +INSERT INTO t2 VALUES ('0000-00-00 00:00:00',1), ('1999-05-10 00:00:00',2); + +--echo +SELECT * FROM t1 WHERE a IS NULL; +SELECT * FROM t2 WHERE a IS NULL; +SELECT * FROM t1 LEFT JOIN t1 AS t1_2 ON 1 WHERE t1_2.a IS NULL; +SELECT * FROM t2 LEFT JOIN t2 AS t2_2 ON 1 WHERE t2_2.a IS NULL; +SELECT * FROM t1 JOIN t1 AS t1_2 ON 1 WHERE t1_2.a IS NULL; +SELECT * FROM t2 JOIN t2 AS t2_2 ON 1 WHERE t2_2.a IS NULL; + +--echo +PREPARE stmt1 FROM + 'SELECT * + FROM t1 LEFT JOIN t1 AS t1_2 ON 1 + WHERE t1_2.a IS NULL AND t1_2.b < 2'; +EXECUTE stmt1; +EXECUTE stmt1; + +DEALLOCATE PREPARE stmt1; +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-9374 having '2015-01-01 01:00:00.000001' > coalesce(NULL) returns true +--echo # +CREATE TABLE t1 (c1 DATETIME(0)); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1 HAVING '2015-01-01 01:00:00.000001' > COALESCE(c1); +DROP TABLE t1; + +--echo # +--echo # End of 5.5 tests +--echo # + +--echo # +--echo # Start of 10.1 tests +--echo # + +let type=DATETIME; +let defval='0000-00-00 00:00:00'; +--source include/type_temporal_zero_default.inc + +--echo # +--echo # MDEV-8336 The meaning of NO_ZERO_DATE is not clear for DATETIME +--echo # +SET sql_mode='NO_ZERO_DATE'; +SELECT TIMESTAMP'0000-00-01 10:20:30'; +SELECT TIMESTAMP'0000-00-00 10:20:30'; +SELECT TIMESTAMP'0000-00-00 00:00:00.000001'; +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES ('0000-00-00 10:20:30'); +SELECT a, LEAST(a,'2001-01-01 10:20:30') FROM t1; +DROP TABLE t1; +CREATE TABLE t1 (a DATETIME(6)); +INSERT INTO t1 VALUES ('0000-00-00 00:00:00.000001'); +SELECT a, LEAST(a,'2001-01-01 10:20:30') FROM t1; +DROP TABLE t1; +SELECT STR_TO_DATE('0000-00-00 10:20:30','%Y-%m-%d %h:%i:%s'); +SELECT STR_TO_DATE('0000-00-00 00:00:00.000001','%Y-%m-%d %H:%i:%s.%f'); +SET old_mode=zero_date_time_cast; +SELECT CAST(TIME'10:20:30' AS DATETIME); +SELECT CAST(TIME'00:00:00.000001' AS DATETIME(6)); +SELECT CAST(CAST('10:20:30' AS TIME) AS DATETIME); +SELECT CAST(CAST('00:00:00.000001' AS TIME(6)) AS DATETIME(6)); +SELECT CAST(CAST(TIMESTAMP'0000-00-00 10:20:30' AS TIME) AS DATETIME); +SELECT CAST(CAST(TIMESTAMP'0000-00-00 00:00:00.000001' AS TIME(6)) AS DATETIME(6)); +SET old_mode=DEFAULT; +SET sql_mode=DEFAULT; + +--echo # +--echo # MDEV-8699 Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x' +--echo # +--let $TYPE= DATETIME +--source include/equal_fields_propagation_datetime.inc + +--echo # +--echo # MDEV-8795 Equal expression propagation does not work for temporal literals +--echo # +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-02 00:00:00'); +EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=TIMESTAMP'2001-01-01 00:00:00' AND COALESCE(a)>=TIMESTAMP'2001-01-01 00:00:00'; +DROP TABLE t1; + +--echo # +--echo # MDEV-8875 Wrong metadata for MAX(CAST(time_column AS DATETIME)) +--echo # +SET timestamp=UNIX_TIMESTAMP('2015-01-01 00:00:00'); +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:00'),('00:01:00'); +--disable_ps_protocol +--enable_metadata +SELECT MAX(CAST(a AS DATETIME)) FROM t1; +--disable_metadata +--enable_ps_protocol +CREATE TABLE t2 AS SELECT MAX(CAST(a AS DATETIME)) FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t2; +DROP TABLE t1; +SET timestamp=DEFAULT; + +--echo # +--echo # MDEV-8860 Wrong result for WHERE 2016 < SOME (SELECT CAST(time_column AS DATETIME) FROM t1) +--echo # +SET timestamp=UNIX_TIMESTAMP('2015-01-01 00:00:00'); +CREATE TABLE t1 (a TIME); +INSERT INTO t1 VALUES ('00:00:00'),('00:01:00'); +SELECT 1 FROM t1 WHERE 2016 > SOME (SELECT CAST(a AS DATETIME) FROM t1); +SELECT * FROM t1 WHERE 2016 > CAST(a AS DATETIME); +SELECT 1 FROM t1 WHERE 20160101 > SOME (SELECT CAST(a AS DATETIME) FROM t1); +SELECT * FROM t1 WHERE 20160101 > CAST(a AS DATETIME); +DROP TABLE t1; + +--echo # +--echo # End of 10.1 tests +--echo # + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # +--echo # MDEV-9337 ALTER from DECIMAL and INT to DATETIME returns a wrong result +--echo # +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES (1000); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DATETIME); +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (1000); +INSERT INTO t1 SELECT * FROM t2; +SELECT * FROM t1; +DROP TABLE t1,t2; + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1000); +ALTER TABLE t1 MODIFY a DATETIME; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES (1000.0); +SELECT * FROM t1; +DROP TABLE IF EXISTS t1; + +CREATE TABLE t1 (a DATETIME); +CREATE TABLE t2 (a DECIMAL(4,0)); +INSERT INTO t2 VALUES (1000); +INSERT INTO t1 SELECT * FROM t2; +SELECT * FROM t1; +DROP TABLE t1,t2; + +CREATE TABLE t1 (a DECIMAL(4,0)); +INSERT INTO t1 VALUES (1000); +ALTER TABLE t1 MODIFY a DATETIME; +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # End of 10.2 tests +--echo # + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-11331 Wrong result for INSERT INTO t1 (datetime_field) VALUES (hybrid_function_of_TIME_data_type) +--echo # +SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:00:00'); +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES (TIME'10:20:30'); +INSERT INTO t1 VALUES (COALESCE(TIME'10:20:30')); +INSERT INTO t1 VALUES (LEAST(TIME'10:20:30',TIME'10:20:30')); +SELECT * FROM t1; +DROP TABLE t1; +SET timestamp=DEFAULT; + +--echo # +--echo # MDEV-15310 Range optimizer does not work well for "WHERE temporal_column NOT IN (const_list)" +--echo # + +--echo # +--echo # DATETIME(0) +--echo # + +CREATE TABLE t1 (a DATETIME, filler CHAR(200), KEY(a)); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:02', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:03', 'yes'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:04', 'yes'); +EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01','2001-01-01 23:00:02'); +SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01','2001-01-01 23:00:02'); +DROP TABLE t1; + + +--echo # +--echo # DATETIME(1) +--echo # + +CREATE TABLE t1 (a DATETIME(1), filler CHAR(200), KEY(a)); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:01.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:02.1', 'no'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:03.1', 'yes'); +INSERT INTO t1 VALUES ('2001-01-01 23:00:04.1', 'yes'); +EXPLAIN SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01.1','2001-01-01 23:00:02.1'); +SELECT * FROM t1 WHERE a NOT IN ('2001-01-01 23:00:01.1','2001-01-01 23:00:02.1'); +DROP TABLE t1; + + +--echo # +--echo # End of 10.3 tests +--echo # |