MySQL Bugs: #11227: DESC says "MUL" where it shoud say "UNI"

Bug #11227	DESC says "MUL" where it shoud say "UNI"
Submitted:	10 Jun 2005 7:59	Modified:	25 Jul 2005 19:52
Reporter:	Carsten Pedersen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.4-beta-standard-log	OS:	Linux (Linux (apparently also Win))
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
mysql> CREATE TABLE t (id INT NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL UNIQUE);
Query OK, 0 rows affected (0.06 sec)

mysql> DESC t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI |         |       |
| name  | varchar(20) | NO   | MUL |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

INFORMATION_SCHEMA gives the same result:

mysql> SELECT COLUMN_KEY  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t' AND COLUMN_NAME='name';
+------------+
| COLUMN_KEY |
+------------+
| MUL        |
+------------+
1 row in set (0.00 sec)

It's not a question of the field being defined wrong. The following behaves as expected for a UNIQUE column:

mysql> INSERT INTO t VALUES (1, "test"), (2, "test");
ERROR 1062 (23000): Duplicate entry 'test' for key 2

This has been marked as Critical, as any external tool trying to analyze the table structure will have no way of knowing that the index is indeed UNIQUE.

How to repeat:
See above

Suggested fix:
I don't believe there is a workaround

Verified with 5.0.7-beta-debug

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

Approved

Fixed in 5.0.11.

Documented in 5.0.11 changelog:

<listitem><para>A <literal>UNIQUE VARCHAR</literal> column would be mis-identified as <literal>MUL</literal> in table descriptions. (Bug #11227)</para></listitem>