diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/timezone2.result | |
parent | Initial commit. (diff) | |
download | mariadb-upstream.tar.xz mariadb-upstream.zip |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/timezone2.result')
-rw-r--r-- | mysql-test/main/timezone2.result | 678 |
1 files changed, 678 insertions, 0 deletions
diff --git a/mysql-test/main/timezone2.result b/mysql-test/main/timezone2.result new file mode 100644 index 00000000..31b17fbc --- /dev/null +++ b/mysql-test/main/timezone2.result @@ -0,0 +1,678 @@ +drop table if exists t1, t2; +drop function if exists f1; +create table t1 (ts timestamp); +set time_zone='+00:00'; +select unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp()); +unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp()) +0 +insert into t1 (ts) values ('2003-03-30 02:30:00'); +set time_zone='+10:30'; +select unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp()); +unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp()) +-37800 +insert into t1 (ts) values ('2003-03-30 02:30:00'); +set time_zone='-10:00'; +select unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp()); +unix_timestamp(utc_timestamp())-unix_timestamp(current_timestamp()) +36000 +insert into t1 (ts) values ('2003-03-30 02:30:00'); +select * from t1; +ts +2003-03-29 16:30:00 +2003-03-29 06:00:00 +2003-03-30 02:30:00 +drop table t1; +select Name from mysql.time_zone_name where Name in +('UTC','Universal','MET','Europe/Moscow','leap/Europe/Moscow'); +Name +Europe/Moscow +leap/Europe/Moscow +MET +Universal +UTC +create table t1 (i int, ts timestamp); +set time_zone='MET'; +insert into t1 (i, ts) values +(unix_timestamp('2003-03-01 00:00:00'),'2003-03-01 00:00:00'); +insert into t1 (i, ts) values +(unix_timestamp('2003-03-30 01:59:59'),'2003-03-30 01:59:59'), +(unix_timestamp('2003-03-30 02:30:00'),'2003-03-30 02:30:00'), +(unix_timestamp('2003-03-30 03:00:00'),'2003-03-30 03:00:00'); +Warnings: +Warning 1299 Invalid TIMESTAMP value in column 'ts' at row 2 +insert into t1 (i, ts) values +(unix_timestamp(20030330015959),20030330015959), +(unix_timestamp(20030330023000),20030330023000), +(unix_timestamp(20030330030000),20030330030000); +Warnings: +Warning 1299 Invalid TIMESTAMP value in column 'ts' at row 2 +insert into t1 (i, ts) values +(unix_timestamp('2003-05-01 00:00:00'),'2003-05-01 00:00:00'); +insert into t1 (i, ts) values +(unix_timestamp('2003-10-26 01:00:00'),'2003-10-26 01:00:00'), +(unix_timestamp('2003-10-26 02:00:00'),'2003-10-26 02:00:00'), +(unix_timestamp('2003-10-26 02:59:59'),'2003-10-26 02:59:59'), +(unix_timestamp('2003-10-26 04:00:00'),'2003-10-26 04:00:00'), +(unix_timestamp('2003-10-26 02:59:59'),'2003-10-26 02:59:59'); +set time_zone='UTC'; +select * from t1; +i ts +1046473200 2003-02-28 23:00:00 +1048985999 2003-03-30 00:59:59 +1048986000 2003-03-30 01:00:00 +1048986000 2003-03-30 01:00:00 +1048985999 2003-03-30 00:59:59 +1048986000 2003-03-30 01:00:00 +1048986000 2003-03-30 01:00:00 +1051740000 2003-04-30 22:00:00 +1067122800 2003-10-25 23:00:00 +1067126400 2003-10-26 00:00:00 +1067129999 2003-10-26 00:59:59 +1067137200 2003-10-26 03:00:00 +1067129999 2003-10-26 00:59:59 +truncate table t1; +set time_zone='Europe/Moscow'; +insert into t1 (i, ts) values +(unix_timestamp('2004-01-01 00:00:00'),'2004-01-01 00:00:00'), +(unix_timestamp('2004-03-28 02:30:00'),'2004-03-28 02:30:00'), +(unix_timestamp('2004-08-01 00:00:00'),'2003-08-01 00:00:00'), +(unix_timestamp('2004-10-31 02:30:00'),'2004-10-31 02:30:00'); +Warnings: +Warning 1299 Invalid TIMESTAMP value in column 'ts' at row 2 +select * from t1; +i ts +1072904400 2004-01-01 00:00:00 +1080428400 2004-03-28 03:00:00 +1091304000 2003-08-01 00:00:00 +1099175400 2004-10-31 02:30:00 +truncate table t1; +set time_zone='leap/Europe/Moscow'; +insert into t1 (i, ts) values +(unix_timestamp('2004-01-01 00:00:00'),'2004-01-01 00:00:00'), +(unix_timestamp('2004-03-28 02:30:00'),'2004-03-28 02:30:00'), +(unix_timestamp('2004-08-01 00:00:00'),'2003-08-01 00:00:00'), +(unix_timestamp('2004-10-31 02:30:00'),'2004-10-31 02:30:00'); +Warnings: +Warning 1299 Invalid TIMESTAMP value in column 'ts' at row 2 +select * from t1; +i ts +1072904422 2004-01-01 00:00:00 +1080428422 2004-03-28 03:00:00 +1091304022 2003-08-01 00:00:00 +1099175422 2004-10-31 02:30:00 +truncate table t1; +insert into t1 (i, ts) values +(unix_timestamp('1981-07-01 03:59:59'),'1981-07-01 03:59:59'), +(unix_timestamp('1981-07-01 04:00:00'),'1981-07-01 04:00:00'); +select * from t1; +i ts +362793608 1981-07-01 03:59:59 +362793610 1981-07-01 04:00:00 +select from_unixtime(362793609); +from_unixtime(362793609) +1981-07-01 03:59:59 +drop table t1; +create table t1 (ts timestamp); +set time_zone='UTC'; +insert into t1 values ('0000-00-00 00:00:00'),('1969-12-31 23:59:59'), +('1970-01-01 00:00:00'),('1970-01-01 00:00:01'), +('2038-01-19 03:14:07'),('2038-01-19 03:14:08'); +Warnings: +Warning 1264 Out of range value for column 'ts' at row 2 +Warning 1264 Out of range value for column 'ts' at row 3 +Warning 1264 Out of range value for column 'ts' at row 6 +select * from t1; +ts +0000-00-00 00:00:00 +0000-00-00 00:00:00 +0000-00-00 00:00:00 +1970-01-01 00:00:01 +2038-01-19 03:14:07 +0000-00-00 00:00:00 +truncate table t1; +set time_zone='MET'; +insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 00:30:00'), +('1970-01-01 01:00:00'),('1970-01-01 01:00:01'), +('2038-01-19 04:14:07'),('2038-01-19 04:14:08'); +Warnings: +Warning 1264 Out of range value for column 'ts' at row 2 +Warning 1264 Out of range value for column 'ts' at row 3 +Warning 1264 Out of range value for column 'ts' at row 6 +select * from t1; +ts +0000-00-00 00:00:00 +0000-00-00 00:00:00 +0000-00-00 00:00:00 +1970-01-01 01:00:01 +2038-01-19 04:14:07 +0000-00-00 00:00:00 +truncate table t1; +set time_zone='+01:30'; +insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 01:00:00'), +('1970-01-01 01:30:00'),('1970-01-01 01:30:01'), +('2038-01-19 04:44:07'),('2038-01-19 04:44:08'); +Warnings: +Warning 1264 Out of range value for column 'ts' at row 2 +Warning 1264 Out of range value for column 'ts' at row 3 +Warning 1264 Out of range value for column 'ts' at row 6 +select * from t1; +ts +0000-00-00 00:00:00 +0000-00-00 00:00:00 +0000-00-00 00:00:00 +1970-01-01 01:30:01 +2038-01-19 04:44:07 +0000-00-00 00:00:00 +drop table t1; +show variables like 'time_zone'; +Variable_name Value +time_zone +01:30 +set time_zone = default; +show variables like 'time_zone'; +Variable_name Value +time_zone SYSTEM +set time_zone= '0'; +ERROR HY000: Unknown or incorrect time zone: '0' +set time_zone= '0:0'; +ERROR HY000: Unknown or incorrect time zone: '0:0' +set time_zone= '-20:00'; +ERROR HY000: Unknown or incorrect time zone: '-20:00' +set time_zone= '+20:00'; +ERROR HY000: Unknown or incorrect time zone: '+20:00' +set time_zone= 'Some/Unknown/Time/Zone'; +ERROR HY000: Unknown or incorrect time zone: 'Some/Unknown/Time/Zone' +select convert_tz(now(),'UTC', 'Universal') = now(); +convert_tz(now(),'UTC', 'Universal') = now() +1 +select convert_tz(now(),'utc', 'UTC') = now(); +convert_tz(now(),'utc', 'UTC') = now() +1 +select convert_tz('1917-11-07 12:00:00', 'MET', 'UTC'); +convert_tz('1917-11-07 12:00:00', 'MET', 'UTC') +1917-11-07 12:00:00 +select convert_tz('1970-01-01 01:00:00', 'MET', 'UTC'); +convert_tz('1970-01-01 01:00:00', 'MET', 'UTC') +1970-01-01 01:00:00 +select convert_tz('1970-01-01 01:00:01', 'MET', 'UTC'); +convert_tz('1970-01-01 01:00:01', 'MET', 'UTC') +1970-01-01 00:00:01 +select convert_tz('2003-03-01 00:00:00', 'MET', 'UTC'); +convert_tz('2003-03-01 00:00:00', 'MET', 'UTC') +2003-02-28 23:00:00 +select convert_tz('2003-03-30 01:59:59', 'MET', 'UTC'); +convert_tz('2003-03-30 01:59:59', 'MET', 'UTC') +2003-03-30 00:59:59 +select convert_tz('2003-03-30 02:30:00', 'MET', 'UTC'); +convert_tz('2003-03-30 02:30:00', 'MET', 'UTC') +2003-03-30 01:00:00 +select convert_tz('2003-03-30 03:00:00', 'MET', 'UTC'); +convert_tz('2003-03-30 03:00:00', 'MET', 'UTC') +2003-03-30 01:00:00 +select convert_tz('2003-05-01 00:00:00', 'MET', 'UTC'); +convert_tz('2003-05-01 00:00:00', 'MET', 'UTC') +2003-04-30 22:00:00 +select convert_tz('2003-10-26 01:00:00', 'MET', 'UTC'); +convert_tz('2003-10-26 01:00:00', 'MET', 'UTC') +2003-10-25 23:00:00 +select convert_tz('2003-10-26 02:00:00', 'MET', 'UTC'); +convert_tz('2003-10-26 02:00:00', 'MET', 'UTC') +2003-10-26 00:00:00 +select convert_tz('2003-10-26 02:59:59', 'MET', 'UTC'); +convert_tz('2003-10-26 02:59:59', 'MET', 'UTC') +2003-10-26 00:59:59 +select convert_tz('2003-10-26 04:00:00', 'MET', 'UTC'); +convert_tz('2003-10-26 04:00:00', 'MET', 'UTC') +2003-10-26 03:00:00 +select convert_tz('2038-01-19 04:14:07', 'MET', 'UTC'); +convert_tz('2038-01-19 04:14:07', 'MET', 'UTC') +2038-01-19 03:14:07 +select convert_tz('2038-01-19 04:14:08', 'MET', 'UTC'); +convert_tz('2038-01-19 04:14:08', 'MET', 'UTC') +2038-01-19 04:14:08 +select convert_tz('2103-01-01 04:00:00', 'MET', 'UTC'); +convert_tz('2103-01-01 04:00:00', 'MET', 'UTC') +2103-01-01 04:00:00 +create table t1 (tz varchar(3)); +insert into t1 (tz) values ('MET'), ('UTC'); +select tz, convert_tz('2003-12-31 00:00:00',tz,'UTC'), convert_tz('2003-12-31 00:00:00','UTC',tz) from t1 order by tz; +tz convert_tz('2003-12-31 00:00:00',tz,'UTC') convert_tz('2003-12-31 00:00:00','UTC',tz) +MET 2003-12-30 23:00:00 2003-12-31 01:00:00 +UTC 2003-12-31 00:00:00 2003-12-31 00:00:00 +drop table t1; +select convert_tz('2003-12-31 04:00:00', NULL, 'UTC'); +convert_tz('2003-12-31 04:00:00', NULL, 'UTC') +NULL +select convert_tz('2003-12-31 04:00:00', 'SomeNotExistingTimeZone', 'UTC'); +convert_tz('2003-12-31 04:00:00', 'SomeNotExistingTimeZone', 'UTC') +NULL +select convert_tz('2003-12-31 04:00:00', 'MET', 'SomeNotExistingTimeZone'); +convert_tz('2003-12-31 04:00:00', 'MET', 'SomeNotExistingTimeZone') +NULL +select convert_tz('2003-12-31 04:00:00', 'MET', NULL); +convert_tz('2003-12-31 04:00:00', 'MET', NULL) +NULL +select convert_tz( NULL, 'MET', 'UTC'); +convert_tz( NULL, 'MET', 'UTC') +NULL +create table t1 (ts timestamp); +set timestamp=1000000000; +insert into t1 (ts) values (now()); +select convert_tz(ts, @@time_zone, 'Japan') from t1; +convert_tz(ts, @@time_zone, 'Japan') +2001-09-09 10:46:40 +drop table t1; +select convert_tz('2005-01-14 17:00:00', 'UTC', custTimeZone) from (select 'UTC' as custTimeZone) as tmp; +convert_tz('2005-01-14 17:00:00', 'UTC', custTimeZone) +2005-01-14 17:00:00 +create table t1 select convert_tz(NULL, NULL, NULL); +select * from t1; +convert_tz(NULL, NULL, NULL) +NULL +drop table t1; +SET @old_log_bin_trust_function_creators = @@global.log_bin_trust_function_creators; +SET GLOBAL log_bin_trust_function_creators = 1; +create table t1 (ldt datetime, udt datetime); +create function f1(i datetime) returns datetime +return convert_tz(i, 'UTC', 'Europe/Moscow'); +create trigger t1_bi before insert on t1 for each row +set new.udt:= convert_tz(new.ldt, 'Europe/Moscow', 'UTC'); +insert into t1 (ldt) values ('2006-04-19 16:30:00'); +select * from t1; +ldt udt +2006-04-19 16:30:00 2006-04-19 12:30:00 +select ldt, f1(udt) as ldt2 from t1; +ldt ldt2 +2006-04-19 16:30:00 2006-04-19 16:30:00 +drop table t1; +drop function f1; +SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (t TIMESTAMP); +INSERT INTO t1 VALUES (NULL), (NULL); +LOCK TABLES t1 WRITE; +SELECT CONVERT_TZ(NOW(), 'UTC', 'Europe/Moscow') IS NULL; +CONVERT_TZ(NOW(), 'UTC', 'Europe/Moscow') IS NULL +0 +UPDATE t1 SET t = CONVERT_TZ(t, 'UTC', 'Europe/Moscow'); +UNLOCK TABLES; +DROP TABLE t1; +# +# Bug #55424: convert_tz crashes when fed invalid data +# +CREATE TABLE t1 (a SET('x') NOT NULL); +INSERT INTO t1 VALUES (''); +SELECT CONVERT_TZ(1, a, 1) FROM t1; +CONVERT_TZ(1, a, 1) +NULL +SELECT CONVERT_TZ(1, 1, a) FROM t1; +CONVERT_TZ(1, 1, a) +NULL +DROP TABLE t1; +End of 5.1 tests +# +# Start of 5.3 tests +# +# +# MDEV-4653 Wrong result for CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5') +# +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); +SELECT CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5'); +CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5') +2001-02-03 07:05:00 +SELECT CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5'); +CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5') +2001-02-03 07:05:00 +SET timestamp=DEFAULT; +# +# MDEV-5506 safe_mutex: Trying to lock unitialized mutex at safemalloc.c on server shutdown after SELECT with CONVERT_TZ +# +SELECT CONVERT_TZ('2001-10-08 00:00:00', MAKE_SET(0,'+01:00'), '+00:00' ); +CONVERT_TZ('2001-10-08 00:00:00', MAKE_SET(0,'+01:00'), '+00:00' ) +NULL +# +# End of 5.3 tests +# +# +# Start of 10.1 tests +# +# +# MDEV-11895 NO_ZERO_DATE affects timestamp values without any warnings +# +SET sql_mode = ''; +CREATE TABLE t1 (a TIMESTAMP NULL) ENGINE = MyISAM; +CREATE TABLE t2 (a TIMESTAMP NULL) ENGINE = MyISAM; +CREATE TABLE t3 (a TIMESTAMP NULL) ENGINE = MyISAM; +SET @@session.time_zone = 'UTC'; +INSERT INTO t1 VALUES ('2011-10-29 23:00:00'); +INSERT INTO t1 VALUES ('2011-10-29 23:00:01'); +INSERT INTO t1 VALUES ('2011-10-29 23:59:59'); +SET @@session.time_zone = 'Europe/Moscow'; +SET sql_mode='NO_ZERO_DATE'; +INSERT INTO t2 SELECT * FROM t1; +SET sql_mode=''; +INSERT INTO t3 SELECT * FROM t1; +SELECT UNIX_TIMESTAMP(a), a FROM t2; +UNIX_TIMESTAMP(a) a +1319929200 2011-10-30 02:00:00 +1319929201 2011-10-30 02:00:01 +1319932799 2011-10-30 02:59:59 +SELECT UNIX_TIMESTAMP(a), a FROM t3; +UNIX_TIMESTAMP(a) a +1319929200 2011-10-30 02:00:00 +1319929201 2011-10-30 02:00:01 +1319932799 2011-10-30 02:59:59 +DROP TABLE t1, t2, t3; +# +# End of 10.1 tests +# +# +# Start of 10.4 tests +# +# +# MDEV-17203 Move fractional second truncation from Item_xxx_typecast::get_date() to Time and Datetime constructors +# (an addition for the test for MDEV-4653) +SET timestamp=unix_timestamp('2001-02-03 10:20:30'); +SET old_mode=ZERO_DATE_TIME_CAST; +SELECT CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5'); +CONVERT_TZ(TIME('00:00:00'),'+00:00','+7:5') +NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '00:00:00' +SELECT CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5'); +CONVERT_TZ(TIME('2010-01-01 00:00:00'),'+00:00','+7:5') +NULL +Warnings: +Warning 1292 Truncated incorrect datetime value: '00:00:00' +SET old_mode=DEFAULT; +SET timestamp=DEFAULT; +# +# MDEV-13995 MAX(timestamp) returns a wrong result near DST change +# +SET time_zone='+00:00'; +CREATE TABLE t1 (a TIMESTAMP); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526) /*summer time in Moscow*/); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526+3599) /*winter time in Moscow*/); +SET time_zone='Europe/Moscow'; +SELECT a, UNIX_TIMESTAMP(a) FROM t1; +a UNIX_TIMESTAMP(a) +2010-10-31 02:25:26 1288477526 +2010-10-31 02:25:25 1288481125 +SELECT UNIX_TIMESTAMP(MAX(a)) AS a FROM t1; +a +1288481125 +CREATE TABLE t2 (a TIMESTAMP); +INSERT INTO t2 SELECT MAX(a) AS a FROM t1; +SELECT a, UNIX_TIMESTAMP(a) FROM t2; +a UNIX_TIMESTAMP(a) +2010-10-31 02:25:25 1288481125 +DROP TABLE t2; +DROP TABLE t1; +SET time_zone='+00:00'; +CREATE TABLE t1 (a TIMESTAMP); +CREATE TABLE t2 (a TIMESTAMP); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526) /*summer time in Moscow*/); +INSERT INTO t2 VALUES (FROM_UNIXTIME(1288477526+3599) /*winter time in Moscow*/); +SET time_zone='Europe/Moscow'; +SELECT UNIX_TIMESTAMP(t1.a), UNIX_TIMESTAMP(t2.a) FROM t1,t2; +UNIX_TIMESTAMP(t1.a) UNIX_TIMESTAMP(t2.a) +1288477526 1288481125 +SELECT * FROM t1,t2 WHERE t1.a < t2.a; +a a +2010-10-31 02:25:26 2010-10-31 02:25:25 +DROP TABLE t1,t2; +BEGIN NOT ATOMIC +DECLARE a,b TIMESTAMP; +SET time_zone='+00:00'; +SET a=FROM_UNIXTIME(1288477526); +SET b=FROM_UNIXTIME(1288481125); +SELECT a < b; +SET time_zone='Europe/Moscow'; +SELECT a < b; +END; +$$ +a < b +1 +a < b +1 +CREATE OR REPLACE FUNCTION f1(uts INT) RETURNS TIMESTAMP +BEGIN +DECLARE ts TIMESTAMP; +DECLARE tz VARCHAR(64) DEFAULT @@time_zone; +SET time_zone='+00:00'; +SET ts=FROM_UNIXTIME(uts); +SET time_zone=tz; +RETURN ts; +END; +$$ +SET time_zone='+00:00'; +SELECT f1(1288477526) < f1(1288481125); +f1(1288477526) < f1(1288481125) +1 +SET time_zone='Europe/Moscow'; +SELECT f1(1288477526) < f1(1288481125); +f1(1288477526) < f1(1288481125) +1 +DROP FUNCTION f1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP); +SET time_zone='+00:00'; +INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526) /*summer time in Mowcow*/, +FROM_UNIXTIME(1288481125) /*winter time in Moscow*/); +SELECT *, LEAST(a,b) FROM t1; +a b LEAST(a,b) +2010-10-30 22:25:26 2010-10-30 23:25:25 2010-10-30 22:25:26 +SET time_zone='Europe/Moscow'; +SELECT *, LEAST(a,b) FROM t1; +a b LEAST(a,b) +2010-10-31 02:25:26 2010-10-31 02:25:25 2010-10-31 02:25:26 +SELECT UNIX_TIMESTAMP(a), UNIX_TIMESTAMP(b), UNIX_TIMESTAMP(LEAST(a,b)) FROM t1; +UNIX_TIMESTAMP(a) UNIX_TIMESTAMP(b) UNIX_TIMESTAMP(LEAST(a,b)) +1288477526 1288481125 1288477526 +DROP TABLE t1; +CREATE TABLE t1 (a TIMESTAMP,b TIMESTAMP,c TIMESTAMP); +SET time_zone='+00:00'; +INSERT INTO t1 VALUES ( +FROM_UNIXTIME(1288477526) /*summer time in Moscow*/, +FROM_UNIXTIME(1288481125) /*winter time in Moscow*/, +FROM_UNIXTIME(1288481126) /*winter time in Moscow*/); +SELECT b BETWEEN a AND c FROM t1; +b BETWEEN a AND c +1 +SET time_zone='Europe/Moscow'; +SELECT b BETWEEN a AND c FROM t1; +b BETWEEN a AND c +1 +DROP TABLE t1; +SET time_zone='+00:00'; +CREATE TABLE t1 (a TIMESTAMP); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526) /*summer time in Mowcow*/); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1288481125) /*winter time in Moscow*/); +SELECT a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a; +a UNIX_TIMESTAMP(a) +2010-10-30 22:25:26 1288477526 +2010-10-30 23:25:25 1288481125 +SELECT COALESCE(a) AS a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a; +a UNIX_TIMESTAMP(a) +2010-10-30 22:25:26 1288477526 +2010-10-30 23:25:25 1288481125 +SET time_zone='Europe/Moscow'; +SELECT a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a; +a UNIX_TIMESTAMP(a) +2010-10-31 02:25:26 1288477526 +2010-10-31 02:25:25 1288481125 +SELECT COALESCE(a) AS a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a; +a UNIX_TIMESTAMP(a) +2010-10-31 02:25:26 1288477526 +2010-10-31 02:25:25 1288481125 +DROP TABLE t1; +SET time_zone='+00:00'; +CREATE TABLE t1 (a TIMESTAMP); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526) /*summer time in Mowcow*/); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1288481126) /*winter time in Moscow*/); +SET time_zone='Europe/Moscow'; +SELECT a, UNIX_TIMESTAMP(a) FROM t1 GROUP BY a; +a UNIX_TIMESTAMP(a) +2010-10-31 02:25:26 1288477526 +2010-10-31 02:25:26 1288481126 +DROP TABLE t1; +SET time_zone='+00:00'; +CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126)); +SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),CASE a WHEN b THEN 'eq' ELSE 'ne' END AS x FROM t1; +UNIX_TIMESTAMP(a) UNIX_TIMESTAMP(b) x +1288477526 1288481126 ne +SET time_zone='Europe/Moscow'; +SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),CASE a WHEN b THEN 'eq' ELSE 'ne' END AS x FROM t1; +UNIX_TIMESTAMP(a) UNIX_TIMESTAMP(b) x +1288477526 1288481126 ne +DROP TABLE t1; +SET time_zone='+00:00'; +CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP,c TIMESTAMP); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126),FROM_UNIXTIME(1288481127)); +SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),a IN (b,c) AS x FROM t1; +UNIX_TIMESTAMP(a) UNIX_TIMESTAMP(b) x +1288477526 1288481126 0 +SET time_zone='Europe/Moscow'; +SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),a IN (b,c) AS x FROM t1; +UNIX_TIMESTAMP(a) UNIX_TIMESTAMP(b) x +1288477526 1288481126 0 +DROP TABLE t1; +SET time_zone='+00:00'; +CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126)); +SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1); +a b +SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1); +a b +SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1)); +a b +SET time_zone='Europe/Moscow'; +SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1); +a b +SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1); +a b +SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1)); +a b +DROP TABLE t1; +SET time_zone='+00:00'; +CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1100000000),FROM_UNIXTIME(1200000000)); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1100000001),FROM_UNIXTIME(1200000001)); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481126)); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1300000000),FROM_UNIXTIME(1400000000)); +INSERT INTO t1 VALUES (FROM_UNIXTIME(1300000001),FROM_UNIXTIME(1400000001)); +SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1); +a b +SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1); +a b +SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1)); +a b +SET time_zone='Europe/Moscow'; +SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1); +a b +SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1); +a b +SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1)); +a b +DROP TABLE t1; +SET time_zone='Europe/Moscow'; +CREATE TABLE t1 (a TIMESTAMP); +CREATE TABLE t2 (a TIMESTAMP); +SET timestamp=1288479599 /*summer time in Mowcow*/; +INSERT INTO t1 VALUES (CURRENT_TIMESTAMP); +SET timestamp=1288479599+3600 /*winter time in Mowcow*/; +INSERT INTO t2 VALUES (CURRENT_TIMESTAMP); +SELECT t1.a, UNIX_TIMESTAMP(t1.a), t2.a, UNIX_TIMESTAMP(t2.a) FROM t1, t2; +a UNIX_TIMESTAMP(t1.a) a UNIX_TIMESTAMP(t2.a) +2010-10-31 02:59:59 1288479599 2010-10-31 02:59:59 1288483199 +SELECT NULLIF(t1.a, t2.a) FROM t1,t2; +NULLIF(t1.a, t2.a) +2010-10-31 02:59:59 +DROP TABLE t1, t2; +SET time_zone=DEFAULT; +SET timestamp=DEFAULT; +# +# MDEV-17979 Assertion `0' failed in Item::val_native upon SELECT with timestamp, NULLIF, GROUP BY +# +SET time_zone='+00:00'; +CREATE TABLE t1 (a INT, ts TIMESTAMP) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1, FROM_UNIXTIME(1288481126) /*winter time in Moscow*/); +SET time_zone='Europe/Moscow'; +CREATE TABLE t2 AS SELECT ts, COALESCE(ts) AS cts FROM t1 GROUP BY cts; +SELECT ts, cts, UNIX_TIMESTAMP(ts) AS uts, UNIX_TIMESTAMP(cts) AS ucts FROM t2; +ts cts uts ucts +2010-10-31 02:25:26 2010-10-31 02:25:26 1288481126 1288481126 +DROP TABLE t1,t2; +SET time_zone=DEFAULT; +# +# MDEV-19961 MIN(timestamp_column) returns a wrong result in a GROUP BY query +# +SET time_zone='Europe/Moscow'; +CREATE OR REPLACE TABLE t1 (i INT, d TIMESTAMP NOT NULL DEFAULT NOW()); +SET timestamp=1288477526 /* this is summer time */ ; +INSERT INTO t1 VALUES (3,NULL); +SET timestamp=1288477526+3599 /* this is winter time*/ ; +INSERT INTO t1 VALUES (3,NULL); +SELECT i, d, UNIX_TIMESTAMP(d) FROM t1 ORDER BY d; +i d UNIX_TIMESTAMP(d) +3 2010-10-31 02:25:26 1288477526 +3 2010-10-31 02:25:25 1288481125 +SELECT i, MIN(d) FROM t1 GROUP BY i; +i MIN(d) +3 2010-10-31 02:25:26 +SELECT i, MAX(d) FROM t1 GROUP BY i; +i MAX(d) +3 2010-10-31 02:25:25 +DROP TABLE t1; +SET timestamp=DEFAULT; +SET time_zone=DEFAULT; +# +# MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() +# +SET time_zone='Europe/Moscow'; +CREATE TABLE t1 (i INT, d TIMESTAMP(6) NOT NULL DEFAULT NOW()); +SET timestamp=1288479599.999999 /* this is the last second in summer time */ ; +INSERT INTO t1 VALUES (1,NULL); +SET timestamp=1288479600.000000 /* this is the first second in winter time */ ; +INSERT INTO t1 VALUES (2,NULL); +SELECT i, d, UNIX_TIMESTAMP(d) FROM t1 ORDER BY d; +i d UNIX_TIMESTAMP(d) +1 2010-10-31 02:59:59.999999 1288479599.999999 +2 2010-10-31 02:00:00.000000 1288479600.000000 +CREATE TABLE t2 (i INT, d TIMESTAMP, expected_unix_timestamp INT UNSIGNED); +INSERT INTO t2 SELECT i, ROUND(d) AS d, ROUND(UNIX_TIMESTAMP(d)) FROM t1; +# UNIX_TIMESTAMP(d) and expected_unix_timestamp should return the same value. +# Currently they do not, because ROUND(timestamp) is performed as DATETIME. +# We should fix this eventually. +SELECT i, d, UNIX_TIMESTAMP(d), expected_unix_timestamp FROM t2 ORDER BY i; +i d UNIX_TIMESTAMP(d) expected_unix_timestamp +1 2010-10-31 03:00:00 1288483200 1288479600 +2 2010-10-31 02:00:00 1288476000 1288479600 +DROP TABLE t2; +DROP TABLE t1; +SET timestamp=DEFAULT; +SET time_zone=DEFAULT; +# +# End of 10.4 tests +# +# +# MDEV-27101 Subquery using the ALL keyword on TIMESTAMP columns produces a wrong result +# +SET time_zone='Europe/Moscow'; +CREATE TABLE t1 (a TIMESTAMP NULL); +SET timestamp=1288477526; +/* this is summer time, earlier */ +INSERT INTO t1 VALUES (NOW()); +SET timestamp=1288477526+3599; +/* this is winter time, later */ +INSERT INTO t1 VALUES (NOW()); +SELECT a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a; +a UNIX_TIMESTAMP(a) +2010-10-31 02:25:26 1288477526 +2010-10-31 02:25:25 1288481125 +SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a <= ALL (SELECT * FROM t1); +a UNIX_TIMESTAMP(a) +2010-10-31 02:25:26 1288477526 +SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a >= ALL (SELECT * FROM t1); +a UNIX_TIMESTAMP(a) +2010-10-31 02:25:25 1288481125 +DROP TABLE t1; |