|
This query:
SELECT UNIX_TIMESTAMP(MAX(a)) AS a FROM t1;
|
returns
+------------+
|
| a |
|
+------------+
|
| 1288481126 |
|
+------------+
|
Looks wrong. The expected result would be 1288481125.
|
|
A similar problem is repeatable with comparison.
This script:
SET time_zone='Europe/Moscow';
|
CREATE OR REPLACE TABLE t1 (a TIMESTAMP);
|
CREATE OR REPLACE TABLE t2 (a TIMESTAMP);
|
SET timestamp=1288477526; -- this is summer time
|
INSERT INTO t1 VALUES (NULL);
|
SET timestamp=1288477526+3599; -- this is winter time
|
INSERT INTO t2 VALUES (null);
|
SELECT UNIX_TIMESTAMP(t1.a), UNIX_TIMESTAMP(t2.a) FROM t1,t2;
|
returns:
+----------------------+----------------------+
|
| UNIX_TIMESTAMP(t1.a) | UNIX_TIMESTAMP(t2.a) |
|
+----------------------+----------------------+
|
| 1288477526 | 1288481125 |
|
+----------------------+----------------------+
|
The timestamp value in t1.a is obviously less than the value in t2.a.
However, this script returns empty set:
SELECT * FROM t1,t2 WHERE t1.a < t2.a;
|
If I now change the current session time zone, it correctly returns one row:
SET timestamp=DEFAULT;
|
SELECT * FROM t1,t2 WHERE t1.a < t2.a;
|
+---------------------+---------------------+
|
| a | a |
|
+---------------------+---------------------+
|
| 2010-10-30 22:25:26 | 2010-10-30 23:25:25 |
|
+---------------------+---------------------+
|
|
|
The same problem is repeatable with SP variables:
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 ;
|
+-------+
|
| a < b |
|
+-------+
|
| 1 |
|
+-------+
|
1 row in set (0.01 sec)
|
+-------+
|
| a < b |
|
+-------+
|
| 0 |
|
+-------+
|
1 row in set (0.01 sec)
|
The second result should return 1 rather than 0.
|
|
The same problem is repeatable with BETWEEN:
This scipt correctly returns 1:
CREATE OR REPLACE TABLE t1 (a TIMESTAMP,b TIMESTAMP,c TIMESTAMP);
|
SET time_zone='+00:00';
|
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481125),FROM_UNIXTIME(1288481126));
|
SELECT b BETWEEN a AND c FROM t1;
|
+-------------------+
|
| b BETWEEN a AND c |
|
+-------------------+
|
| 1 |
|
+-------------------+
|
Now if I change the time zone and re-run the query, it erroneously returns 0:
SET time_zone='Europe/Moscow';
|
SELECT b BETWEEN a AND c FROM t1;
|
+-------------------+
|
| b BETWEEN a AND c |
|
+-------------------+
|
| 0 |
|
+-------------------+
|
|
|
The same problem is repeatable with stored functions returning TIMESTAMP values:
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);
|
+---------------------------------+
|
| f1(1288477526) < f1(1288481125) |
|
+---------------------------------+
|
| 1 |
|
+---------------------------------+
|
SET time_zone='Europe/Moscow';
|
SELECT f1(1288477526) < f1(1288481125);
|
+---------------------------------+
|
| f1(1288477526) < f1(1288481125) |
|
+---------------------------------+
|
| 0 |
|
+---------------------------------+
|
The last query should return 1.
|
|
The same problem is repeatable with LEAST.
This script correctly returns a in LEAST(a,b):
CREATE OR REPLACE TABLE t1 (a TIMESTAMP,b TIMESTAMP);
|
SET time_zone='+00:00';
|
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288481125));
|
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 |
|
+---------------------+---------------------+---------------------+
|
Now if I change the time zone, it wrongly returns the value of b in LEAST(a,b):
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:25 |
|
+---------------------+---------------------+---------------------+
|
Now I add UNIX_TIMESTAMP to all three fields:
SET time_zone='Europe/Moscow';
|
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 | 1288477525 |
|
+-------------------+-------------------+----------------------------+
|
The UNIX_TIMESTAMP value for LEAST is wrong. It's neither UNIX_TIMESTAMP(a) nor UNIX_TIMESTAMP(b).
The expected value should be 1288477526, i.e. the same with UNIX_TIMESTAMP(a).
|
|
The same problem is repeatable with ORDER BY.
I create a table with two TIMESTAMP values near DST change (Moscow time) and do two SELECT..ORDER BY queries:
- using the column a directly, and
- using COALESCE(a):
SET time_zone='+00:00';
|
CREATE OR REPLACE TABLE t1 (a TIMESTAMP);
|
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526)); -- this is summer time in Mowcow
|
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288481125)); -- this is 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;
|
Both SELECT queries return the same correct result:
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2010-10-30 22:25:26 | 1288477526 |
|
| 2010-10-30 23:25:25 | 1288481125 |
|
+---------------------+-------------------+
|
Now I change the time zone and rerun the two SELECT..ORDER BY queries:
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;
|
The first SELECT returns a correct result, according to the timestamp value:
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2010-10-31 02:25:26 | 1288477526 |
|
| 2010-10-31 02:25:25 | 1288481125 |
|
+---------------------+-------------------+
|
The second SELECT with COALESCE(a) returns a result with a wrong order (the result is ordered by DATETIME instead of TIMESTAMP):
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2010-10-31 02:25:25 | 1288481125 |
|
| 2010-10-31 02:25:26 | 1288477526 |
|
+---------------------+-------------------+
|
The second query should return results in the same order with the first query.
|
|
GROUP BY is not affected and correctly returns two rows in this example script:
SET time_zone='+00:00';
|
CREATE OR REPLACE TABLE t1 (a TIMESTAMP);
|
INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526)),(FROM_UNIXTIME(1288481126));
|
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 |
|
+---------------------+-------------------+
|
|
|
Searched CASE is affected:
SET time_zone='+00:00';
|
CREATE OR REPLACE 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;
|
+-------------------+-------------------+----+
|
| UNIX_TIMESTAMP(a) | UNIX_TIMESTAMP(b) | x |
|
+-------------------+-------------------+----+
|
| 1288477526 | 1288481126 | ne |
|
+-------------------+-------------------+----+
|
+-------------------+-------------------+----+
|
| UNIX_TIMESTAMP(a) | UNIX_TIMESTAMP(b) | x |
|
+-------------------+-------------------+----+
|
| 1288477526 | 1288481126 | eq |
|
+-------------------+-------------------+----+
|
The second result is wrong. Both SELECT queries are expected to return ne.
|
|
IN is affected:
SET time_zone='+00:00';
|
CREATE OR REPLACE 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;
|
+-------------------+-------------------+---+
|
| UNIX_TIMESTAMP(a) | UNIX_TIMESTAMP(b) | x |
|
+-------------------+-------------------+---+
|
| 1288477526 | 1288481126 | 0 |
|
+-------------------+-------------------+---+
|
+-------------------+-------------------+---+
|
| UNIX_TIMESTAMP(a) | UNIX_TIMESTAMP(b) | x |
|
+-------------------+-------------------+---+
|
| 1288477526 | 1288481126 | 1 |
|
+-------------------+-------------------+---+
|
The second result is wrong. Both SELECT queries are expected to return 0.
|
|
The same problem is repeatable with subqueries involved in comparison or IN.
This script correctly returns empty set in all three SELECT queries:
SET time_zone='+00:00';
|
CREATE OR REPLACE 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));
|
Now if I change the time zone, all three queries return the row:
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));
|
MariaDB [test]> SELECT * FROM t1 WHERE a = (SELECT MAX(b) FROM t1);
|
+---------------------+---------------------+
|
| a | b |
|
+---------------------+---------------------+
|
| 2010-10-31 02:25:26 | 2010-10-31 02:25:26 |
|
+---------------------+---------------------+
|
|
+---------------------+---------------------+
|
| a | b |
|
+---------------------+---------------------+
|
| 2010-10-31 02:25:26 | 2010-10-31 02:25:26 |
|
+---------------------+---------------------+
|
|
+---------------------+---------------------+
|
| a | b |
|
+---------------------+---------------------+
|
| 2010-10-31 02:25:26 | 2010-10-31 02:25:26 |
|
+---------------------+---------------------+
|
|
|
Ideas we discussed:
1. special type for raw native data
2. probably union to have no problem with short types with fixed length like int, double, etc)
3. using raw is not good as it easy can be mixed with row (better native)
|
|
sanja, please review a new patch version:
The patch is here:
https://github.com/MariaDB/server/commits/bb-10.4-mdev13995
https://github.com/MariaDB/server/commit/d9b70161d4b84c50ea15dafbcdbf61a326b018c1
|
|
OK to push!
|