Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27101

Subquery using the ALL keyword on TIMESTAMP columns produces a wrong result

    XMLWordPrintable

Details

    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

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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