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

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

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

          causes

          Bug - A problem which impairs or prevents the functions of the product. 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

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27098 Subquery using the ALL keyword on TIME columns produces a wrong result

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27099 Subquery using the ALL keyword on INET6 columns produces a wrong result

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27100 Subquery using the ALL keyword on UUID columns produces a wrong result

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 127881 ] MariaDB v4 [ 143376 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.2 [ 14601 ]
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2022-08-22 11:13:38.0 2022-08-22 11:13:38.791
            bar Alexander Barkov made changes -
            Fix Version/s 10.9.2 [ 27115 ]
            Fix Version/s 10.10.1 [ 27913 ]
            Fix Version/s 10.4.27 [ 28405 ]
            Fix Version/s 10.6.10 [ 28407 ]
            Fix Version/s 10.7.6 [ 28408 ]
            Fix Version/s 10.8.5 [ 28308 ]
            Fix Version/s 10.5.18 [ 28421 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.7 [ 24805 ]
            Fix Version/s 10.8 [ 26121 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            elenst Elena Stepanova made changes -

            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.