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

Wrong data type for GREATEST(bit_column, int_column)

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.0(EOL)
    • 10.1.8
    • OTHER
    • None

    Description

      This problem was earlier fixed in 10.1 and is not really repeatable in 10.2.

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a BIT(64),b INT);
      		 
      INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF,-1);
      		 
      SELECT a>b, GREATEST(a,b) FROM t1;


      +------+---------------+
      		 
      | a>b  | GREATEST(a,b) |
      		 
      +------+---------------+
      		 
      |    1 | -1            |
      		 
      +------+---------------+

      The value of the first column looks good, as BIT is treated like a unsigned number, according to design.
      The value of the second column is wrong. It should return 18446744073709551615.

      Now I do:

      DROP TABLE IF EXISTS t2;
      		 
      CREATE TABLE t2 AS SELECT COALESCE(a,b),GREATEST(a,b) FROM t1;
      		 
      SELECT * FROM t2;


      +----------------------+---------------+
      		 
      | COALESCE(a,b)        | GREATEST(a,b) |
      		 
      +----------------------+---------------+
      		 
      | 18446744073709551615 |            -1 |
      		 
      +----------------------+---------------+

      The value of the first column with COALESCE is correct.
      The value of the first column with GREATEST is wrong.

      Now I check the table structure:

      SHOW CREATE TABLE t2;


      +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Table | Create Table                                                                                                                                       |
      		 
      +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | t2    | CREATE TABLE `t2` (
      		 
        `COALESCE(a,b)` decimal(64,0) DEFAULT NULL,
      		 
        `GREATEST(a,b)` bigint(65) DEFAULT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      		 
      +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+

      The data type of the first column with COALESCE is correct.
      The data type of the second column with GREATEST is wrong.

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8871 Wrong result for CREATE TABLE .. SELECT LEAST(unsigned_column,unsigned_column)

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-12506 Split Item_func_min_max::fix_length_and_dec() into methods in Type_handler

          • 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 -
            Description {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a BIT(64),b INT);
            INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF,-1);
            SELECT a>b, GREATEST(a,b) FROM t1;
            {code}
            {noformat}
            +------+---------------+
            | a>b | GREATEST(a,b) |
            +------+---------------+
            | 1 | -1 |
            +------+---------------+
            {noformat}
            The value of the first column looks good, as {{BIT}} is treated like a unsigned number, according to design.
            The value of the second column is wrong. It should return {{18446744073709551615}}.


            Now I do:
            {code:sql}
            DROP TABLE IF EXISTS t2;
            CREATE TABLE t2 AS SELECT COALESCE(a,b),GREATEST(a,b) FROM t1;
            SELECT * FROM t2;
            {code}
            {noformat}
            +----------------------+---------------+
            | COALESCE(a,b) | GREATEST(a,b) |
            +----------------------+---------------+
            | 18446744073709551615 | -1 |
            +----------------------+---------------+
            {noformat}
            The value of the first column with {{COALESCE}} is correct.
            The value of the first column with {{GREATEST}} is wrong.

            Now I check the table structure:
            {code:sql}
            SHOW CREATE TABLE t2;
            {code}
            {noformat}
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            | t2 | CREATE TABLE `t2` (
              `COALESCE(a,b)` decimal(64,0) DEFAULT NULL,
              `GREATEST(a,b)` bigint(65) DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}
            The data type of the first column with {{COALESCE}} is correct.
            The data type of the second column with {{GREATEST}} is wrong.
            		h2. This problem was fixed in 10.1. Closin

            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a BIT(64),b INT);
            INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF,-1);
            SELECT a>b, GREATEST(a,b) FROM t1;
            {code}
            {noformat}
            +------+---------------+
            | a>b | GREATEST(a,b) |
            +------+---------------+
            | 1 | -1 |
            +------+---------------+
            {noformat}
            The value of the first column looks good, as {{BIT}} is treated like a unsigned number, according to design.
            The value of the second column is wrong. It should return {{18446744073709551615}}.


            Now I do:
            {code:sql}
            DROP TABLE IF EXISTS t2;
            CREATE TABLE t2 AS SELECT COALESCE(a,b),GREATEST(a,b) FROM t1;
            SELECT * FROM t2;
            {code}
            {noformat}
            +----------------------+---------------+
            | COALESCE(a,b) | GREATEST(a,b) |
            +----------------------+---------------+
            | 18446744073709551615 | -1 |
            +----------------------+---------------+
            {noformat}
            The value of the first column with {{COALESCE}} is correct.
            The value of the first column with {{GREATEST}} is wrong.

            Now I check the table structure:
            {code:sql}
            SHOW CREATE TABLE t2;
            {code}
            {noformat}
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            | t2 | CREATE TABLE `t2` (
              `COALESCE(a,b)` decimal(64,0) DEFAULT NULL,
              `GREATEST(a,b)` bigint(65) DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}
            The data type of the first column with {{COALESCE}} is correct.
            The data type of the second column with {{GREATEST}} is wrong.
            bar Alexander Barkov made changes -
            Description h2. This problem was fixed in 10.1. Closin

            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a BIT(64),b INT);
            INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF,-1);
            SELECT a>b, GREATEST(a,b) FROM t1;
            {code}
            {noformat}
            +------+---------------+
            | a>b | GREATEST(a,b) |
            +------+---------------+
            | 1 | -1 |
            +------+---------------+
            {noformat}
            The value of the first column looks good, as {{BIT}} is treated like a unsigned number, according to design.
            The value of the second column is wrong. It should return {{18446744073709551615}}.


            Now I do:
            {code:sql}
            DROP TABLE IF EXISTS t2;
            CREATE TABLE t2 AS SELECT COALESCE(a,b),GREATEST(a,b) FROM t1;
            SELECT * FROM t2;
            {code}
            {noformat}
            +----------------------+---------------+
            | COALESCE(a,b) | GREATEST(a,b) |
            +----------------------+---------------+
            | 18446744073709551615 | -1 |
            +----------------------+---------------+
            {noformat}
            The value of the first column with {{COALESCE}} is correct.
            The value of the first column with {{GREATEST}} is wrong.

            Now I check the table structure:
            {code:sql}
            SHOW CREATE TABLE t2;
            {code}
            {noformat}
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            | t2 | CREATE TABLE `t2` (
              `COALESCE(a,b)` decimal(64,0) DEFAULT NULL,
              `GREATEST(a,b)` bigint(65) DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}
            The data type of the first column with {{COALESCE}} is correct.
            The data type of the second column with {{GREATEST}} is wrong.
            		h2. This problem was earlier fixed in 10.1.

            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a BIT(64),b INT);
            INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF,-1);
            SELECT a>b, GREATEST(a,b) FROM t1;
            {code}
            {noformat}
            +------+---------------+
            | a>b | GREATEST(a,b) |
            +------+---------------+
            | 1 | -1 |
            +------+---------------+
            {noformat}
            The value of the first column looks good, as {{BIT}} is treated like a unsigned number, according to design.
            The value of the second column is wrong. It should return {{18446744073709551615}}.


            Now I do:
            {code:sql}
            DROP TABLE IF EXISTS t2;
            CREATE TABLE t2 AS SELECT COALESCE(a,b),GREATEST(a,b) FROM t1;
            SELECT * FROM t2;
            {code}
            {noformat}
            +----------------------+---------------+
            | COALESCE(a,b) | GREATEST(a,b) |
            +----------------------+---------------+
            | 18446744073709551615 | -1 |
            +----------------------+---------------+
            {noformat}
            The value of the first column with {{COALESCE}} is correct.
            The value of the first column with {{GREATEST}} is wrong.

            Now I check the table structure:
            {code:sql}
            SHOW CREATE TABLE t2;
            {code}
            {noformat}
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            | t2 | CREATE TABLE `t2` (
              `COALESCE(a,b)` decimal(64,0) DEFAULT NULL,
              `GREATEST(a,b)` bigint(65) DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}
            The data type of the first column with {{COALESCE}} is correct.
            The data type of the second column with {{GREATEST}} is wrong.
            bar Alexander Barkov made changes -
            Component/s OTHER [ 10125 ]
            Fix Version/s 10.1.8 [ 19605 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Duplicate [ 3 ]
            Status Open [ 1 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            Affects Version/s 10.0 [ 16000 ]
            Affects Version/s 10.2 [ 14601 ]
            Affects Version/s 10.3 [ 22126 ]
            bar Alexander Barkov made changes -
            Description h2. This problem was earlier fixed in 10.1.

            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a BIT(64),b INT);
            INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF,-1);
            SELECT a>b, GREATEST(a,b) FROM t1;
            {code}
            {noformat}
            +------+---------------+
            | a>b | GREATEST(a,b) |
            +------+---------------+
            | 1 | -1 |
            +------+---------------+
            {noformat}
            The value of the first column looks good, as {{BIT}} is treated like a unsigned number, according to design.
            The value of the second column is wrong. It should return {{18446744073709551615}}.


            Now I do:
            {code:sql}
            DROP TABLE IF EXISTS t2;
            CREATE TABLE t2 AS SELECT COALESCE(a,b),GREATEST(a,b) FROM t1;
            SELECT * FROM t2;
            {code}
            {noformat}
            +----------------------+---------------+
            | COALESCE(a,b) | GREATEST(a,b) |
            +----------------------+---------------+
            | 18446744073709551615 | -1 |
            +----------------------+---------------+
            {noformat}
            The value of the first column with {{COALESCE}} is correct.
            The value of the first column with {{GREATEST}} is wrong.

            Now I check the table structure:
            {code:sql}
            SHOW CREATE TABLE t2;
            {code}
            {noformat}
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            | t2 | CREATE TABLE `t2` (
              `COALESCE(a,b)` decimal(64,0) DEFAULT NULL,
              `GREATEST(a,b)` bigint(65) DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}
            The data type of the first column with {{COALESCE}} is correct.
            The data type of the second column with {{GREATEST}} is wrong.
            		h2. This problem was earlier fixed in 10.1 and is not really repeatable in 10.2.

            {code:sql}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a BIT(64),b INT);
            INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF,-1);
            SELECT a>b, GREATEST(a,b) FROM t1;
            {code}
            {noformat}
            +------+---------------+
            | a>b | GREATEST(a,b) |
            +------+---------------+
            | 1 | -1 |
            +------+---------------+
            {noformat}
            The value of the first column looks good, as {{BIT}} is treated like a unsigned number, according to design.
            The value of the second column is wrong. It should return {{18446744073709551615}}.


            Now I do:
            {code:sql}
            DROP TABLE IF EXISTS t2;
            CREATE TABLE t2 AS SELECT COALESCE(a,b),GREATEST(a,b) FROM t1;
            SELECT * FROM t2;
            {code}
            {noformat}
            +----------------------+---------------+
            | COALESCE(a,b) | GREATEST(a,b) |
            +----------------------+---------------+
            | 18446744073709551615 | -1 |
            +----------------------+---------------+
            {noformat}
            The value of the first column with {{COALESCE}} is correct.
            The value of the first column with {{GREATEST}} is wrong.

            Now I check the table structure:
            {code:sql}
            SHOW CREATE TABLE t2;
            {code}
            {noformat}
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            | Table | Create Table |
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            | t2 | CREATE TABLE `t2` (
              `COALESCE(a,b)` decimal(64,0) DEFAULT NULL,
              `GREATEST(a,b)` bigint(65) DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
            +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+
            {noformat}
            The data type of the first column with {{COALESCE}} is correct.
            The data type of the second column with {{GREATEST}} is wrong.
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 80326 ] MariaDB v4 [ 151957 ]

            People

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