Fw: MySQL Bug: Outer Joins producing incorrect result on a single join referencing multiple tables

Fw: MySQL Bug: Outer Joins producing incorrect result on a single join referencing multiple tables

am 20.09.2002 21:43:14 von Mark Matthews

Found this on bugs. I haven't had a chance to see if it's reproducible, but
the user gave us a test case.

-Mark

----- Original Message -----
From: "Jean-Pierre Pelletier"
To:
Cc: "Eric Neron" ; "Dana Lacoste"
; "Matthew Darwin"

Sent: Friday, September 20, 2002 2:07 PM
Subject: MySQL Bug: Outer Joins producing incorrect result on a single join
referencing multiple tables


> >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:
>
> 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
> To unsubscribe, e-mail

> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


------------------------------------------------------------ ---------
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 bugs-thread12570@lists.mysql.com
To unsubscribe, e-mail

Fw: MySQL Bug: Outer Joins producing incorrect result on a single join referencing multiple tables

am 25.09.2002 14:05:53 von Michael Widenius

Hi!

>>>>> "Mark" == Mark Matthews writes:

Mark> Found this on bugs. I haven't had a chance to see if it's reproducible, but
Mark> the user gave us a test case.

Mark> -Mark



It's reproducable; Looking at it just now.

> Description:


> 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;

The reason for the syntax error is that LEFT OUTER JOIN requires a
USING or ON clause in MySQL. (I just checked the SQL 99 spec and it
looks like this is optional, something we have missed to take into
account).

The above syntax is also wrong as you can't have two ON cases here.
(I doubt that ANSI SQL 99 allows this...)

One could argue that the first ON is part for the INNER JOIN and the
second one is part of the OUTER JOIN, but I am not sure that SQL
99 really allows this without braces.

> 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.

Actually this is correct.

If we modify the query to print out the object_id, we get:

If we remove the distinct and the IS NULL part from the query and add
O.Object_id to the select part (to see the involved rows) we get:

mysql> SELECT O.Object_id,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;
+-----------+--------------+-------------+------------------ ---+
| Object_id | Appliance_ID | Health_NMID | Health_DeviceOrPort |
+-----------+--------------+-------------+------------------ ---+
| 101 | 0 | 1 | Device |
| 201 | 0 | 1 | Device |
| 202 | 0 | 1 | Device |
| 302 | 0 | 1 | Device |
| 101 | 0 | 2 | Device |
| 201 | 0 | 2 | Device |
| 202 | 0 | 2 | Device |
| 302 | 0 | 2 | Device |
| 101 | 0 | 3 | Device |
| 201 | 0 | 3 | Device |
| 202 | 0 | 3 | Device |
| 302 | 0 | 3 | Device |
| 101 | 0 | 4 | Device |
| 201 | 0 | 4 | Device |
| 202 | 0 | 4 | Device |
| 302 | 0 | 4 | Device |
+-----------+--------------+-------------+------------------ ---+
16 rows in set (0.01 sec)
>
> 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
resultis wrong.

I think that the above result is correct and in this case adding a
test of 'O.Object_id is NULL' should return 0 rows.

The reason for different results is that the databases preforms the
join in different order. MySQL will group the joins as follows
(which is allowed according to what I know):

(Object (Nmid Health))



> Adding parenthesis to force the inner join to be done before the outer
join
> didn't fix the problem.
> 4)

This is because MySQL doesn't yet support parentheses in joins.
(Sorry about that)
We have this on our TODO but not in the real near future as this will
require a big change in our optimizer and execution engine.

In 4.1 you will be able to use derived tables and sub selects to solve
this particular problem. Until then you have to use temporary tables.

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com

------------------------------------------------------------ ---------
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 bugs-thread12592@lists.mysql.com
To unsubscribe, e-mail