>Description: 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). >How-To-Repeat: CREATE DATABASE test_bug; USE test_bug; CREATE TABLE `not_bug` ( `id` int NOT NULL default '0', `uni` int NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uni` (`uni`) ) TYPE=MyISAM; CREATE TABLE `bug` ( `id` int NOT NULL default '0', `uni` int, PRIMARY KEY (`id`), UNIQUE KEY `uni` (`uni`) ) TYPE=MyISAM; EXPLAIN not_bug; EXPLAIN bug; >Fix: >Submitter-Id: <submitter ID> >Originator: Jonathan Wakely >Organization: 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 >Environment: System: FreeBSD juliet.mintel.co.uk 4.4-RELEASE FreeBSD 4.4-RELEASE #2: Fri Jan 11 11:38:03 GMT 2002 [EMAIL PROTECTED]:/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='' LIBC: -r--r--r-- 1 root wheel 1205382 Sep 18 2001 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Oct 4 2001 /usr/lib/libc.so -> libc.so.4 -r--r--r-- 1 root wheel 572876 Sep 18 2001 /usr/lib/libc.so.4 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 i386--freebsd4.4 Perl: This is perl, version 5.005_03 built for i386-freebsd --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php