Weird result with LEFT/RIGHT JOIN

=2D----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

the following test case produces an unexpected result
on MySQL (both versions 3.23.54 and 4.0.12), while it
works fine with PostgreSQL and Firebird.

The third query is the wrong one. The first two are
there just to show that partial results are correct.

=2D ------------------------------------------------------------ ----

CREATE TABLE a (id integer, name char(30));
CREATE TABLE b (id integer, name char(30));
CREATE TABLE ab (a_id integer, b_id integer);

INSERT INTO a (id, name) VALUES (1, 'Alpha');
INSERT INTO a (id, name) VALUES (2, 'Bravo');
INSERT INTO a (id, name) VALUES (3, 'Charlie');
INSERT INTO a (id, name) VALUES (4, 'Delta');

INSERT INTO b (id, name) VALUES (101, 'Linus');
INSERT INTO b (id, name) VALUES (102, 'Alan');
INSERT INTO b (id, name) VALUES (103, 'Marcelo');
INSERT INTO b (id, name) VALUES (104, 'Andrew');

INSERT INTO ab (a_id, b_id) VALUES (1, 101);
INSERT INTO ab (a_id, b_id) VALUES (1, 102);
INSERT INTO ab (a_id, b_id) VALUES (1, 104);
INSERT INTO ab (a_id, b_id) VALUES (2, 101);

SELECT * FROM b INNER JOIN ab ON (b.id =3D ab.b_id AND b.name =3D 'Linus');
=2D -- +------+-------+------+------+
=2D -- | id | name | a_id | b_id |
=2D -- +------+-------+------+------+
=2D -- | 101 | Linus | 1 | 101 |
=2D -- | 101 | Linus | 2 | 101 |
=2D -- +------+-------+------+------+

SELECT * FROM a LEFT JOIN ab ON (a.id =3D ab.a_id);
=2D -- +------+---------+------+------+
=2D -- | id | name | a_id | b_id |
=2D -- +------+---------+------+------+
=2D -- | 1 | Alpha | 1 | 101 |
=2D -- | 1 | Alpha | 1 | 102 |
=2D -- | 1 | Alpha | 1 | 104 |
=2D -- | 2 | Bravo | 2 | 101 |
=2D -- | 3 | Charlie | NULL | NULL |
=2D -- | 4 | Delta | NULL | NULL |
=2D -- +------+---------+------+------+

SELECT * FROM b INNER JOIN ab ON (b.id =3D ab.b_id AND b.name =3D 'Linus') =
RIGHT JOIN a ON (a.id =3D ab.a_id);
=2D -- +------+---------+------+------+------+---------+
=2D -- | id | name | a_id | b_id | id | name |
=2D -- +------+---------+------+------+------+---------+
=2D -- | 101 | Linus | 1 | 101 | 1 | Alpha |
=2D -- | 102 | Alan | NULL | NULL | 1 | Alpha |
=2D -- | 103 | Marcelo | NULL | NULL | 1 | Alpha |
=2D -- | 104 | Andrew | NULL | NULL | 1 | Alpha |
=2D -- | 101 | Linus | 2 | 101 | 2 | Bravo |
=2D -- | 102 | Alan | NULL | NULL | 2 | Bravo |
=2D -- | 103 | Marcelo | NULL | NULL | 2 | Bravo |
=2D -- | 104 | Andrew | NULL | NULL | 2 | Bravo |
=2D -- | 101 | Linus | NULL | NULL | 3 | Charlie |
=2D -- | 102 | Alan | NULL | NULL | 3 | Charlie |
=2D -- | 103 | Marcelo | NULL | NULL | 3 | Charlie |
=2D -- | 104 | Andrew | NULL | NULL | 3 | Charlie |
=2D -- | 101 | Linus | NULL | NULL | 4 | Delta |
=2D -- | 102 | Alan | NULL | NULL | 4 | Delta |
=2D -- | 103 | Marcelo | NULL | NULL | 4 | Delta |
=2D -- | 104 | Andrew | NULL | NULL | 4 | Delta |
=2D -- +------+---------+------+------+------+---------+

=2D ------------------------------------------------------------ ----

The result we expect should be:

