DROP TABLE IF EXISTS t1, t2;
SET NAMES latin1;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
#
# 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
Warnings:
Warning	4095	Delimiter ':' in position 2 in datetime value '01:01:01' at row 1 is deprecated. Prefer the standard '-'.
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=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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) CHARACTER SET latin1 NOT NULL DEFAULT ''
) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
EXPLAIN SELECT {d'2010-01-01'};
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	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 SELECT DATE'2010-01-01';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	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	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode 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'
SET sql_mode = '';
SELECT DATE'0000-00-00';
DATE'0000-00-00'
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=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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) CHARACTER SET latin1 NOT NULL DEFAULT ''
) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
EXPLAIN SELECT {t'10:01:01'};
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	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 SELECT TIME'10:01:01';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	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,
  `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=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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,
  `{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) CHARACTER SET latin1 NOT NULL DEFAULT ''
) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
EXPLAIN SELECT {ts'2010-01-01 10:10:10'};
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	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 SELECT TIMESTAMP'2010-01-01 10:10:10';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	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
#
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
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;
SET sql_mode = default;
#
# 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;
#
# WL#13601 Deprecate arbitrary delimiters and whitespaces in
# time / date / datetime / timestamp literals
#
------------------  DATETIME literals
superfluous blanks
SELECT TIMESTAMP '  2021-07-15 23:01:02';
TIMESTAMP '  2021-07-15 23:01:02'
2021-07-15 23:01:02
Warnings:
Warning	4096	Delimiter ' ' in position 0 in datetime value '  2021-07-15 23:01:02' at row 1 is superfluous and is deprecated. Please remove.
SELECT TIMESTAMP '2021-07-15 23:01:02  ';
TIMESTAMP '2021-07-15 23:01:02  '
2021-07-15 23:01:02
Warnings:
Warning	4096	Delimiter ' ' in position 19 in datetime value '2021-07-15 23:01:02  ' at row 1 is superfluous and is deprecated. Please remove.
SELECT TIMESTAMP '2021-07-15   23:01:02';
TIMESTAMP '2021-07-15   23:01:02'
2021-07-15 23:01:02
Warnings:
Warning	4096	Delimiter ' ' in position 11 in datetime value '2021-07-15   23:01:02' at row 1 is superfluous and is deprecated. Please remove.
wrong type delimiter
SELECT TIMESTAMP '2021/07-15 23:01:02';
TIMESTAMP '2021/07-15 23:01:02'
2021-07-15 23:01:02
Warnings:
Warning	4095	Delimiter '/' in position 4 in datetime value '2021/07-15 23:01:02' at row 1 is deprecated. Prefer the standard '-'.
SELECT TIMESTAMP '2021-07/15 23:01:02';
TIMESTAMP '2021-07/15 23:01:02'
2021-07-15 23:01:02
Warnings:
Warning	4095	Delimiter '/' in position 7 in datetime value '2021-07/15 23:01:02' at row 1 is deprecated. Prefer the standard '-'.
SELECT TIMESTAMP '2021-07-15 23.01:02';
TIMESTAMP '2021-07-15 23.01:02'
2021-07-15 23:01:02
Warnings:
Warning	4095	Delimiter '.' in position 13 in datetime value '2021-07-15 23.01:02' at row 1 is deprecated. Prefer the standard ':'.
SELECT TIMESTAMP '2021-07-15 23:01.02';
TIMESTAMP '2021-07-15 23:01.02'
2021-07-15 23:01:02
Warnings:
Warning	4095	Delimiter '.' in position 16 in datetime value '2021-07-15 23:01.02' at row 1 is deprecated. Prefer the standard ':'.
wrong &redundant
SELECT TIMESTAMP '2021//07/15 23:01:02';
TIMESTAMP '2021//07/15 23:01:02'
2021-07-15 23:01:02
Warnings:
Warning	4095	Delimiter '/' in position 4 in datetime value '2021//07/15 23:01:02' at row 1 is deprecated. Prefer the standard '-'.
SELECT TIMESTAMP '2021/07//15 23:01:02';
TIMESTAMP '2021/07//15 23:01:02'
2021-07-15 23:01:02
Warnings:
Warning	4095	Delimiter '/' in position 4 in datetime value '2021/07//15 23:01:02' at row 1 is deprecated. Prefer the standard '-'.
SELECT TIMESTAMP '2021-07-15 23..01:02';
TIMESTAMP '2021-07-15 23..01:02'
2021-07-15 23:01:02
Warnings:
Warning	4095	Delimiter '.' in position 13 in datetime value '2021-07-15 23..01:02' at row 1 is deprecated. Prefer the standard ':'.
SELECT TIMESTAMP '2021-07-15 23:01..02';
TIMESTAMP '2021-07-15 23:01..02'
2021-07-15 23:01:02
Warnings:
Warning	4095	Delimiter '.' in position 16 in datetime value '2021-07-15 23:01..02' at row 1 is deprecated. Prefer the standard ':'.
correct type delimiter but redundant
SELECT TIMESTAMP '2021--07-15 23:01:02';
TIMESTAMP '2021--07-15 23:01:02'
2021-07-15 23:01:02
Warnings:
Warning	4096	Delimiter '-' in position 5 in datetime value '2021--07-15 23:01:02' at row 1 is superfluous and is deprecated. Please remove.
SELECT TIMESTAMP '2021-07--15 23:01:02';
TIMESTAMP '2021-07--15 23:01:02'
2021-07-15 23:01:02
Warnings:
Warning	4096	Delimiter '-' in position 8 in datetime value '2021-07--15 23:01:02' at row 1 is superfluous and is deprecated. Please remove.
SELECT TIMESTAMP '2021-07-15 23::01:02';
TIMESTAMP '2021-07-15 23::01:02'
2021-07-15 23:01:02
Warnings:
Warning	4096	Delimiter ':' in position 14 in datetime value '2021-07-15 23::01:02' at row 1 is superfluous and is deprecated. Please remove.
SELECT TIMESTAMP '2021-07-15 23:01::02';
TIMESTAMP '2021-07-15 23:01::02'
2021-07-15 23:01:02
Warnings:
Warning	4096	Delimiter ':' in position 17 in datetime value '2021-07-15 23:01::02' at row 1 is superfluous and is deprecated. Please remove.
all wrong
SELECT TIMESTAMP '  2021??07//15  23:+*&01.,"02   ';
TIMESTAMP '  2021??07//15  23:+*&01.,"02   '
2021-07-15 23:01:02
Warnings:
Warning	4096	Delimiter ' ' in position 0 in datetime value '  2021??07//15  23:+*&01.,"02   ' at row 1 is superfluous and is deprecated. Please remove.
in CAST
SELECT CAST('2015-01-15 23-24:25' AS DATETIME);
CAST('2015-01-15 23-24:25' AS DATETIME)
2015-01-15 23:24:25
Warnings:
Warning	4095	Delimiter '-' in position 13 in datetime value '2015-01-15 23-24:25' at row 1 is deprecated. Prefer the standard ':'.
in JSON_VALUE
SELECT JSON_VALUE('{"data": "2019-01-01 11:11::11"}', '$.data' RETURNING DATETIME) AS v;
v
2019-01-01 11:11:11
Warnings:
Warning	4096	Delimiter ':' in position 17 in datetime value '2019-01-01 11:11::11' at row 1 is superfluous and is deprecated. Please remove.
SELECT LEAST(CAST('01-01-01' AS DATETIME), '01-01:02');
LEAST(CAST('01-01-01' AS DATETIME), '01-01:02')
2001-01-01 00:00:00
Warnings:
Warning	4095	Delimiter ':' in position 5 in datetime value '01-01:02' at row 1 is deprecated. Prefer the standard '-'.
in get_mysql_time_from_str
CREATE TABLE t1(f1 DATE);
INSERT INTO t1 VALUES ('2001-01-01');
SELECT f1 FROM t1 WHERE f1 < "01-4:15";
f1
2001-01-01
Warnings:
Warning	4095	Delimiter ':' in position 4 in datetime value '01-4:15' at row 1 is deprecated. Prefer the standard '-'.
DROP TABLE t1;
CREATE TABLE t1(f1 DATETIME, c VARCHAR(20));
INSERT INTO t1 VALUES (NULL, '2001-01-01 19.55.00');
UPDATE t1 SET f1=c;
Warnings:
Warning	4095	Delimiter '.' in position 13 in datetime value '2001-01-01 19.55.00' at row 1 is deprecated. Prefer the standard ':'.
DROP TABLE t1;
------------------  DATE literals

