399 lines
13 KiB
Plaintext
399 lines
13 KiB
Plaintext
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
|
|
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_general_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_general_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_general_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_general_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_general_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_general_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;
|