MySQL Bugs: #13033: Incorrect execution of joins

Bug #13033	Incorrect execution of joins
Submitted:	7 Sep 2005 10:03	Modified:	22 Sep 2005 9:36
Reporter:	Ilya Pyatigorskiy	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.24-nt, 4.1.15-BK	OS:	Linux (Linux & Windows)
Assigned to:		CPU Architecture:	Any

Description:
When using inner join and right outer join one after another the result is incorrect.

Let's suppose we have three tables:
SELECT * FROM T1;
+---+
| a |
+---+
| 1 |
+---+
SELECT * FROM T2;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
SELECT * FROM T3;
+---+
| a |
+---+
| 2 |
+---+

then I wish to run following query:
mysql> SELECT *
    -> FROM T1
    -> JOIN T2 ON T1.a = T2.a
    -> RIGHT JOIN T3 ON T3.a = T1.a;
the result is:
+---+------+---+
| a | a    | a |
+---+------+---+
| 1 | NULL | 2 |
+---+------+---+

while I'm expecting
NULL, NULL, 2

How to repeat:
DB layout:
----------------------------------------
CREATE TABLE `T1` (
  `a` int(11) NOT NULL default '0'
) TYPE=MyISAM;
INSERT INTO `T1` VALUES (1);

CREATE TABLE `T2` (
  `a` int(11) NOT NULL default '0'
) TYPE=MyISAM;
INSERT INTO `T2` VALUES (1);
INSERT INTO `T2` VALUES (2);

CREATE TABLE `T3` (
  `a` int(11) NOT NULL default '0'
) TYPE=MyISAM;
INSERT INTO `T3` VALUES (2);
----------------------------------------
the query:

SELECT * 
FROM T1
JOIN T2 ON T1.a = T2.a
RIGHT JOIN T3 ON T3.a = T1.a

I was able to repeat it on 4.1.15-BK on Linux as described also:

[openxs@Fedora 4.1]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE `T1` (
    ->    `a` int(11) NOT NULL default '0'
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0,01 sec)

mysql> INSERT INTO `T1` VALUES (1);
Query OK, 1 row affected (0,00 sec)

mysql> CREATE TABLE `T2` (
    ->   `a` int(11) NOT NULL default '0'
    -> )) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0,01 sec)

mysql> INSERT INTO `T2` VALUES (1);
Query OK, 1 row affected (0,00 sec)

mysql> ININSERT INTO `T2` VALUES (2);

Query OK, 1 row affected (0,00 sec)

mysql> CREATE TABLE `T3` (
    ->    `a` int(11) NOT NULL default '0'
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0,01 sec)

mysql> INSERT INTO `T3` VALUES (2);
Query OK, 1 row affected (0,00 sec)

mysql> SELECT *
    -> FROM T1
    -> JOIN T2 ON T1.a = T2.a
    -> RIGHT JOIN T3 ON T3.a = T1.a;
+---+------+---+
| a | a    | a |
+---+------+---+
| 1 | NULL | 2 |
+---+------+---+
1 row in set (0,02 sec)

But, on 5.0.13-BK it works as expected:

mysql> SELECT *
    -> FFROM T1
    -> JOIN T2 ON T1.a = T2.a
    -> RIGHT JOIN T3 ON T3.a = T1.a;
+------+------+---+
| a    | a    | a |
+------+------+---+
| NULL | NULL | 2 |
+------+------+---+
1 row in set (0,04 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.0.13-beta-debug |
+-------------------+
1 row in set (0,00 sec)

It should be due to http://dev.mysql.com/doc/mysql/en/news-5-0-12.html:

"Natural joins and joins with USING, including outer join variants, now are processed according to the SQL:2003 standard. "

So, I am not sure it will be back ported and fixed in 4.0 or 4.1

Hi!

This was fixed for 5.0. The patch for it is rather large and we can not apply it to a production release like 4.1.

  -Brian