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

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov added a comment - - edited

            The same problem is repeatable with the INET6 data type: See MDEV-27099.

            bar Alexander Barkov added a comment - - edited The same problem is repeatable with the INET6 data type: See MDEV-27099 .
            bar Alexander Barkov added a comment - - edited

            The same problem is repeatable with the UUID data type: see MDEV-27100

            bar Alexander Barkov added a comment - - edited The same problem is repeatable with the UUID data type: see MDEV-27100
            bar Alexander Barkov made changes -
            Field Original Value New Value
            Description This bug related to MDEV-27072 but happend because of a very different reason than MDEV-27072.


            I run create a table:
            {code:sql}
            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');
            {code}

            Now I run this query:

            {code:sql}
            SELECT * FROM t1 WHERE d >= ALL (SELECT * FROM t1);
            {code}
            {noformat}
            +-----------+
            | d |
            +-----------+
            | 120:00:00 |
            | 20:00:00 |
            +-----------+
            {noformat}
            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:
            {code:sql}
            SELECT * FROM t1 WHERE d <= ALL (SELECT * FROM t1);
            {code}
            {noformat}
            +------------+
            | d |
            +------------+
            | -120:00:00 |
            | -220:00:00 |
            +------------+
            {noformat}
            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'
            		This bug related to MDEV-27072 but happend because of a very different reason than MDEV-27072.


            I create a table:
            {code:sql}
            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');
            {code}

            Now I run this query:

            {code:sql}
            SELECT * FROM t1 WHERE d >= ALL (SELECT * FROM t1);
            {code}
            {noformat}
            +-----------+
            | d |
            +-----------+
            | 120:00:00 |
            | 20:00:00 |
            +-----------+
            {noformat}
            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:
            {code:sql}
            SELECT * FROM t1 WHERE d <= ALL (SELECT * FROM t1);
            {code}
            {noformat}
            +------------+
            | d |
            +------------+
            | -120:00:00 |
            | -220:00:00 |
            +------------+
            {noformat}
            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'
            bar Alexander Barkov made changes -
            Description This bug related to MDEV-27072 but happend because of a very different reason than MDEV-27072.


            I create a table:
            {code:sql}
            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');
            {code}

            Now I run this query:

            {code:sql}
            SELECT * FROM t1 WHERE d >= ALL (SELECT * FROM t1);
            {code}
            {noformat}
            +-----------+
            | d |
            +-----------+
            | 120:00:00 |
            | 20:00:00 |
            +-----------+
            {noformat}
            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:
            {code:sql}
            SELECT * FROM t1 WHERE d <= ALL (SELECT * FROM t1);
            {code}
            {noformat}
            +------------+
            | d |
            +------------+
            | -120:00:00 |
            | -220:00:00 |
            +------------+
            {noformat}
            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'
            		This bug related to MDEV-27072 but happend because of a very different reason than MDEV-27072.


            I create a table:
            {code:sql}
            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');
            {code}

            Now I run this query:

            {code:sql}
            SELECT * FROM t1 WHERE d >= ALL (SELECT * FROM t1);
            {code}
            {noformat}
            +-----------+
            | d |
            +-----------+
            | 120:00:00 |
            | 20:00:00 |
            +-----------+
            {noformat}
            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:
            {code:sql}
            SELECT * FROM t1 WHERE d <= ALL (SELECT * FROM t1);
            {code}
            {noformat}
            +------------+
            | d |
            +------------+
            | -120:00:00 |
            | -220:00:00 |
            +------------+
            {noformat}
            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()}}.

            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Summary Subquery using the ALL keyword on time columns produces a wrong result Subquery using the ALL keyword on TIME columns produces a wrong result
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.2.43 [ 26804 ]
            Fix Version/s 10.3.34 [ 26806 ]
            Fix Version/s 10.4.24 [ 26808 ]
            Fix Version/s 10.5.15 [ 26810 ]
            Fix Version/s 10.6.7 [ 26812 ]
            Fix Version/s 10.2 [ 14601 ]
            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 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 127878 ] MariaDB v4 [ 159861 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.2.42 [ 26803 ]
            Fix Version/s 10.2.43 [ 26804 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3.33 [ 26805 ]
            Fix Version/s 10.4.23 [ 26807 ]
            Fix Version/s 10.5.14 [ 26809 ]
            Fix Version/s 10.6.6 [ 26811 ]
            Fix Version/s 10.3.34 [ 26806 ]
            Fix Version/s 10.4.24 [ 26808 ]
            Fix Version/s 10.5.15 [ 26810 ]
            Fix Version/s 10.6.7 [ 26812 ]

            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.