drop table if exists t1;
create table t1 (t datetime) ENGINE=InnoDB;
insert ignore into t1 values (101),(691231),(700101),(991231),(10000101),(99991231),(101000000),(691231000000),(700101000000),(991231235959),(10000101000000),(99991231235959),(20030100000000),(20030000000000);
Warnings:
Warning	1265	Data truncated for column 't' at row 13
Warning	1265	Data truncated for column 't' at row 14
select * from t1;
t
2000-01-01 00:00:00
2069-12-31 00:00:00
1970-01-01 00:00:00
1999-12-31 00:00:00
1000-01-01 00:00:00
9999-12-31 00:00:00
2000-01-01 00:00:00
2069-12-31 00:00:00
1970-01-01 00:00:00
1999-12-31 23:59:59
1000-01-01 00:00:00
9999-12-31 23:59:59
0000-00-00 00:00:00
0000-00-00 00:00:00
delete from t1 where t > 0;
optimize table t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	note	Table does not support optimize, doing recreate + analyze instead
test.t1	optimize	status	OK
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
delete from t1;
insert ignore into t1 values("000101"),("691231"),("700101"),("991231"),("00000101"),("00010101"),("99991231"),("00101000000"),("691231000000"),("700101000000"),("991231235959"),("10000101000000"),("99991231235959"),("20030100000000"),("20030000000000");
Warnings:
Warning	1264	Out of range value for column 't' at row 14
Warning	1264	Out of range value for column 't' at row 15
insert into t1 values ("2003-003-03");
insert into t1 values ("20030102T131415"),("2001-01-01T01:01:01"), ("2001-1-1T1:01:01");
select * from t1;
t
2000-01-01 00:00:00
2069-12-31 00:00:00
1970-01-01 00:00:00
1999-12-31 00:00:00
0000-01-01 00:00:00
0001-01-01 00:00:00
9999-12-31 00:00:00
2000-10-10 00:00:00
2069-12-31 00:00:00
1970-01-01 00:00:00
1999-12-31 23:59:59
1000-01-01 00:00:00
9999-12-31 23:59:59
0000-00-00 00:00:00
0000-00-00 00:00:00
2003-03-03 00:00:00
2003-01-02 13:14:15
2001-01-01 01:01:01
2001-01-01 01:01:01
truncate table t1;
insert ignore into t1 values("2003-0303 12:13:14");
Warnings:
Warning	1265	Data truncated for column 't' at row 1
select * from t1;
t
0000-00-00 00:00:00
drop table t1;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
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());
Warnings:
Note	1265	Data truncated for column 'b' at row 1
select date_format(a,"%Y-%m-%d")=b,right(a+0,6)=c+0,a=d+0 from t1;
date_format(a,"%Y-%m-%d")=b	right(a+0,6)=c+0	a=d+0
1	1	1
drop table t1;
SET sql_mode = default;
CREATE TABLE t1 (a datetime not null);
insert ignore into t1 values (0);
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
select * from t1 where a is null;
a
0000-00-00 00:00:00
drop table t1;
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');
id	dt
1	2001-08-14 00:00:00
create index dt on t1 (dt);
select * from t1 where dt > 20021020;
id	dt
4	2003-09-15 01:20:30
select * from t1 ignore index (dt) where dt > 20021020;
id	dt
4	2003-09-15 01:20:30
drop table t1;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
create table t1 (t datetime);
insert into t1 values (20030102030460),(20030102036301),(20030102240401),
(20030132030401),(20031302030401),(100001202030401);
Warnings:
Warning	1265	Data truncated for column 't' at row 1
Warning	1265	Data truncated for column 't' at row 2
Warning	1265	Data truncated for column 't' at row 3
Warning	1265	Data truncated for column 't' at row 4
Warning	1265	Data truncated for column 't' at row 5
Warning	1264	Out of range value for column 't' at row 6
select * from t1;
t
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
delete from t1;
insert 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");
Warnings:
Warning	1265	Data truncated for column 't' at row 1
Warning	1265	Data truncated for column 't' at row 2
Warning	1265	Data truncated for column 't' at row 3
Warning	1265	Data truncated for column 't' at row 4
Warning	1265	Data truncated for column 't' at row 5
Warning	1265	Data truncated for column 't' at row 6
select * from t1;
t
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
delete from t1;
insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer");
Warnings:
Warning	4096	Delimiter ' ' in position 19 in datetime value '0000-00-00 00:00:00 some trailer' at row 1 is superfluous and is deprecated. Please remove.
Warning	1265	Data truncated for column 't' at row 1
Warning	4096	Delimiter ' ' in position 19 in datetime value '2003-01-01 00:00:00 some trailer' at row 2 is superfluous and is deprecated. Please remove.
Warning	1265	Data truncated for column 't' at row 2
select * from t1 order by t;
t
0000-00-00 00:00:00
2003-01-01 00:00:00
drop table t1;
create table t1 (dt datetime);
insert into t1 values ("12-00-00"), ("00-00-00 01:00:00");
insert into t1 values ("00-00-00"), ("00-00-00 00:00:00");
select * from t1;
dt
2012-00-00 00:00:00
2000-00-00 01:00:00
0000-00-00 00:00:00
0000-00-00 00:00:00
drop table t1;
SET sql_mode = default;
select cast('2006-12-05 22:10:10' as datetime) + 0;
cast('2006-12-05 22:10:10' as datetime) + 0
20061205221010
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");
a
2006-06-06 15:55:55
PREPARE s FROM 'SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555")';
EXECUTE s;
a
2006-06-06 15:55:55
DROP PREPARE s;
DROP TABLE t1;
SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6));
CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6))
20060810.000000
SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6));
CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6))
20060810101112.000000
SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6));
CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6))
20060810101112.000014
SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6));
CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6))
101112.000000
set @org_mode=@@sql_mode;
create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03');
Warnings:
Note	1292	Incorrect date value: '1962-03-03 23:33:34' for column 'da' at row 1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `da` date DEFAULT '1962-03-03',
  `dt` datetime DEFAULT '1962-03-03 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
