|
Before repeating the problem, make sure that you have timezone information loaded into MariaDB server, so this statement does not produce errors or warnings:
SET time_zone='Europe/Moscow';
|
I create and populate a table with a TIMESTAMP column as follows:
SET time_zone='Europe/Moscow';
|
CREATE OR REPLACE TABLE t1 (a TIMESTAMP);
|
SET timestamp=1288477526; -- this is summer time
|
INSERT INTO t1 VALUES (NULL);
|
SET timestamp=1288477526+3600; -- this is winter time
|
INSERT INTO t1 VALUES (null);
|
SELECT a, UNIX_TIMESTAMP(a) FROM t1;
|
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2010-10-31 02:25:26 | 1288477526 |
|
| 2010-10-31 02:25:26 | 1288481126 |
|
+---------------------+-------------------+
|
Now I want to get the first record:
SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a='2010-10-31 02:25:26' AND UNIX_TIMESTAMP(a)=1288477526;
|
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2010-10-31 02:25:26 | 1288477526 |
|
| 2010-10-31 02:25:26 | 1288481126 |
|
+---------------------+-------------------+
|
Looks wrong. Expect to get one record.
EXPLAIN EXTENDED for this query reports that the UNIX_TIMESTAMP(a)=1288477526 was removed from the condition:
EXPLAIN EXTENDED SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a='2010-10-31 02:25:26' AND UNIX_TIMESTAMP(a)=1288477526;
|
SHOW WARNINGS;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a`,unix_timestamp(`test`.`t1`.`a`) AS `UNIX_TIMESTAMP(a)` from `test`.`t1` where `test`.`t1`.`a` = TIMESTAMP'2010-10-31 02:25:26' |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Now I want to get the second record:
SELECT a,UNIX_TIMESTAMP(a) FROM t1 WHERE a='2010-10-31 02:25:26' AND UNIX_TIMESTAMP(a)=1288481126;
|
Looks wrong. Expect one record.
EXPLAIN EXTENDED for this query shows that the condition was erroneously calculated as always-FALSE:
EXPLAIN EXTENDED SELECT *, UNIX_TIMESTAMP(a) FROM t1 WHERE a='2010-10-31 02:25:26' AND UNIX_TIMESTAMP(a)=1288481126;
|
SHOW WARNINGS;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
|
+-------+------+---------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+---------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a`,unix_timestamp(`test`.`t1`.`a`) AS `UNIX_TIMESTAMP(a)` from `test`.`t1` where 0 |
|
+-------+------+---------------------------------------------------------------------------------------------------------------+
|
|