create table t1 (a char(16), b date, c datetime);
insert into t1 SET a='test 2000-01-01', b='2000-01-01', c='2000-01-01';
select * from t1 where c = '2000-01-01';
a	b	c
test 2000-01-01	2000-01-01	2000-01-01 00:00:00
select * from t1 where b = '2000-01-01';
a	b	c
test 2000-01-01	2000-01-01	2000-01-01 00:00:00
drop table t1;
CREATE TABLE t1 (name char(6),cdate date);
INSERT INTO t1 VALUES ('name1','1998-01-01');
INSERT INTO t1 VALUES ('name2','1998-01-01');
INSERT INTO t1 VALUES ('name1','1998-01-02');
INSERT INTO t1 VALUES ('name2','1998-01-02');
CREATE TABLE t2 (cdate date, note char(6));
INSERT INTO t2 VALUES ('1998-01-01','note01');
INSERT INTO t2 VALUES ('1998-01-02','note02');
select name,t1.cdate,note from t1,t2 where t1.cdate=t2.cdate and t1.cdate='1998-01-01';
name	cdate	note
name1	1998-01-01	note01
name2	1998-01-01	note01
drop table t1,t2;
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(1);
SELECT * FROM t1 WHERE LAST_DAY('0000-00-00 00:00:00') IS NULL;
a
1
DROP TABLE t1;
CREATE TABLE t1 ( datum DATE );
INSERT INTO t1 VALUES ( "2000-1-1" );
INSERT INTO t1 VALUES ( "2000-1-2" );
INSERT INTO t1 VALUES ( "2000-1-3" );
INSERT INTO t1 VALUES ( "2000-1-4" );
INSERT INTO t1 VALUES ( "2000-1-5" );
SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND cast("2000-1-4" as date);
datum
2000-01-02
2000-01-03
2000-01-04
SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND datum - INTERVAL 100 DAY;
datum
DROP TABLE t1;
CREATE TABLE t1 (
user_id char(10),
summa int(11),
rdate date
);
INSERT INTO t1 VALUES ('aaa',100,'1998-01-01');
INSERT INTO t1 VALUES ('aaa',200,'1998-01-03');
INSERT INTO t1 VALUES ('bbb',50,'1998-01-02');
INSERT INTO t1 VALUES ('bbb',200,'1998-01-04');
select max(rdate) as s from t1 where rdate < '1998-01-03' having s> "1998-01-01";
s
1998-01-02
select max(rdate) as s from t1 having s="1998-01-04";
s
1998-01-04
select max(rdate+0) as s from t1 having s="19980104";
s
19980104
drop table t1;
create table t1 (date date);
insert into t1 values ("2000-08-10"),("2000-08-11");
select date_add(date,INTERVAL 1 DAY),date_add(date,INTERVAL 1 SECOND) from t1;
date_add(date,INTERVAL 1 DAY)	date_add(date,INTERVAL 1 SECOND)
2000-08-11	2000-08-10 00:00:01
2000-08-12	2000-08-11 00:00:01
drop table t1;
CREATE TABLE t1(AFIELD INT);
INSERT INTO t1 VALUES(1);
CREATE TABLE t2(GMT  VARCHAR(32));
INSERT INTO t2 VALUES('GMT-0800');
SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)) as exp
FROM t1, t2 GROUP BY t1.AFIELD;
exp
Wed, 06 March 2002 10:11:12 GMT-0800
INSERT INTO t1 VALUES(1);
SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)) as e1,
DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)) as e2
FROM t1,t2 GROUP BY t1.AFIELD;
e1	e2
Wed, 06 March 2002 10:11:12 GMT-0800	Wed, 06 March 2002 10:11:12 GMT-0800
drop table t1,t2;
CREATE TABLE t1 (f1 time default NULL, f2 time default NULL);
INSERT INTO t1 (f1, f2) VALUES ('09:00', '12:00');
SELECT DATE_FORMAT(f1, "%l.%i %p") , DATE_FORMAT(f2, "%l.%i %p") FROM t1;
DATE_FORMAT(f1, "%l.%i %p")	DATE_FORMAT(f2, "%l.%i %p")
9.00 AM	12.00 PM
DROP TABLE t1;
CREATE TABLE t1 (f1 DATE);
CREATE TABLE t2 (f2 VARCHAR(8));
CREATE TABLE t3 (f2 CHAR(8));
INSERT INTO t1 VALUES ('1978-11-26');
INSERT INTO t2 SELECT f1+0 FROM t1;
INSERT INTO t2 SELECT f1+0 FROM t1 UNION SELECT f1+0 FROM t1;
INSERT INTO t3 SELECT f1+0 FROM t1;
INSERT INTO t3 SELECT f1+0 FROM t1 UNION SELECT f1+0 FROM t1;
SELECT * FROM t2;
f2
19781126
19781126
SELECT * FROM t3;
f2
19781126
19781126
DROP TABLE t1, t2, t3;
CREATE TABLE t1 (y YEAR);
INSERT IGNORE INTO t1 VALUES ('abc');
Warnings:
Warning	1366	Incorrect integer value: 'abc' for column `test`.`t1`.`y` at row 1
SELECT * FROM t1;
y
0000
DROP TABLE t1;
create table t1(start_date date, end_date date);
insert into t1 values ('2000-01-01','2000-01-02');
select 1 from t1 where cast('2000-01-01 12:01:01' as datetime) between start_date and end_date;
1
1
drop table t1;
select @d:=1111;
@d:=1111
1111
select year(@d), month(@d), day(@d), cast(@d as date);
year(@d)	month(@d)	day(@d)	cast(@d as date)
2000	11	11	2000-11-11
select @d:=011111;
@d:=011111
11111
select year(@d), month(@d), day(@d), cast(@d as date);
year(@d)	month(@d)	day(@d)	cast(@d as date)
2001	11	11	2001-11-11
select @d:=1311;
@d:=1311
1311
select year(@d), month(@d), day(@d), cast(@d as date);
year(@d)	month(@d)	day(@d)	cast(@d as date)
NULL	NULL	NULL	NULL
Warnings:
Warning	1292	Incorrect datetime value: '1311'
Warning	1292	Incorrect datetime value: '1311'
Warning	1292	Incorrect datetime value: '1311'
Warning	1292	Incorrect datetime value: '1311'
create table t1 (d  date , dt datetime , ts timestamp);
insert ignore into t1 values (9912101,9912101,9912101);
Warnings:
Warning	1265	Data truncated for column 'd' at row 1
Warning	1265	Data truncated for column 'dt' at row 1
Warning	1265	Data truncated for column 'ts' at row 1
insert into t1 values (11111,11111,11111);
select * from t1;
d	dt	ts
0000-00-00	0000-00-00 00:00:00	0000-00-00 00:00:00
2001-11-11	2001-11-11 00:00:00	2001-11-11 00:00:00
drop table t1;
CREATE TABLE t1 (
a INT
);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (NULL);
SELECT str_to_date( '', a ) FROM t1;
str_to_date( '', a )
0000-00-00 00:00:00.000000
NULL
DROP TABLE t1;
CREATE TABLE t1 (a DATE, b INT, PRIMARY KEY (a,b));
SET timestamp=UNIX_TIMESTAMP('2016-07-21 14:48:18');
INSERT INTO t1 VALUES (DATE(NOW()), 1);
SELECT COUNT(*) FROM t1 WHERE a = NOW();
COUNT(*)
0
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
INSERT INTO t1 VALUES (DATE(NOW()), 2);
SELECT COUNT(*) FROM t1 WHERE a = NOW();
COUNT(*)
0
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1;
COUNT(*)
0
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
ALTER TABLE t1 DROP PRIMARY KEY;
SELECT COUNT(*) FROM t1 WHERE a = NOW();
COUNT(*)
0
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
SET timestamp=DEFAULT;
DROP TABLE t1;
CREATE TABLE t1 (a DATE);
CREATE TABLE t2 (a DATE);
CREATE INDEX i ON t1 (a);
INSERT INTO t1 VALUES ('0000-00-00'),('0000-00-00');
INSERT INTO t2 VALUES ('0000-00-00'),('0000-00-00');
SELECT * FROM t1 WHERE a = '0000-00-00';
a
0000-00-00
0000-00-00
SELECT * FROM t2 WHERE a = '0000-00-00';
a
0000-00-00
0000-00-00
SET SQL_MODE=TRADITIONAL;
EXPLAIN SELECT * FROM t1 WHERE a = '0000-00-00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	i	i	4	const	2	Using index
SELECT * FROM t1 WHERE a = '0000-00-00';
a
0000-00-00
0000-00-00
SELECT * FROM t2 WHERE a = '0000-00-00';
a
0000-00-00
0000-00-00
INSERT INTO t1 VALUES ('0000-00-00');
ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`a` at row 1
SET SQL_MODE=DEFAULT;
DROP TABLE t1,t2;
CREATE TABLE t1 (a DATE);
CREATE TABLE t2 (a DATE);
CREATE INDEX i ON t1 (a);
INSERT INTO t1 VALUES ('1000-00-00'),('1000-00-00');
INSERT INTO t2 VALUES ('1000-00-00'),('1000-00-00');
SELECT * FROM t1 WHERE a = '1000-00-00';
a
1000-00-00
1000-00-00
SELECT * FROM t2 WHERE a = '1000-00-00';
a
1000-00-00
1000-00-00
SET SQL_MODE=TRADITIONAL;
EXPLAIN SELECT * FROM t1 WHERE a = '1000-00-00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	i	i	4	const	2	Using index
SELECT * FROM t1 WHERE a = '1000-00-00';
a
1000-00-00
1000-00-00
SELECT * FROM t2 WHERE a = '1000-00-00';
a
1000-00-00
1000-00-00
INSERT INTO t1 VALUES ('1000-00-00');
ERROR 22007: Incorrect date value: '1000-00-00' for column `test`.`t1`.`a` at row 1
SET SQL_MODE=DEFAULT;
DROP TABLE t1,t2;
CREATE TABLE t1 SELECT curdate() AS f1;
SELECT hour(f1), minute(f1), second(f1) FROM t1;
hour(f1)	minute(f1)	second(f1)
0	0	0
DROP TABLE t1;
End of 5.0 tests
create table t1 (a date, primary key (a))engine=memory;
insert into t1 values ('0000-01-01'), ('0000-00-01'), ('0001-01-01');
select * from t1 where a between '0000-00-01' and '0000-00-02';
a
0000-00-01
drop table t1;
#
# Bug#50918: Date columns treated differently in Views than in Base
# Tables
#
CREATE TABLE t1 ( the_date DATE, the_time TIME );
INSERT INTO t1 VALUES ( '2010-01-01', '01:01:01' );
SELECT * FROM t1 t11 JOIN t1 t12 ON addtime( t11.the_date, t11.the_time ) = 
addtime( t12.the_date, t12.the_time );
the_date	the_time	the_date	the_time
2010-01-01	01:01:01	2010-01-01	01:01:01
CREATE VIEW v1 AS SELECT * FROM t1;
SELECT * FROM t1 JOIN v1 ON addtime( t1.the_date, t1.the_time ) = 
addtime( v1.the_date, v1.the_time );
the_date	the_time	the_date	the_time
2010-01-01	01:01:01	2010-01-01	01:01:01
SELECT * FROM t1 JOIN v1 ON addtime( t1.the_date, t1.the_time ) = 
addtime( cast(v1.the_date AS DATETIME), v1.the_time );
the_date	the_time	the_date	the_time
2010-01-01	01:01:01	2010-01-01	01:01:01
DROP TABLE t1;
DROP VIEW v1;
#
# Bug#59685 crash in String::length with date types
#
CREATE TABLE t1(a DATE, b YEAR, KEY(a));
INSERT INTO t1 VALUES ('2011-01-01',2011);
SELECT b = (SELECT CONVERT(a, DATE) FROM t1 GROUP BY a) FROM t1;
b = (SELECT CONVERT(a, DATE) FROM t1 GROUP BY a)
0
SELECT b = CONVERT((SELECT CONVERT(a, DATE) FROM t1 GROUP BY a), DATE) FROM t1;
b = CONVERT((SELECT CONVERT(a, DATE) FROM t1 GROUP BY a), DATE)
0
DROP TABLE t1;
End of 5.1 tests
create table t1 (f1 date, key (f1));
insert ignore t1 values ('2010-10-10 15:foobar');
Warnings:
Warning	1265	Data truncated for column 'f1' at row 1
drop table t1;
#
# MDEV-4634 Crash in CONVERT_TZ
#
SELECT CONVERT_TZ(GREATEST(DATE('2021-00-00'),DATE('2022-00-00')),'+00:00','+7:5') as exp;
exp
NULL
Warnings:
Warning	1292	Incorrect datetime value: '2022-00-00'
#
# MDEV-4804 Date comparing false result 
#
SET @h0="20111107";
SET @h1="0";
SET @@timestamp=UNIX_TIMESTAMP('2013-08-19 20:30:00');
SELECT
COALESCE(DATE(@h0),DATE("1901-01-01")) AS h0d,
COALESCE(DATE(@h1),DATE(NOW())) AS h1d,
COALESCE(DATE(@h0),DATE("1901-01-01"))>COALESCE(DATE(@h1),DATE(NOW())) AS compare_h0_gt_h1;
h0d	h1d	compare_h0_gt_h1
2011-11-07	2013-08-19	0
Warnings:
Warning	1292	Incorrect datetime value: '0'
Warning	1292	Incorrect datetime value: '0'
SELECT
DATE('20011107'),
DATE('0'),
COALESCE(DATE('0'),CURRENT_DATE) AS d1,
DATE('20011107')>COALESCE(DATE('0'),CURRENT_DATE) AS cmp;
DATE('20011107')	DATE('0')	d1	cmp
2001-11-07	NULL	2013-08-19	0
Warnings:
Warning	1292	Incorrect datetime value: '0'
Warning	1292	Incorrect datetime value: '0'
Warning	1292	Incorrect datetime value: '0'
SELECT
DATE('20011107'),
DATE('0'),
IFNULL(DATE('0'),CURRENT_DATE) AS d1,
DATE('20011107')>IFNULL(DATE('0'),CURRENT_DATE) AS cmp;
DATE('20011107')	DATE('0')	d1	cmp
2001-11-07	NULL	2013-08-19	0
Warnings:
Warning	1292	Incorrect datetime value: '0'
Warning	1292	Incorrect datetime value: '0'
Warning	1292	Incorrect datetime value: '0'
#
# MDEV-5041 Inserting a TIME with hour>24 into a DATETIME column produces a wrong value
#
SELECT CAST(TIME('-800:20:30') AS DATE);
CAST(TIME('-800:20:30') AS DATE)
2013-07-16
SELECT CAST(TIME('800:20:30') AS DATE);
CAST(TIME('800:20:30') AS DATE)
2013-09-21
SELECT CAST(TIME('33 08:20:30') AS DATE);
CAST(TIME('33 08:20:30') AS DATE)
2013-09-21
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES (TIME('800:20:30'));
Warnings:
Note	1265	Data truncated for column 'a' at row 1
INSERT INTO t1 VALUES (TIME('33 08:20:30'));
Warnings:
Note	1265	Data truncated for column 'a' at row 1
SET SQL_MODE=NO_ZERO_IN_DATE;
INSERT INTO t1 VALUES (TIME('48:20:30'));
Warnings:
Note	1265	Data truncated for column 'a' at row 1
SET SQL_MODE=DEFAULT;
SELECT * FROM t1;
a
2013-09-21
2013-09-21
2013-08-21
DROP TABLE t1;
CREATE PROCEDURE test5041()
BEGIN
DECLARE t TIME;
DECLARE d DATE;
SET t= TIME('800:00:00');
SET d= t;
SELECT d;
END;|
call test5041();
d
2013-09-21
Warnings:
Note	1265	Data truncated for column 'd' at row 0
drop procedure test5041;
SET @@timestamp=DEFAULT;
#
# End of 5.3 tests
#
#
# Bug #33629: last_day function can return null, but has 'not null'
#             flag set for result
#
SELECT 1 FROM (SELECT LAST_DAY('0')) a;
1
1
Warnings:
Warning	1292	Incorrect datetime value: '0'
SELECT 1 FROM (SELECT MAKEDATE(2011,0)) a;
1
1
CREATE TABLE t1 AS
SELECT LAST_DAY('1970-01-01') AS f1,
MAKEDATE(1970, 1)      AS f2;
DESCRIBE t1;
Field	Type	Null	Key	Default	Extra
f1	date	YES		NULL	
f2	date	YES		NULL	
DROP TABLE t1;
#
#
# Bug#57278: Crash on min/max + with date out of range.
#
set @a=(select min(makedate('111','1'))) ;
select @a;
@a
0111-01-01
#
#
# BUG LP:1008487 virtual bool Item_cache::is_expensive(): Assertion `example' failed
#
SET TIMESTAMP=UNIX_TIMESTAMP('2017-01-03 00:00:00');
create table t1(a date,key(a));
insert into t1 values ('2012-01-01'),('2012-02-02');
explain
select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1_0	ref	a	a	4	const	1	Using where; Using index
1	SIMPLE	t2	index	NULL	a	4	NULL	2	Using index; Using join buffer (flat, BNL join)
1	SIMPLE	t1	index	NULL	a	4	NULL	2	Using index; Using join buffer (incremental, BNL join)
select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
1
SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-03 00:00:01');
explain
select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1;
1
drop table t1;
SET TIMESTAMP=DEFAULT;
#
# MDEV-9521 Least function returns 0000-00-00 for null date columns instead of null
# MDEV-9972 Least function retuns date in date time format
#
CREATE TABLE t1 (
id BIGINT NOT NULL,
date_debut DATE NOT NULL,
date_fin DATE DEFAULT NULL);
CREATE TABLE t2(
id BIGINT NOT NULL,
date_debut DATE NOT NULL,
date_fin DATE DEFAULT NULL);
INSERT INTO t1 VALUES (1,'2016-01-01','2016-01-31');
INSERT INTO t1 VALUES (2,'2016-02-01',null);
INSERT INTO t1 VALUES (3,'2016-03-01','2016-03-31');
INSERT INTO t1 VALUES (4,'2016-04-01',null);
INSERT INTO t2 VALUES (1,'2016-01-01','2016-01-31');
INSERT INTO t2 VALUES (2,'2016-02-01','2016-01-28');
INSERT INTO t2 VALUES (3,'2016-03-01',null);
INSERT INTO t2 VALUES (4,'2016-04-01',null);
SELECT t1.id,
GREATEST(t2.date_debut, t1.date_debut) AS date_debut,
LEAST(IFNULL(t2.date_fin, IFNULL(t1.date_fin, NULL)),
IFNULL(t1.date_fin, IFNULL(t2.date_fin, NULL))) AS date_fin
FROM t1 LEFT JOIN t2 ON (t1.id=t2.id);
id	date_debut	date_fin
1	2016-01-01	2016-01-31
2	2016-02-01	2016-01-28
3	2016-03-01	2016-03-31
4	2016-04-01	NULL
DROP TABLE t1,t2;
SELECT
LEAST(COALESCE(DATE(NULL), DATE(NULL)), COALESCE(DATE(NULL), DATE(NULL))) AS d0,
LEAST(IFNULL(DATE(NULL), DATE(NULL)), IFNULL(DATE(NULL), DATE(NULL))) AS d1;
d0	d1
NULL	NULL
#
# MDEV-9511 Valgrind warnings 'Invalid read' in Field_newdate::cmp and Field_newdate::val_str
#
CREATE TABLE t1 (f1 DATE, f2 VARCHAR(1));
INSERT INTO t1 VALUES ('2003-04-27','a'),('1900-01-01','a');
SELECT GROUP_CONCAT(f2, IF(f1, f2, f1), f1 ORDER BY 2,1,3) FROM t1;
GROUP_CONCAT(f2, IF(f1, f2, f1), f1 ORDER BY 2,1,3)
aa1900-01-01,aa2003-04-27
DROP TABLE t1;
#
# Start of 10.1 tests
#
#
# MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
#
SET sql_mode=DEFAULT;
CREATE TABLE t1 (a DATE DEFAULT '0000-00-00');
SET sql_mode=TRADITIONAL;
INSERT INTO t1 VALUES ('0000-00-00');
ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`a` at row 1
INSERT INTO t1 VALUES ();
ERROR 22007: Incorrect default value '0000-00-00' for column 'a'
INSERT INTO t1 VALUES (DEFAULT);
ERROR 22007: Incorrect default value '0000-00-00' for column 'a'
DROP TABLE t1;
SET sql_mode=DEFAULT;
CREATE TABLE t1 (a DATE NOT NULL DEFAULT '0000-00-00', b DATE NOT NULL DEFAULT '0000-00-00');
CREATE TABLE t2 (a DATE NOT NULL DEFAULT '0000-00-00');
INSERT INTO t2 VALUES ('0000-00-00');
SET sql_mode=TRADITIONAL;
INSERT INTO t1 (a) SELECT a FROM t2;
ERROR 22007: Incorrect default value '0000-00-00' for column 'b'
DROP TABLE t1, t2;
SET sql_mode=DEFAULT;
CREATE TABLE t1 (a DATE DEFAULT '0000-00-00', b DATE DEFAULT '0000-00-00');
INSERT INTO t1 VALUES (DEFAULT,DEFAULT);;
SELECT a INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/mdev-7824.txt' FROM t1;
DELETE FROM t1;
SET sql_mode=TRADITIONAL;
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/mdev-7824.txt' INTO TABLE t1 (a);
ERROR 22007: Incorrect default value '0000-00-00' for column 'b'
DROP TABLE t1;
SET sql_mode=DEFAULT;
CREATE TABLE t1 (a DATE DEFAULT '0000-00-00');;
SET sql_mode='NO_ZERO_DATE';
ALTER TABLE t1 ADD b INT NOT NULL;
ERROR 42000: Invalid default value for 'a'
DROP TABLE t1;
SET sql_mode=DEFAULT;
#
# End of MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
#
#
# MDEV-8373 Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field
#
SET sql_mode=DEFAULT;
CREATE TABLE t1 (a DATE);;
INSERT INTO t1 VALUES (0);
SET sql_mode='TRADITIONAL';
CREATE TABLE t2 AS SELECT * FROM t1;
ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t2`.`a` at row 1
DROP TABLE t1;
#
# End of MDEV-8373 Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field
#
#
# MDEV-8722 The patch for MDEV-8688 disabled equal field propagation for temporal column and BETWEEN and IN
#
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a BETWEEN '2001-01-01' AND '2001-01-02';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-01'
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a IN ('2001-01-01','2001-01-02');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-01'
DROP TABLE t1;
#
#     MDEV-8699 Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x'
#
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02');
SELECT * FROM t1 WHERE a='2001-01-01x';
a
2001-01-01
Warnings:
Warning	1292	Truncated incorrect date value: '2001-01-01x'
SELECT * FROM t1 WHERE HEX(a)!='323030312D30312D3031';
a
2001-01-02
SELECT * FROM t1 WHERE HEX(a)!='323030312D30312D3031' AND a='2001-01-01x';
a
Warnings:
Warning	1292	Truncated incorrect date value: '2001-01-01x'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE HEX(a)!='323030312D30312D3031' AND a='2001-01-01x';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Warning	1292	Truncated incorrect date value: '2001-01-01x'
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE HEX(a)!=CONCAT('xx',RAND()) AND a='2001-01-01x';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Warning	1292	Truncated incorrect date value: '2001-01-01x'
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-01' and <cache>(hex(DATE'2001-01-01')) <> concat('xx',rand())
DROP TABLE t1;
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02');
SELECT * FROM t1 WHERE LENGTH(a)=11;
a
SELECT * FROM t1 WHERE LENGTH(a)=11 AND a=' 2001-01-01';
a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=11 AND a=' 2001-01-01';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=11+RAND() AND a=' 2001-01-01';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-01' and <cache>(octet_length(DATE'2001-01-01')) = 11 + rand()
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=11+RAND() AND a=' garbage ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Warning	1292	Truncated incorrect datetime value: ' garbage '
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'0000-00-00' and <cache>(octet_length(DATE'0000-00-00')) = 11 + rand()
DROP TABLE t1;
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-01');
SELECT * FROM t1 WHERE LENGTH(a)=8;
a
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='20010101';
a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='20010101';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8+RAND() AND a='20010101';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-01' and <cache>(octet_length(DATE'2001-01-01')) = 8 + rand()
DROP TABLE t1;
#
# MDEV-8706 Wrong result for SELECT..WHERE time_column=TIMESTAMP'2015-08-30 00:00:00' AND time_column='00:00:00'
#
SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30');
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31');
SELECT * FROM t1 WHERE a=TIME'00:00:00';
a
2015-08-30
SELECT * FROM t1 WHERE LENGTH(a)=10;
a
2015-08-30
2015-08-31
SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIME'00:00:00';
a
2015-08-30
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIME'00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2015-08-30'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2015-08-30' and <cache>(octet_length(DATE'2015-08-30')) = 30 + rand()
DROP TABLE t1;
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31');
SELECT * FROM t1 WHERE a=TIME'24:00:00';
a
2015-08-31
SELECT * FROM t1 WHERE LENGTH(a)=10;
a
2015-08-30
2015-08-31
SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIME'24:00:00';
a
2015-08-31
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIME'24:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2015-08-31'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'24:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2015-08-31' and <cache>(octet_length(DATE'2015-08-31')) = 30 + rand()
DROP TABLE t1;
# In this example '00:00:00' is not recognized as TIME'00:00:00'
# and is treated as DATE'0000-00-00'.
# This may change after MDEV-8322 Distinguish between time and date strings more carefully 
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31');
SELECT * FROM t1 WHERE a='00:00:00';
a
SELECT * FROM t1 WHERE LENGTH(a)=10;
a
2015-08-30
2015-08-31
SELECT * FROM t1 WHERE LENGTH(a)=10 AND a='00:00:00';
a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=10 AND a='00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'0000-00-00'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'0000-00-00' and <cache>(octet_length(DATE'0000-00-00')) = 30 + rand()
DROP TABLE t1;
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31');
SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00';
a
2015-08-30
SELECT * FROM t1 WHERE LENGTH(a)=10;
a
2015-08-30
2015-08-31
SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIMESTAMP'2015-08-30 00:00:00';
a
2015-08-30
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIMESTAMP'2015-08-30 00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2015-08-30 00:00:00'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2015-08-30 00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2015-08-30 00:00:00' and <cache>(octet_length(DATE'2015-08-30')) = 30 + rand()
DROP TABLE t1;
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31');
SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00.1';
a
SELECT * FROM t1 WHERE LENGTH(a)=10;
a
2015-08-30
2015-08-31
SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIMESTAMP'2015-08-30 00:00:00.1';
a
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIMESTAMP'2015-08-30 00:00:00.1';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2015-08-30 00:00:00.1'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2015-08-30 00:00:00.1';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2015-08-30 00:00:00.1' and <cache>(octet_length(DATE'2015-08-30')) = 30 + rand()
DROP TABLE t1;
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31');
SELECT * FROM t1 WHERE a='2015-08-30 00:00:00';
a
2015-08-30
SELECT * FROM t1 WHERE LENGTH(a)=10;
a
2015-08-30
2015-08-31
SELECT * FROM t1 WHERE LENGTH(a)=10 AND a='2015-08-30 00:00:00';
a
2015-08-30
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=10 AND a='2015-08-30 00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2015-08-30'
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='2015-08-30 00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2015-08-30' and <cache>(octet_length(DATE'2015-08-30')) = 30 + rand()
DROP TABLE t1;
SET timestamp=DEFAULT;
#
# MDEV-8795 Equal expression propagation does not work for temporal literals
#
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=DATE'2001-01-01' AND COALESCE(a)>=DATE'2001-01-01';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(`test`.`t1`.`a`) = DATE'2001-01-01'
DROP TABLE t1;
#
# MDEV-8658 DATE(zerofill_column) and DATE(COALESCE(zerofill_column)) return different results
#
CREATE TABLE t1 (a INT(6) ZEROFILL, b DECIMAL(6) ZEROFILL, c DOUBLE(6,0) ZEROFILL);
INSERT INTO t1 VALUES (1,1,1);
INSERT INTO t1 VALUES (10101,10101,10101);
SELECT DATE(a), DATE(b), DATE(c) FROM t1;
DATE(a)	DATE(b)	DATE(c)
NULL	NULL	NULL
2001-01-01	2001-01-01	2001-01-01
Warnings:
Warning	1292	Incorrect datetime value: '1' for column `test`.`t1`.`a` at row 1
Warning	1292	Incorrect datetime value: '1' for column `test`.`t1`.`b` at row 1
Warning	1292	Incorrect datetime value: '1' for column `test`.`t1`.`c` at row 1
SELECT DATE(COALESCE(a)), DATE(COALESCE(b)), DATE(COALESCE(c)) FROM t1;
DATE(COALESCE(a))	DATE(COALESCE(b))	DATE(COALESCE(c))
NULL	NULL	NULL
2001-01-01	2001-01-01	2001-01-01
Warnings:
Warning	1292	Incorrect datetime value: '1'
Warning	1292	Incorrect datetime value: '1'
Warning	1292	Incorrect datetime value: '1'
DROP TABLE t1;
CREATE TABLE t1 (a INT(6), b DECIMAL(6), c DOUBLE(6,0));
INSERT INTO t1 VALUES (1,1,1);
INSERT INTO t1 VALUES (10101,10101,10101);
SELECT DATE(a), DATE(b), DATE(c) FROM t1;
DATE(a)	DATE(b)	DATE(c)
NULL	NULL	NULL
2001-01-01	2001-01-01	2001-01-01
Warnings:
Warning	1292	Incorrect datetime value: '1' for column `test`.`t1`.`a` at row 1
Warning	1292	Incorrect datetime value: '1' for column `test`.`t1`.`b` at row 1
Warning	1292	Incorrect datetime value: '1' for column `test`.`t1`.`c` at row 1
SELECT DATE(COALESCE(a)), DATE(COALESCE(b)), DATE(COALESCE(c)) FROM t1;
DATE(COALESCE(a))	DATE(COALESCE(b))	DATE(COALESCE(c))
NULL	NULL	NULL
2001-01-01	2001-01-01	2001-01-01
Warnings:
Warning	1292	Incorrect datetime value: '1'
Warning	1292	Incorrect datetime value: '1'
Warning	1292	Incorrect datetime value: '1'
DROP TABLE t1;
#
# MDEV-14221 Assertion `0' failed in Item::field_type_for_temporal_comparison
#
CREATE TABLE t1 (d DATE);
INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24');
SELECT d, COUNT(*) FROM t1 GROUP BY d WITH ROLLUP HAVING CASE d WHEN '2017-05-25' THEN 0 ELSE 1 END;
d	COUNT(*)
1985-05-13	1
1989-12-24	1
NULL	2
DROP TABLE t1;
#
# MDEV-17299 Assertion `maybe_null' failed in make_sortkey
#
CREATE TABLE t1 (pk int NOT NULL, d1 date, d2 date NOT NULL);
INSERT INTO t1 values (1,'2018-06-22','2018-06-22'),(2,'2018-07-11','2018-07-11');
CREATE VIEW v1 AS SELECT * FROM t1;
SELECT group_concat(d1/(CASE 'b' WHEN 'j' THEN 'c' END))
FROM v1 GROUP BY greatest(pk, 0, d2);
group_concat(d1/(CASE 'b' WHEN 'j' THEN 'c' END))
NULL
NULL
Warnings:
Warning	1292	Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1
Warning	1292	Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 1
Warning	1292	Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1
Warning	1292	Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1
Warning	1292	Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2
Warning	1292	Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2
CREATE TABLE t2 AS SELECT greatest(pk, 0, d2) AS c1 FROM t1 LIMIT 0;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `c1` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t2;
DROP VIEW v1;
DROP TABLE t1;
#
# MDEV-19699 Server crashes in Item_null_result::field_type upon SELECT with ROLLUP on constant table
#
CREATE TABLE t1 (d DATE) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('1999-11-04');
SELECT d FROM t1 GROUP BY d WITH ROLLUP HAVING d > '1990-01-01';
d
1999-11-04
DROP TABLE t1;
#
# MDEV-20431 GREATEST(int_col,date_col) returns wrong results in a view
#
CREATE TABLE t1 (pk INT NOT NULL, d DATE NOT NULL);
CREATE VIEW v1 AS SELECT * FROM t1;
INSERT INTO t1 VALUES (1,'2018-06-22'),(2,'2018-07-11');
SELECT GREATEST(pk, d) FROM t1;
GREATEST(pk, d)
2018-06-22
2018-07-11
Warnings:
Warning	1292	Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1
Warning	1292	Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2
SELECT GREATEST(pk, d) FROM v1;
GREATEST(pk, d)
2018-06-22
2018-07-11
Warnings:
Warning	1292	Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1
Warning	1292	Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2
DROP VIEW v1;
DROP TABLE t1;
#
# End of 10.1 tests
#
#
# Start of 10.2 tests
#
#
# MDEV-21619 Server crash or assertion failures in my_datetime_to_str
#
CREATE  TABLE t1 (f DATE, KEY(f));
INSERT INTO t1 VALUES ('2020-01-01'),('2020-01-02');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1995.0000000 BETWEEN f AND '2012-12-12';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	f	f	4	NULL	1	100.00	Using where; Using index
Warnings:
Warning	1292	Truncated incorrect datetime value: '1995.0000000'
Note	1003	select `test`.`t1`.`f` AS `f` from `test`.`t1` where '0000-00-00' between `test`.`t1`.`f` and <cache>('2012-12-12')
DROP TABLE t1;
#
# MDEV-27072 Subquery using the ALL keyword on date columns produces a wrong result
#
CREATE TABLE t1 (d DATE);
INSERT INTO t1 VALUES ('2021-11-17'), ('2021-10-17'),('2022-11-17'), ('2020-10-17');
SELECT * FROM t1 WHERE d >= ALL (SELECT * FROM t1);
d
2022-11-17
SELECT * FROM t1 WHERE d <= ALL (SELECT * FROM t1);
d
2020-10-17
DROP TABLE t1;
#
# End of 10.2 tests
#
#
# Start of 10.3 tests
#
#
# MDEV-12721 Wrong execution plan for WHERE (date_field <=> timestamp_expr AND TRUE)
#
CREATE TABLE t1(a DATE,KEY(a));
INSERT INTO t1 VALUES ('2012-01-01'),('2012-02-02');
EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP'2001-01-01 00:00:01' AND TRUE;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP('2001-01-01', '00:00:01') AND TRUE;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP'2001-01-01 00:00:00' AND TRUE;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	4	const	1	Using where; Using index
EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP('2001-01-01', '00:00:00') AND TRUE;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	4	const	1	Using where; Using index
DROP TABLE t1;
#
# End of 10.3 tests
#
#
# Start of 10.4 tests
#
#
# MDEV-11362 True condition elimination does not work for DECIMAL and temporal dynamic SQL parameters
#
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02'),('2001-01-03');
# Equal values
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE'2001:01:01',a)<=>COALESCE(DATE'2001-01-01',a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING DATE'2001-01-01',DATE'2001-01-01';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE''2001-01-01'',a)<=>COALESCE(?,a)' USING DATE'2001-01-01';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(DATE''2001-01-01'',a)' USING DATE'2001-01-01';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
# Equal values but of different data types (should not propagate)
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE'2001:01:01',a)<=>COALESCE(TIMESTAMP'2001-01-01 00:00:00',a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(DATE'2001-01-01',`test`.`t1`.`a`) <=> coalesce(TIMESTAMP'2001-01-01 00:00:00',`test`.`t1`.`a`)
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING DATE'2001-01-01',TIMESTAMP'2001-01-01 00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(<cache>(DATE'2001-01-01'),`test`.`t1`.`a`) <=> coalesce(<cache>(TIMESTAMP'2001-01-01 00:00:00'),`test`.`t1`.`a`)
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE''2001-01-01'',a)<=>COALESCE(?,a)' USING TIMESTAMP'2001-01-01 00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(DATE'2001-01-01',`test`.`t1`.`a`) <=> coalesce(<cache>(TIMESTAMP'2001-01-01 00:00:00'),`test`.`t1`.`a`)
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(DATE''2001-01-01'',a)' USING TIMESTAMP'2001-01-01 00:00:00';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(<cache>(TIMESTAMP'2001-01-01 00:00:00'),`test`.`t1`.`a`) <=> coalesce(DATE'2001-01-01',`test`.`t1`.`a`)
# Not equal values
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE'2001-01-01',a)<=>COALESCE(DATE'2001-01-02',a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(DATE'2001-01-01',`test`.`t1`.`a`) <=> coalesce(DATE'2001-01-02',`test`.`t1`.`a`)
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING DATE'2001-01-01',DATE'2001-01-02';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(<cache>(DATE'2001-01-01'),`test`.`t1`.`a`) <=> coalesce(<cache>(DATE'2001-01-02'),`test`.`t1`.`a`)
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE''2001-01-01'',a)<=>COALESCE(?,a)' USING DATE'2001-01-02';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(DATE'2001-01-01',`test`.`t1`.`a`) <=> coalesce(<cache>(DATE'2001-01-02'),`test`.`t1`.`a`)
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(DATE''2001-01-01'',a)' USING DATE'2001-01-02';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(<cache>(DATE'2001-01-02'),`test`.`t1`.`a`) <=> coalesce(DATE'2001-01-01',`test`.`t1`.`a`)
DROP TABLE t1;
#
# MDEV-15406 NO_ZERO_IN_DATE erroneously affects how CAST(AS DATE) warns about fractional digit truncation
#
SET sql_mode='';
CREATE TABLE t1 (a DATE);
SELECT CAST(20061108.01 AS DATE);
CAST(20061108.01 AS DATE)
2006-11-08
Warnings:
Note	1292	Truncated incorrect date value: '20061108.01'
INSERT INTO t1 VALUES (20061108.01);
Warnings:
Note	1265	Data truncated for column 'a' at row 1
DROP TABLE t1;
SET sql_mode=NO_ZERO_IN_DATE;
SELECT CAST(20061108.01 AS DATE);
CAST(20061108.01 AS DATE)
2006-11-08
Warnings:
Note	1292	Truncated incorrect date value: '20061108.01'
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES (20061108.01);
Warnings:
Note	1265	Data truncated for column 'a' at row 1
DROP TABLE t1;
SET sql_mode=DEFAULT;
#
# MDEV-17216 Assertion `!dt->fraction_remainder(decimals())' failed in Field_temporal_with_date::store_TIME_with_warning
#
SET sql_mode='';
CREATE TABLE t1 (i1 date );
CREATE TABLE t2 (i2 int unsigned );
INSERT INTO t2 VALUES (0);
INSERT INTO t1 SELECT * FROM t2;
DROP TABLE t1,t2;
SET sql_mode=DEFAULT;
#
# MDEV-19301 Assertion `!is_valid_datetime() || fraction_remainder(((item->decimals) < (6) ? (item->decimals) : (6))) == 0' failed in Datetime_truncation_not_needed::Datetime_truncation_not_needed
#
SELECT NULLIF(CAST(1012.5 AS DATE), 1);
NULLIF(CAST(1012.5 AS DATE), 1)
2000-10-12
Warnings:
Note	1292	Truncated incorrect date value: '1012.5'
Warning	1292	Truncated incorrect datetime value: '1'
Note	1292	Truncated incorrect date value: '1012.5'
SELECT CAST(1012.5 AS DATE) * 1.0;
CAST(1012.5 AS DATE) * 1.0
20001012.0
Warnings:
Note	1292	Truncated incorrect date value: '1012.5'
#
# MDEV-23351 Rounding functions return wrong data types for DATE input
#
CREATE TABLE t1 (a date);
CREATE TABLE t2 AS SELECT FLOOR(a), CEIL(a),ROUND(a),TRUNCATE(a,0) FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `FLOOR(a)` int(8) unsigned DEFAULT NULL,
  `CEIL(a)` int(8) unsigned DEFAULT NULL,
  `ROUND(a)` int(8) unsigned DEFAULT NULL,
  `TRUNCATE(a,0)` int(8) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t2,t1;
