MySQL Bugs: #33381: Temporary table created on disk when it should be in ram

Bug #33381	Temporary table created on disk when it should be in ram
Submitted:	19 Dec 2007 19:27	Modified:	6 Oct 2009 16:32
Reporter:	J Rabbit	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S5 (Performance)
Version:	5.0.45, 5.0.52, 5.1.40	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
In the following sequence of queries, the first select statement correctly creates a temporary table in ram. However, the second statement creates the table on disk, despite the maximum theoretical size of the result being way below the 32Mb maximum in both cases.

How to repeat:
SET GLOBAL tmp_table_size=33554432;
SET GLOBAL max_heap_table_size=33554432;

CREATE TABLE test (  
  id int(11) NOT NULL,
  name varchar(512) NOT NULL,
  description varchar(513) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
/* note: Engine MyISAM yields the same results */

INSERT INTO test VALUES (1,'a','b');

show status like 'cre%';

(SELECT name FROM test WHERE id=1)
UNION 
(SELECT name FROM test WHERE id=2);

show status like 'cre%';

(SELECT description FROM test WHERE id=1)
UNION 
(SELECT description FROM test WHERE id=2);

show status like 'cre%';

DROP TABLE test;

Thank you for a bug report. Looks like this page, http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html, should just be updated. This list:

"Some conditions prevent the use of a MEMORY  temporary table, in which case the server uses a MyISAM table instead:

    - Presence of a TEXT or BLOB column in the table
    - Presence of any column in a GROUP BY or DISTINCT clause larger than 512 bytes"

should also include:

- Presence of any column larger than 512 bytes in SELECT list if UNION or UNION ALL is used.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I have added the information provided by Valeriy.

That still doesn't quite explain it. If you change the encoding to utf8, then the maximum length of both columns can be more than 512 bytes despite the name column only being 512 characters. However, it still only creates a temporary table on disk if you use the 513 character column in the select list.

It seems strange that the 512 byte limit exists at all, when the MEMORY storage engine is capable of storing varchar fields longer than this.

Please, send the exact CREATE TABLE statement you had used when testing with utf8. Look:

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(512) NOT NULL,
  `description` varchar(513) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> (SELECT name FROM test WHERE id=1)
    -> UNION
    -> (SELECT name FROM test WHERE id=2);
Field   1:  `name`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     512
Max_length: 0
Decimals:   0
Flags:      NOT_NULL

0 rows in set (0.03 sec)

mysql> (SELECT description FROM test WHERE id=1)
    -> UNION
    -> (SELECT description FROM test WHERE id=2);
Field   1:  `description`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     1539
Max_length: 0
Decimals:   0
Flags:      NOT_NULL BLOB

0 rows in set (0.01 sec)

The above is what I've got.

Using the UTF-8 version of the create table statement from the previous comment illustrates my point. I get the same output with --debug-info enabled: the name column in the result set is type VAR_STRING and the description column is type BLOB.

If, as specified in the manual, the criteria for selecting between VAR_STRING and BLOB is whether the field is defined as more than 512 bytes, the name column should also be BLOB, as one UTF-8 character can be multiple bytes in length.

If there is an error in the manual, and it should read "characters" rather than "bytes" (as suggested by the fact that no errors occur when processing the query on the name field without a temporary disk table even if you fill it with 512 multi-byte characters), that proves the underlying code can cope with having more than 512 bytes in a temporary memory table column. 

If the system does work ok with more 512 bytes of data in such a column, is necessary for the limit defining when to switch to a BLOB to remain hard-coded at 512 bytes/characters?

Actually, this indeed looks like a server bug (verified with latest 5.1.40). Look:

mysql> show create table test_utf8\G
*************************** 1. row ***************************
       Table: test_utf8
Create Table: CREATE TABLE `test_utf8` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(512) DEFAULT NULL,
  `description` varchar(513) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table test_utf8 engine=MEMORY;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test_utf8\G
*************************** 1. row ***************************
       Table: test_utf8
Create Table: CREATE TABLE `test_utf8` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(512) DEFAULT NULL,
  `description` varchar(513) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from test_utf8;
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `test_utf8`
Org_table:  `test_utf8`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NUM 

Field   2:  `name`
Catalog:    `def`
Database:   `test`
Table:      `test_utf8`
Org_table:  `test_utf8`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     1536
Max_length: 0
Decimals:   0
Flags:      

Field   3:  `description`
Catalog:    `def`
Database:   `test`
Table:      `test_utf8`
Org_table:  `test_utf8`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     1539
Max_length: 0
Decimals:   0
Flags:      

0 rows in set (0.00 sec)

So, MEMORY table can store long enough (513 utf8 characters) VARCHAR columns, but, for some reason, temporary table is still created on disk when such column is used in UNION:
 
mysql> show session status like 'cre%';
Field   1:  `Variable_name`
Catalog:    `def`
Database:   ``
Table:      `STATUS`
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     192
Max_length: 23
Decimals:   0
Flags:      NOT_NULL 

Field   2:  `Value`
Catalog:    `def`
Database:   ``
Table:      `STATUS`
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     3072
Max_length: 2
Decimals:   0
Flags:      

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 2     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 11    |
+-------------------------+-------+
3 rows in set (0.01 sec)

mysql> select description from test_utf8 where id=1 union select description from test_utf8 where id=2;
Field   1:  `description`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       BLOB
Collation:  utf8_general_ci (33)
Length:     4617
Max_length: 0
Decimals:   0
Flags:      BLOB 

0 rows in set (0.00 sec)

mysql> show session status like 'cre%';
Field   1:  `Variable_name`
Catalog:    `def`
Database:   ``
Table:      `STATUS`
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     192
Max_length: 23
Decimals:   0
Flags:      NOT_NULL 

Field   2:  `Value`
Catalog:    `def`
Database:   ``
Table:      `STATUS`
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     3072
Max_length: 2
Decimals:   0
Flags:      

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 3     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 12    |
+-------------------------+-------+
3 rows in set (0.00 sec)

Why column type is converted to BLOB in the above? It looks like a server bug to me! Especially taking into account the fact that there are no data at all in this case:

mysql> explain select description from test_utf8 where id=1 union select description from test_utf8 where id=2;

...

+----+--------------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | PRIMARY      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
|  2 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL |                                                     |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
3 rows in set (0.01 sec)