RE: A outer join with where for the inner table does not contain all rows in the outer table - BICHOT Guilhem 172613 - com.mysql.lists.bugs

5 messages in com.mysql.lists.bugsRE: A outer join with where for the i...

From	Sent On	Attachments
Anders Hermansen	06 Mar 2002 06:05
BICHOT Guilhem 172613	06 Mar 2002 06:30
Anders Hermansen	06 Mar 2002 06:53
BICHOT Guilhem 172613	06 Mar 2002 07:20
Mary Stickney	06 Mar 2002 07:42

Subject:	RE: A outer join with where for the inner table does not contain all rows in the outer table
From:	BICHOT Guilhem 172613 (guil...@ipsn.fr)
Date:	03/06/2002 06:30:03 AM
List:	com.mysql.lists.bugs

Hi,

The results you get are right, there is no bug. Here is an explaination : - LEFT OUTER JOIN, which is the same as LEFT JOIN, does this : (taken from the MySQL manual http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#JOI N) if there is no matching record for the right table (b) in the ON part of the LEFT JOIN, a row with all columns set to NULL is used for the right table (b). - So in your first query, the join produces the following "temporary table" a.identtype a.betegntype b.identtype b.somefield 1 Type1 1 3 1 Type1 1 2 2 Type2 2 3 2 Type2 2 3 3 Type3 NULL NULL 4 Type4 NULL NULL and then it groups by a.betegntype and counts not-NULL values. OK. - In the second query, the same temporary table is generated, then the "WHERE b.somefield = 3" is applied and *this* discards all the rows of the temporary table which do not match, hence (NULL=3 is false) the second, fifth and sixth rows of the temporary column which now becomes a.identtype <other a columns> b.identtype b.somefield 1 Type1 1 3 2 Type2 2 3 2 Type2 2 3 and then it groups by a.betegntype and counts, and you get your results.

Guilhem

-----Message d'origine----- De : Anders Hermansen [mailto:and...@dfun.no] Envoyé : mercredi 6 mars 2002 15:06 À : bu...@lists.mysql.com Objet : A outer join with where for the inner table does not contain all rows in the outer table

Description:

An outer join with a where clause which restricts the inner table does not contain all rows in the outer table.

How-To-Repeat:

The problem can be repeated by using the SELECT statment show in this testcase:

# # Table structure for table 'sometype' #

CREATE TABLE sometype ( identtype int(11) NOT NULL auto_increment, betegntype char(100) default NULL, PRIMARY KEY (identtype) ) TYPE=MyISAM;

# # Dumping data for table 'sometype' #

INSERT INTO sometype VALUES (1,'Type1'); INSERT INTO sometype VALUES (2,'Type2'); INSERT INTO sometype VALUES (3,'Type3'); INSERT INTO sometype VALUES (4,'Type4');

# # Table structure for table 'somedata' #

CREATE TABLE somedata ( ident int(11) NOT NULL auto_increment, betegn char(100) default NULL, somefield int(11) NOT NULL default '0', identtype int(11) NOT NULL default '0', PRIMARY KEY (ident), KEY identtype (identtype) ) TYPE=MyISAM;

# # Dumping data for table 'somedata' #

INSERT INTO somedata VALUES (1,'Data1',3,1); INSERT INTO somedata VALUES (2,'Data2',3,2); INSERT INTO somedata VALUES (3,'Data3',3,2); INSERT INTO somedata VALUES (4,'Data4',2,1);

# # Select that works correctly #

SELECT a.betegntype, count(b.identtype) FROM sometype a LEFT OUTER JOIN somedata b on (a.identtype = b.identtype) GROUP BY a.betegntype;

# # Select that does not contain all rows in sometype (a) #

SELECT a.betegntype, count(b.identtype) FROM sometype a LEFT OUTER JOIN somedata b on (a.identtype = b.identtype) WHERE b.somefield = 3 GROUP BY a.betegntype;

Fix:

No known fix.

Submitter-Id: Originator: Anders Hermansen Organization: dFun Solutions AS MySQL support: none Synopsis: Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.47 (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.47, for pc-linux-gnu on

i686 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.47 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 1 day 27 min 0 sec

Threads: 105 Questions: 32847 Slow queries: 0 Opens: 408 Flush tables: 1 Open tables: 64 Queries per second avg: 0.373

Environment:

<machine, os, target, libraries (multiple lines)> System: Linux stargate.ithuset.com 2.2.19-6.2.12smp #1 SMP Fri Oct 26 13:31:09 EDT 2001 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/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer - felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Apr 17 2000 /lib/libc.so.6 -> libc-2.1.3.so -rwxr-xr-x 1 root root 4105868 Dec 8 15:31 /lib/libc-2.1.3.so -rw-r--r-- 1 root root 20299228 Dec 8 15:31 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Dec 8 15:31 /usr/lib/libc.so Configure command: ../configure --disable-shared --with-mysqld-ldflags=-all-static --with-clien t-ldflags=-all-static --without-berkeley-db --without-innodb --enable-assembler --with-mysqld-user=mysql --with-unix-soc ket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-char sets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --d atadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr /info --includedir=/usr/include --mandir=/usr/man '--with-comment=Official MySQL RPM' Perl: This is perl, version 5.005_03 built for i386-linux

To request this thread, e-mail bugs...@lists.mysql.com To unsubscribe, e-mail <bugs...@lists.mysql.com>