Description:
Notice:
I searched in mysql web site, I found a similar buy #12537, that bug had been fix in 2005 year. There are some different between my problem and bug #12537.
My problem:
installor package: MySQL-server-5.6.11-2.rhel5.x86_64.rpm
character: utf8
When creating a table based on the result of a UNION ALL that includes VARCHAR(4000) columns, the created table will be defined using a TEXT column instead of VARCHAR(4000), unless only a specific number of characters from the VARCHAR column is selected with LEFT function, the specific number of characters is 512 (In Bug #12537, the number is 85 for 4.1version, and 170for 5.0version).
It is same to using UNION.
How to repeat:
CREATE TABLE test1(f1 int(10) unsigned NOT NULL, f2 varchar(4000) NOT NULL,
PRIMARY KEY (f1)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE test2(fa int(10) unsigned NOT NULL, fb varchar(4000) NOT NULL,
PRIMARY KEY (fa)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE test3 AS SELECT f1, f2 from test1 union all select fa, fb from test2;
desc test3;
===>
Field Type
f1 int(11)unsigned
f2 text
DROP TABLE IF EXISTS test3;
CREATE TABLE test3 AS SELECT f1, LEFT(f2, 512) AS f2 from test1 union select fa, LEFT(fb, 512) AS fb from test2;
desc test3;
===>
Field Type
f1 int(11)unsigned
f2 varchar(512)
Suggested fix:
In UNION SELECT usage, do not change the type of the field, just use the original type for the result field.
