MySQL Bugs: #4207: NULLable UNIQUE index shown as "MUL" under SHOW COLUMNS

Bug #4207	NULLable UNIQUE index shown as "MUL" under SHOW COLUMNS
Submitted:	18 Jun 2004 16:45	Modified:	22 Jun 2004 18:42
Reporter:	Frederic Briere	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.18	OS:	FreeBSD (FreeBSD 4.9)
Assigned to:	Matthew Lord	CPU Architecture:	Any

Description:
This was already pointed out by Thuon Chen in 14.3.1 (DESCRIBE):

>> DESCRIBE TABLE may show you a field with a value of MUL even though you defined the field as UNIQUE KEY. If you have not specified NOT NULL, the field can contain multiple values of NULL, thus the MUL. To change value of UNIQUE KEY to UNI specify NOT NULL in your CREATE TABLE statement.

I propose that this is actually a bug, as the SQL standard defines NULLs as being unique; in fact, MySQL makes a difference beween a NULLable UNIQUE and a NULLable INDEX.  Showing UNIQUEs as "MUL" can be a bit confusing -- it had me scratching my head for a few moments until I found Thuon's note.

How to repeat:
mysql> create table test (a int unique);
Query OK, 0 rows affected (0.01 sec)

mysql> desc test\G
*************************** 1. row ***************************
  Field: a
   Type: int(11)
   Null: YES
    Key: MUL
Default: NULL
  Extra: 
1 row in set (0.01 sec)

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Frederic,

This is not a bug but expected, documented behavior.  What you have requested is a feature request.  If 
you would like to you can post another issue and mark it as such "feature request".  I am unable to 
change it at this point.

Best Regards

http://bugs.mysql.com/bug.php?id=11227