id | name | a_id | b_id | id | name
=2D -----+----------------+------+------+----+----------
101 | Linus | 1 | 101 | 1 | Alpha
101 | Linus | 2 | 101 | 2 | Bravo
| | | | 3 | Charlie
| | | | 4 | Delta

=2D --=20
// Bernardo Innocenti - Develer S.r.l., R&D dept.
\X/ http://www.develer.com/
=2D----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE+gNv3ltU4TfxqZsoRAhk8AJ4+OI96Z5BKnh+Oahj/qtOH8iG4mQCe LU6m
J/8/Yco/4hHLeonQTvjo25Y=3D
=3DZO8k
=2D----END PGP SIGNATURE-----


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

Re: Weird result with LEFT/RIGHT JOIN

Bernardo,

Bernardo Innocenti wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello,
>
> the following test case produces an unexpected result
> on MySQL (both versions 3.23.54 and 4.0.12), while it
> works fine with PostgreSQL and Firebird.

Thank you for you report, but I think we must first check what SQL99 says about
this case as it is not that obvious in what order join must be made for your query:

SELECT * FROM b INNER JOIN ab ON (b.id = ab.b_id AND b.name = 'Linus') RIGHT JOIN
a ON (a.id = ab.a_id);

not to mention what exactly how it should interpret ON (... AND b.name =
'Linus'...) part of the query.

Note that moving this into "proper" place e.g. WHERE clause gives you result you
expect.


Skipping your test case you believe that correct result should be the one given by:

mysql> create temporary table j1 SELECT * FROM b INNER JOIN ab ON (b.id = ab.b_id
AND b.name = 'Linus');

mysql> select * from j1;
+------+-------+------+------+
| id | name | a_id | b_id |
+------+-------+------+------+
| 101 | Linus | 1 | 101 |
| 101 | Linus | 2 | 101 |
+------+-------+------+------+

mysql> select * from j1 RIGHT JOIN a ON (a.id = j1.a_id);;
+------+-------+------+------+------+---------+
| id | name | a_id | b_id | id | name |
+------+-------+------+------+------+---------+
| 101 | Linus | 1 | 101 | 1 | Alpha |
| 101 | Linus | 2 | 101 | 2 | Bravo |
| NULL | NULL | NULL | NULL | 3 | Charlie |
| NULL | NULL | NULL | NULL | 4 | Delta |
+------+-------+------+------+------+---------+

Am I right?

We will doublecheck it, but as I can remember "legal" way to do it is:


mysql> SELECT * FROM b INNER JOIN ab ON (b.id = ab.b_id) RIGHT JOIN a ON (a.id =
ab.a_id) WHERE b.name = 'Linus';
+------+-------+------+------+------+---------+
| id | name | a_id | b_id | id | name |
+------+-------+------+------+------+---------+
| 101 | Linus | 1 | 101 | 1 | Alpha |
| 101 | Linus | 2 | 101 | 2 | Bravo |
| 101 | Linus | NULL | NULL | 3 | Charlie |
| 101 | Linus | NULL | NULL | 4 | Delta |
+------+-------+------+------+------+---------+



I will come up later with comment on it.

Best regards

--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Weird result with LEFT/RIGHT JOIN

Bernardo,

Bernardo Innocenti wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello,
>
> the following test case produces an unexpected result


> SELECT * FROM b INNER JOIN ab ON (b.id = ab.b_id AND b.name = 'Linus') RIGHT JOIN a ON (a.id = ab.a_id);

The situation is following.
Above is allowed by standard, but not supported by MySQL.
You can find note about it in manual chapter 6.4.1.1 JOIN Syntax

http://www.mysql.com/doc/en/JOIN.html

6.4.1.1 JOIN Syntax

....

You should generally not have any conditions in the ON part that are used to
restrict which rows you have in the result set (there are exceptions to this
rule). If you want to restrict which rows should be in the result, you have to do
this in the WHERE clause.

end of quote



Your query is one of exceptions mentioned.
So we can clasify it as "known difference" from SQL99.

As I said before result you need can be achieved by simply moving

.... b.name = 'Linus' ...

from ON() to WHERE clause

SELECT * FROM b INNER JOIN ab ON (b.id = ab.b_id) RIGHT JOIN a ON (a.id = ab.a_id)
WHERE b.name = 'Linus';

which is also valid SQL statement.

Best regards

--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org