RE: MySQL Bug: Outer Joins producing incorrect result on a single

1) Are nested joins valid

I first saw the nested syntax/semantics in an article published about 2
years ago.
I then started using it on MS SQL Server and on DB2 where it works fine.
I didn't have the chance to try it on Oracle 9i but
that would be interesting.

Specifying the join order for semantic and not for performance is what
is needed. Having to use braces is ok with me but it doesn't
bring much to clarify how the joins should be processed.
That's probably why MS SQL Server and DB2 have elected to support this
with or without braces.

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

Is this easier to understand ? (the joins have to be nested anyway)

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

This later syntax is referenced here:
http://216.239.53.100/search?q=cache:cxaQ0v-eNMoC:jakarta.ap ache.org/ojb/xre
f/org/apache/ojb/broker/accesslayer/JoinSyntaxTypes.html+apa che+outer+join+s
yntax&hl=en&ie=UTF-8

2) Joins Order

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

LEFT OUTER JOIN and RIGHT OUTER JOIN are not commutative and the join order
makes a difference
when more than 2 tables are involved.

Here the right join has an additional level of complexity because it join
Health
to two tables, NMID and Object. Joining first Health to NMID as MYSQL does
makes little sense.

I believed that tables should be joined from left to right unless joins are
nested.
Here is what I have found on this:

Mutable Joins are those where more than two tables are joined. The SQL: 1999
standard assumes
the tables are joined from the left to the right, with the join conditions
only being able to
reference columns relating to the current join and any previous joins to the
left:
http://www.oracle-base.com/Articles/9i/ANSIISOSQLSupport.asp

MySQL is the only DBMS I know that allow columns to be used in a join clause
before their
table is. With inner joins only, it's ok but with non commutative joins such
as left outer
or right outer I don't believe it's ok.

Here is another case where join orders are important but it is simpler
because
there is only 2 tables on each join.

A)
SELECT
T1.ID AS T1,
T2.ID AS T2,
T3.ID AS T3
FROM
T1

LEFT OUTER JOIN T2
ON T1.ID = T2.ID

LEFT OUTER JOIN T3
ON T2.ID = T3.ID
ORDER BY
T1.ID,
T2.ID,
T3.ID;

B)
SELECT
T1.ID AS T1,
T2.ID AS T2,
T3.ID AS T3
FROM
T2

LEFT OUTER JOIN T3
ON T2.ID = T3.ID

RIGHT OUTER JOIN T1
ON T1.ID = T2.ID
ORDER BY
T1.ID,
T2.ID,
T3.ID;

C)
SELECT
T1.ID AS T1,
T2.ID AS T2,
T3.ID AS T3
FROM
T1

LEFT OUTER JOIN (T2
LEFT OUTER JOIN T3
ON T2.ID = T3.ID)
ON T1.ID = T2.ID
ORDER BY
T1.ID,
T2.ID,
T3.ID;

All 3 queries should return the same 3 rows (as do MS SQL Server and DB2)

+------+------+------+
| T1 | T2 | T3 |
+------+------+------+
| 1 | NULL | NULL |
| 2 | 2 | NULL |
| 3 | 3 | 3 |
+------+------+------+

MySQL gives the right answer for the first query,
but 9 rows for the second query and a syntax error on the third one.

+------+------+------+
| T1 | T2 | T3 |
+------+------+------+
| 1 | 2 | NULL |
| 1 | 3 | NULL |
| 1 | 4 | NULL |
| 2 | 2 | NULL |
| 2 | 3 | NULL |
| 2 | 4 | NULL |
| 3 | 2 | NULL |
| 3 | 3 | 3 |
| 3 | 4 | NULL |
+------+------+------+

How-To-Repeat:

create table T1 (ID integer);
create table T2 (ID integer);
create table T3 (ID integer);

insert into T1 values (1);
insert into T1 values (2);
insert into T1 values (3);

insert into T2 values (2);
insert into T2 values (3);
insert into T2 values (4);

insert into T3 values (3);
insert into T3 values (4);
insert into T3 values (5);

Regards,
Jean-Pierre Pelletier

-----Original Message-----
From: Michael Widenius [mailto:monty@mysql.com]
Sent: Wednesday, September 25, 2002 8:06 AM
To: Mark Matthews; Jean-Pierre Pelletier
Cc: bugs@lists.mysql.com
Subject: Fw: MySQL Bug: Outer Joins producing incorrect result on a
single join referencing multiple tables



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