diff options
Diffstat (limited to 'mysql-test/main/temporal_literal.result')
| -rw-r--r-- | mysql-test/main/temporal_literal.result | 643 |
1 files changed, 643 insertions, 0 deletions
diff --git a/mysql-test/main/temporal_literal.result b/mysql-test/main/temporal_literal.result new file mode 100644 index 00000000000..f4f43fe9ba3 --- /dev/null +++ b/mysql-test/main/temporal_literal.result @@ -0,0 +1,643 @@ +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 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +{d'2001-01-01'}, +{ 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, + `{ d '2001-01-01' }` date NOT NULL, + `2001-01-01 10:10:10` varchar(19) NOT NULL +) 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 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 DATE'2010-01-01' AS `DATE'2010-01-01'` +# +# Testing DATE literals in non-default sql_mode +# +SET sql_mode=no_zero_in_date; +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; +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, + `TIME'10:10:10.'` time NOT NULL, + `TIME'10:10:10.1'` time(1) NOT NULL, + `TIME'10:10:10.12'` time(2) NOT NULL, + `TIME'10:10:10.123'` time(3) NOT NULL, + `TIME'10:10:10.1234'` time(4) NOT NULL, + `TIME'10:10:10.12345'` time(5) NOT NULL, + `TIME'10:10:10.123456'` time(6) NOT NULL +) 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.'}, +{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, + `{ t '10:10:10' }` time NOT NULL, + `{t'10:10:10.'}` time NOT NULL, + `{t'10:10:10.123456'}` time(6) NOT NULL, + `2001-01-01` varchar(10) NOT NULL +) 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 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 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, + `TIMESTAMP'2010-01-01 10:10:10.'` datetime NOT NULL, + `TIMESTAMP'2010-01-01 10:10:10.1'` datetime(1) NOT NULL, + `TIMESTAMP'2010-01-01 10:10:10.12'` datetime(2) NOT NULL, + `TIMESTAMP'2010-01-01 10:10:10.123'` datetime(3) NOT NULL, + `TIMESTAMP'2010-01-01 10:10:10.1234'` datetime(4) NOT NULL, + `TIMESTAMP'2010-01-01 10:10:10.12345'` datetime(5) NOT NULL, + `TIMESTAMP'2010-01-01 10:10:10.123456'` datetime(6) NOT NULL +) 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.'}, +{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, + `{ ts '2001-01-01 10:10:10' }` datetime NOT NULL, + `{ts'2001-01-01 10:10:10.'}` datetime NOT NULL, + `{ts'2001-01-01 10:10:10.123456'}` datetime(6) NOT NULL, + `2001-01-01` varchar(10) NOT NULL +) 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 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 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'; +TIMESTAMP'2001-00-00 00:00:00.9999999' +2001-00-00 00:00:00.999999 +Warnings: +Note 1292 Truncated incorrect datetime value: '2001-00-00 00:00:00.9999999' +SELECT TIMESTAMP'2001-00-01 00:00:00.9999999'; +TIMESTAMP'2001-00-01 00:00:00.9999999' +2001-00-01 00:00:00.999999 +Warnings: +Note 1292 Truncated incorrect datetime value: '2001-00-01 00:00:00.9999999' +SELECT TIMESTAMP'2001-01-00 00:00:00.9999999'; +TIMESTAMP'2001-01-00 00:00:00.9999999' +2001-01-00 00:00:00.999999 +Warnings: +Note 1292 Truncated 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: +Note 1265 Data truncated for column 'a' at row 1 +INSERT INTO t1 VALUES ('2001-00-01 00:00:00.9999999'); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +INSERT INTO t1 VALUES ('2001-01-00 00:00:00.9999999'); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +SELECT * FROM t1; +a +2001-00-00 00:00:00.999999 +2001-00-01 00:00:00.999999 +2001-01-00 00:00:00.999999 +DROP TABLE t1; +CREATE TABLE t1 (a DATETIME(5)); +INSERT INTO t1 VALUES ('2001-00-00 00:00:00.9999999'); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +INSERT INTO t1 VALUES ('2001-00-01 00:00:00.9999999'); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +INSERT INTO t1 VALUES ('2001-01-00 00:00:00.9999999'); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +SELECT * FROM t1; +a +2001-00-00 00:00:00.99999 +2001-00-01 00:00:00.99999 +2001-01-00 00:00:00.99999 +DROP TABLE t1; +CREATE TABLE t1 (a DATETIME); +INSERT INTO t1 VALUES ('2001-00-00 00:00:00.9999999'); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +INSERT INTO t1 VALUES ('2001-00-01 00:00:00.9999999'); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +INSERT INTO t1 VALUES ('2001-01-00 00:00:00.9999999'); +Warnings: +Note 1265 Data truncated for column 'a' at row 1 +SELECT * FROM t1; +a +2001-00-00 00:00:00 +2001-00-01 00:00:00 +2001-01-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; +# +# TIME literals in no-zero date context +# +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); +SELECT TO_DAYS(TIME'00:00:00'); +TO_DAYS(TIME'00:00:00') +730884 +SELECT TO_SECONDS(TIME'00:00:00'); +TO_SECONDS(TIME'00:00:00') +63148377600 +SELECT DAYOFYEAR(TIME'00:00:00'); +DAYOFYEAR(TIME'00:00:00') +34 +SELECT WEEK(TIME'00:00:00'); +WEEK(TIME'00:00:00') +4 +SELECT YEARWEEK(TIME'00:00:00'); +YEARWEEK(TIME'00:00:00') +200104 +SELECT WEEKDAY(TIME'00:00:00'); +WEEKDAY(TIME'00:00:00') +5 +SELECT CONVERT_TZ(TIME'00:00:00','+00:00','+01:00'); +CONVERT_TZ(TIME'00:00:00','+00:00','+01:00') +2001-02-03 01:00:00 +SELECT DATE_ADD(TIME'00:00:00', INTERVAL 1 HOUR); +DATE_ADD(TIME'00:00:00', INTERVAL 1 HOUR) +01:00:00 +SELECT TIMESTAMPDIFF(SECOND,TIME'00:00:00', TIME'00:00:00'); +TIMESTAMPDIFF(SECOND,TIME'00:00:00', TIME'00:00:00') +0 +SET timestamp=DEFAULT; +# +# Testing Item_func::fix_fields() +# +SELECT 'a' LIKE 'a' ESCAPE DATE'2001-01-01'; +ERROR HY000: Incorrect arguments to ESCAPE +SELECT 'a' LIKE 'a' ESCAPE TIMESTAMP'2001-01-01 00:00:00'; +ERROR HY000: Incorrect arguments to ESCAPE +SELECT 'a' LIKE 'a' ESCAPE TIME'00:00:00'; +ERROR HY000: Incorrect arguments to ESCAPE +# +# MDEV-4871 Temporal literals do not accept nanoseconds +# +SELECT TIME'10:10:10.1234567'; +TIME'10:10:10.1234567' +10:10:10.123456 +Warnings: +Note 1292 Truncated incorrect time value: '10:10:10.1234567' +SELECT TIME('10:10:10.1234567'); +TIME('10:10:10.1234567') +10:10:10.123456 +Warnings: +Note 1292 Truncated incorrect time value: '10:10:10.1234567' +SELECT TIME'10:10:10.123456xyz'; +ERROR HY000: Incorrect TIME value: '10:10:10.123456xyz' +SELECT TIME'10:10:10.1234567xyz'; +ERROR HY000: Incorrect TIME value: '10:10:10.1234567xyz' +SELECT TIME('10:10:10.123456xyz'); +TIME('10:10:10.123456xyz') +10:10:10.123456 +Warnings: +Warning 1292 Truncated incorrect time value: '10:10:10.123456xyz' +SELECT TIME('10:10:10.1234567xyz'); +TIME('10:10:10.1234567xyz') +10:10:10.123456 +Warnings: +Warning 1292 Truncated incorrect time value: '10:10:10.1234567xyz' +SELECT TIMESTAMP'2001-01-01 10:10:10.1234567'; +TIMESTAMP'2001-01-01 10:10:10.1234567' +2001-01-01 10:10:10.123456 +Warnings: +Note 1292 Truncated incorrect datetime value: '2001-01-01 10:10:10.1234567' +SELECT TIMESTAMP('2001-01-01 10:10:10.1234567'); +TIMESTAMP('2001-01-01 10:10:10.1234567') +2001-01-01 10:10:10.123456 +Warnings: +Note 1292 Truncated incorrect datetime value: '2001-01-01 10:10:10.1234567' +SELECT TIMESTAMP'2001-01-01 10:10:10.123456xyz'; +ERROR HY000: Incorrect DATETIME value: '2001-01-01 10:10:10.123456xyz' +SELECT TIMESTAMP'2001-01-01 10:10:10.1234567xyz'; +ERROR HY000: Incorrect DATETIME value: '2001-01-01 10:10:10.1234567xyz' +SELECT TIMESTAMP('2001-01-01 10:10:10.123456xyz'); +TIMESTAMP('2001-01-01 10:10:10.123456xyz') +2001-01-01 10:10:10.123456 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2001-01-01 10:10:10.123456xyz' +SELECT TIMESTAMP('2001-01-01 10:10:10.1234567xyz'); +TIMESTAMP('2001-01-01 10:10:10.1234567xyz') +2001-01-01 10:10:10.123456 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2001-01-01 10:10:10.1234567xyz' +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 VALUES (TIME'10:20:30.1234567'); +Warnings: +Note 1292 Truncated incorrect time value: '10:20:30.1234567' +INSERT INTO t1 VALUES (TIME('10:20:30.1234567')); +Warnings: +Note 1292 Truncated incorrect time value: '10:20:30.1234567' +SELECT * FROM t1; +a +10:20:30.123456 +10:20:30.123456 +DROP TABLE t1; +# +# MDEV-5969 Crash in prepared statement with NO_ZERO_IN_DATE and ROLLUP +# +CREATE TABLE t1 +( +year INT NOT NULL, +product VARCHAR(32) NOT NULL, +profit INT +); +INSERT INTO t1 VALUES ('2001','car',101); +INSERT INTO t1 VALUES ('2001','gas',102); +INSERT INTO t1 VALUES ('2001','toy',103); +INSERT INTO t1 VALUES ('2002','car',201); +INSERT INTO t1 VALUES ('2002','gas',202); +INSERT INTO t1 VALUES ('2002','toy',203); +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT DATE'2001-00-00' AS c,year, SUM(profit) FROM t1 GROUP BY c,year WITH ROLLUP"; +EXECUTE stmt; +c year SUM(profit) +2001-00-00 2001 306 +2001-00-00 2002 606 +2001-00-00 NULL 912 +NULL NULL 912 +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +c year SUM(profit) +NULL 2001 306 +NULL 2002 606 +NULL NULL 912 +NULL NULL 912 +Warnings: +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +Warning 1292 Incorrect datetime value: '2001-00-00' +SET sql_mode=DEFAULT; +DROP TABLE t1; +# +# MDEV-5971 Asymmetry between CAST(DATE'2001-00-00') to INT and TO CHAR in prepared statements +# +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS SIGNED) AS c"; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +c +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2001-00-00' +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS CHAR) AS c"; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +c +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2001-00-00' +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS DECIMAL(30,0)) AS c"; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +c +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2001-00-00' +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS DOUBLE) AS c"; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +c +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2001-00-00' +# +# Zero month or zero day automatically mean NULL flag, no matter SQL_MODE is. +# Only zero year is OK for NOT NULL. +# +SET sql_mode=DEFAULT; +PREPARE stmt FROM "CREATE TABLE t1 AS SELECT CAST(DATE'2001-00-00' AS CHAR) AS c"; +EXECUTE stmt; +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +c varchar(10) YES NULL +SELECT * FROM t1; +c +2001-00-00 +DROP TABLE t1; +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +Warnings: +Warning 1292 Incorrect datetime value: '2001-00-00' +SHOW COLUMNS FROM t1; +Field Type Null Key Default Extra +c varchar(10) YES NULL +SELECT * FROM t1; +c +NULL +DROP TABLE t1; +SET sql_mode=DEFAULT; +CREATE TABLE t1 AS SELECT +DATE'2001-01-01', +DATE'0000-01-01', +DATE'2001-00-00', +DATE'2001-00-01', +DATE'2001-01-00'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `DATE'2001-01-01'` date NOT NULL, + `DATE'0000-01-01'` date NOT NULL, + `DATE'2001-00-00'` date DEFAULT NULL, + `DATE'2001-00-01'` date DEFAULT NULL, + `DATE'2001-01-00'` date DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 AS SELECT +TIMESTAMP'2001-01-01 00:00:00', +TIMESTAMP'0000-01-01 00:00:00', +TIMESTAMP'2001-00-00 00:00:00', +TIMESTAMP'2001-00-01 00:00:00', +TIMESTAMP'2001-01-00 00:00:00'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `TIMESTAMP'2001-01-01 00:00:00'` datetime NOT NULL, + `TIMESTAMP'0000-01-01 00:00:00'` datetime NOT NULL, + `TIMESTAMP'2001-00-00 00:00:00'` datetime DEFAULT NULL, + `TIMESTAMP'2001-00-01 00:00:00'` datetime DEFAULT NULL, + `TIMESTAMP'2001-01-00 00:00:00'` datetime DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# MDEV-5975 Prepared statements with DATE literals do not honor NO_ZERO_IN_DATE +# +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT (SELECT DATE'2001-00-00') AS c"; +EXECUTE stmt; +c +2001-00-00 +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +c +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2001-00-00' +DEALLOCATE PREPARE stmt; +SET sql_mode=DEFAULT; +PREPARE stmt FROM "SELECT (SELECT TIMESTAMP'2001-00-00 10:20:30') AS c"; +EXECUTE stmt; +c +2001-00-00 10:20:30 +SET sql_mode='no_zero_in_date'; +EXECUTE stmt; +c +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2001-00-00 10:20:30' +DEALLOCATE PREPARE stmt; +SET sql_mode=DEFAULT; |