Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5, 10.6, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.7(EOL), 10.8(EOL)
-
None
Description
This bug is related to MDEV-27098.
To reproduce this bug, make sure the time zone information is loaded, e.g.:
mysql_tzinfo_to_sql /usr/share/zoneinfo/|mysql -uroot mysql
|
I create a table with a TIMESTAMP column and insert to values near DST change:
- one value before the DST change
- one value after the DST change
SET time_zone='Europe/Moscow'; |
CREATE OR REPLACE TABLE t1 (a TIMESTAMP NULL); |
SET timestamp=1288477526; -- this is summer time |
INSERT INTO t1 VALUES (NOW()); |
SET timestamp=1288477526+3599; -- this is winter time |
INSERT INTO t1 VALUES (NOW()); |
SELECT a, UNIX_TIMESTAMP(a) FROM t1; |
It returns the following result:
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2010-10-31 02:25:26 | 1288477526 | <-- this value is smaller
|
| 2010-10-31 02:25:25 | 1288481125 | <-- this value is greater
|
+---------------------+-------------------+
|
Now I run a query with an ALL subquery:
SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a <= ALL (SELECT * FROM t1); |
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2010-10-31 02:25:26 | 1288477526 |
|
| 2010-10-31 02:25:25 | 1288481125 |
|
+---------------------+-------------------+
|
The result is wrong. The query is expected to return only the smallest value - the first record.
Now I run a different query with an ALL subquery:
SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a >= ALL (SELECT * FROM t1); |
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2010-10-31 02:25:26 | 1288477526 |
|
| 2010-10-31 02:25:25 | 1288481125 |
|
+---------------------+-------------------+
|
The result is wrong. The query is expected to return only the greatest value - the second record.
Attachments
Issue Links
- causes
-
MDEV-29924 Assertion `(((nr) % (1LL << 24)) % (int) log_10_int[6 - dec]) == 0' failed in my_time_packed_to_binary on SELECT when using TIME field
- Closed
- relates to
-
MDEV-27098 Subquery using the ALL keyword on TIME columns produces a wrong result
- Closed
-
MDEV-27099 Subquery using the ALL keyword on INET6 columns produces a wrong result
- Closed
-
MDEV-27100 Subquery using the ALL keyword on UUID columns produces a wrong result
- Closed