insert into t1 values ();
insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
Warnings:
Note	1292	Incorrect date value: '2007-03-23 13:49:38' for column 'da' at row 1
set @@sql_mode='ansi,traditional';
insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
Warnings:
Note	1292	Incorrect date value: '2007-03-23 13:49:38' for column 'da' at row 1
insert into t1 set dt='2007-03-23 13:49:38',da=dt;
Warnings:
Note	1292	Incorrect date value: '2007-03-23 13:49:38' for column 'da' at row 1
insert into t1 values ('2007-03-32','2007-03-23 13:49:38');
ERROR 22007: Incorrect date value: '2007-03-32' for column 'da' at row 1
select * from t1;
da	dt
1962-03-03	1962-03-03 00:00:00
2007-03-23	2007-03-23 13:49:38
2007-03-23	2007-03-23 13:49:38
2007-03-23	2007-03-23 13:49:38
drop table t1;
create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03');
ERROR 42000: Invalid default value for 'da'
create table t1 (t time default '916:00:00 a');
ERROR 42000: Invalid default value for 't'
set @@sql_mode= @org_mode;
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;
curdate() < now()	f1 < now()	cast(f1 as date) < now()
1	1	1
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';
f1	f3
2001-02-05	2001-02-05 01:01:01
2001-03-10	2001-03-10 01:01:01
2001-04-15	2001-04-15 00:00:00
select f1, f3 from t1 where f1 >= '2001-2-5 0:0:0' and f2 <= '2001-4-15';
f1	f3
2001-02-05	2001-02-05 01:01:01
2001-03-10	2001-03-10 01:01:01
2001-04-15	2001-04-15 00:00:00
select f1, f2 from t1 where if(1, f1, 0) >= f2;
f1	f2
2001-02-05	2001-02-05 00:00:00
2001-03-10	2001-03-09 01:01:01
2001-04-15	2001-04-15 00:00:00
select 1 from dual where cast('2001-1-1 2:3:4' as date) = cast('2001-01-01' as datetime);
1
1
select f1, f2, f1 > f2, f1 = f2, f1 < f2 from t1;
f1	f2	f1 > f2	f1 = f2	f1 < f2
2001-01-01	2001-01-01 01:01:01	0	0	1
2001-02-05	2001-02-05 00:00:00	0	1	0
2001-03-10	2001-03-09 01:01:01	1	0	0
2001-04-15	2001-04-15 00:00:00	0	1	0
2001-05-20	2001-05-20 01:01:01	0	0	1
drop table t1;
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';
f2
2001-02-05 00:00:00
2001-03-09 01:01:01
select f1, f2, f3 from t1 where f1 between f2 and f3;
f1	f2	f3
2001-02-05	2001-02-05 00:00:00	2001-02-05 01:01:01
2001-03-10	2001-03-09 01:01:01	2001-03-10 01:01:01
2001-04-15	2001-04-15 00:00:00	2001-04-15 00:00:00
select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and
cast(f3 as date);
f1	f2	f3
2001-02-05	2001-02-05 00:00:00	2001-02-05 01:01:01
2001-03-10	2001-03-09 01:01:01	2001-03-10 01:01:01
2001-04-15	2001-04-15 00:00:00	2001-04-15 00:00:00
select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01';
f2
2001-01-01 01:01:01
2001-02-05 00:00:00
2001-03-09 01:01:01
select f2, f3 from t1 where '01-03-10' between f2 and f3;
f2	f3
2001-03-09 01:01:01	2001-03-10 01:01:01
select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15";
f2
2001-04-15 00:00:00
SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE();
1
drop table t1;
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');
f1
2001-01-01
2001-01-02
2001-01-03
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');
f2
2001-01-01 00:00:00
2001-02-03 12:34:56
select * from t1,t2 where '01-01-02' in (f1, cast(f2 as date));
f1	f2
2001-01-02	2001-01-01 00:00:00
2001-01-02	2001-02-03 12:34:56
2001-01-02	2002-04-06 11:22:33
select * from t1,t2 where '01-01-01' in (f1, '01-02-03');
f1	f2
2001-01-01	2001-01-01 00:00:00
2001-01-01	2001-02-03 12:34:56
2001-01-01	2002-04-06 11:22:33
select * from t1,t2 where if(1,'01-02-03 12:34:56','') in (f1, f2);
f1	f2
2001-01-01	2001-02-03 12:34:56
2001-01-02	2001-02-03 12:34:56
2001-01-03	2001-02-03 12:34:56
create table t3(f3 varchar(20));
insert into t3 select * from t2;
select * from t2,t3 where f2 in (f3,'03-04-05');
f2	f3
2001-01-01 00:00:00	2001-01-01 00:00:00
2001-02-03 12:34:56	2001-02-03 12:34:56
2002-04-06 11:22:33	2002-04-06 11:22:33
select f1,f2,f3 from t1,t2,t3 where (f1,'1') in ((f2,'1'),(f3,'1')) order by f1,f2,f3;
f1	f2	f3
2001-01-01	2001-01-01 00:00:00	2001-01-01 00:00:00
2001-01-01	2001-01-01 00:00:00	2001-02-03 12:34:56
2001-01-01	2001-01-01 00:00:00	2002-04-06 11:22:33
2001-01-01	2001-02-03 12:34:56	2001-01-01 00:00:00
2001-01-01	2002-04-06 11:22:33	2001-01-01 00:00:00
select f1 from t1 where ('1',f1) in (('1','01-01-01'),('1','2001-1-1 0:0:0'),('1','02-02-02'));
f1
2001-01-01
drop table t1,t2,t3;
select least(cast('01-01-01' as date), '01-01-02');
least(cast('01-01-01' as date), '01-01-02')
2001-01-01
select greatest(cast('01-01-01' as date), '01-01-02');
greatest(cast('01-01-01' as date), '01-01-02')
2001-01-02
select least(cast('01-01-01' as date), '01-01-02') + 0;
least(cast('01-01-01' as date), '01-01-02') + 0
20010101
select greatest(cast('01-01-01' as date), '01-01-02') + 0;
greatest(cast('01-01-01' as date), '01-01-02') + 0
20010102
select least(cast('01-01-01' as datetime), '01-01-02') + 0;
least(cast('01-01-01' as datetime), '01-01-02') + 0
20010101000000
select cast(least(cast('01-01-01' as datetime), '01-01-02') as signed);
cast(least(cast('01-01-01' as datetime), '01-01-02') as signed)
20010101000000
select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2));
cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2))
20010101000000.00
DROP PROCEDURE IF EXISTS test27759 ;
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;|
call test27759();
a	b	a_then_b	b_then_a	c_then_a
2007-04-10	2007-04-11	2007-04-10	2007-04-10	2004-04-09 00:00:00
drop procedure test27759;
create table t1 (f1 date);
insert into t1 values (curdate());
select left(f1,10) = curdate() from t1;
left(f1,10) = curdate()
1
drop table t1;
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;
if(@bug28261 = f1, '', @bug28261:= f1)
2001-01-01
2002-02-02
2001-01-01
2002-02-02
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning	1292	Incorrect datetime value: ''
select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
if(@bug28261 = f1, '', @bug28261:= f1)
2001-01-01
2002-02-02
2001-01-01
2002-02-02
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
if(@bug28261 = f1, '', @bug28261:= f1)
2001-01-01
2002-02-02
2001-01-01
2002-02-02
Warnings:
Warning	1287	Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
drop table t1;
create table t1(f1 datetime);
insert into t1 values('2001-01-01'),('2002-02-02');
select * from t1 where f1 between 20020101 and 20070101000000;
f1
2002-02-02 00:00:00
select * from t1 where f1 between 2002010 and 20070101000000;
f1
2001-01-01 00:00:00
2002-02-02 00:00:00
Warnings:
Warning	1292	Incorrect datetime value: '2002010' for column 'f1' at row 1
select * from t1 where f1 between 20020101 and 2007010100000;
f1
Warnings:
Warning	1292	Incorrect datetime value: '2007010100000' for column 'f1' at row 1
drop table t1;
#
# Bug#27216: functions with parameters of different date types may
#            return wrong type of the result.
#
create table t1 (f1 date, f2 datetime, f3 varchar(20));
create table t2 as select coalesce(f1,f1) as f4 from t1;
desc t2;
Field	Type	Null	Key	Default	Extra
f4	date	YES		NULL	
create table t3 as select coalesce(f1,f2) as f4 from t1;
desc t3;
Field	Type	Null	Key	Default	Extra
f4	datetime	YES		NULL	
create table t4 as select coalesce(f2,f2) as f4 from t1;
desc t4;
Field	Type	Null	Key	Default	Extra
f4	datetime	YES		NULL	
create table t5 as select coalesce(f1,f3) as f4 from t1;
desc t5;
Field	Type	Null	Key	Default	Extra
f4	varchar(20)	YES		NULL	
create table t6 as select coalesce(f2,f3) as f4 from t1;
desc t6;
Field	Type	Null	Key	Default	Extra
f4	varchar(20)	YES		NULL	
create table t7 as select coalesce(makedate(1997,1),f2) as f4 from t1;
desc t7;
Field	Type	Null	Key	Default	Extra
f4	datetime	YES		NULL	
create table t8 as select coalesce(cast('01-01-01' as datetime),f2) as f4
from t1;
desc t8;
Field	Type	Null	Key	Default	Extra
f4	datetime	YES		NULL	
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;
Field	Type	Null	Key	Default	Extra
f4	date	YES		NULL	
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;
Field	Type	Null	Key	Default	Extra
f4	datetime	YES		NULL	
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;
Field	Type	Null	Key	Default	Extra
f4	datetime	YES		NULL	
create table t12 as select least(cast('01-01-01' as datetime),
cast('01-01-01' as date)) as f4 from t1;
desc t12;
Field	Type	Null	Key	Default	Extra
f4	datetime	YES		NULL	
create table t13 as select ifnull(cast('01-01-01' as datetime),
cast('01-01-01' as date)) as f4 from t1;
desc t13;
Field	Type	Null	Key	Default	Extra
f4	datetime	YES		NULL	
drop tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13;
###################################################################
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;
f1
45:44:44
15:44:44
drop table t1;
create table t1 (a tinyint);
insert into t1 values (), (), ();
select sum(a) from t1 group by convert(a, datetime);
sum(a)
NULL
drop table t1;
create table t1 (id int(10) not null, cur_date datetime not null);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
create table t2 (id int(10) not null, cur_date date not null);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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');
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
explain
select * from t1
where id in (select id from t1 as x1 where (t1.cur_date is null));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
2	DEPENDENT SUBQUERY	x1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1276	Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`id`,<exists>(/* select#2 */ select `test`.`x1`.`id` from `test`.`t1` `x1` where ((`test`.`t1`.`cur_date` = TIMESTAMP'0000-00-00 00:00:00') and (<cache>(`test`.`t1`.`id`) = `test`.`x1`.`id`))))
select * from t1
where id in (select id from t1 as x1 where (t1.cur_date is null));
id	cur_date
explain
select * from t2
where id in (select id from t2 as x1 where (t2.cur_date is null));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
2	DEPENDENT SUBQUERY	x1	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1276	Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(/* select#2 */ select `test`.`x1`.`id` from `test`.`t2` `x1` where ((`test`.`t2`.`cur_date` = DATE'0000-00-00') and (<cache>(`test`.`t2`.`id`) = `test`.`x1`.`id`))))
select * from t2
where id in (select id from t2 as x1 where (t2.cur_date is null));
id	cur_date
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
select * from t1
where id in (select id from t1 as x1 where (t1.cur_date is null));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
2	DEPENDENT SUBQUERY	x1	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where
Warnings:
Note	1276	Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`id`,<exists>(/* select#2 */ select `test`.`x1`.`id` from `test`.`t1` `x1` where ((`test`.`t1`.`cur_date` = TIMESTAMP'0000-00-00 00:00:00') and (<cache>(`test`.`t1`.`id`) = `test`.`x1`.`id`))))
select * from t1
where id in (select id from t1 as x1 where (t1.cur_date is null));
id	cur_date
explain
select * from t2
where id in (select id from t2 as x1 where (t2.cur_date is null));
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
2	DEPENDENT SUBQUERY	x1	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where
Warnings:
Note	1276	Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`id`,<exists>(/* select#2 */ select `test`.`x1`.`id` from `test`.`t2` `x1` where ((`test`.`t2`.`cur_date` = DATE'0000-00-00') and (<cache>(`test`.`t2`.`id`) = `test`.`x1`.`id`))))
select * from t2
where id in (select id from t2 as x1 where (t2.cur_date is null));
id	cur_date
set @@optimizer_switch=default;
drop table t1,t2;
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;
n1	n2	n3	n4	n5	n6	n7	n8	n9
0	0	1	NULL	NULL	NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: 'NULL'
Warning	1292	Incorrect datetime value: 'NULL'
Warning	1292	Incorrect datetime value: 'NULL'
Warning	1292	Incorrect datetime value: 'NULL'
Warning	1292	Incorrect datetime value: 'NULL'
Warning	1292	Incorrect datetime value: 'NULL'
Warning	1292	Incorrect datetime value: 'NULL'
Warning	1292	Incorrect datetime value: 'NULL'
Warning	1292	Incorrect datetime value: 'NULL'
Warning	1292	Incorrect datetime value: 'NULL'
Warning	1292	Incorrect datetime value: 'NULL'
Warning	1292	Incorrect datetime value: 'NULL'
Warning	1292	Incorrect datetime value: 'NULL'
End of 5.0 tests
set @org_mode=@@sql_mode;
create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03');
Warnings:
Note	1292	Incorrect date value: '1962-03-03 23:33:34' for column 'da' at row 1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `da` date DEFAULT '1962-03-03',
  `dt` datetime DEFAULT '1962-03-03 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