SELECT DATE'2021/09-20';
DATE'2021/09-20'
2021-09-20
Warnings:
Warning	4095	Delimiter '/' in position 4 in datetime value '2021/09-20' at row 1 is deprecated. Prefer the standard '-'.
SELECT DATE' 2021-09-20';
DATE' 2021-09-20'
2021-09-20
Warnings:
Warning	4096	Delimiter ' ' in position 0 in datetime value ' 2021-09-20' at row 1 is superfluous and is deprecated. Please remove.
SELECT DATE'2021-09-20 ';
DATE'2021-09-20 '
2021-09-20
Warnings:
Warning	4096	Delimiter ' ' in position 10 in datetime value '2021-09-20 ' at row 1 is superfluous and is deprecated. Please remove.
in CAST
SELECT CAST('2015-01-15 23-24:25' AS DATE);
CAST('2015-01-15 23-24:25' AS DATE)
2015-01-15
Warnings:
Warning	4095	Delimiter '-' in position 13 in datetime value '2015-01-15 23-24:25' at row 1 is deprecated. Prefer the standard ':'.
in JSON_VALUE
SELECT JSON_VALUE('{"data": "2019-01/01"}', '$.data' RETURNING DATE) AS v;
v
2019-01-01
Warnings:
Warning	4095	Delimiter '/' in position 7 in datetime value '2019-01/01' at row 1 is deprecated. Prefer the standard '-'.
SELECT LEAST(CAST('2001-12-10' AS DATE), '2001-12/11');
LEAST(CAST('2001-12-10' AS DATE), '2001-12/11')
2001-12-10
Warnings:
Warning	4095	Delimiter '/' in position 7 in datetime value '2001-12/11' at row 1 is deprecated. Prefer the standard '-'.
CREATE TABLE t1(f1 DATE);
INSERT INTO t1 VALUES ('2001-01-01');
SELECT f1 FROM t1 WHERE f1 < "2001-01/01";
f1
Warnings:
Warning	4095	Delimiter '/' in position 7 in datetime value '2001-01/01' at row 1 is deprecated. Prefer the standard '-'.
DROP TABLE t1;
CREATE TABLE t1(f1 DATE, c VARCHAR(20));
INSERT INTO t1 VALUES (NULL, '2001-01/01');
UPDATE t1 SET f1=c;
Warnings:
Warning	4095	Delimiter '/' in position 7 in datetime value '2001-01/01' at row 1 is deprecated. Prefer the standard '-'.
DROP TABLE t1;
------------------  TIME literals
These were much stricter before the WL, we only add checks for
leading and trailing blanks

