RE: MySQL Bug: Outer Joins producing incorrect result on a single
am 27.09.2002 17:10:05 von Jean-Pierre PelletierThanks for your answers,
1) Supporting nested joins with braces is a good addition to MySQL,
I am looking forward to use it when available.
2) I rewrote one of the query of my first case to take advantage of the
MySQL misfeature
than you pointed out "OUTER JOIN having higher priority than a normal join"
to force the
join on Object and NMID to be done before the right join.
With the data I have, every row of table NMID match with exactly one row of
table Object
and every row of table Object match with exactly one row of table NMID.
In this case, joining these tables using an OUTER JOIN left or right would
do
the same as joining them with an INNER JOIN.
Knowing this, I modified my query changing the INNER by a LEFT OUTER join,
as follows.
According to what you said, this should give me the right result...
SELECT DISTINCT
H.Appliance_ID, H.Health_NMID, H.Health_DeviceOrPort
FROM
Object O
-- INNER JOIN NMID N
LEFT OUTER 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;
It gives a wrong result again, 0 rows instead of 2 rows
I then tried by changing the INNER by a RIGHT OUTER
SELECT DISTINCT
H.Appliance_ID, H.Health_NMID, H.Health_DeviceOrPort
FROM
Object O
-- INNER JOIN NMID N
RIGHT OUTER 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;
It gives a wrong result, 4 rows instead of 2 rows
I am not sure why it gives this result is it because of join ordering or
something else,
I will leave you guys figured that out, but as far as I am concerned these
results are wrong in all cases!
3) I have added (see below) what is needed to reproduce the second case
I agree that ((T2 LEFT JOIN T3) RIGHT JOIN T1) is the right order
for processing these joins.
MySQL return 9 rows instead of 3, again as far as I am concerned, this
result is wrong!
4) I also have the book "SQL-99 Complete, Really" and on p.585 it talks
about
a LEFT JOIN followed by a RIGHT JOIN.
My first case only had a INNER JOIN followed by a RIGHT join which should
not pose
a problem of interpretation.
Again, I am not sure why it gives this result is it because of join ordering
or something else,
but the result is wrong.
5) Furthermore, on the first case, how can MySQL process the joins in this
order
(Object INNER JOIN (Nmid RIGHT JOIN Health)) when the RIGHT JOIN references
columns
of table Object which hasn't been processed yet.
In my opinion, this can only give an unpredictable result.
How-To-Repeat:
FIRST CASE:
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 RIGHT JOIN REFERENCES ALL THREE TABLES, it can't be processed first
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;
The following 2 rows should be returned
Appliance_ID Health_NMID Health_DeviceOrPort
------------ ----------- -------------------
0 3 Device
0 4 Device
On MYSQL, This incorrectly returns no rows.
SECOND CASE)
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);
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;
The following 3 rows should be returned
+------+------+------+
| T1 | T2 | T3 |
+------+------+------+
| 1 | NULL | NULL |
| 2 | 2 | NULL |
| 3 | 3 | 3 |
+------+------+------+
MySQL incorrectly returns these 9 rows.
+------+------+------+
| 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 |
+------+------+------+
Regards
Jean-Pierre Pelletier
-----Original Message-----
From: Michael Widenius [mailto:monty@mysql.com]
Sent: Thursday, September 26, 2002 5:23 PM
To: Jean-Pierre Pelletier
Cc: bugs@lists.mysql.com; Mark Matthews; Robert Berman; Eric Neron;
Matthew Darwin; Allan Mertner
Subject: RE: MySQL Bug: Outer Joins producing incorrect result on a
single join referencing multiple tables
>>>>> "JP" == Jean-Pierre Pelletier
writes:
JP> 1) Are nested joins valid
JP> I first saw the nested syntax/semantics in an article published about 2
JP> years ago.
JP> I then started using it on MS SQL Server and on DB2 where it works fine.
JP> I didn't have the chance to try it on Oracle 9i but
JP> that would be interesting.
Thanks for the long explanation about join order.
The way we plan to fix this in the future is to introduce braces when
specifying the join. The problem with this is that to do this we have
to do a lot of work on the optimizer/execution unit and we can't do
this until 4.1 is ready. Until this the recommended way to solve this
things like this is to use temporary tables.
A misfeature that you pointed out is that MySQL currently defines a
OUTER JOIN to have higher priority than a normal join. In other words,
there is always an invisible set of braces around any LEFT or RIGHT outer
join.
JP> Mutable Joins are those where more than two tables are joined. The SQL:
1999
JP> standard assumes
JP> the tables are joined from the left to the right, with the join
conditions
JP> only being able to
JP> reference columns relating to the current join and any previous joins to
the
JP> left:
JP> http://www.oracle-base.com/Articles/9i/ANSIISOSQLSupport.asp
I checked my reference book (SQL-99, Complete Really, page 585) and
there they conclude that all DRMS seams to do this differently :(
From this I conclude that SQL-99 doesn't really specify exactly in
which order the join is done.
JP> SELECT
JP> T1.ID AS T1,
JP> T2.ID AS T2,
JP> T3.ID AS T3
JP> FROM
JP> T2
JP> LEFT OUTER JOIN T3
JP> ON T2.ID = T3.ID
JP> RIGHT OUTER JOIN T1
JP> ON T1.ID = T2.ID
JP> ORDER BY
JP> T1.ID,
JP> T2.ID,
JP> T3.ID;
The above should in MySQL be joined as follows:
((T2 LEFT JOIN T3) RIGHT JOIN T1)
Which should be as you wanted. AS you didn't provide me with a full
example, I can't verify the results for this case.
JP> MySQL gives the right answer for the first query,
JP> but 9 rows for the second query and a syntax error on the third one.
The syntax errors comes from MySQL not supporting braces (as I pointed
out in my previous email)
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-thread12623@lists.mysql.com
To unsubscribe, e-mail