insert into t1 values ();
insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
Warnings:
Note	1292	Incorrect date value: '2007-03-23 13:49:38' for column 'da' at row 1
set @@sql_mode='ansi,traditional';
insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38');
Warnings:
Note	1292	Incorrect date value: '2007-03-23 13:49:38' for column 'da' at row 1
insert into t1 set dt='2007-03-23 13:49:38',da=dt;
Warnings:
Note	1292	Incorrect date value: '2007-03-23 13:49:38' for column 'da' at row 1
insert into t1 values ('2007-03-32','2007-03-23 13:49:38');
ERROR 22007: Incorrect date value: '2007-03-32' for column 'da' at row 1
select * from t1;
da	dt
1962-03-03	1962-03-03 00:00:00
2007-03-23	2007-03-23 13:49:38
2007-03-23	2007-03-23 13:49:38
2007-03-23	2007-03-23 13:49:38
drop table t1;
create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03');
ERROR 42000: Invalid default value for 'da'
create table t1 (t time default '916:00:00 a');
ERROR 42000: Invalid default value for 't'
set @@sql_mode= @org_mode;
SELECT CAST(CAST('2006-08-10 10:11:12.0123450' AS DATETIME) AS DECIMAL(30,7));
CAST(CAST('2006-08-10 10:11:12.0123450' AS DATETIME) AS DECIMAL(30,7))
20060810101112.0000000
SELECT CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.0123450' AS DATETIME) AS DECIMAL(30,7));
CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.0123450' AS DATETIME) AS DECIMAL(30,7))
20060810101112.0000000
SELECT CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.012345'  AS DATETIME) AS DECIMAL(30,7));
CAST(CAST('00000002006-000008-0000010 000010:0000011:00000012.012345'  AS DATETIME) AS DECIMAL(30,7))
20060810101112.0000000
SELECT CAST(CAST('2008-07-29T10:42:51.1234567' AS DateTime) AS DECIMAL(30,7));
CAST(CAST('2008-07-29T10:42:51.1234567' AS DateTime) AS DECIMAL(30,7))
20080729104251.0000000
# 
# Bug#59173: Failure to handle DATE(TIME) values where Year, Month or
# Day is ZERO 
# 
CREATE TABLE t1 (dt1 DATETIME);
INSERT IGNORE INTO t1 (dt1) VALUES ('0000-00-01 00:00:01');
Warnings:
Warning	1264	Out of range value for column 'dt1' at row 1
DELETE IGNORE  FROM t1 WHERE dt1 = '0000-00-01 00:00:01';
ERROR HY000: Incorrect DATETIME value: '0000-00-01 00:00:01'
# Should be empty
SELECT * FROM t1;
dt1
0000-00-00 00:00:00
DROP TABLE t1;
End of 5.1 tests
#
# Start of 5.5 tests
#
#
# Bug#52849 datetime index not work
#
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 SELECT * FROM t1 FORCE INDEX(attime) WHERE AtTime = '2010-02-22 18:40:07';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	AtTime	AtTime	6	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`Id` AS `Id`,`test`.`t1`.`AtTime` AS `AtTime` from `test`.`t1` FORCE INDEX (`attime`) where (`test`.`t1`.`AtTime` = TIMESTAMP'2010-02-22 18:40:07')
DROP TABLE t1;
SET NAMES latin1;
#
# Bug#56271: Wrong comparison result with STR_TO_DATE function
#
CREATE TABLE t1 (
`year` int(4) NOT NULL,
`month` int(2) NOT NULL
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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');
year	month
2010	3
2010	4
create table t2(f1 datetime primary key);
insert into t2 select STR_TO_DATE(CONCAT_WS('/01/',`month`,`year`), '%m/%d/%Y') from t1;
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
select * from t2 where f1=STR_TO_DATE('4/1/2010', '%m/%d/%Y');
f1
2010-04-01 00:00:00
t2 should be const
explain select * from t2 where f1=STR_TO_DATE('4/1/2010', '%m/%d/%Y');
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	const	PRIMARY	PRIMARY	5	const	1	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select '2010-04-01 00:00:00' AS `f1` from `test`.`t2` where true
DROP TABLE t1,t2;
#
# Bug#57095: Wrongly chosen expression cache type led to a wrong
#            result.
#
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());
b
2010-01-01 00:00:00
2010-01-01 00:00:00
DROP TABLE t1;
#
#
# BUG#12561818: RERUN OF STORED FUNCTION GIVES ERROR 1172: 
#               RESULT CONSISTED OF MORE THAN ONE ROW
#
CREATE TABLE t1 (a DATE NOT NULL, b INT);
INSERT IGNORE INTO t1 VALUES ('0000-00-00',1), ('1999-05-10',2);
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
CREATE TABLE t2 (a DATETIME NOT NULL, b INT);
INSERT IGNORE INTO t2 VALUES ('0000-00-00 00:00:00',1), ('1999-05-10 00:00:00',2);
Warnings:
Warning	1264	Out of range value for column 'a' at row 1

