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
Category:	Server: Docs	Severity:	S3 (Non-critical)
Version:	5.0.12-bk Aug 17	OS:	Linux (SuSE Linux 9.2)
Assigned to:	Paul DuBois	Target Version:
Triage:	D4 (Minor)

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).