>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