path: root/mysql-test/t/type_time.test

#
# testing of the TIME column type
#

--disable_warnings
drop table if exists t1;
--enable_warnings

create table t1 (t time);
insert into t1 values("10:22:33"),("12:34:56.78"),(10),(1234),(123456.78),(1234559.99),("1"),("1:23"),("1:23:45"), ("10.22"), ("-10  1:22:33.45"),("20 10:22:33"),("1999-02-03 20:33:34");
insert t1 values (30),(1230),("1230"),("12:30"),("12:30:35"),("1 12:30:31.32");
select * from t1;
# Test wrong values
insert into t1 values("10.22.22"),(1234567),(123456789),(123456789.10),("10 22:22"),("12.45a");
select * from t1;
drop table t1;

create table t1 (t time);
insert into t1 values ('09:00:00'),('13:00:00'),('19:38:34'), ('13:00:00'),('09:00:00'),('09:00:00'),('13:00:00'),('13:00:00'),('13:00:00'),('09:00:00');
select t, time_to_sec(t),sec_to_time(time_to_sec(t)) from t1;
select sec_to_time(time_to_sec(t)) from t1;
drop table t1;

#
# BUG #12440: Incorrect processing of time values containing
# long fraction part and/or large exponent part.
#
# These must return normal result:
# ##########################################################
# To be uncommented after fix BUG #15805
# ##########################################################
# SELECT CAST(235959.123456 AS TIME);
# SELECT CAST(0.235959123456e+6 AS TIME);
# SELECT CAST(235959123456e-6 AS TIME);
# These must cut fraction part and produce warning:
# SELECT CAST(235959.1234567 AS TIME);
# SELECT CAST(0.2359591234567e6 AS TIME);
# This must return NULL and produce warning:
# SELECT CAST(0.2359591234567e+30 AS TIME);
# ##########################################################

--echo End of 4.1 tests

#
# Bug#29555: Comparing time values as strings may lead to a wrong result.
#
select cast('100:55:50' as time) < cast('24:00:00' as time);
select cast('100:55:50' as time) < cast('024:00:00' as time);
select cast('300:55:50' as time) < cast('240:00:00' as time);
select cast('100:55:50' as time) > cast('24:00:00' as time);
select cast('100:55:50' as time) > cast('024:00:00' as time);
select cast('300:55:50' as time) > cast('240:00:00' as time);
create table t1 (f1 time);
insert into t1 values ('24:00:00');
select cast('24:00:00' as time) = (select f1 from t1);
drop table t1;

#
# Bug#29739: Incorrect time comparison in BETWEEN.
#
create table t1(f1 time, f2 time);
insert into t1 values('20:00:00','150:00:00');
select 1 from t1 where cast('100:00:00' as time) between f1 and f2;
drop table t1;

#
# Bug#29729: Wrong conversion error led to an empty result set.
#
CREATE TABLE  t1 (
  f2 date NOT NULL,
  f3 int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (f3, f2)
);
insert into t1 values('2007-07-01', 1);
insert into t1 values('2007-07-01', 2);
insert into t1 values('2007-07-02', 1);
insert into t1 values('2007-07-02', 2);
SELECT sum(f3) FROM t1 where f2='2007-07-01 00:00:00' group by f2;
drop table t1;


--echo #
--echo # Bug #44792: valgrind warning when casting from time to time
--echo #

CREATE TABLE t1 (c TIME);
INSERT INTO t1 VALUES ('0:00:00');
SELECT CAST(c AS TIME) FROM t1;
DROP TABLE t1;

--echo End of 5.0 tests

--echo #
--echo # Bug#53942 valgrind warnings with timestamp() function and incomplete datetime values
--echo #

SET timestamp=unix_timestamp('2001-02-03 10:20:30');
CREATE TABLE t1(f1 TIME);
INSERT INTO t1 VALUES ('23:38:57');
SELECT TIMESTAMP(f1,'1') FROM t1;
DROP TABLE t1;
SET timestamp=DEFAULT;

--echo End of 5.1 tests

create table t1 (a time);
insert t1 values (-131415);
select * from t1;
drop table t1;

