>Description:
I wanted to get the rows from table Health for which no match exists in the
table resulting from the inner join of Object and NMID.
I ran these queries in MySQL 3.23 and 4.0 with the same incorrect results.
They all work fine on MS SQL Server 2000 and on DB2 7.2.
With a DBMS supporting subqueries, I would have expressed it like this:
1)
SELECT DISTINCT
H.Appliance_ID, H.Health_NMID, H.Health_DeviceOrPort
FROM
Health H
WHERE
NOT EXISTS (SELECT 0
FROM
Object O
INNER JOIN NMID N
ON O.Object_ID = N.Object_ID
WHERE
H.Appliance_ID = N.Appliance_ID
AND H.Health_NMID = N.NMID_NMID
AND H.Health_DeviceOrPOrt = O.Object_Table
)
AND H.Appliance_ID = 0;
Because MySQL lacks support for subqueries,
I tried this SQL-92 compliant form of outer join, but MySQL reject it
with a syntax error.
2)
SELECT DISTINCT
H.Appliance_ID, H.Health_NMID, H.Health_DeviceOrPort
FROM
Health H
LEFT OUTER JOIN Object O
INNER JOIN NMID N
ON O.Object_ID = N.Object_ID
ON H.Appliance_ID = N.Appliance_ID
AND H.Health_DeviceOrPOrt = O.Object_Table
AND H.Health_NMID = N.NMID_NMID
WHERE
H.Appliance_ID = 0
AND N.Object_ID IS NULL;
With a right outer join, no syntax errors is generated but the result is
incorrect.
3)
SELECT DISTINCT
H.Appliance_ID, H.Health_NMID, H.Health_DeviceOrPort
FROM
Object O
INNER JOIN NMID N
ON O.Object_ID = N.Object_ID
RIGHT OUTER JOIN Health H
ON H.Appliance_ID = N.Appliance_ID
AND H.Health_NMID = N.NMID_NMID
AND H.Health_DeviceOrPOrt = O.Object_Table
WHERE
H.Appliance_ID = 0
AND O.Object_ID IS NULL;
This incorrectly returns no rows.
With MS SQL Server 2000 and DB2 v7.2 it makes no difference to test
O.Object_ID IS NULL
or N.Object_ID IS NULL, but with MySQL, it does but in both cases the result
is wrong.
Testing N.Object_ID incorrectly returns 4 rows:
+--------------+-------------+---------------------+
| Appliance_ID | Health_NMID | Health_DeviceOrPort |
+--------------+-------------+---------------------+
| 0 | 1 | device |
| 0 | 2 | device |
| 0 | 3 | device |
| 0 | 4 | device |
+--------------+-------------+---------------------+
Adding parenthesis to force the inner join to be done before the outer join
didn't fix the problem.
4)
SELECT DISTINCT
H.Appliance_ID, H.Health_NMID, H.Health_DeviceOrPort
FROM
-- (Object O
Object O
INNER JOIN NMID N
-- ON O.Object_ID = N.Object_ID)
ON O.Object_ID = N.Object_ID
RIGHT OUTER JOIN Health H
ON H.Appliance_ID = N.Appliance_ID
AND H.Health_NMID = N.NMID_NMID
AND H.Health_DeviceOrPOrt = O.Object_Table
WHERE
H.Appliance_ID = 0
AND O.Object_ID IS NULL;
>How-To-Repeat:
CREATE TABLE Object (Object_ID INTEGER NOT NULL, Object_Table VARCHAR(6) NOT
NULL);
CREATE TABLE NMID (Appliance_ID INTEGER NOT NULL, NMID_NMID INTEGER,
Object_ID INTEGER NOT NULL);
CREATE TABLE Health (Appliance_ID INTEGER NOT NULL, Health_NMID INTEGER NOT
NULL, Health_DeviceOrPort VARCHAR(6) NOT NULL);
INSERT INTO Object(Object_ID, Object_Table) VALUES (101,'Device');
INSERT INTO Object(Object_ID, Object_Table) VALUES (201,'Device');
INSERT INTO Object(Object_ID, Object_Table) VALUES (202,'Port');
INSERT INTO Object(Object_ID, Object_Table) VALUES (302,'Port');
INSERT INTO NMID (Appliance_ID, NMID_NMID, Object_ID) VALUES (0, 1, 101);
INSERT INTO NMID (Appliance_ID, NMID_NMID, Object_ID) VALUES (0, 2, 201);
INSERT INTO NMID (Appliance_ID, NMID_NMID, Object_ID) VALUES (0, 2, 202);
INSERT INTO NMID (Appliance_ID, NMID_NMID, Object_ID) VALUES (0, 3, 302);
INSERT INTO Health (Appliance_ID, Health_NMID, Health_DeviceOrPort) VALUES
(0, 1, 'Device');
INSERT INTO Health (Appliance_ID, Health_NMID, Health_DeviceOrPort) VALUES
(0, 2, 'Device');
INSERT INTO Health (Appliance_ID, Health_NMID, Health_DeviceOrPort) VALUES
(0, 3, 'Device');
INSERT INTO Health (Appliance_ID, Health_NMID, Health_DeviceOrPort) VALUES
(0, 4, 'Device');
The following 2 rows should be returned
Appliance_ID Health_NMID Health_DeviceOrPort
------------ ----------- -------------------
0 3 Device
0 4 Device
>Fix:
Create a temporary table with the result of the inner join
and then use the temporary table in an outer join
>Submitter-Id: Jean-Pierre Pelletier
>Originator: root
>Organization: Peregrine Systems, Inc.
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis: MySQL Bug: Outer Joins producing incorrect result on a single
join referencing multiple tables
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.3-beta (Source distribution)
>Server: /usr/mysql/bin/mysqladmin Ver 8.37 Distrib 4.0.3-beta, 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 4.0.3-beta
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 11 days 15 min 14 sec
Threads: 1 Questions: 440 Slow queries: 0 Opens: 16 Flush tables: 1
Open tables: 10 Queries per second avg: 0.000
>Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux warez.reboot.loran.com 2.4.18 #1 Mon Feb 25 15:35:43 EST 2002
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/i686-pc-linux-gnu/2.95.3/specs
gcc version 2.95.3 20010315 (release)
Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Apr 3 10:19 /lib/libc.so.6 ->
libc-2.2.3.so
-rwxr-xr-x 1 root root 1193256 Sep 26 2001 /lib/libc-2.2.3.so
-rw-r--r-- 1 root root 24962644 Apr 4 12:14 /usr/lib/libc.a
-rw-r--r-- 1 root root 190 Aug 31 2001 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql
---------------------------------------------------------------------
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
