Bug #70068 UNION ALL produces text instead of varchar
Submitted: 17 Aug 2013 11:57 Modified: 19 Aug 2013 14:46
Reporter: Frank Zhang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.6.11,5.6.13 OS:Any
Assigned to: CPU Architecture:Any
Tags: text, UION ALL, varchar

[17 Aug 2013 11:57] Frank Zhang
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.
[19 Aug 2013 14:46] Umesh Shastry
Hello Zhang,

Thank you for the bug report and the test case. 
Verified as described.

Thanks,
Umesh