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

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

Details

    Description

      This bug related to MDEV-27072 but happend because of a very different reason than MDEV-27072.

      I create a table:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (d TIME);
      		 
      INSERT INTO t1 VALUES ('120:00:00'), ('20:00:00'), ('-120:00:00'), ('-220:00:00');

      Now I run this query:

      SELECT * FROM t1 WHERE d >= ALL (SELECT * FROM t1);


      +-----------+
      		 
      | d         |
      		 
      +-----------+
      		 
      | 120:00:00 |
      		 
      | 20:00:00  |
      		 
      +-----------+

      The result is wrong. It should return only one row the maxumum value, which is '120:00:00'.

      The same problem happens with this query:

      SELECT * FROM t1 WHERE d <= ALL (SELECT * FROM t1);


      +------------+
      		 
      | d          |
      		 
      +------------+
      		 
      | -120:00:00 |
      		 
      | -220:00:00 |
      		 
      +------------+

      It should return only one row with the minumum value, which is '-220:00:00'.

      The problem happens because select_max_min_finder_subselect::cmp_str() is executed at some point during these queries. It compares values as strings, therefore:

      • '120:00:00' is erroneously compared as less than '20:00:00'
      • '-220:00:00' is erroneously compared as greater than '-120:00:00'

      The TIME data type needs its own implementation, e.g. select_max_min_finder_subselect::cmp_time().

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27072 Subquery using the ALL keyword on date 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

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

          • Major - Major loss of function.
          • Closed

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.