diff options
Diffstat (limited to 'mysql-test/t/type_time.test')
| -rw-r--r-- | mysql-test/t/type_time.test | 415 |
1 files changed, 415 insertions, 0 deletions
diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test index d36c6af65e8..d9d34ac124f 100644 --- a/mysql-test/t/type_time.test +++ b/mysql-test/t/type_time.test @@ -276,3 +276,418 @@ SET timestamp=DEFAULT; --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 # |