MySQL Bugs: #14139: Incorrect CREATE TABLE (...) SELECT ... behaviour

Bug #14139	Incorrect CREATE TABLE (...) SELECT ... behaviour
Submitted:	19 Oct 2005 12:16	Modified:	1 Nov 2005 19:42
Reporter:	MySQL-Front Team	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15-BK, 5.0.13-rc	OS:	Linux (Linux, Windows XP)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
If I execute the queries given in the "How to repeat" section I get the error 

Specified key was too long; max key length is 1000 bytes (# 1071)

The table "test_1" has been created well.

How to repeat:
CREATE TABLE `test_1` (
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`name`)
) DEFAULT CHARSET=utf8;

CREATE TABLE `test_2` (
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`name`)
) DEFAULT CHARSET=utf8 SELECT * FROM `test_1`;

Thank you for a bug report. I was able to repeat this weird behaviour just as you described, both on 5.0.13-rc-nt and on lates 5.0.15-BK build on Linux

mysql> CREATE TABLE `test_1` (
    ->   `name` varchar(255) NOT NULL default '',
    ->   PRIMARY KEY  (`name`)
    -> ) DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE `test_2` (
    ->   `name` varchar(255) NOT NULL default '',
    ->   PRIMARY KEY  (`name`)
    -> ) DEFAULT CHARSET=utf8 SELECT * FROM `test_1`;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

Let's try without that PRIMARY KEY:

mysql> desc test_1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | NO   | PRI |         |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test_2` (`name` varchar(255) NOT NULL default '') DEFAULT CHARSET=utf8 SELECT * FROM `test_1`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(765) | NO   |     |         |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

Where that 765 (=255*3) came from? And even if it is 765, then where that error message came from in the previous attempt to create test_2?

Nothing similar is described in http://dev.mysql.com/doc/refman/5.0/en/create-table.html...

This also seems to be true on 4.1.14.  For a simple test case:

Works:

DROP TABLE IF EXISTS t;
CREATE TABLE t (a VARCHAR(111) CHARSET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (a)) SELECT 'test' AS a;

Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

Doesn't:

DROP TABLE IF EXISTS t;
CREATE TABLE t (a VARCHAR(112) CHARSET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (a)) SELECT 'test' AS a;

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

This only seems to be a bug with INSERT ... SELECT ...; the following works fine:

DROP TABLE IF EXISTS t;
CREATE TABLE t (a VARCHAR(112) CHARSET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (a));

Query OK, 0 rows affected (0.00 sec)

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/31470

Fix pushed into 4.1.16 tree.

Notes for the changelog:
For queries in form "CREATE TABLE  (... columnX char(N) ... ) SELECT .... ", MySQL would try to create a table with not columnX(N),  but with
 columnX ( N * {max_chararcter_length_of_column_charset}) column instead. If columnX is used in a key of a new table, this could cause an unexpected "Specified key was too long" error.

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/31668

Noted in 4.1.16, 5.0.16 changelogs.