#
# lp:731229 Different results depending on table access method with TIME column and CURDATE()
#
create table t1 (f1 time , f2 varchar(5), key(f1));
insert into t1 values ('00:20:01','a'),('00:20:03','b');
select * from t1 force key (f1) where f1 < curdate();
select * from t1 ignore key (f1) where f1 < curdate();
select * from t1 force key (f1) where f1 > curdate();
select * from t1 ignore key (f1) where f1 > curdate();
delete from t1;
insert into t1 values ('-00:20:01','a'),('-00:20:03','b');
select * from t1 force key (f1) where f1 < curdate();
select * from t1 ignore key (f1) where f1 < curdate();
drop table t1;

#
# comparison of time and datetime:
#
create table t1(f1 time);
insert into t1 values ('23:38:57');
select f1, f1 = '2010-10-11 23:38:57' from t1;
drop table t1;

--echo #
--echo # MDEV-4634 Crash in CONVERT_TZ
--echo #
SET timestamp=unix_timestamp('2001-02-03 10:20:30');
SELECT CONVERT_TZ(GREATEST(TIME('00:00:00'),TIME('00:00:00')),'+00:00','+7:5');
SET timestamp=DEFAULT;

--echo #
--echo # MDEV-4652 Wrong result for CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00')))
--echo #
SELECT CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00')));
SELECT CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00')));

--echo #
--echo # MDEV-4859 Wrong value and data type of "SELECT MAX(time_column) + 1 FROM t1"
--echo #
CREATE TABLE t1 (t0 TIME);
INSERT INTO t1 VALUES ('10:10:10');
SELECT MAX(t0)+1 FROM t1;
CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1;
SELECT * FROM t2;
SHOW COLUMNS FROM t2;
DROP TABLE t2,t1;

CREATE TABLE t1 (t0 TIME);
INSERT INTO t1 VALUES ('10:10:10');
SELECT MAX(t0)+1.1 FROM t1;
CREATE TABLE t2 AS SELECT MAX(t0)+1.1 FROM t1;
SELECT * FROM t2;
SHOW COLUMNS FROM t2;
DROP TABLE t2,t1;

CREATE TABLE t1 (t0 TIME);
INSERT INTO t1 VALUES ('10:10:10');
SELECT MAX(t0)+1e0 FROM t1;
CREATE TABLE t2 AS SELECT MAX(t0)+1e0 FROM t1;
SELECT * FROM t2;
SHOW COLUMNS FROM t2;
DROP TABLE t2,t1;

CREATE TABLE t1 (t1 TIME(1));
INSERT INTO t1 VALUES ('10:10:10');
SELECT MAX(t1)+1 FROM t1;
CREATE TABLE t2 AS SELECT MAX(t1)+1 FROM t1;
SELECT * FROM t2;
SHOW COLUMNS FROM t2;
DROP TABLE t2,t1;

CREATE TABLE t1 (t0 DATETIME);
INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
SELECT MAX(t0)+1 FROM t1;
CREATE TABLE t2 AS SELECT MAX(t0)+1 FROM t1;
SELECT * FROM t2;
SHOW COLUMNS FROM t2;
DROP TABLE t2,t1;

CREATE TABLE t1 (t0 DATETIME);
INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
SELECT MAX(t0)+1.1 FROM t1;
CREATE TABLE t2 AS SELECT MAX(t0)+1.1 FROM t1;
SELECT * FROM t2;
SHOW COLUMNS FROM t2;
DROP TABLE t2,t1;

CREATE TABLE t1 (t0 DATETIME);
INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
SELECT MAX(t0)+1e0 FROM t1;
CREATE TABLE t2 AS SELECT MAX(t0)+1e0 FROM t1;
SELECT * FROM t2;
SHOW COLUMNS FROM t2;
DROP TABLE t2,t1;

CREATE TABLE t1 (t1 DATETIME(1));
INSERT INTO t1 VALUES ('2001-01-01 10:10:10');
SELECT MAX(t1)+1 FROM t1;
CREATE TABLE t2 AS SELECT MAX(t1)+1 FROM t1;
SELECT * FROM t2;
SHOW COLUMNS FROM t2;
DROP TABLE t2,t1;

