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

Wrong field type or metadata for COALESCE(bit_column, 1)

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • N/A
    • OTHER

    Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (val bit(1));
      INSERT INTO t1 VALUES (0);
      DROP TABLE IF EXISTS t2;
      CREATE TABLE t2 AS SELECT COALESCE(val, 1) AS c FROM t1;
      SELECT  * FROM t2;
      SHOW CREATE TABLE t2;

      returns

      +------+
      | c    |
      +------+
      |    0 |
      +------+

      +-------+--------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                               |
      +-------+--------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `c` decimal(1,0) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+--------------------------------------------------------------------------------------------+

      Now if I run "mysql --column-type-info test" and execute this query:

      SELECT COALESCE(val, 1) FROM t1;

      it returns the following result and metadata:

      +------------------+
      | COALESCE(val, 1) |
      +------------------+
      | 0                |
      +------------------+

      Field   1:  `COALESCE(val, 1)`
      ...
      Type:       VAR_STRING
      Collation:  binary (63)
      Length:     1
      Max_length: 1
      Decimals:   31
      Flags:      UNSIGNED BINARY

      Notice, COALESCE(val,1) positions itself as:

      • DECIMAL(1,0) in CREATE TABLE...SELECT
      • VARBINARY(1) in metadata returned by SELECT

      The expected result is to return the same data type in both cases

      Note, a UNION between a BIT column and an INT literal creates a column of type VARBINARY(20):

      DROP TABLE IF EXISTS t1, t2;
      CREATE TABLE t1 (val BIT(1));
      CREATE TABLE t2 AS SELECT val FROM t1 UNION SELECT 1;
      SHOW CREATE TABLE t2;

      +-------+-----------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                  |
      +-------+-----------------------------------------------------------------------------------------------+
      | t2    | CREATE TABLE `t2` (
        `val` varbinary(20) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-----------------------------------------------------------------------------------------------+

      which is closer to what metadata returns for COALESCE(val,1) rather than what CREATE TABLE..SELECT returns for COALESCE(val,1).

      Summary:

      • SELECT bit_column FROM t1 - returns a string-alike value with BIT type in metadata
      • SELECT bit_column FROM t1 UNION SELECT 1 – returns a number-alike value with VAR_STRING data type in metadata
      • SELECT COALESCE(bit_column,1) FROM t1 – returns a number-alike value with VAR_STRING metadata
      • CREATE TABLE t2 AS SELECT val FROM t1 UNION SELECT 1; – creates a column of type VARBINARY(20), with number-alike values
      • CREATE TABLE t2 AS SELECT COALESCE(val,1) FROM t1; – creates a column of type DECIMAL(1,0)

      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.