MySQL Bugs: #72588: MySQL creates large temporary tables with UNION ALL and strings

Bug #72588	MySQL creates large temporary tables with UNION ALL and strings
Submitted:	9 May 2014 3:41	Modified:	9 Jun 2014 8:19
Reporter:	Andrew Dalgleish	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5.37	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html
says:
-----
Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead: 

Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used 
-----

This can be used to force the temporary table to not expand VARCHARs by using
(SELECT col1, col2 FROM table1) UNION ALL (SELECT NULL, NULL LIMIT 0)

This only works if *all* VARCHAR columns are > 512 characters, if any column is small the VARCHARs will be expanded.

How to repeat:
Create a table like:
CREATE TABLE table1
(
        col0    INT AUTO_INCREMENT PRIMARY KEY,
        col1    VARCHAR(1000),
        col2    VARCHAR(1000),
        col3    VARCHAR(500)
) ENGINE=InnoDB CHARSET=utf8;

Insert 1M rows with mostly empty strings.

Compare the size of the temp files executing these queries.
(SELECT col1, col2 FROM table1) UNION ALL (SELECT NULL, NULL LIMIT 0);
(SELECT col1, col3 FROM table1) UNION ALL (SELECT NULL, NULL LIMIT 0);

Posted by developer:
 
It is unclear to me what behavioral change is actually requested here.  Please, elaborate.

Note also that since MySQL 5.7.3, temporary tables will not be used for UNION ALL.

The documentation states it will avoid using an in-memory table if *any* variable length string is > 512 characters, but it only works if *all* strings are > 512 characters.

If you have 1 column which is smaller, you end up with potentially huge temp files.

If you want to avoid a temporary table to become in-memory, you can set the session variable big_tables.  I also think you can use "SELECT SQL_BIG_RESULT ..." to achieve this.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".