--echo #
--echo # MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column
--echo #
SET sql_mode=traditional;
CREATE TABLE t1 (a TIME(6));
--error ER_TRUNCATED_WRONG_VALUE
INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED)); 
SET sql_mode=DEFAULT;
INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED)); 
SELECT * FROM t1;
DROP TABLE t1;
SELECT TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED);

--echo #
--echo # MDEV-6100 No warning on CAST(9000000 AS TIME)
--echo #
SELECT CAST(9000000 AS TIME);

--echo #
--echo # End of 5.3 tests
--echo #

#
# Bug#42664 - Sign ignored for TIME types when not comparing as longlong
#

CREATE TABLE t1 (f1 TIME);
INSERT INTO t1 VALUES ('24:00:00');
SELECT      '24:00:00' = (SELECT f1 FROM t1);
SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1);
SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1);
TRUNCATE t1;
INSERT INTO t1 VALUES ('-24:00:00');
SELECT CAST('24:00:00' AS TIME) = (SELECT f1 FROM t1);
SELECT CAST('-24:00:00' AS TIME) = (SELECT f1 FROM t1);
SELECT '-24:00:00' = (SELECT f1 FROM t1);
DROP TABLE t1;

--echo #
--echo # MDEV-6592 Assertion `ltime->day == 0' failed with TIMESTAMP, MAKETIME
--echo #
CREATE TABLE t1 (d DATE, c VARCHAR(10), KEY(d))  engine=myisam;
INSERT INTO t1 VALUES ('2008-10-02','2008-10-02'), ('2008-10-02','2008-10-02');
SELECT * FROM t1 WHERE TIMESTAMP(c,'02:04:42') AND d <=> MAKETIME(97,0,7);
DROP TABLE t1;

--echo #
--echo # End of 5.5 tests
--echo #

--echo #
--echo # Start of 10.0 tests
--echo #

--echo #
--echo # MDEV-6102 Comparison between TIME and DATETIME does not use CURRENT_DATE
--echo #
SET timestamp=UNIX_TIMESTAMP('2014-04-14 01:02:03');
SELECT CAST(TIME'10:20:30' AS DATETIME), TIME'10:20:30'=TIMESTAMP'2014-04-14 10:20:30' AS cmp;
SET timestamp=DEFAULT;

--echo #
--echo # End of 10.0 tests
--echo #

--echo #
--echo # Start of 10.1 tests
--echo #

--echo #
--echo # MDEV-8699 Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x'
--echo #

--echo # Trailing garbage in string literals

CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='00:00:00x';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='00:00:00x';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='00:00:00x';
DROP TABLE t1;

--echo # Trailing fractional digits in string literals
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='00:00:00.000000';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='00:00:00.000000';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='00:00:00.000000';
DROP TABLE t1;

--echo # Trailing fractional digits in temporal literals
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'00:00:00.000000';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'00:00:00.000000';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'00:00:00.000000';
DROP TABLE t1;

--echo # Trailing fractional digits in temporal literals, same precision
CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'00:00:00.000000';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'00:00:00.000000';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'00:00:00.000000';
DROP TABLE t1;

--echo # Leading spaces in string literals
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=' 00:00:00';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=' 00:00:00';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=' 00:00:00';
DROP TABLE t1;

--echo # Numeric format in string literals
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='000000';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='000000';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='000000';
DROP TABLE t1;

--echo # 
--echo # MDEV-8766 Wrong result for SELECT..WHERE LENGTH(time_column)=8 AND time_column=TIMESTAMP'2001-01-01 10:20:31'
--echo #
SET timestamp=UNIX_TIMESTAMP('2001-01-01 01:02:03');

--echo # TIMESTAMP literal with the same scale, ok to propagate
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:31';
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31';
DROP TABLE t1;

--echo # TIMESTAMP literal with a bigger scale and fractional second truncation
--echo # Ok to propagate with precision truncation
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:31.123';
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.123';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.123';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31.123';
DROP TABLE t1;

--echo # TIMESTAMP literal with a bigger scale and no fractional second truncation
--echo # Ok to propagate
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:31.000';
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.000';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.000';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31.000';
DROP TABLE t1;

