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

Wrong data type for GREATEST(bit_column, int_column)

    XMLWordPrintable

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

          Activity

            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.