drop table if exists t1,t2;
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))
FROM t1, t2 GROUP BY t1.AFIELD;
DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT))
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)),
DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT))
FROM t1,t2 GROUP BY t1.AFIELD;
DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT))	DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT))
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 INTO t1 VALUES ('abc');
Warnings:
Warning	1366	Incorrect integer value: 'abc' for column '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 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	1	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 '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	1	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 '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 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');
CONVERT_TZ(GREATEST(DATE('2021-00-00'),DATE('2022-00-00')),'+00:00','+7:5')
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 1
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
#
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	0	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
drop table t1;
#
# 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 '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 '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>(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	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>(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>(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>(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>(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>(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>(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>(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>(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 'a' at row 1
Warning	1292	Incorrect datetime value: '1' for column 'b' at row 1
Warning	1292	Incorrect datetime value: '1' for column '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 'a' at row 1
Warning	1292	Incorrect datetime value: '1' for column 'b' at row 1
Warning	1292	Incorrect datetime value: '1' for column '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;
#
# End of 10.1 tests
#