>Description:
SELECT DISTINCT in combination with ORDER BY a non-selected field
will return multiple identical result rows
if the WHERE clause eliminates 'other' rows
>How-To-Repeat:
drop table if exists test1;
create table test1 (
id1 int PRIMARY KEY,
name1 varchar(80)
);
drop table if exists test2;
create table test2 (
id2 int PRIMARY KEY,
id1 int,
name2 varchar(80)
);
insert into test1 values (1, 'A');
insert into test1 values (2, 'B');
insert into test2 values (1, 1, 'a');
insert into test2 values (2, 2, 'b');
insert into test2 values (3, 1, 'a');
select distinct A.id1 as dupl, B.id1 as Bid1
from test1 A, test2 B
where A.id1=B.id1
and B.name2='a'
order by B.id2;
>Fix:
add: GROUP BY dupl
or: ask bugfix :-)
>Submitter-Id: <submitter ID>
>Originator: Guido A.J. Stevens
>Organization:
NFG Net Facilities Group BV (Coal Connectivity)
>MySQL support: licence (certificate serial #51)
>Synopsis: duplicate rows in SELECT DISTINCT
>Severity: non-critical
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.49 (Source distribution)
>Environment:
System: Linux bohme 2.4.20 #8 Thu Jan 9 09:27:15 CET 2003 i686 unknown
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/2.95.4/specs
gcc version 2.95.4 20011002 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 14 May 16 2002 /lib/libc.so.5 ->
libc.so.5.4.46
-rw-r--r-- 1 root root 563068 Feb 4 2002 /lib/libc.so.5.4.46
lrwxrwxrwx 1 root root 13 Feb 26 13:34 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x 1 root root 1145456 Sep 18 04:50 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2376426 Sep 18 04:51 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Sep 18 04:51 /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 --with-libwrap --enable-assembler
--with-berkeley-db --with-innodb --enable-static --enable-shared --enable-local-infile
--with-raid --enable-thread-safe-client --without-readline
--with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql
--without-bench --with-client-ldflags=-lstdc++ --with-extra-charsets=all
---------------------------------------------------------------------
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