--echo # TIMESTAMP literal with a smaller scale
--echo # Ok to propagate
CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 10:20:31.123';
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.123';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIMESTAMP'2001-01-01 10:20:31.123';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 10:20:31.123';
DROP TABLE t1;


--echo # TIME literal with a bigger scale and fractional second truncation
--echo # Ok to propagate with precision truncation
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIME'10:20:31.123';
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.123';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.123';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'10:20:31.123';
DROP TABLE t1;

--echo # TIME literal with a bigger scale and no fractional second truncation
--echo # Ok to propagate
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIME'10:20:31.000';
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.000';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.000';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'10:20:31.000';
DROP TABLE t1;

--echo # TIME literal with a smaller scale
--echo # Ok to propagate
CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a=TIME'10:20:31.123';
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.123';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a=TIME'10:20:31.123';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'10:20:31.123';
DROP TABLE t1;


--echo # TIME-alike string literal with a bigger scale and fractional second truncation
--echo # Ok to propagate with precision truncation
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a='10:20:31.123';
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.123';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.123';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='10:20:31.123';
DROP TABLE t1;

--echo # TIME-alike string literal with a bigger scale and no fractional second truncation
--echo # Ok to propagate
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a='10:20:31.000';
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.000';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.000';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='10:20:31.000';
DROP TABLE t1;

--echo # TIME-alike string literal with a smaller scale
--echo # Ok to propagate
CREATE TABLE t1 (a TIME(6));
INSERT INTO t1 VALUES ('10:20:31'),('-10:20:31');
SELECT * FROM t1 WHERE a='10:20:31.123';
SELECT * FROM t1 WHERE LENGTH(a)=8;
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.123';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='10:20:31.123';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='10:20:31.123';
DROP TABLE t1;

SET timestamp=DEFAULT;


SET @@old_mode=zero_date_time_cast;
--echo # TIMESTAMP literal, old mode
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('0000-00-00 10:20:30'),('0000-00-00 10:20:31');
INSERT INTO t1 VALUES ('0000-00-01 10:20:30'),('0000-00-01 10:20:31');
INSERT INTO t1 VALUES ('31 10:20:30'),('32 10:20:30'),('33 10:20:30'),('34 10:20:30');
SELECT * FROM t1;

--echo # Old mode, TIMESTAMP literal, zero YYYYMMDD, Ok to propagate
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-00 10:20:30';
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-00 10:20:30' AND LENGTH(a)=8;
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-00 10:20:30' AND LENGTH(a)=8;
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-00 10:20:30' AND LENGTH(a)=30+RAND();

--echo # Old mode, TIMESTAMP literal, zon-zero YYYYMMDD, no propagation
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30';
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30' AND LENGTH(a)=8;
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30' AND LENGTH(a)=8;
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=TIMESTAMP'0000-00-01 10:20:30' AND LENGTH(a)=30+RAND();

--echo # Old mode, TIMESTAMP-alike string literal, zero YYYYMMDD, Ok to propagate
SELECT * FROM t1 WHERE a='0000-00-00 10:20:30';
SELECT * FROM t1 WHERE a='0000-00-00 10:20:30' AND LENGTH(a)=8;
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a='0000-00-00 10:20:30' AND LENGTH(a)=8;
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a='0000-00-00 10:20:30' AND LENGTH(a)=30+RAND();

--echo # Old mode, TIMESTAMP-alike literal, zon-zero YYYYMMDD, no propagation
SELECT * FROM t1 WHERE a='0000-00-01 10:20:30';
SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8;
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=8;
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a='0000-00-01 10:20:30' AND LENGTH(a)=30+RAND();

DROP TABLE t1;

SET @@old_mode=DEFAULT;

--echo #
--echo # MDEV-8706 Wrong result for SELECT..WHERE time_column=TIMESTAMP'2015-08-30 00:00:00' AND time_column='00:00:00'
--echo #
SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30');
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00';
SELECT * FROM t1 WHERE a='00:00:00';
SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00' AND a='00:00:00';
SELECT * FROM t1 WHERE a>=TIMESTAMP'2015-08-30 00:00:00' AND a='00:00:00';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a>=TIMESTAMP'2015-08-30 00:00:00' AND a='00:00:00';
DROP TABLE t1;

