[#MDEV-16325] CREATE..SELECT..UNION creates a wrong field type for old varchar

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.

[MDEV-16325] CREATE..SELECT..UNION creates a wrong field type for old varchar Created: 2018-05-30 Updated: 2019-11-18
Status:	Open
Project:	MariaDB Server
Component/s:	Data types
Affects Version/s:	10.3, 10.4
Fix Version/s:	10.4