SELECT * FROM t1 WHERE a IS NULL;
a	b
0000-00-00	1
SELECT * FROM t2 WHERE a IS NULL;
a	b
0000-00-00 00:00:00	1
SELECT * FROM t1 LEFT JOIN t1 AS t1_2 ON 1 WHERE t1_2.a IS NULL;
a	b	a	b
0000-00-00	1	0000-00-00	1
1999-05-10	2	0000-00-00	1
SELECT * FROM t2 LEFT JOIN t2 AS t2_2 ON 1 WHERE t2_2.a IS NULL;
a	b	a	b
0000-00-00 00:00:00	1	0000-00-00 00:00:00	1
1999-05-10 00:00:00	2	0000-00-00 00:00:00	1
SELECT * FROM t1 JOIN t1 AS t1_2 ON 1 WHERE t1_2.a IS NULL;
a	b	a	b
0000-00-00	1	0000-00-00	1
1999-05-10	2	0000-00-00	1
SELECT * FROM t2 JOIN t2 AS t2_2 ON 1 WHERE t2_2.a IS NULL;
a	b	a	b
0000-00-00 00:00:00	1	0000-00-00 00:00:00	1
1999-05-10 00:00:00	2	0000-00-00 00:00:00	1

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;
a	b	a	b
0000-00-00	1	0000-00-00	1
1999-05-10	2	0000-00-00	1
EXECUTE stmt1;
a	b	a	b
0000-00-00	1	0000-00-00	1
1999-05-10	2	0000-00-00	1
DEALLOCATE PREPARE stmt1;
DROP TABLE t1,t2;
#
# End of 5.5 tests
#
#
# Start of 5.6 tests
#
#
# WL#946: Testing rounding
#
CREATE TABLE t1 (a DATETIME);
INSERT INTO t1 VALUES ('2001-01-01 10:10:10.9999994'), ('2001-01-01 10:10:10.9999995');
INSERT INTO t1 VALUES (20010101101010.9999994), (20010101101010.9999995);
SELECT * FROM t1;
a
2001-01-01 10:10:11
2001-01-01 10:10:11
2001-01-01 10:10:11
2001-01-01 10:10:11
DROP TABLE t1;
#
# Test for bug#11747847 - 34280: create table fails if NO_ZERO_DATE
# or NO_ZERO_IN_DATE SQL mode is set.
DROP TABLE if EXISTS t1, t2, t3;
SET @org_mode=@@sql_mode;
#Table creation in strict mode
SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
CREATE TABLE t1 (c1 DATETIME DEFAULT 0);
ERROR 42000: Invalid default value for 'c1'
CREATE TABLE t1 (c1 DATETIME DEFAULT '0000-00-00 00:00:00');
ERROR 42000: Invalid default value for 'c1'
SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
CREATE TABLE t1 (c1 DATETIME DEFAULT '2012-02-00 12:12:12');
ERROR 42000: Invalid default value for 'c1'
#Table creation in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
SET @@sql_mode='NO_ZERO_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
CREATE TABLE t1 (c1 DATETIME DEFAULT 0);
Warnings:
Warning	1264	Out of range value for column 'c1' at row 1
CREATE TABLE t2 (c1 DATETIME DEFAULT '0000-00-00 00:00:00');
Warnings:
Warning	1264	Out of range value for column 'c1' at row 1
SET @@sql_mode='NO_ZERO_IN_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
CREATE TABLE t3 (c1 DATETIME DEFAULT '2012-02-00 12:12:12');
Warnings:
Warning	1264	Out of range value for column 'c1' at row 1
DROP TABLE t1, t2, t3;
#Table creation with out any SQL modes
SET @@sql_mode='';
CREATE TABLE t1 (c1 DATETIME DEFAULT 0);
CREATE TABLE t2 (c1 DATETIME DEFAULT '0000-00-00 00:00:00');
CREATE TABLE t3 (c1 DATETIME DEFAULT '2012-02-00 12:12:12');
DROP TABLE t1, t2, t3;
CREATE TABLE t1 (c1 int);
#Alter table in strict mode with NO_ZERO_DATE/NO_ZERO_IN_DATE
SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
ALTER TABLE t1 ADD c2 DATETIME DEFAULT 0;
ERROR 42000: Invalid default value for 'c2'
ALTER TABLE t1 ADD c2 DATETIME DEFAULT '0000-00-00';
ERROR 42000: Invalid default value for 'c2'
SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
ALTER TABLE t1 ADD c2 DATETIME DEFAULT '2012-02-00';
ERROR 42000: Invalid default value for 'c2'
#Alter table in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
SET @@sql_mode='NO_ZERO_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
ALTER TABLE t1 ADD c2 DATETIME DEFAULT 0;
Warnings:
Warning	1264	Out of range value for column 'c2' at row 1
ALTER TABLE t1 ADD c3 DATETIME DEFAULT '0000-00-00';
Warnings:
Warning	1264	Out of range value for column 'c2' at row 1
Warning	1264	Out of range value for column 'c3' at row 1
SET @@sql_mode='NO_ZERO_IN_DATE';
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
ALTER TABLE t1 ADD c4 DATETIME DEFAULT '2012-02-00';
Warnings:
Warning	1264	Out of range value for column 'c4' at row 1
DROP TABLE t1;
CREATE TABLE t1 (c1 INT);
#Alter table with out any SQL modes
SET @@sql_mode='';
ALTER TABLE t1 ADD c2 DATETIME DEFAULT 0;
ALTER TABLE t1 ADD c3 DATETIME DEFAULT '0000-00-00';
ALTER TABLE t1 ADD c4 DATETIME DEFAULT '2012-02-00';
DROP TABLE t1;
SET @@sql_mode= @org_mode;
# END of Test for bug#11747847 - 34280
#
# End of 5.6 tests
#
#
#Bug#25586673: ASSERT IN DA FOR SQL_CMD_UPDATE::UPDATE_SINGLE_TABLE
#Bug#25586959: SERVER CRASH IN SQL_CMD_DELETE::DELETE_FROM_SINGLE_TABLE
#
CREATE TABLE test.t(col_varchar_nokey VARCHAR(1) NOT NULL,
col_time_nokey time NOT NULL,
col_int_nokey INT NOT NULL,
col_datetime_key datetime not null,
key(col_datetime_key)) ENGINE= InnoDB;
DELETE FROM test.t WHERE `col_int_nokey` > `col_int_nokey`
                         AND TIMESTAMP( '18:16:35.025453' ) IS  NULL;
