>Description:
Joining multiple tables together in a select statement with where clauses
using the "IN (...)" construct generate duplicated output rows.
>How-To-Repeat:
1. ======>>Load the following into the "test_bug" database:
# MySQL dump 8.14
#
# Host: localhost Database: test_bug
#--------------------------------------------------------
# Server version 3.23.41
#
# Table structure for table 'event'
#
CREATE TABLE event (
id int(11) NOT NULL auto_increment,
status int(11) NOT NULL default '0',
severity int(11) NOT NULL default '0',
PRIMARY KEY (id),
UNIQUE KEY lens_event_0 (status,severity,id)
) TYPE=MyISAM;
#
# Dumping data for table 'event'
#
INSERT INTO event VALUES (1,0,2);
INSERT INTO event VALUES (2,0,2);
INSERT INTO event VALUES (3,0,3);
INSERT INTO event VALUES (4,0,3);
INSERT INTO event VALUES (5,1,2);
INSERT INTO event VALUES (6,2,3);
#
# Table structure for table 'severity'
#
CREATE TABLE severity (
severity int(11) NOT NULL default '0',
description text
) TYPE=MyISAM;
#
# Dumping data for table 'severity'
#
INSERT INTO severity VALUES (0,'Unknown');
INSERT INTO severity VALUES (1,'Alert');
INSERT INTO severity VALUES (2,'Critical');
INSERT INTO severity VALUES (3,'Error');
INSERT INTO severity VALUES (4,'Warning');
INSERT INTO severity VALUES (5,'Notification');
INSERT INTO severity VALUES (6,'Informational');
INSERT INTO severity VALUES (7,'Debugging');
#
# Table structure for table 'status'
#
CREATE TABLE status (
status int(11) NOT NULL default '0',
description text
) TYPE=MyISAM;
#
# Dumping data for table 'status'
#
INSERT INTO status VALUES (0,'New');
INSERT INTO status VALUES (1,'Acknowledged');
INSERT INTO status VALUES (2,'Working');
INSERT INTO status VALUES (3,'Resolved');
INSERT INTO status VALUES (4,'Closed');
2. ======>>Execute the query "query_normal.sql" which produces the expected output:
(all 6 rows of "event" are returned)
select * from event, status, severity
where event.status = status.status
and event.severity = severity.severity;
yields a result set of 6 rows
3. ======>>Execute the query "query_failure.sql" which produces the failed output:
(Note the "IN (...)" clauses should have no effect on the output)
select * from event, status, severity
where event.status = status.status
and event.severity = severity.severity
and event.status in (0,1,2)
and event.severity in (2,3);
yields a result set of 30 rows, with duplicated IDs
>Fix:
>Submitter-Id: Greg Bailey <[EMAIL PROTECTED]>
>Originator: Greg Bailey
>Organization: LXPRO.COM
>MySQL support: none
>Synopsis: Joined tables return duplicate rows with "IN (...)" construct
>Severity: serious
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.41 (Source distribution)
>Server: /usr/bin/mysqladmin Ver 8.21 Distrib 3.23.41, for redhat-linux-gnu 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.41
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 1 day 21 hours 54 min 25 sec
Threads: 1 Questions: 3409 Slow queries: 0 Opens: 106 Flush tables: 1 Open
tables: 9 Queries per second avg: 0.021
>Environment:
System: Linux arizona.i-link.net 2.4.9-21 #1 Thu Jan 17 14:16:30 EST 2002 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-98)
Compilation info: CC='gcc' CFLAGS='-O2 -march=i386 -mcpu=i686 -D_GNU_SOURCE
-D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' CXX='c++' CXXFLAGS='-O2 -march=i386
-mcpu=i686 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Dec 14 16:16 /lib/libc.so.6 -> libc-2.2.4.so
-rwxr-xr-x 2 root root 1283964 Dec 8 07:14 /lib/libc-2.2.4.so
-rw-r--r-- 1 root root 27314296 Dec 8 07:02 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Dec 8 07:02 /usr/lib/libc.so
Configure command: ./configure i386-redhat-linux --prefix=/usr --exec-prefix=/usr
--bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share
--includedir=/usr/include --libdir=/usr/lib --libexecdir=/usr/libexec
--localstatedir=/var --sharedstatedir=/usr/com --mandir=/usr/share/man
--infodir=/usr/share/info --without-debug --without-readline --enable-shared
--with-extra-charsets=complex --with-bench --localstatedir=/var/lib/mysql
--with-unix-socket-path=/var/lib/mysql/mysql.sock --with-mysqld-user=mysql
--with-extra-charsets=all --disable-assember --with-berkeley-db
--enable-large-files=yes --enable-largefile=yes --with-thread-safe-client
--enable-assembler
---------------------------------------------------------------------
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
