MySQL Bugs: #61184: RIGHT JOIN with WHERE gives wrong result

Bug #61184	RIGHT JOIN with WHERE gives wrong result
Submitted:	16 May 2011 9:33	Modified:	11 Feb 2018 13:39
Reporter:	Florian Bantner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.1.54-1ubuntu4, 5.1.56	OS:	Any (Ubuntu 11.04, Widnows XP)
Assigned to:		CPU Architecture:	Any
Tags:	RIGHT JOIN, where

Description:
RIGHT JOIN when used with WHERE gives wrong result.

The second query above should only result in the first entry.

How to repeat:
create table A(id int primary key, s varchar(255));
create table B(id int primary key, s varchar(255));
create table mn( aid int, bid int );
insert into A values (1,'A1'),(2,'A2');
insert into B values (1,'B1'),(2,'B2');
insert into mn values (1,1);

mysql> select * from B right join mn on bid=B.id right join A on aid=A.id;
+------+------+------+------+----+------+
| id   | s    | aid  | bid  | id | s    |
+------+------+------+------+----+------+
|    1 | A1   |    1 |    1 |  1 | A1   |
| NULL | NULL | NULL | NULL |  2 | A2   |
+------+------+------+------+----+------+
2 rows in set (0.00 sec)

mysql> select * from B right join mn on bid=B.id right join A on aid=A.id where B.id is null;
+------+------+------+------+----+------+
| id   | s    | aid  | bid  | id | s    |
+------+------+------+------+----+------+
| NULL | NULL | NULL | NULL |  1 | A1   |
| NULL | NULL | NULL | NULL |  2 | A2   |
+------+------+------+------+----+------+
2 rows in set (0.00 sec)

joined in the other direction (with LEFT JOIN) everything is fine.

Verified on Windows XP:

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.56-community |
+------------------+
1 row in set (0.00 sec)

mysql> create table A(id int primary key, s varchar(255));
Query OK, 0 rows affected (0.09 sec)

mysql> create table B(id int primary key, s varchar(255));
Query OK, 0 rows affected (0.09 sec)

mysql> create table mn( aid int, bid int );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into A values (1,'A1'),(2,'A2');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into B values (1,'B1'),(2,'B2');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into mn values (1,1);
Query OK, 1 row affected (0.03 sec)

mysql> select * from B right join mn on bid=B.id right join A on aid=A.id;
+------+------+------+------+----+------+
| id   | s    | aid  | bid  | id | s    |
+------+------+------+------+----+------+
|    1 | B1   |    1 |    1 |  1 | A1   |
| NULL | NULL | NULL | NULL |  2 | A2   |
+------+------+------+------+----+------+
2 rows in set (0.00 sec)

mysql> select * from B right join mn on bid=B.id right join A on aid=A.id where
B.id is
    -> null;
+------+------+------+------+----+------+
| id   | s    | aid  | bid  | id | s    |
+------+------+------+------+----+------+
| NULL | NULL | NULL | NULL |  1 | A1   |
| NULL | NULL | NULL | NULL |  2 | A2   |
+------+------+------+------+----+------+
2 rows in set (0.00 sec)

mysql> explain select * from B right join mn on bid=B.id right join A on aid=A.i
d where B.id is NULL;
+----+-------------+-------+--------+---------------+---------+---------+-------
------+------+-------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref
      | rows | Extra                   |
+----+-------------+-------+--------+---------------+---------+---------+-------
------+------+-------------------------+
|  1 | SIMPLE      | A     | ALL    | NULL          | NULL    | NULL    | NULL
      |    2 |                         |
|  1 | SIMPLE      | mn    | ALL    | NULL          | NULL    | NULL    | NULL
      |    1 |                         |
|  1 | SIMPLE      | B     | eq_ref | PRIMARY       | PRIMARY | 4       | mydb.m
n.bid |    1 | Using where; Not exists |
+----+-------------+-------+--------+---------------+---------+---------+-------
------+------+-------------------------+
3 rows in set (0.01 sec)

Yep a see the same

MySQL 5.5 is NOT affected:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.5.14-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table A(id int primary key, s varchar(255));
Query OK, 0 rows affected (0.06 sec)

mysql> create table B(id int primary key, s varchar(255));
Query OK, 0 rows affected (0.11 sec)

mysql> create table mn( aid int, bid int );
Query OK, 0 rows affected (0.12 sec)

mysql> insert into A values (1,'A1'),(2,'A2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into B values (1,'B1'),(2,'B2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into mn values (1,1);
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> select * from B right join mn on bid=B.id right join A on aid=A.id;
+------+------+------+------+----+------+
| id   | s    | aid  | bid  | id | s    |
+------+------+------+------+----+------+
|    1 | B1   |    1 |    1 |  1 | A1   |
| NULL | NULL | NULL | NULL |  2 | A2   |
+------+------+------+------+----+------+
2 rows in set (0.01 sec)

mysql>  select * from B right join mn on bid=B.id right join A on aid=A.id where B.id is
    -> null;
+------+------+------+------+----+------+
| id   | s    | aid  | bid  | id | s    |
+------+------+------+------+----+------+
| NULL | NULL | NULL | NULL |  2 | A2   |
+------+------+------+------+----+------+
1 row in set (0.00 sec)

Posted by developer:
 
Fixed in 5.6.40 and up.