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

BIGINT 99991231000000 to TIME'00:00:00' comparison is not consistent

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.0.14, 10.10.1, 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
    • 10.4(EOL)
    • None
    • None

    Description

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a BIGINT, b TIME);
      		 
      INSERT INTO t1 VALUES (99991231000000,'00:00:00');
      		 
      SELECT a, b, 99991231000000=TIME'00:00:00',a=TIME'00:00:00',99991231000000=b,a=b FROM t1;

      returns the following:

      +----------------+----------+-------------------------------+------------------+------------------+------+
      		 
      | a              | b        | 99991231000000=TIME'00:00:00' | a=TIME'00:00:00' | 99991231000000=b | a=b  |
      		 
      +----------------+----------+-------------------------------+------------------+------------------+------+
      		 
      | 99991231000000 | 00:00:00 |                             1 |                0 |                1 |    0 |
      		 
      +----------------+----------+-------------------------------+------------------+------------------+------+

      That is 999991231000000=TIME'00:00:00' is:

      • equal when both are literals
      • not equal with a numeric field and a TIME literal
      • equal with a numeric literal and a TIME field
      • not equal when both are fields

      This looks inconsistent, because in all cases it should be compared as TIME,
      according to the current comparison rules.

      Attachments

        Issue Links

          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases

          • 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
            Summary BIGING 99991231000000 to TIME'00:00:00' comparison is not consistent. BIGINT 99991231000000 to TIME'00:00:00' comparison is not consistent.
            bar Alexander Barkov made changes -
            Summary BIGINT 99991231000000 to TIME'00:00:00' comparison is not consistent. BIGINT 99991231000000 to TIME'00:00:00' comparison is not consistent
            serg Sergei Golubchik made changes -
            Fix Version/s 10.0 [ 16000 ]
            bar Alexander Barkov made changes -
            Description {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a BIGINT, b TIME);
            INSERT INTO t1 VALUES (99991231000000,'00:00:00');
            SELECT 99991231000000=TIME'00:00:00',a,a='00:00:00',b,b=99991231000000,a=b FROM t1;
            {code}

            returns the following:

            {noformat}
            +-------------------------------+----------------+--------------+----------+------------------+------+
            | 99991231000000=TIME'00:00:00' | a | a='00:00:00' | b | b=99991231000000 | a=b |
            +-------------------------------+----------------+--------------+----------+------------------+------+
            | 1 | 99991231000000 | 0 | 00:00:00 | 1 | 0 |
            +-------------------------------+----------------+--------------+----------+------------------+------+
            {noformat}

            That is 999991231000000=TIME'00:00:00' is:
            - equal when both are literals
            - not equal with a numeric field and a TIME literal
            - equal with a numeric literal and a TIME field
            - not equal when both are fields

            This looks inconsistent, because in all cases it should be compared as TIME,
            according to the current comparison rules.
            		 {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a BIGINT, b TIME);
            INSERT INTO t1 VALUES (99991231000000,'00:00:00');
            SELECT a, b, 99991231000000=TIME'00:00:00',a=TIME'00:00:00',99991231000000=b,a=b FROM t1;
            {code}

            returns the following:

            {noformat}
            +----------------+----------+-------------------------------+------------------+------------------+------+
            | a | b | 99991231000000=TIME'00:00:00' | a=TIME'00:00:00' | 99991231000000=b | a=b |
            +----------------+----------+-------------------------------+------------------+------------------+------+
            | 99991231000000 | 00:00:00 | 1 | 0 | 1 | 0 |
            +----------------+----------+-------------------------------+------------------+------------------+------+
            {noformat}

            That is 999991231000000=TIME'00:00:00' is:
            - equal when both are literals
            - not equal with a numeric field and a TIME literal
            - equal with a numeric literal and a TIME field
            - not equal when both are fields

            This looks inconsistent, because in all cases it should be compared as TIME,
            according to the current comparison rules.
            bar Alexander Barkov added a comment -

            This should be easier to fix after pushing the refactoring done under terms of MDEV-6991.

            bar Alexander Barkov added a comment - This should be easier to fix after pushing the refactoring done under terms of MDEV-6991 .
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Fix Version/s 10.1 [ 16100 ]
            Fix Version/s 10.0 [ 16000 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.0 [ 16000 ]
            Fix Version/s 10.1 [ 16100 ]
            bar Alexander Barkov made changes -
            Priority Major [ 3 ] Minor [ 4 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 57301 ] MariaDB v3 [ 66941 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 66941 ] MariaDB v4 [ 139717 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.0 [ 16000 ]
            bar Alexander Barkov added a comment - - edited

            The inconsistency is still repeatable in all versions between 10.4 10.10, but in a different way:

            DROP TABLE IF EXISTS t1;
            		 
            CREATE TABLE t1 (a BIGINT, b TIME);
            		 
            INSERT INTO t1 VALUES (99991231000000,'00:00:00');
            		 
            SELECT a, b, 99991231000000=TIME'00:00:00',a=TIME'00:00:00',99991231000000=b,a=b FROM t1;


            +----------------+----------+-------------------------------+------------------+------------------+------+
            		 
            | a              | b        | 99991231000000=TIME'00:00:00' | a=TIME'00:00:00' | 99991231000000=b | a=b  |
            		 
            +----------------+----------+-------------------------------+------------------+------------------+------+
            		 
            | 99991231000000 | 00:00:00 |                             1 |                0 |                1 |    1 |
            		 
            +----------------+----------+-------------------------------+------------------+------------------+------+

            bar Alexander Barkov added a comment - - edited The inconsistency is still repeatable in all versions between 10.4 10.10, but in a different way: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a BIGINT , b TIME ); INSERT INTO t1 VALUES (99991231000000, '00:00:00' ); SELECT a, b, 99991231000000= TIME '00:00:00' ,a= TIME '00:00:00' ,99991231000000=b,a=b FROM t1; +----------------+----------+-------------------------------+------------------+------------------+------+ | a | b | 99991231000000=TIME'00:00:00' | a=TIME'00:00:00' | 99991231000000=b | a=b | +----------------+----------+-------------------------------+------------------+------------------+------+ | 99991231000000 | 00:00:00 | 1 | 0 | 1 | 1 | +----------------+----------+-------------------------------+------------------+------------------+------+
            bar Alexander Barkov made changes -
            Affects Version/s 10.10 [ 27530 ]
            bar Alexander Barkov made changes -
            Affects Version/s 10.10.1 [ 27913 ]
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6 [ 24028 ]
            Affects Version/s 10.7 [ 24805 ]
            Affects Version/s 10.8 [ 26121 ]
            Affects Version/s 10.9 [ 26905 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.4 [ 22408 ]

            People

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

              Dates

                Created:
                Updated:

                Git Integration

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