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

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

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3(EOL), 10.4(EOL)
    • 10.4(EOL)
    • 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

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov made changes -
          Field Original Value New Value
          bar Alexander Barkov made changes -
          Description I create this test file:
          {code:sql}
          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;
          {code}

          Now I run this test and it produces the following output:
          {noformat}
          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;
          {noformat}
          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.

          		I create this test file:
          {code:sql}
          # 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;
          {code}

          Now I run this test and it produces the following output:
          {noformat}
          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;
          {noformat}
          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:
          {code:sql}
          CREATE TABLE vchar (v VARCHAR(30));
          {code}
          and return the test, it correctly creates {{VARCHAR}} in all queries.
          So this problem is specific to the old {{VARCHAR}}.
          bar Alexander Barkov made changes -
          Description I create this test file:
          {code:sql}
          # 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;
          {code}

          Now I run this test and it produces the following output:
          {noformat}
          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;
          {noformat}
          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:
          {code:sql}
          CREATE TABLE vchar (v VARCHAR(30));
          {code}
          and return the test, it correctly creates {{VARCHAR}} in all queries.
          So this problem is specific to the old {{VARCHAR}}.
          		I create this test file:
          {code:sql}
          # 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;
          {code}

          Now I run this test and it produces the following output:
          {noformat}
          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;
          {noformat}
          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:
          {code:sql}
          CREATE TABLE vchar (v VARCHAR(30));
          {code}
          and return the test, it correctly creates {{VARCHAR}} in all queries.
          So this problem is specific to the old {{VARCHAR}}.
          julien.fritsch Julien Fritsch made changes -
          Epic Link MDEV-21071 [ 80504 ]
          julien.fritsch Julien Fritsch made changes -
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 87550 ] MariaDB v4 [ 140802 ]

          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.