Edit File: temporal_literal.result
DROP TABLE IF EXISTS t1, t2; SET NAMES latin1; # # Testing DATE literals # SELECT DATE'xxxx'; ERROR HY000: Incorrect DATE value: 'xxxx' SELECT DATE'01'; ERROR HY000: Incorrect DATE value: '01' SELECT DATE'01-01'; ERROR HY000: Incorrect DATE value: '01-01' SELECT DATE'2001'; ERROR HY000: Incorrect DATE value: '2001' SELECT DATE'2001-01'; ERROR HY000: Incorrect DATE value: '2001-01' SELECT DATE'2001-00-00'; DATE'2001-00-00' 2001-00-00 SELECT DATE'2001-01-00'; DATE'2001-01-00' 2001-01-00 SELECT DATE'0000-00-00'; DATE'0000-00-00' 0000-00-00 SELECT DATE'2001-01-01 00:00:00'; ERROR HY000: Incorrect DATE value: '2001-01-01 00:00:00' SELECT DATE'01:01:01'; DATE'01:01:01' 2001-01-01 SELECT DATE'01-01-01'; DATE'01-01-01' 2001-01-01 SELECT DATE'2010-01-01'; DATE'2010-01-01' 2010-01-01 SELECT DATE '2010-01-01'; DATE '2010-01-01' 2010-01-01 CREATE TABLE t1 AS SELECT DATE'2010-01-01'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `DATE'2010-01-01'` date NOT NULL DEFAULT '0000-00-00' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 AS SELECT {d'2001-01-01'}, {d'2001-01-01 10:10:10'}; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `{d'2001-01-01'}` date NOT NULL DEFAULT '0000-00-00', `2001-01-01 10:10:10` varchar(19) NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; EXPLAIN EXTENDED SELECT {d'2010-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 No tables used Warnings: Note 1003 /* select#1 */ select DATE'2010-01-01' AS `{d'2010-01-01'}` EXPLAIN EXTENDED SELECT DATE'2010-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 No tables used Warnings: Note 1003 /* select#1 */ select DATE'2010-01-01' AS `DATE'2010-01-01'` # # Testing DATE literals in non-default sql_mode # SET sql_mode=no_zero_in_date; Warnings: Warning 1681 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release. SELECT DATE'2001-00-00'; ERROR HY000: Incorrect DATE value: '2001-00-00' SELECT DATE'2001-01-00'; ERROR HY000: Incorrect DATE value: '2001-01-00' SELECT DATE'0000-00-00'; DATE'0000-00-00' 0000-00-00 SET sql_mode=no_zero_date; Warnings: Warning 1681 'NO_ZERO_DATE' is deprecated and will be removed in a future release. SELECT DATE'0000-00-00'; ERROR HY000: Incorrect DATE value: '0000-00-00' SET sql_mode=default; # # Testing TIME literals # SELECT TIME'xxxx'; ERROR HY000: Incorrect TIME value: 'xxxx' SELECT TIME'900:00:00'; ERROR HY000: Incorrect TIME value: '900:00:00' SELECT TIME'-900:00:00'; ERROR HY000: Incorrect TIME value: '-900:00:00' SELECT TIME'1 24:00:00'; TIME'1 24:00:00' 48:00:00 SELECT TIME'30 24:00:00'; TIME'30 24:00:00' 744:00:00 SELECT TIME'0000-00-00 00:00:00'; ERROR HY000: Incorrect TIME value: '0000-00-00 00:00:00' SELECT TIME'40 24:00:00'; ERROR HY000: Incorrect TIME value: '40 24:00:00' SELECT TIME'10'; TIME'10' 00:00:10 SELECT TIME'10:10'; TIME'10:10' 10:10:00 SELECT TIME'10:11.12'; TIME'10:11.12' 10:11:00.12 SELECT TIME'10:10:10'; TIME'10:10:10' 10:10:10 SELECT TIME'10:10:10.'; TIME'10:10:10.' 10:10:10 SELECT TIME'10:10:10.1'; TIME'10:10:10.1' 10:10:10.1 SELECT TIME'10:10:10.12'; TIME'10:10:10.12' 10:10:10.12 SELECT TIME'10:10:10.123'; TIME'10:10:10.123' 10:10:10.123 SELECT TIME'10:10:10.1234'; TIME'10:10:10.1234' 10:10:10.1234 SELECT TIME'10:10:10.12345'; TIME'10:10:10.12345' 10:10:10.12345 SELECT TIME'10:10:10.123456'; TIME'10:10:10.123456' 10:10:10.123456 SELECT TIME'-10:00:00'; TIME'-10:00:00' -10:00:00 SELECT TIME '10:11:12'; TIME '10:11:12' 10:11:12 CREATE TABLE t1 AS SELECT TIME'10:10:10', TIME'10:10:10.', TIME'10:10:10.1', TIME'10:10:10.12', TIME'10:10:10.123', TIME'10:10:10.1234', TIME'10:10:10.12345', TIME'10:10:10.123456'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `TIME'10:10:10'` time NOT NULL DEFAULT '00:00:00', `TIME'10:10:10.'` time NOT NULL DEFAULT '00:00:00', `TIME'10:10:10.1'` time(1) NOT NULL DEFAULT '00:00:00.0', `TIME'10:10:10.12'` time(2) NOT NULL DEFAULT '00:00:00.00', `TIME'10:10:10.123'` time(3) NOT NULL DEFAULT '00:00:00.000', `TIME'10:10:10.1234'` time(4) NOT NULL DEFAULT '00:00:00.0000', `TIME'10:10:10.12345'` time(5) NOT NULL DEFAULT '00:00:00.00000', `TIME'10:10:10.123456'` time(6) NOT NULL DEFAULT '00:00:00.000000' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 AS SELECT {t'10:10:10'}, {t'10:10:10.'}, {t'10:10:10.123456'}, {t'2001-01-01'}; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `{t'10:10:10'}` time NOT NULL DEFAULT '00:00:00', `{t'10:10:10.'}` time NOT NULL DEFAULT '00:00:00', `{t'10:10:10.123456'}` time(6) NOT NULL DEFAULT '00:00:00.000000', `2001-01-01` varchar(10) NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; EXPLAIN EXTENDED SELECT {t'10: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 No tables used Warnings: Note 1003 /* select#1 */ select TIME'10:01:01' AS `{t'10:01:01'}` EXPLAIN EXTENDED SELECT TIME'10: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 No tables used Warnings: Note 1003 /* select#1 */ select TIME'10:01:01' AS `TIME'10:01:01'` # # Testing TIMESTAMP literals # SELECT TIMESTAMP'xxxx'; ERROR HY000: Incorrect DATETIME value: 'xxxx' SELECT TIMESTAMP'2010'; ERROR HY000: Incorrect DATETIME value: '2010' SELECT TIMESTAMP'2010-01'; ERROR HY000: Incorrect DATETIME value: '2010-01' SELECT TIMESTAMP'2010-01-01'; ERROR HY000: Incorrect DATETIME value: '2010-01-01' SELECT TIMESTAMP'2010-01-01 00'; TIMESTAMP'2010-01-01 00' 2010-01-01 00:00:00 SELECT TIMESTAMP'2010-01-01 00:01'; TIMESTAMP'2010-01-01 00:01' 2010-01-01 00:01:00 SELECT TIMESTAMP'2010-01-01 10:10:10'; TIMESTAMP'2010-01-01 10:10:10' 2010-01-01 10:10:10 SELECT TIMESTAMP'2010-01-01 10:10:10.'; TIMESTAMP'2010-01-01 10:10:10.' 2010-01-01 10:10:10 SELECT TIMESTAMP'2010-01-01 10:10:10.1'; TIMESTAMP'2010-01-01 10:10:10.1' 2010-01-01 10:10:10.1 SELECT TIMESTAMP'2010-01-01 10:10:10.12'; TIMESTAMP'2010-01-01 10:10:10.12' 2010-01-01 10:10:10.12 SELECT TIMESTAMP'2010-01-01 10:10:10.123'; TIMESTAMP'2010-01-01 10:10:10.123' 2010-01-01 10:10:10.123 SELECT TIMESTAMP'2010-01-01 10:10:10.1234'; TIMESTAMP'2010-01-01 10:10:10.1234' 2010-01-01 10:10:10.1234 SELECT TIMESTAMP'2010-01-01 10:10:10.12345'; TIMESTAMP'2010-01-01 10:10:10.12345' 2010-01-01 10:10:10.12345 SELECT TIMESTAMP'2010-01-01 10:10:10.123456'; TIMESTAMP'2010-01-01 10:10:10.123456' 2010-01-01 10:10:10.123456 SELECT TIMESTAMP '2010-01-01 10:20:30'; TIMESTAMP '2010-01-01 10:20:30' 2010-01-01 10:20:30 CREATE TABLE t1 AS SELECT TIMESTAMP'2010-01-01 10:10:10', TIMESTAMP'2010-01-01 10:10:10.', TIMESTAMP'2010-01-01 10:10:10.1', TIMESTAMP'2010-01-01 10:10:10.12', TIMESTAMP'2010-01-01 10:10:10.123', TIMESTAMP'2010-01-01 10:10:10.1234', TIMESTAMP'2010-01-01 10:10:10.12345', TIMESTAMP'2010-01-01 10:10:10.123456'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `TIMESTAMP'2010-01-01 10:10:10'` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `TIMESTAMP'2010-01-01 10:10:10.'` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `TIMESTAMP'2010-01-01 10:10:10.1'` datetime(1) NOT NULL DEFAULT '0000-00-00 00:00:00.0', `TIMESTAMP'2010-01-01 10:10:10.12'` datetime(2) NOT NULL DEFAULT '0000-00-00 00:00:00.00', `TIMESTAMP'2010-01-01 10:10:10.123'` datetime(3) NOT NULL DEFAULT '0000-00-00 00:00:00.000', `TIMESTAMP'2010-01-01 10:10:10.1234'` datetime(4) NOT NULL DEFAULT '0000-00-00 00:00:00.0000', `TIMESTAMP'2010-01-01 10:10:10.12345'` datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00.00000', `TIMESTAMP'2010-01-01 10:10:10.123456'` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 AS SELECT {ts'2001-01-01 10:10:10'}, {ts'2001-01-01 10:10:10.'}, {ts'2001-01-01 10:10:10.123456'}, {ts'2001-01-01'}; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `{ts'2001-01-01 10:10:10'}` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `{ts'2001-01-01 10:10:10.'}` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `{ts'2001-01-01 10:10:10.123456'}` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', `2001-01-01` varchar(10) NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; EXPLAIN EXTENDED SELECT {ts'2010-01-01 10:10:10'}; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select TIMESTAMP'2010-01-01 10:10:10' AS `{ts'2010-01-01 10:10:10'}` EXPLAIN EXTENDED SELECT TIMESTAMP'2010-01-01 10:10:10'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select TIMESTAMP'2010-01-01 10:10:10' AS `TIMESTAMP'2010-01-01 10:10:10'` # # Testing nanosecond rounding for TIMESTAMP literals with bad dates # SELECT TIMESTAMP'2001-00-00 00:00:00.999999'; TIMESTAMP'2001-00-00 00:00:00.999999' 2001-00-00 00:00:00.999999 SELECT TIMESTAMP'2001-00-01 00:00:00.999999'; TIMESTAMP'2001-00-01 00:00:00.999999' 2001-00-01 00:00:00.999999 SELECT TIMESTAMP'2001-01-00 00:00:00.999999'; TIMESTAMP'2001-01-00 00:00:00.999999' 2001-01-00 00:00:00.999999 SELECT TIMESTAMP'2001-00-00 00:00:00.9999999'; ERROR HY000: Incorrect DATETIME value: '2001-00-00 00:00:00.9999999' SELECT TIMESTAMP'2001-00-01 00:00:00.9999999'; ERROR HY000: Incorrect DATETIME value: '2001-00-01 00:00:00.9999999' SELECT TIMESTAMP'2001-01-00 00:00:00.9999999'; ERROR HY000: Incorrect DATETIME value: '2001-01-00 00:00:00.9999999' # # String literal with bad dates and nanoseconds to DATETIME(N) # CREATE TABLE t1 (a DATETIME(6)); INSERT INTO t1 VALUES ('2001-00-00 00:00:00.9999999'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 INSERT INTO t1 VALUES ('2001-00-01 00:00:00.9999999'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 INSERT INTO t1 VALUES ('2001-01-00 00:00:00.9999999'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 SELECT * FROM t1; a 0000-00-00 00:00:00.000000 0000-00-00 00:00:00.000000 0000-00-00 00:00:00.000000 DROP TABLE t1; CREATE TABLE t1 (a DATETIME(5)); INSERT INTO t1 VALUES ('2001-00-00 00:00:00.9999999'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 INSERT INTO t1 VALUES ('2001-00-01 00:00:00.9999999'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 INSERT INTO t1 VALUES ('2001-01-00 00:00:00.9999999'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 SELECT * FROM t1; a 0000-00-00 00:00:00.00000 0000-00-00 00:00:00.00000 0000-00-00 00:00:00.00000 DROP TABLE t1; CREATE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ('2001-00-00 00:00:00.9999999'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 INSERT INTO t1 VALUES ('2001-00-01 00:00:00.9999999'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 INSERT INTO t1 VALUES ('2001-01-00 00:00:00.9999999'); Warnings: Warning 1264 Out of range value for column 'a' at row 1 SELECT * FROM t1; a 0000-00-00 00:00:00 0000-00-00 00:00:00 0000-00-00 00:00:00 DROP TABLE t1; # # Testing Item_date_literal::eq # CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2001-01-01'),('2003-01-01'); SELECT * FROM t1 WHERE a BETWEEN DATE'2001-01-01' AND DATE'2002-01-01'; a 2001-01-01 SELECT DATE'2001-01-01' FROM t1 GROUP BY DATE'2001-01-01'; DATE'2001-01-01' 2001-01-01 DROP TABLE t1; # # BUG #16812821: INCONSISTANT RESULTS WHEN ODBC DATE FORMAT IS USED # CREATE TABLE t1(c1 INT, c2 DATE, c3 TIME, c4 TIMESTAMP); CREATE TABLE t2(c11 INT, C12 INT); INSERT INTO t1 VALUES (1, '2014-01-02', '01:01:01', '2014-01-02 01:01:01'), (2, '2014-01-04', '01:01:01', '2014-01-02 01:01:01'); INSERT INTO t2 VALUES (3, 4), (5, 6); SELECT * FROM t1 LEFT JOIN t2 ON c1 = c11 WHERE c2 BETWEEN {d'2014-01-01'} AND {d'2014-01-05'}; c1 c2 c3 c4 c11 C12 1 2014-01-02 01:01:01 2014-01-02 01:01:01 NULL NULL 2 2014-01-04 01:01:01 2014-01-02 01:01:01 NULL NULL SELECT * FROM t1 LEFT JOIN t2 ON c1 = c11 WHERE c3 BETWEEN {t'01:01:01'} AND {t'01:01:05'}; c1 c2 c3 c4 c11 C12 1 2014-01-02 01:01:01 2014-01-02 01:01:01 NULL NULL 2 2014-01-04 01:01:01 2014-01-02 01:01:01 NULL NULL SELECT * FROM t1 LEFT JOIN t2 ON c1 = c11 WHERE c4 BETWEEN {ts'2014-01-01 01:01:01'} AND {ts'2014-01-05 01:01:01'}; c1 c2 c3 c4 c11 C12 1 2014-01-02 01:01:01 2014-01-02 01:01:01 NULL NULL 2 2014-01-04 01:01:01 2014-01-02 01:01:01 NULL NULL SELECT * FROM t1 LEFT JOIN t2 ON c1 = c11 WHERE c2 BETWEEN DATE'2014-01-01' AND DATE'2014-01-05'; c1 c2 c3 c4 c11 C12 1 2014-01-02 01:01:01 2014-01-02 01:01:01 NULL NULL 2 2014-01-04 01:01:01 2014-01-02 01:01:01 NULL NULL SELECT * FROM t1 LEFT JOIN t2 ON c1 = c11 WHERE c3 BETWEEN TIME'01:01:01' AND TIME'01:01:05'; c1 c2 c3 c4 c11 C12 1 2014-01-02 01:01:01 2014-01-02 01:01:01 NULL NULL 2 2014-01-04 01:01:01 2014-01-02 01:01:01 NULL NULL SELECT * FROM t1 LEFT JOIN t2 ON c1 = c11 WHERE c4 BETWEEN TIMESTAMP'2014-01-01 01:01:01' AND TIMESTAMP'2014-01-05 01:01:01'; c1 c2 c3 c4 c11 C12 1 2014-01-02 01:01:01 2014-01-02 01:01:01 NULL NULL 2 2014-01-04 01:01:01 2014-01-02 01:01:01 NULL NULL DROP TABLE t1, t2;