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

Wrong result set metadata for a mix of INT+ENUM

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
    • Fix Version/s: 10.7.5, 10.8.4, 10.9.2
    • Component/s: Data types
    • Labels:
      None

      Description

      This MTR test:

      CREATE TABLE t1
      (
        c_int INT,
        c_enum ENUM('1')
      );
       
      CREATE TABLE t2 AS SELECT c_int FROM t1 UNION SELECT c_enum FROM t1;
      SHOW CREATE TABLE t2;
      DROP TABLE t2;
       
      CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(c_int, c_enum)  FROM t1;
      SHOW CREATE TABLE t2;
      DROP TABLE t2;
       
      --enable_metadata
      SELECT c_int FROM t1 UNION SELECT c_enum FROM t1;
      SELECT COALESCE(c_int, c_enum)  FROM t1;
      --disable_metadata
       
      DROP TABLE t1;
      

      produces the following output:

      CREATE TABLE t1
      (
      c_int INT,
      c_enum ENUM('1')
      );
      CREATE TABLE t2 AS SELECT c_int FROM t1 UNION SELECT c_enum FROM t1;
      SHOW CREATE TABLE t2;
      Table	Create Table
      t2	CREATE TABLE `t2` (
        `c_int` varchar(11) DEFAULT NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
      DROP TABLE t2;
      CREATE OR REPLACE TABLE t2 AS SELECT COALESCE(c_int, c_enum)  FROM t1;
      SHOW CREATE TABLE t2;
      Table	Create Table
      t2	CREATE TABLE `t2` (
        `COALESCE(c_int, c_enum)` varchar(11) DEFAULT NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
      DROP TABLE t2;
      SELECT c_int FROM t1 UNION SELECT c_enum FROM t1;
      Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
      def				c_int	c_int	253	11	0	Y	00	8
      c_int
      SELECT COALESCE(c_int, c_enum)  FROM t1;
      Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
      def					COALESCE(c_int, c_enum)	254	11	0Y	0	39	8
      COALESCE(c_int, c_enum)
      DROP TABLE t1;
      

      Obvervations:

      • according to the output from the two SHOW CREATE TABLE t2 queries, a mixture of INT+ENUM produces VARCHAR columns, in both UNION and COALEACE statements.
      • the result set metadata the UNION query reports type code 253 (MYSQL_TYPE_VAR_STRING), which is correct.
      • the result set metadata the COALESCE query reports type code 254 (MYSQL_TYPE_STRING), which is wrong.

        Attachments

          Activity

            People

            Assignee:
            bar Alexander Barkov
            Reporter:
            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.