ERROR 22007: Incorrect datetime value: '18:16:35.025453'
UPDATE test.t SET `col_varchar_nokey` = TIMESTAMP(`col_time_nokey`,'00:00:00')
WHERE `col_int_nokey` > `col_int_nokey`
        AND TIMESTAMP('18:16:35.025453') IS  NULL;
ERROR 22007: Incorrect datetime value: '18:16:35.025453'
INSERT INTO t VALUES('x', CURRENT_TIME(), 0, CURRENT_TIMESTAMP());
UPDATE test.t SET `col_datetime_key` = `col_datetime_key` WHERE
`col_datetime_key` <=> FROM_UNIXTIME(1151860736,CONCAT_WS(':','%D','%v'));
ERROR 22007: Incorrect datetime value: '2nd:26' for column 'col_datetime_key' at row 1
DROP TABLE test.t;
#
# Bug#25866743 - ASSERTION FAILED: FALSE IN
#                ITEM_FUNC_GEOMETRY_FROM_TEXT::VAL_STR
# Bug#25866143 CRASH IN ST_ASWKB STRING::COPY
#
do is_ipv4_mapped(bin(maketime(11111,exp(repeat('1',32)),'')));
ERROR 22003: DOUBLE value is out of range in 'exp(repeat('1',32))'
do ifnull((bin_to_uuid(uuid_to_bin(date_format('7088-02-14'
   ,0xabbd95)))),(maketime('4678-07-20 03:51:44.174847',1,'b')));
