MySQL Bugs: #12764: DESC shows column with NULL value as "UNI"

Bug #12764	DESC shows column with NULL value as "UNI"
Submitted:	23 Aug 2005 22:50	Modified:	1 Sep 2005 17:32
Reporter:	Shuichi Tamagawa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.12-bk Aug 17	OS:	Linux (SuSE Linux 9.2)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
DESC show the field as 'UNI' even if NULL values are allowed within the field. As of 5.0.10 and 4.1.13 it is displayed as 'MUL'. And according to the manual it should be displayed as 'MUL' when NULL values are allowed.

How to repeat:
mysql> create table t1(a int primary key, b int unique not null);
Query OK, 0 rows affected (0.11 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | NO   | PRI |         |       |
| b     | int(11) | NO   | UNI |         |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> create table t1(a int primary key, b int unique);
Query OK, 0 rows affected (0.12 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | NO   | PRI |         |       |
| b     | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.0.12-beta |
+-------------+
1 row in set (0.09 sec)

Suggested fix:
mysql> create table t1(a int primary key, b int unique not null);
Query OK, 0 rows affected (0.13 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | NO   | PRI |         |       |
| b     | int(11) | NO   | UNI |         |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(a int primary key, b int unique);
Query OK, 0 rows affected (0.11 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | NO   | PRI |         |       |
| b     | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.0.10-beta |
+-------------+
1 row in set (0.00 sec)

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1(a int primary key, b int unique);
Query OK, 0 rows affected (0.05 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | NO   | PRI |         |       |
| b     | int(11) | YES  | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

4.1.15 isn't affected.

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

I understand this is a bug since it modifies the documented behavior, however it seems to me that the documented behavior itself is buggy -- at least it's not what I would expect.

Adding a UNIQUE index to a nullable column adds a constraint that *is* enforced by the server, meaning that in your example you can't add two rows with b=5 :

> insert into t1 values (null, 5);
Query OK, 1 row affected
> insert into t1 values (null, 5);
ERROR 1062 (23000): Duplicate entry '5' for key 2

It's true you *can* add two rows with a null b, *but* in SQL NULL is not a value, hence it's not supposed to be indexed, and two null b's are just two, not comparable (hence not equal), "non-values".
Besides, with the default mysql behaviour, there's no other way to determine whether b is UNIQUE than to analyse the SHOW CREATE TABLE, which is error-prone, slow, and overall not satisfactory.
Hence returning 'MUL' is not coherent and does not properly accounts for what the SQL standard says NULL means.

Returning 'UNI' + 'NULL' is IMO the right thing to do as it gives complete information (seeing 'NULL' you know you may have multiple rows with NULL columns) and doesn't add any other limitation.

Seems to me that the default behaviour is a fardel we may have to bear for the sake of backward compatibility, but I'd suggest taking a good look before making a decision...

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
product(s).