#
# MDEV-23388 Assertion `args[0]->decimals == 0' failed in Item_func_round::fix_arg_int
#
SELECT ROUND(GREATEST('1', CAST('2020-12-12' AS DATE)));
ROUND(GREATEST('1', CAST('2020-12-12' AS DATE)))
20201212
Warnings:
Warning	1292	Truncated incorrect datetime value: '1'
SELECT GREATEST('1', CAST('2020-12-12' AS DATE));
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					GREATEST('1', CAST('2020-12-12' AS DATE))	10	10	10	Y	128	0	63
GREATEST('1', CAST('2020-12-12' AS DATE))
2020-12-12
Warnings:
Warning	1292	Truncated incorrect datetime value: '1'
CREATE TABLE t1 (c_date DATE NOT NULL, c_int INT NOT NULL);
CREATE TABLE t2 AS SELECT
GREATEST(c_date,c_date),
GREATEST(c_date,c_int),
GREATEST(c_int,c_date)
FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `GREATEST(c_date,c_date)` date NOT NULL,
  `GREATEST(c_date,c_int)` date DEFAULT NULL,
  `GREATEST(c_int,c_date)` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t2;
DROP TABLE t1;
#
# MDEV-29890 Update with inner join false row count result
#
set sql_mode='NO_ZERO_DATE';
create table t1 (a1 bigint primary key, a2 date not null, a3 bigint not null);
create table t2 (b1 bigint primary key);
insert into t2 (b1) values (1);
insert into t1 (a1, a2, a3) values (1, current_date, 1),( 2, current_date, 1);
update t1 inner join t2 on t1.a3 = t2.b1 set t1.a2 = t1.a2 + interval 1 day;
select row_count();
row_count()
2
set sql_mode='';
alter table t1 modify a2 date not null default '0000-00-00';
set sql_mode='NO_ZERO_DATE';
update t1 inner join t2 on t1.a3 = t2.b1 set t1.a2 = t1.a2 + interval 1 day;
drop table t1, t2;
set sql_mode=default;
#
# End of 10.4 tests
#
#
# Start of 10.6 tests
#
#
# MDEV-32203 Raise notes when an index cannot be used on data type mismatch
#
SET note_verbosity=unusable_keys;
CREATE TABLE t1 (indexed_col DATE, KEY(indexed_col));
FOR i IN 1..31
DO
INSERT INTO t1 VALUES (MAKEDATE(2023, i));
END FOR;
$$
SELECT * FROM t1 WHERE indexed_col=20230101;
indexed_col
2023-01-01
SELECT * FROM t1 WHERE indexed_col=20230101102030;
indexed_col
SELECT * FROM t1 WHERE indexed_col=20230101102030.1;
indexed_col
SELECT * FROM t1 WHERE indexed_col=20230101102030.1e0;
indexed_col
SELECT * FROM t1 WHERE indexed_col='10:20:30';
indexed_col
Warnings:
Warning	1292	Truncated incorrect datetime value: '10:20:30'
SELECT * FROM t1 WHERE indexed_col='2001-01-01';
indexed_col
SELECT * FROM t1 WHERE indexed_col='2001-01-01 10:20:30';
indexed_col
SELECT * FROM t1 WHERE indexed_col=DATE'2001-01-01';
indexed_col
SELECT * FROM t1 WHERE indexed_col=TIME'10:20:30';
indexed_col
SELECT * FROM t1 WHERE indexed_col=TIMESTAMP'2001-01-01 10:20:30';
indexed_col
SELECT * FROM t1 WHERE indexed_col=0x00;
indexed_col
Warnings:
Warning	1292	Truncated incorrect datetime value: '\x00'
SELECT * FROM t1 WHERE indexed_col=_utf8mb3'0' COLLATE utf8mb3_bin;
indexed_col
Warnings:
Warning	1292	Truncated incorrect datetime value: '0'
CREATE TABLE t2 (not_indexed_col INT);
INSERT INTO t2 VALUES (20230101),(20230102);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
2023-01-01	20230101
2023-01-02	20230102
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col INT UNSIGNED);
INSERT INTO t2 VALUES (20230101),(20230102);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
2023-01-01	20230101
2023-01-02	20230102
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col BIGINT);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col BIGINT UNSIGNED);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DECIMAL(30,6));
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col FLOAT);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DOUBLE);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DATE);
INSERT INTO t2 VALUES ('2023-01-01'),('2023-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
2023-01-01	2023-01-01
2023-01-02	2023-01-02
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DATETIME);
INSERT INTO t2 VALUES ('2023-01-01 00:00:00'),('2023-01-01 00:00:01');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
2023-01-01	2023-01-01 00:00:00
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col TIMESTAMP);
INSERT INTO t2 VALUES ('2023-01-01 00:00:00'),('2023-01-01 00:00:01');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
2023-01-01	2023-01-01 00:00:00
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARBINARY(32));
INSERT INTO t2 VALUES (0x30),(0x31);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
Warnings:
Warning	1292	Incorrect datetime value: '0'
Warning	1292	Incorrect datetime value: '1'
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARCHAR(32)) CHARACTER SET latin1;
INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARCHAR(32) CHARACTER SET utf8mb3);
INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
DROP TABLE t1;
SET note_verbosity=DEFAULT;