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

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