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

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

    XMLWordPrintable

    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

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              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.