[MDEV-27101] Subquery using the ALL keyword on TIMESTAMP columns produces a wrong result - Jira

XML

Word

Printable

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL)
Fix Version/s: 10.9.2, 10.10.1, 10.4.27, 10.5.18, 10.6.10, 10.7.6, 10.8.5
Component/s: Data types
Labels:
None

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:

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.

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

relates to

MDEV-27098 Subquery using the ALL keyword on TIME columns produces a wrong result

MDEV-27099 Subquery using the ALL keyword on INET6 columns produces a wrong result

MDEV-27100 Subquery using the ALL keyword on UUID columns produces a wrong result

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.