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

CREATE..SELECT..UNION creates a wrong field type for old varchar

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3, 10.4
    • 10.4
    • Data types
    • None

    Description

      I create this test file:

      # Copy a table with the old MySQL-4.0 VARCHAR
      let $MYSQLD_DATADIR= `select @@datadir`;
      copy_file $MYSQL_TEST_DIR/std_data/vchar.frm $MYSQLD_DATADIR/test/vchar.frm;
      truncate table vchar;
      SHOW CREATE TABLE vchar;
       
      CREATE TABLE t2_simple AS SELECT v FROM vchar;
      SHOW CREATE TABLE t2_simple;
      DROP TABLE t2_simple;
       
      CREATE TABLE t2_union_vv AS SELECT v FROM vchar UNION SELECT v FROM vchar;
      SHOW CREATE TABLE t2_union_vv;
      DROP TABLE t2_union_vv;
       
      CREATE TABLE t2_union_vn AS SELECT v FROM vchar UNION SELECT NULL;
      SHOW CREATE TABLE t2_union_vn;
      DROP TABLE t2_union_vn;
       
      CREATE TABLE t2_union_nv AS SELECT NULL AS v UNION SELECT v FROM vchar;
      SHOW CREATE TABLE t2_union_nv;
      DROP TABLE t2_union_nv;
       
      DROP TABLE vchar;
      

      Now I run this test and it produces the following output:

      truncate table vchar;
      SHOW CREATE TABLE vchar;
      Table	Create Table
      vchar	CREATE TABLE `vchar` (
        `v` varchar(30) DEFAULT NULL,
        `c` char(3) DEFAULT NULL,
        `e` enum('abc','def','ghi') DEFAULT NULL,
        `t` text DEFAULT NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
      CREATE TABLE t2_simple AS SELECT v FROM vchar;
      SHOW CREATE TABLE t2_simple;
      Table	Create Table
      t2_simple	CREATE TABLE `t2_simple` (
        `v` varchar(30) DEFAULT NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
      DROP TABLE t2_simple;
      CREATE TABLE t2_union_vv AS SELECT v FROM vchar UNION SELECT v FROM vchar;
      SHOW CREATE TABLE t2_union_vv;
      Table	Create Table
      t2_union_vv	CREATE TABLE `t2_union_vv` (
        `v` char(30) DEFAULT NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
      DROP TABLE t2_union_vv;
      CREATE TABLE t2_union_vn AS SELECT v FROM vchar UNION SELECT NULL;
      SHOW CREATE TABLE t2_union_vn;
      Table	Create Table
      t2_union_vn	CREATE TABLE `t2_union_vn` (
        `v` char(30) DEFAULT NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
      DROP TABLE t2_union_vn;
      CREATE TABLE t2_union_nv AS SELECT NULL AS v UNION SELECT v FROM vchar;
      SHOW CREATE TABLE t2_union_nv;
      Table	Create Table
      t2_union_nv	CREATE TABLE `t2_union_nv` (
        `v` char(30) DEFAULT NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
      DROP TABLE t2_union_nv;
      DROP TABLE vchar;
      

      Notice:

      • the data type in t2_simple is VARCHAR as expected.
      • the data type in t2_union_vv, t2_union_vn, t2_union_nv is CHAR, which is wrong.

      Note, if I replace copying of vchar.frm (the old VARCHAR) to a normal CREATE like this:

      CREATE TABLE vchar (v VARCHAR(30));
      

      and return the test, it correctly creates VARCHAR in all queries.
      So this problem is specific to the old VARCHAR.

      Attachments

        Activity

          People

            bar Alexander Barkov
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.