360 lines
10 KiB
Text
360 lines
10 KiB
Text
#Get deafult engine value
|
|
--let $DEFAULT_ENGINE = `select @@global.default_storage_engine`
|
|
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS t1, t2;
|
|
--enable_warnings
|
|
|
|
SET NAMES latin1;
|
|
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
|
|
|
|
--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';
|
|
|
|
#Replace default engine value with static engine string
|
|
--replace_result $DEFAULT_ENGINE ENGINE
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 AS SELECT
|
|
{d'2001-01-01'},
|
|
{d'2001-01-01 10:10:10'};
|
|
|
|
#Replace default engine value with static engine string
|
|
--replace_result $DEFAULT_ENGINE ENGINE
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
EXPLAIN SELECT {d'2010-01-01'};
|
|
EXPLAIN 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';
|
|
SET sql_mode = '';
|
|
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';
|
|
|
|
#Replace default engine value with static engine string
|
|
--replace_result $DEFAULT_ENGINE ENGINE
|
|
SHOW CREATE TABLE t1;
|
|
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'};
|
|
|
|
#Replace default engine value with static engine string
|
|
--replace_result $DEFAULT_ENGINE ENGINE
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
EXPLAIN SELECT {t'10:01:01'};
|
|
EXPLAIN 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';
|
|
|
|
#Replace default engine value with static engine string
|
|
--replace_result $DEFAULT_ENGINE ENGINE
|
|
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.123456'},
|
|
{ts'2001-01-01'};
|
|
|
|
#Replace default engine value with static engine string
|
|
--replace_result $DEFAULT_ENGINE ENGINE
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
EXPLAIN SELECT {ts'2010-01-01 10:10:10'};
|
|
EXPLAIN SELECT TIMESTAMP'2010-01-01 10:10:10';
|
|
|
|
--echo #
|
|
--echo # Testing nanosecond rounding for TIMESTAMP literals with bad dates
|
|
--echo #
|
|
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
|
|
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';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2001-00-00 00:00:00.9999999';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2001-00-01 00:00:00.9999999';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2001-01-00 00:00:00.9999999';
|
|
|
|
--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;
|
|
SET sql_mode = default;
|
|
|
|
--echo #
|
|
--echo # BUG #16812821: INCONSISTANT RESULTS WHEN ODBC DATE FORMAT IS USED
|
|
--echo #
|
|
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'};
|
|
|
|
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c11
|
|
WHERE c3 BETWEEN {t'01:01:01'} AND {t'01:01:05'};
|
|
|
|
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'};
|
|
|
|
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c11
|
|
WHERE c2 BETWEEN DATE'2014-01-01' AND DATE'2014-01-05';
|
|
|
|
SELECT * FROM t1 LEFT JOIN t2 ON c1 = c11
|
|
WHERE c3 BETWEEN TIME'01:01:01' AND TIME'01:01:05';
|
|
|
|
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';
|
|
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo #
|
|
--echo # WL#13601 Deprecate arbitrary delimiters and whitespaces in
|
|
--echo # time / date / datetime / timestamp literals
|
|
--echo #
|
|
|
|
--echo ------------------ DATETIME literals
|
|
|
|
--echo superfluous blanks
|
|
SELECT TIMESTAMP ' 2021-07-15 23:01:02';
|
|
SELECT TIMESTAMP '2021-07-15 23:01:02 ';
|
|
SELECT TIMESTAMP '2021-07-15 23:01:02';
|
|
|
|
--echo wrong type delimiter
|
|
SELECT TIMESTAMP '2021/07-15 23:01:02';
|
|
SELECT TIMESTAMP '2021-07/15 23:01:02';
|
|
SELECT TIMESTAMP '2021-07-15 23.01:02';
|
|
SELECT TIMESTAMP '2021-07-15 23:01.02';
|
|
|
|
--echo wrong &redundant
|
|
SELECT TIMESTAMP '2021//07/15 23:01:02';
|
|
SELECT TIMESTAMP '2021/07//15 23:01:02';
|
|
SELECT TIMESTAMP '2021-07-15 23..01:02';
|
|
SELECT TIMESTAMP '2021-07-15 23:01..02';
|
|
|
|
--echo correct type delimiter but redundant
|
|
SELECT TIMESTAMP '2021--07-15 23:01:02';
|
|
SELECT TIMESTAMP '2021-07--15 23:01:02';
|
|
SELECT TIMESTAMP '2021-07-15 23::01:02';
|
|
SELECT TIMESTAMP '2021-07-15 23:01::02';
|
|
|
|
--echo all wrong
|
|
SELECT TIMESTAMP ' 2021??07//15 23:+*&01.,"02 ';
|
|
|
|
--echo in CAST
|
|
SELECT CAST('2015-01-15 23-24:25' AS DATETIME);
|
|
|
|
--echo in JSON_VALUE
|
|
SELECT JSON_VALUE('{"data": "2019-01-01 11:11::11"}', '$.data' RETURNING DATETIME) AS v;
|
|
|
|
SELECT LEAST(CAST('01-01-01' AS DATETIME), '01-01:02');
|
|
|
|
--echo 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";
|
|
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;
|
|
DROP TABLE t1;
|
|
|
|
--echo ------------------ DATE literals
|
|
--echo
|
|
SELECT DATE'2021/09-20';
|
|
SELECT DATE' 2021-09-20';
|
|
SELECT DATE'2021-09-20 ';
|
|
|
|
--echo in CAST
|
|
SELECT CAST('2015-01-15 23-24:25' AS DATE);
|
|
|
|
--echo in JSON_VALUE
|
|
SELECT JSON_VALUE('{"data": "2019-01/01"}', '$.data' RETURNING DATE) AS v;
|
|
|
|
SELECT LEAST(CAST('2001-12-10' AS DATE), '2001-12/11');
|
|
|
|
CREATE TABLE t1(f1 DATE);
|
|
INSERT INTO t1 VALUES ('2001-01-01');
|
|
SELECT f1 FROM t1 WHERE f1 < "2001-01/01";
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(f1 DATE, c VARCHAR(20));
|
|
INSERT INTO t1 VALUES (NULL, '2001-01/01');
|
|
UPDATE t1 SET f1=c;
|
|
DROP TABLE t1;
|
|
|
|
--echo ------------------ TIME literals
|
|
--echo These were much stricter before the WL, we only add checks for
|
|
--echo leading and trailing blanks
|
|
--echo
|
|
SELECT TIME' 10:11:12.098700';
|
|
SELECT TIME'10:11:12.098700 ';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIME'10-11:12.098700 ';
|
|
|
|
--echo 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';
|
|
|
|
--echo in CAST
|
|
select CAST('10:11:12.098700 ' AS TIME);
|
|
|
|
--echo in JSON_VALUE
|
|
SELECT JSON_VALUE('{"data": "10:11:12.098700 "}', '$.data' RETURNING TIME) AS v;
|
|
|
|
--echo # For some reason, the second argument below doesn't get
|
|
--echo # attempted converted to TIME, so we do not see any warning.
|
|
--echo # The comparison is done on strings instead.
|
|
--echo # This is in contrast to DATE and DATETIME. Bug? FIXME
|
|
SELECT LEAST(CAST('10:11:12' AS TIME), '10:11:13 ');
|
|
|
|
CREATE TABLE t1(f1 TIME, c VARCHAR(20));
|
|
INSERT INTO t1 VALUES (NULL, ' 19:55:00');
|
|
UPDATE t1 SET f1=c;
|
|
DROP TABLE t1;
|
|
|
|
--echo
|
|
--echo Test weird delimiters between date and time parts
|
|
--echo
|
|
--echo # Delimiter instead of space
|
|
SELECT TIMESTAMP'2021-07-17.18:45:00';
|
|
SELECT TIMESTAMP'2021-07-17-18:45:00';
|
|
--echo # No warning given here, since we cannot replace the period with a space
|
|
SELECT TIMESTAMP'20211018.121000';
|