# TIMESTAMP literal with fractional seconds
SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30');
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE DATE(a)<=TIMESTAMP'2015-08-30 00:00:00.1' AND a='00:00:00';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE DATE(a)<=TIMESTAMP'2015-08-30 00:00:00.1' AND a='00:00:00';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE TIMESTAMP('2015-08-08',a+RAND())<=TIMESTAMP'2015-08-30 00:00:00.1' AND a='00:00:00';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE TIMESTAMP('2015-08-08',a+RAND())<=TIMESTAMP'2015-08-30 00:00:00.1' AND a='00:00:00.1';
DROP TABLE t1;

# DATE literal
SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30');
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:00'),('00:00:01');
SELECT * FROM t1 WHERE DATE(a)<=DATE'2015-08-30' AND a='00:00:00';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE DATE(a)<=DATE'2015-08-30' AND a='00:00:00';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE TIMESTAMP('2015-08-08',a+RAND())<=DATE'2015-08-30' AND a='00:00:00';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE TIMESTAMP('2015-08-08',a+RAND())<=DATE'2015-08-30' AND a='00:00:00.1';
DROP TABLE t1;

SET timestamp=DEFAULT;

--echo #
--echo # MDEV-8795 Equal expression propagation does not work for temporal literals
--echo #
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:01'),('00:00:02');
EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:01' AND COALESCE(a)>=TIME'00:00:01';
DROP TABLE t1;

--echo #
--echo # MDEV-8793 Wrong result set for SELECT ... WHERE COALESCE(time_column)=TIME('00:00:00') AND COALESCE(time_column)=DATE('2015-09-11')
--echo #
SET timestamp=UNIX_TIMESTAMP('2015-09-11 20:20:20');
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES('10:20:30'),('00:00:00');
SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00');
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11');

--echo # TIME cast + DATE cast
SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11');
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE('2015-09-11');

--echo # TIME cast + DATE literal
SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE'2015-09-11';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=TIME('00:00:00') AND COALESCE(a)=DATE'2015-09-11';

--echo # TIME literal + DATE cast
SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE('2015-09-11');
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE('2015-09-11');

--echo # TIME literal + DATE literal
SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=TIME'00:00:00' AND COALESCE(a)=DATE'2015-09-11';

--echo # TIME-alike string literal + DATE cast
SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE('2015-09-11');
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE('2015-09-11');

--echo # TIME-alike string literal + DATE literal
SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE'2015-09-11';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)='00:00:00' AND COALESCE(a)=DATE'2015-09-11';

--echo # TIME-alike integer literal + DATE cast
SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE('2015-09-11');
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE('2015-09-11');

--echo # TIME-alike integer literal + DATE literal
SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE'2015-09-11';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=0 AND COALESCE(a)=DATE'2015-09-11';


### Now test the opposite order of the two equality expressions

--echo # DATE cast + TIME cast
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME('00:00:00');
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME('00:00:00');

--echo # DATE cast + TIME literal
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME'00:00:00';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=TIME'00:00:00';

--echo # DATE cast + TIME-alike string literal
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)='00:00:00';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)='00:00:00';

--echo # DATE cast + TIME-alike integer literal
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=0;
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=DATE('2015-09-11') AND COALESCE(a)=0;

--echo # DATE literal + TIME cast
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME('00:00:00');
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME('00:00:00');

--echo # DATE literal + TIME literal
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME'00:00:00';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=TIME'00:00:00';

--echo # DATE literal + TIME-alike string literal
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)='00:00:00';
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)='00:00:00';

--echo # DATE literal + TIME-alike integer literal
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=0;
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2015-09-11' AND COALESCE(a)=0;

DROP TABLE t1;
SET timestamp=DEFAULT;

--echo #
--echo # MDEV-8814 Wrong result for WHERE datetime_column > TIME('00:00:00')
--echo #
CREATE TABLE t1 (a DATETIME);
INSERT INTO t1 VALUES ('2000-09-12 00:00:00'), ('2007-04-25 05:08:49');
SELECT * FROM t1 WHERE a>TIME'00:00:00';
SELECT * FROM t1 WHERE a>TIME('00:00:00');
DROP TABLE t1;

--echo #
--echo # End of 10.1 tests
--echo #