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

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

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3, 10.4
    • Fix Version/s: 10.4
    • Component/s: Data types
    • Labels:
      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

          Issue Links

            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: