MySQL Bugs: #12537: UNION produces longtext instead of varchar

Bug #12537	UNION produces longtext instead of varchar
Submitted:	11 Aug 2005 22:45	Modified:	1 Sep 2005 2:05
Reporter:	Kolbe Kegel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1, 5.0	OS:	Linux (Linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
When creating a table based on the result of a UNION that includes long utf8 VARCHAR columns, the created table will be defined using a LONGTEXT column instead of VARCHAR, unless only a specific number of characters from the VARCHAR column is selected. The specific number of characters is different in 4.1 than 5.0.

In 4.1, the "cutoff" is 85 characters. Possibly, this is because 85 * 3 = 255, where 3 is the maximum number of bytes per character in utf8. A threshhold might be calculated based on bytes rather than characters, which would cause this behavior.

In 5.0, the "cutoff" is 170 characters. There is no particularly obvious relationship between 170 characters/bytes and the 255 character/byte limit, aside from the fact that 170 is a multiple of 85.

This issue has the side effect of preventing such a query from using a memory-based temporary table since MEMORY tables do not support TEXT/LONGTEXT columns.

How to repeat:
CREATE TABLE a (v VARCHAR(255) CHARACTER SET utf8);
CREATE TABLE b (v VARCHAR(255) CHARACTER SET utf8);

-- In 5.0, substitute 170 for 85
CREATE TABLE t1 AS SELECT LEFT(v,85) FROM a UNION SELECT LEFT(v,85) FROM b;
SHOW CREATE TABLE t1;

-- CREATE TABLE `t1` (
--   `LEFT(v,85)` varchar(85) character set utf8 default NULL
-- )

-- In 5.0, substitute 171 for 86
CREATE TABLE t2 AS SELECT LEFT(v,86) FROM a UNION SELECT LEFT(v,86) FROM b;
SHOW CREATE TABLE t2;

-- CREATE TABLE `t2` (
--  `LEFT(v,86)` longtext character set utf8
-- ) 

Suggested fix:
A table created from the output of a SELECT with a UNION should be created with VARCHAR instead of LONGTEXT columns when appropriate and possible.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28682

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29027

Fixed in 4.1.15, cset 1.2377.6.1

Item::tmp_table_field_from_field_type() and create_tmp_field_from_item()
was converting string field to blob depending on byte-wise length instead of
character length, which results in converting valid varchar string with
length == 86 to longtext.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29126

Fixed in 5.0.13, csets 1.1928,1.1929

Noted in 4.1.15, 5.0.13 changelogs.