SELECT TIME' 10:11:12.098700';
TIME' 10:11:12.098700'
10:11:12.098700
Warnings:
Warning	4096	Delimiter ' ' in position 0 in datetime value ' 10:11:12.098700' at row 1 is superfluous and is deprecated. Please remove.
SELECT TIME'10:11:12.098700  ';
TIME'10:11:12.098700  '
10:11:12.098700
Warnings:
Warning	4096	Delimiter ' ' in position 15 in datetime value '10:11:12.098700  ' at row 1 is superfluous and is deprecated. Please remove.
SELECT TIME'10-11:12.098700  ';
ERROR HY000: Incorrect TIME value: '10-11:12.098700  '
Superfluous blanks in the 'D hh:mm:ss' format
SELECT TIME'1  01:01:01',
TIME'1  01:01',
TIME'1  01',
TIME'1 ',
TIME'1', # OK
TIME' 1';
TIME'1  01:01:01'	TIME'1  01:01'	TIME'1  01'	TIME'1 '	TIME'1'	TIME' 1'
25:01:01	25:01:00	25:00:00	00:00:01	00:00:01	00:00:01
Warnings:
Warning	4096	Delimiter ' ' in position 1 in datetime value '1  01:01:01' at row 1 is superfluous and is deprecated. Please remove.
Warning	4096	Delimiter ' ' in position 1 in datetime value '1  01:01' at row 1 is superfluous and is deprecated. Please remove.
Warning	4096	Delimiter ' ' in position 1 in datetime value '1  01' at row 1 is superfluous and is deprecated. Please remove.
Warning	4096	Delimiter ' ' in position 1 in datetime value '1 ' at row 1 is superfluous and is deprecated. Please remove.
Warning	4096	Delimiter ' ' in position 0 in datetime value ' 1' at row 1 is superfluous and is deprecated. Please remove.
in CAST
select CAST('10:11:12.098700    ' AS TIME);
CAST('10:11:12.098700    ' AS TIME)
10:11:12
Warnings:
Warning	4096	Delimiter ' ' in position 15 in datetime value '10:11:12.098700    ' at row 1 is superfluous and is deprecated. Please remove.
in JSON_VALUE
SELECT JSON_VALUE('{"data": "10:11:12.098700    "}', '$.data' RETURNING TIME) AS v;
v
10:11:12
Warnings:
Warning	4096	Delimiter ' ' in position 15 in datetime value '10:11:12.098700    ' at row 1 is superfluous and is deprecated. Please remove.
# For some reason, the second argument below doesn't get
# attempted converted to TIME, so we do not see any warning.
# The comparison is done on strings instead.
# This is in contrast to DATE and DATETIME. Bug? FIXME
SELECT LEAST(CAST('10:11:12' AS TIME), '10:11:13 ');
LEAST(CAST('10:11:12' AS TIME), '10:11:13 ')
10:11:12
CREATE TABLE t1(f1 TIME, c VARCHAR(20));
INSERT INTO t1 VALUES (NULL, ' 19:55:00');
UPDATE t1 SET f1=c;
Warnings:
Warning	4096	Delimiter ' ' in position 0 in datetime value ' 19:55:00' at row 1 is superfluous and is deprecated. Please remove.
DROP TABLE t1;

Test weird delimiters between date and time parts

# Delimiter instead of space
SELECT TIMESTAMP'2021-07-17.18:45:00';
TIMESTAMP'2021-07-17.18:45:00'
2021-07-17 18:45:00
Warnings:
Warning	4095	Delimiter '.' in position 10 in datetime value '2021-07-17.18:45:00' at row 1 is deprecated. Prefer the standard ' '.
SELECT TIMESTAMP'2021-07-17-18:45:00';
TIMESTAMP'2021-07-17-18:45:00'
2021-07-17 18:45:00
Warnings:
Warning	4095	Delimiter '-' in position 10 in datetime value '2021-07-17-18:45:00' at row 1 is deprecated. Prefer the standard ' '.
# No warning given here, since we cannot replace the period with a space
SELECT TIMESTAMP'20211018.121000';
TIMESTAMP'20211018.121000'
2021-10-18 12:10:00