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

Wrong result set metadata for a mix of INT+ENUM

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.5, 10.6, 10.3(EOL), 10.4(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL)
    • 10.7.5, 10.8.4, 10.9.2
    • Data types
    • 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

            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.