MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:jwakely Date:September 18 2002 2:46pm
Subject:Output of "DESCRIBE tbl_name" wrong for unique key on NULL column
View as plain text  

The output of DESCRIBE tbl_name shows unique keys to be "MUL" for columns
defined as NULL.

The SQL shown is a minimal test case that creates a new database and two
tables, both with a unique key on the column "uni"

The first table is fine.
The second table "bug" shows the wrong output for "EXPLAIN bug",
the index on the second column is shown to be non-unique.

The only difference between the tables is whether the second column
is defined to be NULL or NOT NULL.

The result is exactly the same if the column type is VARCHAR, not INT.

Version 3.23.32 shows UNI in both cases, as I expect to see.
Version 3.23.49 shows the wrong output, as described in this bug report.

If this is actually the correct output, because a NULL column with a
UNIQUE key can contain multiple NULL values, then ignore this bug.
Instead the manual needs to be fixed to explain the output of this command.
Currently the manual contains circular references for "EXPLAIN tbl_name",
"DESCRIBE tbl_name" and "SHOW COLUMNS FROM tbl_name", none of the relevant
sections explain the output, they just refer you to one of the other
sections (I'll be raising this as a separate issue anyway).


USE test_bug;

CREATE TABLE `not_bug` (
  `id` int NOT NULL default '0',
  `uni` int NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uni` (`uni`)

  `id` int NOT NULL default '0',
  `uni` int,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `uni` (`uni`)

EXPLAIN not_bug;



>Submitter-Id:	<submitter ID>
>Originator:	Jonathan Wakely
 Mintel International Group Ltd.
>MySQL support: none
>Synopsis:	Output of "DESCRIBE tbl_name" wrong for unique key on NULL column
>Severity:	non-critical
>Priority:	medium
>Category:	mysql
>Class:		sw-bug
>Release:	mysql-3.23.42 (Source distribution)
>Server: /usr/local/bin/mysqladmin  Ver 8.21 Distrib 3.23.42, for -freebsd4.4 on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version		3.23.42
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/tmp/mysql.sock
Uptime:			20 hours 12 min 29 sec

Threads: 129  Questions: 2394902  Slow queries: 18  Opens: 1701  Flush tables: 1  Open
tables: 295 Queries per second avg: 32.920
System: FreeBSD 4.4-RELEASE FreeBSD 4.4-RELEASE #2: Fri Jan 11
11:38:03 GMT 2002     jason@stripped:/usr/src/sys/compile/JULIET  i386

Some paths:  /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.3 20010315 (release) [FreeBSD]
Compilation info: CC='cc'  CFLAGS='-O -pipe '  CXX='c++'  CXXFLAGS='-O -pipe 
-felide-constructors -fno-rtti -fno-exceptions'  LDFLAGS=''
-r--r--r--  1 root  wheel  1205382 Sep 18  2001 /usr/lib/libc.a
lrwxrwxrwx  1 root  wheel  9 Oct  4  2001 /usr/lib/ ->
-r--r--r--  1 root  wheel  572876 Sep 18  2001 /usr/lib/
Configure command: ./configure  --localstatedir=/var/db/mysql --without-perl
--without-debug --without-readline --without-bench --with-mit-threads=no --with-libwrap
--with-low-memory --enable-assembler --with-berkeley-db --prefix=/usr/local
Perl: This is perl, version 5.005_03 built for i386-freebsd
Output of "DESCRIBE tbl_name" wrong for unique key on NULL columnjwakely18 Sep
  • re: Output of "DESCRIBE tbl_name" wrong for unique key on NULL columnVictoria Reznichenko18 Sep