1 files changed, 348 insertions, 0 deletions
diff --git a/mysql-test/main/temporal_literal.test b/mysql-test/main/temporal_literal.test
new file mode 100644
index 00000000..5126796d
--- /dev/null
+++ b/mysql-test/main/temporal_literal.test
@@ -0,0 +1,348 @@
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+SET NAMES latin1;
+
+
+--echo #
+--echo # Testing DATE literals
+--echo #
+--error ER_WRONG_VALUE
+SELECT DATE'xxxx';
+--error ER_WRONG_VALUE
+SELECT DATE'01';
+--error ER_WRONG_VALUE
+SELECT DATE'01-01';
+--error ER_WRONG_VALUE
+SELECT DATE'2001';
+--error ER_WRONG_VALUE
+SELECT DATE'2001-01';
+SELECT DATE'2001-00-00';
+SELECT DATE'2001-01-00';
+SELECT DATE'0000-00-00';
+--error ER_WRONG_VALUE
+SELECT DATE'2001-01-01 00:00:00';
+SELECT DATE'01:01:01';
+SELECT DATE'01-01-01';
+SELECT DATE'2010-01-01';
+SELECT DATE '2010-01-01';
+CREATE TABLE t1 AS SELECT DATE'2010-01-01';
+SHOW CREATE TABLE t1;
+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;
+DROP TABLE t1;
+EXPLAIN EXTENDED SELECT {d'2010-01-01'};
+EXPLAIN EXTENDED SELECT DATE'2010-01-01';
+
+--echo #
+--echo # Testing DATE literals in non-default sql_mode
+--echo #
+SET sql_mode=no_zero_in_date;
+--error ER_WRONG_VALUE
+SELECT DATE'2001-00-00';
+--error ER_WRONG_VALUE
+SELECT DATE'2001-01-00';
+SELECT DATE'0000-00-00';
+
+SET sql_mode=no_zero_date;
+--error ER_WRONG_VALUE
+SELECT DATE'0000-00-00';
+SET sql_mode=default;
+
+--echo #
+--echo # Testing TIME literals
+--echo #
+--error ER_WRONG_VALUE
+SELECT TIME'xxxx';
+--error ER_WRONG_VALUE
+SELECT TIME'900:00:00';
+--error ER_WRONG_VALUE
+SELECT TIME'-900:00:00';
+SELECT TIME'1 24:00:00';
+SELECT TIME'30 24:00:00';
+--error ER_WRONG_VALUE
+SELECT TIME'0000-00-00 00:00:00';
+--error ER_WRONG_VALUE
+SELECT TIME'40 24:00:00';
+SELECT TIME'10';
+SELECT TIME'10:10';
+SELECT TIME'10:11.12';
+SELECT TIME'10:10:10';
+SELECT TIME'10:10:10.';
+SELECT TIME'10:10:10.1';
+SELECT TIME'10:10:10.12';
+SELECT TIME'10:10:10.123';
+SELECT TIME'10:10:10.1234';
+SELECT TIME'10:10:10.12345';
+SELECT TIME'10:10:10.123456';
+SELECT TIME'-10:00:00';
+SELECT TIME '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;
+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;
+DROP TABLE t1;
+EXPLAIN EXTENDED SELECT {t'10:01:01'};
+EXPLAIN EXTENDED SELECT TIME'10:01:01';
+
+
+--echo #
+--echo # Testing TIMESTAMP literals
+--echo #
+--error ER_WRONG_VALUE
+SELECT TIMESTAMP'xxxx';
+--error ER_WRONG_VALUE
+SELECT TIMESTAMP'2010';
+--error ER_WRONG_VALUE
+SELECT TIMESTAMP'2010-01';
+--error ER_WRONG_VALUE
+SELECT TIMESTAMP'2010-01-01';
+SELECT TIMESTAMP'2010-01-01 00';
+SELECT TIMESTAMP'2010-01-01 00:01';
+SELECT TIMESTAMP'2010-01-01 10:10:10';
+SELECT TIMESTAMP'2010-01-01 10:10:10.';
+SELECT TIMESTAMP'2010-01-01 10:10:10.1';
+SELECT TIMESTAMP'2010-01-01 10:10:10.12';
+SELECT TIMESTAMP'2010-01-01 10:10:10.123';
+SELECT TIMESTAMP'2010-01-01 10:10:10.1234';
+SELECT TIMESTAMP'2010-01-01 10:10:10.12345';
+SELECT TIMESTAMP'2010-01-01 10:10:10.123456';
+SELECT TIMESTAMP '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;
+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;
+DROP TABLE t1;
+EXPLAIN EXTENDED SELECT {ts'2010-01-01 10:10:10'};
+EXPLAIN EXTENDED SELECT TIMESTAMP'2010-01-01 10:10:10';
+
+--echo #
+--echo # Testing nanosecond rounding for TIMESTAMP literals with bad dates
+--echo #
+SELECT TIMESTAMP'2001-00-00 00:00:00.999999';
+SELECT TIMESTAMP'2001-00-01 00:00:00.999999';
+SELECT TIMESTAMP'2001-01-00 00:00:00.999999';
+--disable_ps_protocol
+SELECT TIMESTAMP'2001-00-00 00:00:00.9999999';
+SELECT TIMESTAMP'2001-00-01 00:00:00.9999999';
+SELECT TIMESTAMP'2001-01-00 00:00:00.9999999';
+--enable_ps_protocol
+
+--echo #
+--echo # String literal with bad dates and nanoseconds to DATETIME(N)
+--echo #
+CREATE TABLE t1 (a DATETIME(6));
+INSERT INTO t1 VALUES ('2001-00-00 00:00:00.9999999');
+INSERT INTO t1 VALUES ('2001-00-01 00:00:00.9999999');
+INSERT INTO t1 VALUES ('2001-01-00 00:00:00.9999999');
+SELECT * FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME(5));
+INSERT INTO t1 VALUES ('2001-00-00 00:00:00.9999999');
+INSERT INTO t1 VALUES ('2001-00-01 00:00:00.9999999');
+INSERT INTO t1 VALUES ('2001-01-00 00:00:00.9999999');
+SELECT * FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES ('2001-00-00 00:00:00.9999999');
+INSERT INTO t1 VALUES ('2001-00-01 00:00:00.9999999');
+INSERT INTO t1 VALUES ('2001-01-00 00:00:00.9999999');
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Testing Item_date_literal::eq
+--echo #
+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';
+SELECT DATE'2001-01-01' FROM t1 GROUP BY DATE'2001-01-01';
+DROP TABLE t1;
+
+--echo #
+--echo # TIME literals in no-zero date context
+--echo #
+SET timestamp=unix_timestamp('2001-02-03 10:20:30');
+SELECT TO_DAYS(TIME'00:00:00');
+SELECT TO_SECONDS(TIME'00:00:00');
+SELECT DAYOFYEAR(TIME'00:00:00');
+SELECT WEEK(TIME'00:00:00');
+SELECT YEARWEEK(TIME'00:00:00');
+SELECT WEEKDAY(TIME'00:00:00');
+SELECT CONVERT_TZ(TIME'00:00:00','+00:00','+01:00');
+SELECT DATE_ADD(TIME'00:00:00', INTERVAL 1 HOUR);
+SELECT TIMESTAMPDIFF(SECOND,TIME'00:00:00', TIME'00:00:00');
+SET timestamp=DEFAULT;
+
+--echo #
+--echo # Testing Item_func::fix_fields()
+--echo #
+--error ER_WRONG_ARGUMENTS
+SELECT 'a' LIKE 'a' ESCAPE DATE'2001-01-01';
+--error ER_WRONG_ARGUMENTS
+SELECT 'a' LIKE 'a' ESCAPE TIMESTAMP'2001-01-01 00:00:00';
+--error ER_WRONG_ARGUMENTS
+SELECT 'a' LIKE 'a' ESCAPE TIME'00:00:00';
+
+--echo #
+--echo # MDEV-4871 Temporal literals do not accept nanoseconds
+--echo #
+--disable_ps_protocol
+SELECT TIME'10:10:10.1234567';
+--enable_ps_protocol
+SELECT TIME('10:10:10.1234567');
+--error ER_WRONG_VALUE
+SELECT TIME'10:10:10.123456xyz';
+--error ER_WRONG_VALUE
+SELECT TIME'10:10:10.1234567xyz';
+SELECT TIME('10:10:10.123456xyz');
+SELECT TIME('10:10:10.1234567xyz');
+
+--disable_ps_protocol
+SELECT TIMESTAMP'2001-01-01 10:10:10.1234567';
+--enable_ps_protocol
+SELECT TIMESTAMP('2001-01-01 10:10:10.1234567');
+--error ER_WRONG_VALUE
+SELECT TIMESTAMP'2001-01-01 10:10:10.123456xyz';
+--error ER_WRONG_VALUE
+SELECT TIMESTAMP'2001-01-01 10:10:10.1234567xyz';
+SELECT TIMESTAMP('2001-01-01 10:10:10.123456xyz');
+SELECT TIMESTAMP('2001-01-01 10:10:10.1234567xyz');
+
+CREATE TABLE t1 (a TIME(6));
+--enable_prepare_warnings
+INSERT INTO t1 VALUES (TIME'10:20:30.1234567');
+--disable_prepare_warnings
+INSERT INTO t1 VALUES (TIME('10:20:30.1234567'));
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-5969 Crash in prepared statement with NO_ZERO_IN_DATE and ROLLUP
+--echo #
+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;
+SET sql_mode='no_zero_in_date';
+EXECUTE stmt;
+SET sql_mode=DEFAULT;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-5971 Asymmetry between CAST(DATE'2001-00-00') to INT and TO CHAR in prepared statements
+--echo #
+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;
+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;
+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;
+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;
+
+--echo #
+--echo # Zero month or zero day automatically mean NULL flag, no matter SQL_MODE is.
+--echo # Only zero year is OK for NOT NULL.
+--echo #
+
+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;
+SELECT * FROM t1;
+DROP TABLE t1;
+SET sql_mode='no_zero_in_date';
+EXECUTE stmt;
+SHOW COLUMNS FROM t1;
+SELECT * FROM t1;
+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;
+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;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-5975 Prepared statements with DATE literals do not honor NO_ZERO_IN_DATE
+--echo #
+SET sql_mode=DEFAULT;
+PREPARE stmt FROM "SELECT (SELECT DATE'2001-00-00') AS c";
+EXECUTE stmt;
+SET sql_mode='no_zero_in_date';
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET sql_mode=DEFAULT;
+PREPARE stmt FROM "SELECT (SELECT TIMESTAMP'2001-00-00 10:20:30') AS c";
+EXECUTE stmt;
+SET sql_mode='no_zero_in_date';
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+SET sql_mode=DEFAULT;