ERROR HY000: Incorrect string value: '' for function uuid_to_bin
do ucase(insert(maketime(9.565398e+307 ,'',
st_longfromgeohash(('4358-04-12 03:45:08.727399'))),
0xdc0823,6756,release_all_locks()));
ERROR HY000: Incorrect geohash value: '4358-04-12 03:45:08.727399' for function ST_LONGFROMGEOHASH
do soundex(maketime(cast((2530967061762658367) as decimal),
exp(repeat('1',32)),((period_add(579349005,27636))xor (sha(current_user)))));
ERROR 22003: DOUBLE value is out of range in 'exp(repeat('1',32))'
do st_mlinefromtext(
maketime(
now(),
instr(1,pow(15270551309908264860,100)),
31
)
);
ERROR 22003: DOUBLE value is out of range in 'pow(15270551309908264860,100)'
#
# Bug#26147274 ASSERTION `ITEM->MAYBE_NULL' AT MAKE_SORTKEY_FROM_ITEM
#              IN SQL/FILESORT.CC
#
CREATE TABLE t1 (
col_datetime_1_not_null datetime NOT NULL,
col_datetime_2_not_null datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SET sql_mode=NO_ENGINE_SUBSTITUTION;
INSERT INTO t1 VALUES ('0000-00-00 00:00:00.0','2005-12-22 19:53:31.01');
INSERT INTO t1 VALUES ('9999-00-00 00:00:00.0','2005-12-22 19:53:31.01');
SET sql_mode=default;
SELECT * FROM t1 order by col_datetime_1_not_null;
col_datetime_1_not_null	col_datetime_2_not_null
0000-00-00 00:00:00	2005-12-22 19:53:31
9999-00-00 00:00:00	2005-12-22 19:53:31
SELECT LEAST( '2005-06-05',
LEAST( col_datetime_1_not_null , col_datetime_2_not_null ) )
AS c1 FROM t1 ORDER BY c1;
c1
0000-00-00 00:00:00
2005-06-05 00:00:00
DROP TABLE t1;
#
# Tests of date/time literals with time zone displacement.
#
SET time_zone = '+00:00';
SELECT CAST('2019-09-20 10:00:00.999999+00:00' AS DATETIME);
CAST('2019-09-20 10:00:00.999999+00:00' AS DATETIME)
2019-09-20 10:00:01
SELECT CAST('2019-09-20 10:00:00.999999+00:00' AS DATETIME(1));
CAST('2019-09-20 10:00:00.999999+00:00' AS DATETIME(1))
2019-09-20 10:00:01.0
SELECT CAST('2019-09-20 10:00:00.999999+00:00' AS DATETIME(2));
CAST('2019-09-20 10:00:00.999999+00:00' AS DATETIME(2))
2019-09-20 10:00:01.00
SELECT CAST('2019-09-20 10:00:00.999999+00:00' AS DATETIME(3));
CAST('2019-09-20 10:00:00.999999+00:00' AS DATETIME(3))
2019-09-20 10:00:01.000
SELECT CAST('2019-09-20 10:00:00.999999+00:00' AS DATETIME(4));
CAST('2019-09-20 10:00:00.999999+00:00' AS DATETIME(4))
2019-09-20 10:00:01.0000
SELECT CAST('2019-09-20 10:00:00.999999+00:00' AS DATETIME(5));
CAST('2019-09-20 10:00:00.999999+00:00' AS DATETIME(5))
2019-09-20 10:00:01.00000
SELECT CAST('2019-09-20 10:00:00.999999+00:00' AS DATETIME(6));
CAST('2019-09-20 10:00:00.999999+00:00' AS DATETIME(6))
2019-09-20 10:00:00.999999
SELECT CAST('2019-09-20 10:00:00.999999+02:00' AS DATETIME);
CAST('2019-09-20 10:00:00.999999+02:00' AS DATETIME)
2019-09-20 08:00:01
SELECT CAST('2019-09-20 10:00:00.999999+02:00' AS DATETIME(1));
CAST('2019-09-20 10:00:00.999999+02:00' AS DATETIME(1))
2019-09-20 08:00:01.0
SELECT CAST('2019-09-20 10:00:00.999999+02:00' AS DATETIME(2));
CAST('2019-09-20 10:00:00.999999+02:00' AS DATETIME(2))
2019-09-20 08:00:01.00
SELECT CAST('2019-09-20 10:00:00.999999+02:00' AS DATETIME(3));
CAST('2019-09-20 10:00:00.999999+02:00' AS DATETIME(3))
2019-09-20 08:00:01.000
SELECT CAST('2019-09-20 10:00:00.999999+02:00' AS DATETIME(4));
CAST('2019-09-20 10:00:00.999999+02:00' AS DATETIME(4))
2019-09-20 08:00:01.0000
SELECT CAST('2019-09-20 10:00:00.999999+02:00' AS DATETIME(5));
CAST('2019-09-20 10:00:00.999999+02:00' AS DATETIME(5))
2019-09-20 08:00:01.00000
SELECT CAST('2019-09-20 10:00:00.999999+02:00' AS DATETIME(6));
CAST('2019-09-20 10:00:00.999999+02:00' AS DATETIME(6))
2019-09-20 08:00:00.999999
SELECT TIMESTAMP'2019-09-20 10:00:00.999999+02:00 ';
TIMESTAMP'2019-09-20 10:00:00.999999+02:00 '
2019-09-20 08:00:00.999999
SET time_zone = DEFAULT;
# Bug#32915973: Prepare DATE_ADD/DATE_SUB incorrectly returns DATETIME
set @d_str = '2020-01-01';
set @t_str = '01:01:01';
set @dt_str = '2020-01-01 01:01:01';
set @dt_tz = '2020-01-01 01:01:01+03:00';
set @d_int = 20200101;
set @t_int = 010101;
set @dt_int = 20200101010101;
set @d_dec = 20200101.0;
set @t_dec = 010101.0;
set @dt_dec = 20200101010101.0;
set @d_flt = 20200101E0;
set @t_flt = 010101E0;
set @dt_flt = 20200101010101E0;
prepare stmt from "SELECT TIMESTAMP'2020-01-01 01:01:01' = ?";
execute stmt using @d_str;
TIMESTAMP'2020-01-01 01:01:01' = ?
0
prepare stmt from "SELECT TIMESTAMP'2020-01-01 01:01:01' = ?";
execute stmt using @t_str;
TIMESTAMP'2020-01-01 01:01:01' = ?
0
prepare stmt from "SELECT TIMESTAMP'2020-01-01 01:01:01' = ?";
execute stmt using @dt_str;
TIMESTAMP'2020-01-01 01:01:01' = ?
1
prepare stmt from "SELECT TIMESTAMP'2020-01-01 01:01:01' = ?";
execute stmt using @dt_tz;
TIMESTAMP'2020-01-01 01:01:01' = ?
1
prepare stmt from "SELECT TIMESTAMP'2020-01-01 01:01:01' = ?";
execute stmt using @d_int;
TIMESTAMP'2020-01-01 01:01:01' = ?
0
prepare stmt from "SELECT TIMESTAMP'2020-01-01 01:01:01' = ?";
execute stmt using @t_int;
TIMESTAMP'2020-01-01 01:01:01' = ?
0
prepare stmt from "SELECT TIMESTAMP'2020-01-01 01:01:01' = ?";
execute stmt using @dt_int;
TIMESTAMP'2020-01-01 01:01:01' = ?
1
prepare stmt from "SELECT TIMESTAMP'2020-01-01 01:01:01' = ?";
execute stmt using @d_dec;
TIMESTAMP'2020-01-01 01:01:01' = ?
0
prepare stmt from "SELECT TIMESTAMP'2020-01-01 01:01:01' = ?";
execute stmt using @t_dec;
TIMESTAMP'2020-01-01 01:01:01' = ?
0
prepare stmt from "SELECT TIMESTAMP'2020-01-01 01:01:01' = ?";
execute stmt using @dt_dec;
TIMESTAMP'2020-01-01 01:01:01' = ?
1
prepare stmt from "SELECT TIMESTAMP'2020-01-01 01:01:01' = ?";
execute stmt using @d_flt;
TIMESTAMP'2020-01-01 01:01:01' = ?
0
prepare stmt from "SELECT TIMESTAMP'2020-01-01 01:01:01' = ?";
execute stmt using @t_flt;
TIMESTAMP'2020-01-01 01:01:01' = ?
0
prepare stmt from "SELECT TIMESTAMP'2020-01-01 01:01:01' = ?";
execute stmt using @dt_flt;
TIMESTAMP'2020-01-01 01:01:01' = ?
1
deallocate prepare stmt;
# Bug#33788832: wrong result with COALESCE function
CREATE TABLE t1 (
pk INTEGER PRIMARY KEY,
t0 TIME NOT NULL,
t1 TIME NOT NULL,
dt DATETIME
);
INSERT INTO t1 VALUES
(1, TIME'08:00:00', TIME'12:00:00', TIMESTAMP'2020-01-01 00:00:00'),
(2, TIME'12:00:00', TIME'12:00:00', TIMESTAMP'2020-01-01 00:00:00'),
(3, TIME'16:00:00', TIME'12:00:00', TIMESTAMP'2020-01-01 00:00:00');
SELECT pk, t0, t1, dt,
t0 BETWEEN NULL AND t1 AS b1,
t0 NOT BETWEEN NULL AND t1 AS b2,
t0 BETWEEN NULL AND COALESCE(t1, dt) AS b3,
t0 NOT BETWEEN NULL AND COALESCE(t1, dt) AS b4
FROM t1;
pk	t0	t1	dt	b1	b2	b3	b4
1	08:00:00	12:00:00	2020-01-01 00:00:00	NULL	NULL	NULL	NULL
2	12:00:00	12:00:00	2020-01-01 00:00:00	NULL	NULL	NULL	NULL
3	16:00:00	12:00:00	2020-01-01 00:00:00	0	1	0	1
SELECT pk, t0, t1, dt,
t1 BETWEEN t0 AND NULL AS b1,
t1 NOT BETWEEN t0 AND NULL AS b2,
t1 BETWEEN COALESCE(t0, dt) AND NULL AS b3,
t1 NOT BETWEEN COALESCE(t0, dt) AND NULL AS b4
FROM t1;
pk	t0	t1	dt	b1	b2	b3	b4
1	08:00:00	12:00:00	2020-01-01 00:00:00	NULL	NULL	NULL	NULL
2	12:00:00	12:00:00	2020-01-01 00:00:00	NULL	NULL	NULL	NULL
3	16:00:00	12:00:00	2020-01-01 00:00:00	0	1	0	1
DROP TABLE t1;
#
# Bug #33897969 DATE/DATETIME
#               type cover other values type in Item_func_in
#
CREATE TABLE t1 (
pk INTEGER PRIMARY KEY,
col_date date DEFAULT NULL,
col_datetime datetime DEFAULT NULL,
col_varchar VARCHAR(1) DEFAULT NULL,
KEY (col_varchar)
);
INSERT INTO t1 VALUES(5, '2022-02-25', '2022-02-25 11:01:14', 'I');
SELECT * FROM t1 WHERE pk = 5 AND
(col_datetime, col_varchar)
IN (('2022-02-25 11:01:14', 'Y'), ('2022-02-25 11:01:14', 'I'));
pk	col_date	col_datetime	col_varchar
5	2022-02-25	2022-02-25 11:01:14	I
SELECT * FROM t1 WHERE pk = 5 AND
(col_datetime, col_varchar) IN ((@a, @b), (@c, @d));
pk	col_date	col_datetime	col_varchar
SELECT * FROM t1 WHERE pk = 5 AND
(col_date, col_varchar) IN (('2022-02-25', 'Y'), ('2022-02-25', 'I'));
pk	col_date	col_datetime	col_varchar
5	2022-02-25	2022-02-25 11:01:14	I
SELECT * FROM t1 wHERE pk = 5 AND
(col_varchar, col_date) IN (('Y','2022-02-25'), ('I', '2022-02-25'));
pk	col_date	col_datetime	col_varchar
5	2022-02-25	2022-02-25 11:01:14	I
SELECT * FROM t1 wHERE pk = 5 AND
(col_varchar, col_date) IN (('I','2022-02-25'), ('I', '2022-02-25'));
pk	col_date	col_datetime	col_varchar
5	2022-02-25	2022-02-25 11:01:14	I
DROP TABLE t1;
#
# Bug#35080094: MySQL server crash -
#               Assertion CompatibleTypesForIndexLookup failed
#
CREATE TABLE t(a VARCHAR(100) CHARSET latin1, KEY(a));
INSERT INTO t VALUES ('2023-1-1 1:2:3'), ('2023-01-01 01:02:03');
SELECT * FROM t WHERE a = TIMESTAMP'2023-01-01 01:02:03';
a
2023-01-01 01:02:03
2023-1-1 1:2:3
DROP TABLE t;
#
# Bug#35115909: Wrong join result when one of the join columns
#               is compared to a temporal literal
#
CREATE TABLE t (a VARCHAR(30) CHARSET latin1,
b VARCHAR(30) CHARSET latin1);
INSERT INTO t VALUES
('2023-01-01 01:02:03', '2023-01-01 01:02:03'),
('2023-01-01 01:02:03', '2023-1-1 1:2:3'),
('2023-1-1 1:2:3',      '2023-01-01 01:02:03'),
('2023-1-1 1:2:3',      '2023-1-1 1:2:3');
SELECT * FROM t WHERE a = b and b = TIMESTAMP'2023-01-01 01:02:03';
a	b
2023-01-01 01:02:03	2023-01-01 01:02:03
2023-1-1 1:2:3	2023-1-1 1:2:3
DROP TABLE t;
#
# Bug#35173907: Assertion `CompatibleTypesForIndexLookup(eq_item,
#               field, right)' failed
#
CREATE TABLE t(a DATETIME, KEY(a));
INSERT INTO t VALUES ('2023-03-15 00:00:00');
SET timestamp = UNIX_TIMESTAMP('2023-03-15 01:02:03');
SELECT * FROM t WHERE a = CURRENT_TIME;
a
SELECT * FROM t WHERE a = CURRENT_TIMESTAMP;
a
SELECT * FROM t WHERE a = CURRENT_DATE;
a
2023-03-15 00:00:00
SET timestamp = DEFAULT;
DROP TABLE t;