MySQL Bugs: #6106: Views: SELECT of 2 views with RIGHT JOIN returns wrong result

Bug #6106	Views: SELECT of 2 views with RIGHT JOIN returns wrong result
Submitted:	14 Oct 2004 20:16	Modified:	21 Apr 2005 1:12
Reporter:	Trudy Pelzer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Any (*)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
A SELECT that does a RIGHT JOIN of two views  
does not return the correct result. 

How to repeat:
create table t1 (col1 int primary key, col2 varchar(10)); 
insert into t1 values(1,'trudy'); 
insert into t1 values(2,'peter'); 
insert into t1 values(3,'sanja'); 
insert into t1 values(4,'monty'); 
insert into t1 values(5,'david'); 
insert into t1 values(6,'kent'); 
insert into t1 values(7,'carsten'); 
insert into t1 values(8,'ranger'); 
insert into t1 values(10,'matt'); 
create table t2 (col1 int, col2 int, col3 char(1)); 
insert into t2 values (1,1,'y'); 
insert into t2 values (1,2,'y'); 
insert into t2 values (2,1,'n'); 
insert into t2 values (3,1,'n'); 
insert into t2 values (4,1,'y'); 
insert into t2 values (4,2,'n'); 
insert into t2 values (4,3,'n'); 
insert into t2 values (6,1,'n'); 
insert into t2 values (8,1,'y'); 
create view vt1 as select * from t1; 
create view vt2 as select * from t2; 
 
select a.col1,a.col2,b.col2,b.col3 from vt2 b right join vt1 a on  a.col1=b.col1 where b.col2 is null 
or b.col2=(select max(col2) from vt2 b where  b.col1=a.col1); 
+------+---------+------+------+ 
| col1 | col2    | col2 | col3 | 
+------+---------+------+------+ 
|    4 | monty   |    3 | n    | 
|    5 | david   | NULL | NULL | 
|    7 | carsten | NULL | NULL | 
|   10 | matt    | NULL | NULL | 
+------+---------+------+------+ 
4 rows in set (0.01 sec) 
-- This is the incorrect response. The correct response is: 
 
+------+---------+------+------+ 
| col1 | col2    | col2 | col3 | 
+------+---------+------+------+ 
|    1 | trudy   |    2 | y    | 
|    2 | peter   |    1 | n    | 
|    3 | sanja   |    1 | n    | 
|    4 | monty   |    3 | n    | 
|    5 | david   | NULL | NULL | 
|    6 | kent    |    1 | n    | 
|    7 | carsten | NULL | NULL | 
|    8 | ranger  |    1 | y    | 
|   10 | matt    | NULL | NULL | 
+------+---------+------+------+ 
9 rows in set (0.00 sec)

The problem can be demonstrated with a simpler query:

mysql> SELECT a.col1,a.col2,b.col2,b.col3
    ->   FROM vt1 a LEFT JOIN t2 b ON a.col1=b.col1
    ->     WHERE b.col2 IS NULL OR
    ->           b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);
+------+---------+------+------+
| col1 | col2    | col2 | col3 |
+------+---------+------+------+
|    1 | trudy   | NULL | NULL |
|    2 | peter   | NULL | NULL |
|    3 | sanja   | NULL | NULL |
|    4 | monty   | NULL | NULL |
|    5 | david   | NULL | NULL |
|    6 | kent    | NULL | NULL |
|    7 | carsten | NULL | NULL |
|    8 | ranger  | NULL | NULL |
|   10 | matt    | NULL | NULL |
+------+---------+------+------+

The result must be the same as for the following equivalent query that does not use views:
mysql> SELECT a.col1,a.col2,b.col2,b.col3
    ->   FROM t1 a LEFT JOIN t2 b ON a.col1=b.col1
    ->     WHERE b.col2 IS NULL OR
    ->           b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);
+------+---------+------+------+
| col1 | col2    | col2 | col3 |
+------+---------+------+------+
|    1 | trudy   |    2 | y    |
|    2 | peter   |    1 | n    |
|    3 | sanja   |    1 | n    |
|    4 | monty   |    3 | n    |
|    5 | david   | NULL | NULL |
|    6 | kent    |    1 | n    |
|    7 | carsten | NULL | NULL |
|    8 | ranger  |    1 | y    |
|   10 | matt    | NULL | NULL |
+------+---------+------+------+

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24001

The fix will appear in version 5.0.5.

ChangeSet
  1.1826 05/04/13 23:06:37 igor@rurik.mysql.com +6 -0
  information_schema.result, information_schema.test:
    Added a test in connection with the fix for
    bug #6106.
  view.result, view.test:
    Added test cases for bugs #6106/6107.
  sql_show.cc:
    The addition of the case for items of the type REF_ITEM in the
    function uses_only_table_name_fields became necessary after
    the fix for bug #6106.
  sql_base.cc:
    The problem was due to the fact that two different column
    references were glued together though one of them belonged to
    a subquery while another to an outer query. This caused
    eventually a wrong calculation of values for the used_tables
    attribute.

Noted in 5.0.5 changelog.