>Description:
"select distinct FOO from BAR" reports multiple NULL rows
>How-To-Repeat:
Unknown. It's a large table (10000 entries or so). The problem
didn't show with a simple test table.
The table:
-- MySQL dump 10.0
--
-- Host: localhost Database: pop
---------------------------------------------------------
-- Server version 4.1.0-alpha-debug-log
--
-- Table structure for table 'person'
--
DROP TABLE IF EXISTS person;
CREATE TABLE person (
descr int(11) default NULL,
id int(11) NOT NULL default '0',
kunde int(11) NOT NULL default '0',
nameid int(11) default NULL,
abtid int(11) default NULL,
mailid int(11) default NULL,
foneid int(11) default NULL,
faxid int(11) default NULL,
pagerid int(11) default NULL,
isdnid int(11) default NULL,
dest varchar(4) default NULL,
adrid int(11) default NULL,
ausweis int(11) default NULL,
zusatz int(11) default NULL,
suche int(11) default NULL,
username int(11) default NULL,
passwort int(11) default NULL,
uid int(11) NOT NULL default '0',
pwsubdir int(11) default NULL,
pwuse bigint(20) NOT NULL default '0',
udomain int(11) default NULL,
uip int(11) default NULL,
proto smallint(6) default NULL,
maxconn tinyint(4) default NULL,
ulocip int(11) default NULL,
uremip int(11) default NULL,
prefcall smallint(6) default NULL,
tarif int(11) default NULL,
satz tinyint(4) default NULL,
mperson int(11) default NULL,
gebtag smallint(6) default NULL,
gebjahr smallint(6) default NULL,
funktion int(11) default NULL,
ustid varchar(10) default NULL,
timestamp timestamp NOT NULL,
PRIMARY KEY (id),
KEY suche (suche),
KEY username (username),
KEY uid (uid),
KEY mailid (mailid),
KEY mperson (mperson),
KEY timestamp (timestamp)
) TYPE=InnoDB CHARSET=latin1;
The statement: ("explain select distinct mperson from person"):
id select_type table type possible_keys key key_len ref rows
Extra
1 SIMPLE person index NULL mperson 5 NULL 8125 Using index
>Fix:
None known.
>Submitter-Id: <submitter ID>
>Originator: Matthias Urlichs
>Organization:
noris network AG, Nuernberg, Germany
>MySQL support: licence
>Synopsis: "select distinct" reports multiple NULL rows
>Severity: serious
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-4.1.0-alpha (Up-to-date Bitkeeper distribution)
>C compiler: gcc.real (GCC) 3.2.3 20030228 (Debian prerelease)
>C++ compiler: g++.real (GCC) 3.2.3 20030228 (Debian prerelease)
>Environment:
System: Linux play.smurf.noris.de 2.4.19-586tsc #1 Sun Oct 6 18:00:21 EST 2002 i686
unknown unknown GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/3.2.3/specs
Configured with: ../src/configure -v
--enable-languages=c,c++,java,f77,proto,pascal,objc,ada --prefix=/usr
--mandir=/usr/share/man --infodir=/usr/share/info
--with-gxx-include-dir=/usr/include/c++/3.2 --enable-shared --with-system-zlib
--enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu
--enable-java-gc=boehm --enable-objc-gc i386-linux
Thread model: posix
gcc version 3.2.3 20030308 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 2003-02-28 17:59 /lib/libc.so.6 ->
libc-2.3.1.so
-rwxr-xr-x 1 root root 1104072 2003-02-25 14:46 /lib/libc-2.3.1.so
-rw-r--r-- 1 root root 2337976 2003-02-25 14:47 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 2003-02-25 14:47 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr'
'--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql'
'--localstatedir=/var/lib/mysql' '--includedir=/usr/include'
'--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared'
'--enable-static' '--enable-thread-safe-client' '--enable-assembler'
'--enable-local-infile' '--with-raid'
'--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql'
'--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server'
'--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline'
'--with-extra-charsets=all' '--with-debug' '--with-innodb'
---------------------------------------------------------------------
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
