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

[11 Aug 2005 22:45] Kolbe Kegel
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.
[23 Aug 2005 13:34] Bugs System
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
[30 Aug 2005 12:19] Bugs System
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
[30 Aug 2005 20:07] Evgeny Potemkin
Fixed in 4.1.15, cset 1.2377.6.1
[30 Aug 2005 21:07] Evgeny Potemkin
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.
[31 Aug 2005 18:05] Bugs System
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
[31 Aug 2005 18:08] Evgeny Potemkin
Fixed in 5.0.13, csets 1.1928,1.1929
[1 Sep 2005 2:05] Paul Dubois
Noted in 4.1.15, 5.0.13 changelogs.