diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/main/timezone2.result | 189 | ||||
| -rw-r--r-- | mysql-test/main/timezone2.test | 167 | ||||
| -rw-r--r-- | mysql-test/main/type_timestamp.result | 20 | ||||
| -rw-r--r-- | mysql-test/main/type_timestamp.test | 20 |
4 files changed, 396 insertions, 0 deletions
diff --git a/mysql-test/main/timezone2.result b/mysql-test/main/timezone2.result index 6de62d7ea30..a58a0ee632f 100644 --- a/mysql-test/main/timezone2.result +++ b/mysql-test/main/timezone2.result @@ -353,5 +353,194 @@ 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; +# # End of 10.4 tests # diff --git a/mysql-test/main/timezone2.test b/mysql-test/main/timezone2.test index 773b40ec86c..e0c6eb08108 100644 --- a/mysql-test/main/timezone2.test +++ b/mysql-test/main/timezone2.test @@ -325,5 +325,172 @@ SET old_mode=DEFAULT; SET timestamp=DEFAULT; --echo # +--echo # MDEV-13995 MAX(timestamp) returns a wrong result near DST change +--echo # + +# MAX() +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; +SELECT UNIX_TIMESTAMP(MAX(a)) AS a FROM t1; +CREATE TABLE t2 (a TIMESTAMP); +INSERT INTO t2 SELECT MAX(a) AS a FROM t1; +SELECT a, UNIX_TIMESTAMP(a) FROM t2; +DROP TABLE t2; +DROP TABLE t1; + + +# Comparison +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; +SELECT * FROM t1,t2 WHERE t1.a < t2.a; +DROP TABLE t1,t2; + + +# SP variable comparison +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + + +# SP function comparison +DELIMITER $$; +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; +$$ +DELIMITER ;$$ +SET time_zone='+00:00'; +SELECT f1(1288477526) < f1(1288481125); +SET time_zone='Europe/Moscow'; +SELECT f1(1288477526) < f1(1288481125); +DROP FUNCTION f1; + + +# LEAST() +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; +SET time_zone='Europe/Moscow'; +SELECT *, LEAST(a,b) FROM t1; +SELECT UNIX_TIMESTAMP(a), UNIX_TIMESTAMP(b), UNIX_TIMESTAMP(LEAST(a,b)) FROM t1; +DROP TABLE t1; + + +# BETWEEN +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; +SET time_zone='Europe/Moscow'; +SELECT b BETWEEN a AND c FROM t1; +DROP TABLE t1; + + +# ORDER BY +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; +SELECT COALESCE(a) AS a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a; +SET time_zone='Europe/Moscow'; +SELECT a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a; +SELECT COALESCE(a) AS a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a; +DROP TABLE t1; + + +# GROUP BY +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; +DROP TABLE t1; + + +# CASE +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; +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; +DROP TABLE t1; + + +# IN +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; +SET time_zone='Europe/Moscow'; +SELECT UNIX_TIMESTAMP(a),UNIX_TIMESTAMP(b),a IN (b,c) AS x FROM t1; +DROP TABLE t1; + +# Comparison and IN in combination with a subquery (with one row) + +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); +SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1); +SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1)); + +SET time_zone='Europe/Moscow'; +SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1); +SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1); +SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1)); +DROP TABLE t1; + +# Comparison and IN in combinarion with a subquery (with multiple rows) +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); +SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1); +SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1)); + +SET time_zone='Europe/Moscow'; +SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1); +SELECT * FROM t1 WHERE a = (SELECT MIN(b) FROM t1); +SELECT * FROM t1 WHERE a IN ((SELECT MAX(b) FROM t1), (SELECT MIN(b) FROM t1)); +DROP TABLE t1; + + +--echo # --echo # End of 10.4 tests --echo # diff --git a/mysql-test/main/type_timestamp.result b/mysql-test/main/type_timestamp.result index a708ef5ec34..97ab8744b8c 100644 --- a/mysql-test/main/type_timestamp.result +++ b/mysql-test/main/type_timestamp.result @@ -1033,5 +1033,25 @@ CREATE TABLE t1 (a TIMESTAMP); INSERT INTO t1 SELECT CAST(20010101 AS UNSIGNED); DROP TABLE t1; # +# MDEV-13995 MAX(timestamp) returns a wrong result near DST change +# +# Testing Item_func_rollup_const::val_native() +# There is a bug in the below output (MDEV-16612) +# Please remove this comment when MDEV-16612 is fixed and results are re-recorded +CREATE TABLE t1 (id INT); +INSERT INTO t1 VALUES (1),(2); +BEGIN NOT ATOMIC +DECLARE v TIMESTAMP DEFAULT '2001-01-01 10:20:30'; -- "v" will be wrapped into Item_func_rollup_const +SELECT id, v AS v, COUNT(*) FROM t1 GROUP BY id,v WITH ROLLUP; +END; +$$ +id v COUNT(*) +1 2001-01-01 10:20:30 1 +1 2001-01-01 10:20:30 1 +2 2001-01-01 10:20:30 1 +2 2001-01-01 10:20:30 1 +NULL 2001-01-01 10:20:30 2 +DROP TABLE t1; +# # End of 10.4 tests # diff --git a/mysql-test/main/type_timestamp.test b/mysql-test/main/type_timestamp.test index eeb6e2f8213..7fb3f5d6cd2 100644 --- a/mysql-test/main/type_timestamp.test +++ b/mysql-test/main/type_timestamp.test @@ -626,5 +626,25 @@ INSERT INTO t1 SELECT CAST(20010101 AS UNSIGNED); DROP TABLE t1; --echo # +--echo # MDEV-13995 MAX(timestamp) returns a wrong result near DST change +--echo # + +--echo # Testing Item_func_rollup_const::val_native() + +--echo # There is a bug in the below output (MDEV-16612) +--echo # Please remove this comment when MDEV-16612 is fixed and results are re-recorded + +CREATE TABLE t1 (id INT); +INSERT INTO t1 VALUES (1),(2); +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE v TIMESTAMP DEFAULT '2001-01-01 10:20:30'; -- "v" will be wrapped into Item_func_rollup_const + SELECT id, v AS v, COUNT(*) FROM t1 GROUP BY id,v WITH ROLLUP; +END; +$$ +DELIMITER ;$$ +DROP TABLE t1; + +--echo # --echo # End of 10.4 tests --echo # |