From: Harald Kleiner <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subject: unexpected results using LEFT JOIN with more than one join_condition
>Description:
I use LEFT JOIN to join together two tables. And (for some 'complex'
reason) I want to add additional conditions into the join_condition-part
(and not in the WHERE part where it would belong to). But these additional
conditions seem to be ignored just as if they weren't there. If I cut the
'ON'-part and move conditions to the 'WHERE' all goes the way it should.
Changing the join method from LEFT JOIN to INNER JOIN does fix the problem
- yes but alas I need a LEFT JOIN in this case.
The manual says
"The ON conditional is any conditional of the form that may be used in a
WHERE clause."
So it _should_ be allowed to do the things that I do...
Maybe this is all my fault - so please be patient. But in any case: Thank
you very much for spending time with my problem!
>How-To-Repeat:
I have constructed a simplified example that shows the unexpected behavior.
A table called person is linked with room-table. Both have a date-typed
field that holds deletion-date of the record. Now I want to get all persons
with their room codes which are _not_ deleted
(i.e. deleted==0).
As you can see the first query uses LEFT JOIN ... ON ... and returns all
four persons. (Joe gets returned even though he has a non-zero
deletion-date -- that's what I don't understand)
And in the second query I just replaced the first 'AND' with a 'WHERE' and
voila Joe falls out (because of his non-zero deletion-date) and Fred falls
out because of his null room (room.deleted gets null I think) -- that's
what I expected to get.
DROP DATABASE JOINTest;
CREATE DATABASE JOINTest;
USE JOINTest;
CREATE TABLE person (
personID INT AUTO_INCREMENT NOT NULL,
roomID INT,
name VARCHAR(20),
deleted DATE,
PRIMARY KEY(personID)
);
CREATE TABLE room (
roomID INT AUTO_INCREMENT NOT NULL,
roomcode VARCHAR(20),
deleted DATE,
PRIMARY KEY(roomID)
);
INSERT INTO person VALUES(NULL, 0, 'Fred', 0);
INSERT INTO person VALUES(NULL, 1, 'Max', 0);
INSERT INTO person VALUES(NULL, 1, 'Joe', '2002-07-01');
INSERT INTO person VALUES(NULL, 1, 'Anne', 0);
INSERT INTO room VALUES(NULL, 'R001', 0);
SELECT DISTINCTROW person.name, person.deleted, room.roomcode
FROM person LEFT JOIN room ON person.roomID=room.roomID AND
person.deleted=0 AND room.deleted=0;
+------+------------+----------+
| name | deleted | roomcode |
+------+------------+----------+
| Fred | 0000-00-00 | NULL |
| Max | 0000-00-00 | R001 |
| Joe | 2002-07-01 | NULL |
| Anne | 0000-00-00 | R001 |
+------+------------+----------+
4 rows in set (0.00 sec)
SELECT DISTINCTROW person.name, person.deleted, room.roomcode
FROM person LEFT JOIN room ON person.roomID=room.roomID WHERE
person.deleted=0 AND room.deleted=0;
+------+------------+----------+
| name | deleted | roomcode |
+------+------------+----------+
| Max | 0000-00-00 | R001 |
| Anne | 0000-00-00 | R001 |
+------+------------+----------+
2 rows in set (0.00 sec)
>Fix:
Yes, I could move additional criteria into the WHERE but that would be not
as beautiful and easy for me as putting it into the ON - part. And even in
the manual (chapter 9.8.1, The Twin Project) such a join is used. So it
_should_ work.
>Submitter-Id:
>Originator: Harald Kleiner
>Organization:
>MySQL support: none
>Synopsis: unexpected LEFT JOIN behavior
>Severity: non-critical
>Priority: low
>Category: mysql
>Class: sw-bug | support
>Release: mysql-3.23.33 (Source distribution)
>Server: /usr/bin/mysqladmin Ver 8.15 Distrib 3.23.33, for suse-linux 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.33-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 54 min 52 sec
Threads: 3 Questions: 618 Slow queries: 0 Opens: 56 Flush tables:
1 Open tables: 50 Queries per second avg: 0.188
>Environment:
SuSE Linux 7.1 (but with some updates already) on an AMD Duron,
256MB RAM
System: Linux haraldk 2.4.7-4GB #1 Thu Oct 25 19:17:11 GMT 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/i486-suse-linux/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='-O2 -m486' CXX='c++' CXXFLAGS='-O2
-m486' LDFLAGS=''
LIBC:
-rwxr-xr-x 1 root root 1382179 Jan 19 2001 /lib/libc.so.6
-rw-r--r-- 1 root root 2585872 Jan 19 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Jan 19 2001 /usr/lib/libc.so
-rw-r--r-- 1 root root 851660 Jan 19 2001 /usr/lib/libc-client.a
lrwxrwxrwx 1 root root 19 Apr 24 23:52
/usr/lib/libc-client.so -> libc-client.so.2000
-rwxr-xr-x 1 root root 714876 Jan 19 2001
/usr/lib/libc-client.so.2000
Configure command: ./configure --enable-shared --enable-assembler
--infodir=/usr/share/info --libdir=/usr/lib --libexecdir=/usr/sbin --local
---------------------------------------------------------------------
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
