476 lines
16 KiB
Text
476 lines
16 KiB
Text
|
|
#
|
|
# Test of SYSTEM time zone handling ( for my_system_gmt_sec()).
|
|
# This script must be run with TZ=CET
|
|
--source include/have_met_timezone.inc
|
|
|
|
# Initialization
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS t1;
|
|
--enable_warnings
|
|
|
|
# The following is because of daylight saving time
|
|
--replace_result CEST CET
|
|
show variables like "system_time_zone";
|
|
|
|
#
|
|
# Test unix timestamp
|
|
#
|
|
select @a:=FROM_UNIXTIME(1);
|
|
select unix_timestamp(@a);
|
|
|
|
#
|
|
# Test of some values, including some with daylight saving time
|
|
#
|
|
|
|
CREATE TABLE t1 (ts int);
|
|
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00'));
|
|
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
|
|
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 03:00'));
|
|
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
|
|
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00'));
|
|
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
|
|
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 02:59:59'));
|
|
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 03:00:00'));
|
|
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 03:59:59'));
|
|
INSERT INTO t1 (ts) VALUES (Unix_timestamp('2003-03-30 04:00:01'));
|
|
|
|
SELECT ts,from_unixtime(ts) FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
#
|
|
# Test of warning for spring time-gap values for system time zone
|
|
#
|
|
CREATE TABLE t1 (ts timestamp);
|
|
INSERT IGNORE INTO t1 (ts) VALUES ('2003-03-30 01:59:59'),
|
|
('2003-03-30 02:59:59'),
|
|
('2003-03-30 03:00:00');
|
|
DROP TABLE t1;
|
|
|
|
# End of 4.1 tests
|
|
|
|
#
|
|
# Bug #27970159: DUPLICATE ENTRY FOR KEY '<GROUP_KEY>' ERROR
|
|
#
|
|
|
|
CREATE TABLE t1 (c1 TIMESTAMP);
|
|
SET TIME_ZONE = '+00:00';
|
|
SET explicit_defaults_for_timestamp=OFF;
|
|
|
|
INSERT INTO t1 VALUES('2019-10-27 00:47:42'), ('2019-10-27 00:47:42');
|
|
INSERT INTO t1 VALUES('2019-10-27 01:47:42'), ('2019-10-27 01:47:42');
|
|
|
|
SET TIME_ZONE = 'SYSTEM';
|
|
|
|
SELECT DISTINCT c1 FROM t1;
|
|
|
|
--skip_if_hypergraph # Chooses a different query plan, which causes different grouping.
|
|
SELECT COUNT(*) FROM t1 GROUP BY c1;
|
|
|
|
SET sql_mode='';
|
|
--skip_if_hypergraph # Chooses a different query plan, which causes different grouping.
|
|
SELECT COUNT(*) FROM t1 GROUP BY c1;
|
|
SET sql_mode=DEFAULT;
|
|
|
|
#Cleanup
|
|
SET explicit_defaults_for_timestamp=ON;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # WL#10828: TIMESTAMP/DATETIME VALUES CAN INCLUDE TIMEZONE DETAILS
|
|
--echo #
|
|
|
|
SET time_zone = '+01:00';
|
|
|
|
--echo # Test of the format
|
|
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10+05:30';
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.1+05:30';
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.01+05:30';
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.001+05:30';
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.0001+05:30';
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.00001+05:30';
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.000001+05:30';
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.0000001+05:30';
|
|
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10-05:30';
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.1-05:30';
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.01-05:30';
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.001-05:30';
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.0001-05:30';
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.00001-05:30';
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.000001-05:30';
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.0000001-05:30';
|
|
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10+5:30';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10+5:030';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10+005:30';
|
|
|
|
SELECT TIMESTAMP'2001-01-01 00:00:00+02:00';
|
|
SELECT TIMESTAMP( '2001-01-01 00:00:00+02:00' );
|
|
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10+05:300';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.0+05:300';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.0+05:30xxx';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10.0+25:30';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10+05';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10+05:';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10+0';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10+1';
|
|
--echo # No extra space allowed before +/-
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10 +05:30';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10+14:01';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10-14:01';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10+zx:00';
|
|
--error ER_WRONG_VALUE
|
|
SELECT TIMESTAMP'2015-01-01 10:10:10+00:00zx';
|
|
|
|
|
|
--echo #
|
|
--echo # The TIMESTAMP type.
|
|
--echo #
|
|
SET time_zone = '+02:00';
|
|
|
|
CREATE TABLE t1 ( a TIMESTAMP DEFAULT '1995-05-05 00:00:00+05:30' );
|
|
INSERT INTO t1 VALUES ();
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
SET time_zone = '+01:00';
|
|
CREATE TABLE t1 (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
a TIMESTAMP NOT NULL
|
|
) AUTO_INCREMENT = 1;
|
|
|
|
INSERT INTO t1( a ) VALUES ( '2015-01-01 10:10:10' );
|
|
INSERT INTO t1( a ) VALUES ( '2015-01-01 10:10:10+05:30' );
|
|
|
|
--echo # The valid timezone displacement range as prescribed by the
|
|
--echo # sql standard is -14:00 to +14:00.
|
|
INSERT INTO t1( a ) VALUES ( '2015-01-01 10:10:10+14:00' );
|
|
INSERT INTO t1( a ) VALUES ( '2015-01-01 10:10:10-14:00' );
|
|
|
|
--echo # Requirement F2.
|
|
SET @a = '2015-01-01 10:10:10+02:00';
|
|
PREPARE stmt1 FROM "INSERT INTO t1( a ) VALUES ( ? )";
|
|
EXECUTE stmt1 USING @a;
|
|
|
|
SELECT * FROM t1;
|
|
|
|
--echo # To see values stored in SE use UTC timezone.
|
|
SET time_zone = '+00:00';
|
|
INSERT INTO t1( a ) VALUES ( '2015-01-01 10:10:10' );
|
|
SELECT * FROM t1;
|
|
|
|
SET time_zone = '+03:00';
|
|
INSERT INTO t1( a ) VALUES ( '2015-01-01 10:10:10+02:00' );
|
|
SELECT * FROM t1;
|
|
|
|
--echo # Make sure comparisions work correctly for TIMESTAMP.
|
|
SET time_zone = '+05:30';
|
|
SELECT * FROM t1;
|
|
SELECT * FROM t1 WHERE a = '2015-01-01 10:10:10+03:30';
|
|
SELECT * FROM t1 WHERE a = '2015-01-01 10:10:10+02:00';
|
|
SELECT * FROM t1 WHERE a = '2015-01-01 10:10:10+05:30';
|
|
|
|
SET time_zone = '+00:00';
|
|
SELECT * FROM t1;
|
|
SELECT * FROM t1 WHERE a = '2015-01-01 10:10:10+03:30';
|
|
SELECT * FROM t1 WHERE a = '2015-01-01 10:10:10+02:00';
|
|
SELECT * FROM t1 WHERE a = '2015-01-01 10:10:10+05:30';
|
|
|
|
SET time_zone = DEFAULT;
|
|
|
|
DROP TABLE t1;
|
|
|
|
SET time_zone = '+01:00';
|
|
|
|
CREATE TABLE t1 ( a TIMESTAMP(1) );
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'2015-01-01 10:10:10.1+05:30' );
|
|
SELECT * FROM t1 WHERE a = '2015-01-01 05:40:10.1';
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 ( a TIMESTAMP(2) );
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'2015-01-01 10:10:10.01+05:30' );
|
|
SELECT * FROM t1 WHERE a = '2015-01-01 05:40:10.01';
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 ( a TIMESTAMP(3) );
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'2015-01-01 10:10:10.001+05:30' );
|
|
SELECT * FROM t1 WHERE a = '2015-01-01 05:40:10.001';
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 ( a TIMESTAMP(4) );
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'2015-01-01 10:10:10.0001+05:30' );
|
|
SELECT * FROM t1 WHERE a = '2015-01-01 05:40:10.0001';
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 ( a TIMESTAMP(5) );
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'2015-01-01 10:10:10.00001+05:30' );
|
|
SELECT * FROM t1 WHERE a = '2015-01-01 05:40:10.00001';
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 ( a TIMESTAMP(6) );
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'2015-01-01 10:10:10.000001+05:30' );
|
|
SELECT * FROM t1 WHERE a = '2015-01-01 05:40:10.000001';
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # The DATETIME type.
|
|
--echo #
|
|
|
|
SET time_zone = '+02:00';
|
|
|
|
CREATE TABLE t2 ( a DATETIME DEFAULT '1995-05-05 00:00:00+05:30' );
|
|
INSERT INTO t2 VALUES ();
|
|
SELECT * FROM t2;
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2 (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
a DATETIME NOT NULL
|
|
) AUTO_INCREMENT = 1;
|
|
|
|
INSERT INTO t2 ( a ) VALUES ( '2015-01-01 10:10:10' );
|
|
SELECT * FROM t2;
|
|
|
|
--echo # No effect of time_zone on the stored values of DATETIME datatype.
|
|
SET time_zone = '+05:30';
|
|
INSERT INTO t2 ( a ) VALUES ( '2015-01-01 10:10:10+05:30' );
|
|
INSERT INTO t2 ( a ) VALUES ( '2015-01-01 10:10:10+00:00' );
|
|
SELECT * FROM t2;
|
|
|
|
SET time_zone = '+02:00';
|
|
INSERT INTO t2 ( a ) VALUES ( '2015-01-01 10:10:10+03:00' );
|
|
SELECT * FROM t2;
|
|
|
|
--echo # Make sure comparisions work correctly for DATETIME.
|
|
SET time_zone = '+05:30';
|
|
SELECT * FROM t2;
|
|
SELECT * FROM t2 WHERE a = '2015-01-01 10:10:10+03:30';
|
|
SELECT * FROM t2 WHERE a = '2015-01-01 10:10:10+02:00';
|
|
SELECT * FROM t2 WHERE a = '2015-01-01 10:10:10+05:30';
|
|
|
|
SET time_zone = '+00:00';
|
|
SELECT * FROM t2;
|
|
SELECT * FROM t2 WHERE a = '2015-01-01 10:10:10+03:30';
|
|
SELECT * FROM t2 WHERE a = '2015-01-01 10:10:10+02:00';
|
|
SELECT * FROM t2 WHERE a = '2015-01-01 10:10:10+05:30';
|
|
|
|
|
|
SET time_zone = DEFAULT;
|
|
|
|
--echo # Negative test case in Item_func_add_time::val_datetime.
|
|
--echo # Second argument shouldn't have datetime + timezone info.
|
|
SELECT ADDTIME( '2001-01-01 00:00:00+02:00', '2001-01-01 00:00:00+02:00' );
|
|
|
|
SET time_zone = '+05:30';
|
|
|
|
SELECT CAST( '2001-01-01 00:00:00+02:00' AS DATETIME );
|
|
|
|
SELECT ADDTIME( TIMESTAMP'2001-01-01 00:00:00+02:00', '00:00:00.123456' );
|
|
SELECT CONVERT_TZ( '2015-01-01 10:10:10','+00:00','+05:00' );
|
|
SELECT CONVERT_TZ( '2015-01-01 10:10:10+02:00','+00:00','+05:00' );
|
|
|
|
SELECT TO_DAYS( '2001-01-01 00:00:00+07:00' );
|
|
SELECT TO_DAYS( '2001-01-01 00:00:00-07:00' );
|
|
SELECT DATEDIFF( '2007-12-31 23:59:59+02:00', '2007-12-30 00:00:00' );
|
|
SELECT TIMEDIFF( '2000:01:01 00:00:00+02:00',
|
|
'2000:01:01 00:00:00.000001+01:00' );
|
|
SELECT DATE_ADD( '2000-12-31 23:59:59+02:00', INTERVAL 1 SECOND );
|
|
SELECT DATE_SUB( '2000-12-31 23:59:59+02:00', INTERVAL 1 SECOND );
|
|
SELECT DATE_FORMAT( '2009-10-10 23:59:59+02:00', '%W %M %Y' );
|
|
SELECT TIME( '2003-12-31 23:59:59+02:00' );
|
|
SELECT TIMESTAMP( '2003-12-31 23:59:59+02:00', '12:00:00' );
|
|
SELECT TIMESTAMPADD(MINUTE, 1, '2003-01-02 23:59:59+02:00' );
|
|
SELECT TIMESTAMPDIFF(MONTH, '2003-02-01 23:59:59+02:00', '2003-05-01' );
|
|
SELECT TO_SECONDS( '2009-11-29 23:59:59+02:00' );
|
|
SELECT UNIX_TIMESTAMP( '2015-11-13 23:59:59+02:00' );
|
|
|
|
--echo # Change of time_zone should give different results.
|
|
SET time_zone = '+00:00';
|
|
SELECT TIMESTAMP'2001-01-01 00:00:00+02:00';
|
|
SELECT TIMESTAMP( '2001-01-01 00:00:00+02:00' );
|
|
SELECT CAST( '2001-01-01 00:00:00+02:00' AS DATETIME);
|
|
|
|
SELECT ADDTIME(TIMESTAMP'2001-01-01 00:00:00+02:00', '00:00:00.123456' );
|
|
|
|
SELECT CONVERT_TZ( '2015-01-01 10:10:10', '+00:00', '+05:00' );
|
|
SELECT CONVERT_TZ( '2015-01-01 10:10:10+02:00', '+00:00', '+05:00' );
|
|
|
|
SELECT TO_DAYS( '2001-01-01 00:00:00+07:00' );
|
|
SELECT TO_DAYS( '2001-01-01 00:00:00-07:00' );
|
|
SELECT DATEDIFF( '2007-12-31 23:59:59+02:00', '2007-12-30 00:00:00' );
|
|
SELECT TIMEDIFF( '2000:01:01 00:00:00+02:00',
|
|
'2000:01:01 00:00:00.000001+01:00' );
|
|
SELECT DATE_ADD( '2000-12-31 23:59:59+02:00', INTERVAL 1 SECOND);
|
|
SELECT DATE_SUB( '2000-12-31 23:59:59+02:00', INTERVAL 1 SECOND);
|
|
SELECT DATE_FORMAT( '2009-10-10 23:59:59+02:00', '%W %M %Y' );
|
|
SELECT TIME( '2003-12-31 23:59:59+02:00' );
|
|
SELECT TIMESTAMP( '2003-12-31 23:59:59+02:00', '12:00:00' );
|
|
SELECT TIMESTAMPADD(MINUTE, 1, '2003-01-02 23:59:59+02:00' );
|
|
SELECT TIMESTAMPDIFF(MONTH, '2003-02-01 23:59:59+02:00', '2003-05-01' );
|
|
SELECT TO_SECONDS( '2009-11-29 23:59:59+02:00' );
|
|
SELECT UNIX_TIMESTAMP( '2015-11-13 23:59:59+02:00' );
|
|
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2 ( a DATETIME(1) );
|
|
INSERT INTO t2 VALUES ( '2015-01-01 10:10:10.1+05:30' );
|
|
SELECT * FROM t2 WHERE a = '2015-01-01 04:40:10.1';
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2 ( a DATETIME(2) );
|
|
INSERT INTO t2 VALUES ( '2015-01-01 10:10:10.01+05:30' );
|
|
SELECT * FROM t2 WHERE a = '2015-01-01 04:40:10.01';
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2 ( a DATETIME(3) );
|
|
INSERT INTO t2 VALUES ( '2015-01-01 10:10:10.001+05:30' );
|
|
SELECT * FROM t2 WHERE a = '2015-01-01 04:40:10.001';
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2 ( a DATETIME(4) );
|
|
INSERT INTO t2 VALUES ( '2015-01-01 10:10:10.0001+05:30' );
|
|
SELECT * FROM t2 WHERE a = '2015-01-01 04:40:10.0001';
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2 ( a DATETIME(5) );
|
|
INSERT INTO t2 VALUES ( '2015-01-01 10:10:10.00001+05:30' );
|
|
SELECT * FROM t2 WHERE a = '2015-01-01 04:40:10.00001';
|
|
DROP TABLE t2;
|
|
|
|
CREATE TABLE t2 ( a DATETIME(6) );
|
|
INSERT INTO t2 VALUES ( '2015-01-01 10:10:10.000001+05:30' );
|
|
SELECT * FROM t2 WHERE a = '2015-01-01 04:40:10.000001';
|
|
DROP TABLE t2;
|
|
|
|
SET time_zone = DEFAULT;
|
|
|
|
--echo #
|
|
--echo # Bug#31239157: NO BOUNDS CHECK FOR TIMESTAMPS WITH TIME ZONE
|
|
--echo # DISPLACEMENT
|
|
--echo #
|
|
|
|
--echo # This is needed to check the below boundary case when time zone
|
|
--echo # adjustment causes a value to be outside the DATETIME range.
|
|
SET time_zone = CET;
|
|
|
|
CREATE TABLE t1 ( a TIMESTAMP );
|
|
CREATE TABLE t2 ( a TIMESTAMP(6) );
|
|
|
|
--replace_result '1901-12-13 21:45:52' X '2038-01-19 04:14:08' X
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'2038-01-19 03:14:08+00:00' );
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'2038-01-19 03:14:07+00:00' );
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'2038-01-19 03:14:07.000001+00:00' );
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'2038-01-19 03:14:07.999999+00:00' );
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'1970-01-01 00:00:00.000000+00:00' );
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'1970-01-01 01:00:00.000000+00:00' );
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'1970-01-01 00:00:01.000000+00:00' );
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'1970-01-01 00:00:00.000001+00:00' );
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'1970-01-01 00:00:00.999999+00:00' );
|
|
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
INSERT INTO t1 VALUES ( '2038-01-19 03:14:08+00:00' );
|
|
|
|
--error ER_TRUNCATED_WRONG_VALUE, ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
|
INSERT INTO t1 VALUES ( TIMESTAMP'9999-12-31 23:59:59.999999+00:00' );
|
|
|
|
--error ER_TRUNCATED_WRONG_VALUE, ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
|
INSERT INTO t1 VALUES ( '9999-12-31 23:59:59.999999+00:00' );
|
|
|
|
|
|
--replace_result '2038-01-19 04:14:08.000000' X '1901-12-13 21:45:52.000000' X
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
INSERT INTO t2 VALUES ( TIMESTAMP'2038-01-19 03:14:08+00:00' );
|
|
INSERT INTO t2 VALUES ( TIMESTAMP'2038-01-19 03:14:07+00:00' );
|
|
INSERT INTO t2 VALUES ( TIMESTAMP'2038-01-19 03:14:07.000001+00:00' );
|
|
INSERT INTO t2 VALUES ( TIMESTAMP'2038-01-19 03:14:07.999999+00:00' );
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
INSERT INTO t2 VALUES ( TIMESTAMP'1970-01-01 00:00:00.000000+00:00' );
|
|
INSERT INTO t2 VALUES ( TIMESTAMP'1970-01-01 01:00:00.000000+00:00' );
|
|
INSERT INTO t2 VALUES ( TIMESTAMP'1970-01-01 00:00:01.000000+00:00' );
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
INSERT INTO t2 VALUES ( TIMESTAMP'1970-01-01 00:00:00.000001+00:00' );
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
INSERT INTO t2 VALUES ( TIMESTAMP'1970-01-01 00:00:00.999999+00:00' );
|
|
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
INSERT INTO t2 VALUES ( '2038-01-19 03:14:08+00:00' );
|
|
|
|
--error ER_TRUNCATED_WRONG_VALUE, ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
|
INSERT INTO t2 VALUES ( TIMESTAMP'9999-12-31 23:59:59.999999+00:00' );
|
|
|
|
--error ER_TRUNCATED_WRONG_VALUE, ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
|
|
INSERT INTO t2 VALUES ( '9999-12-31 23:59:59.999999+00:00' );
|
|
|
|
SET time_zone = UTC;
|
|
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
INSERT INTO t1 VALUES ( '2038-01-19 03:14:08+00:00' );
|
|
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
INSERT INTO t1 VALUES ( '2038-01-19 03:14:08' );
|
|
|
|
SET sql_mode = '';
|
|
|
|
--echo # Tests that we can handle years which are negative wrt. the epoch.
|
|
INSERT INTO t1 VALUES ( '1969-12-31 23:59:59' );
|
|
|
|
SET sql_mode = DEFAULT;
|
|
|
|
SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
|
|
SET time_zone = DEFAULT;
|
|
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo #
|
|
--echo # Bug#30786762: FAILED ASSERTION FOR DATES WITH ZERO OR ABOVE TIMESTAMP
|
|
--echo # LIMIT
|
|
--echo #
|
|
|
|
SET sql_mode = regexp_replace(@@sql_mode, 'NO_ZERO_IN_DATE', '');
|
|
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
SELECT TIMESTAMP '2020-00-01 08:00:00.123456+00:00';
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
SELECT TIMESTAMP '2020-01-00 08:00:00.123456+00:00';
|
|
|
|
CREATE TABLE t1 ( a DATETIME(6) );
|
|
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
INSERT INTO t1 VALUES ( '2020-00-01 00:00:00.123456+00:00' );
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
INSERT INTO t1 VALUES ( '2020-01-00 00:00:00.123456+00:00' );
|
|
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
SELECT * FROM t1 WHERE a = '2020-00-01 00:00:00.123456+00:00';
|
|
--error ER_TRUNCATED_WRONG_VALUE
|
|
SELECT * FROM t1 WHERE a = '2020-01-00 00:00:00.123456+00:00';
|
|
|
|
DROP TABLE t1;
|
|
|
|
SET sql_mode = DEFAULT;
|