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

Hi,

I agree that the rules for processing multiple joins haven't been clearly
established by the standard bodies.

But it is imperative that each DBMS uses a set of consistent and logical
rules for processing multiple joins so that the result can be accurately
predict by looking at the query.

Processing the joins from left to right seems to be the only rule behind
MS SQL Server, DB2 and Oracle and also seems to be the only rule published.
It has consistently allows the result of a query to be predicted on these
DBMS.

There is possibly (but unlikely) other set of logical rules that can be
used to process multiple joins.

Using a high level language (not through explain and pseudo codes specific
to a given query)
could you explain what are MySQL rules to process multiple joins ?

The result returned by MySQL on the first case I send (a single join
referencing 3 tables)
indicates that whatever MySQL rules are, they are not general enough to
process such a query
in a meaningful way.


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


MySQL incorrectly returns no rows.

Thanks
Jean-Piere Pelletier

-----Original Message-----
From: Michael Widenius [mailto:monty@mysql.com]
Sent: Thursday, October 03, 2002 4:06 AM
To: Jean-Pierre Pelletier
Cc: w@sfgate.com; Eric Neron; Robert Berman; bugs@lists.mysql.com; Mark
Matthews
Subject: RE: MySQL Bug: Outer Joins producing incorrect result on a
single join referencing multiple tables



>>>>> "JP" == Jean-Pierre Pelletier
writes:

JP> Hi,
JP> I have observed these 4 queries to return the same 4 rows on both MS SQL
JP> Server and DB2 and
JP> I suspect on Oracle 9 too.

JP> As there is very few documentation published on how to process multiple
JP> joins,
JP> I deduced the joining rules myself, I might be wrong on them, but the
fact
JP> remains
JP> that Mysql gives a different result than the other DBMS.

It's a well known fact that when you are using many outer joins, many
of the existing databases will give you different results (as noted in
the 'SQL-99 Complete, really'). That the current est gives the same
result in two databases doesn't prove that they are right or that ANSI
SQL99 doesn't allow the other behavior. (Note that ANSI SQL99 leaves
a lot of things to the implementation. After all, it's a join of a
lot of different SQL standards). Oracle and MS SQL does also not
rigidly follow ANSI SQL 99 at this point.

If you check the crash-me page on
http://www.mysql.com/information/crash-me.php
you will find a lot of cases where many of the major vendors doesn't
follow the ANSI specs.

Regards,
Monty

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