Bug #6107 Views: RIGHT JOIN with WHERE clause on views returns wrong result
Submitted: 14 Oct 2004 20:31 Modified: 21 Apr 2005 1:18
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:Linux (SuSE 9.1)
Assigned to: Igor Babaev CPU Architecture:Any

[14 Oct 2004 20:31] Trudy Pelzer
Description:
This is another example of a SELECT with a RIGHT JOIN on two 
views returning the wrong result (see Bug#6106). This time, the  
query also contains an additional WHERE clause, or the view 
is created with a WHERE clause. 
 

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 a.col1 in (1,5,9) 
and (b.col2 is null or b.col2=(select max(col2) from vt2 b where b.col1=a.col1)); 
+------+-------+------+------+ 
| col1 | col2  | col2 | col3 | 
+------+-------+------+------+ 
|    5 | david | NULL | NULL | 
+------+-------+------+------+ 
1 row in set (0.01 sec) 
-- This is the incorrect result. The correct result is: 
 
+------+-------+------+------+ 
| col1 | col2  | col2 | col3 | 
+------+-------+------+------+ 
|    1 | trudy |    2 | y    | 
|    5 | david | NULL | NULL | 
+------+-------+------+------+ 
2 rows in set (0.00 sec) 
 
create view vt1_2 as select * from t1 where col1 in (1,5,9); 
 
select a.col1,a.col2,b.col2,b.col3 from vt2 b right join vt1_2 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 | 
+------+-------+------+------+ 
|    5 | david | NULL | NULL | 
+------+-------+------+------+ 
1 row in set (0.00 sec) 
-- This is the incorrect response. The correct response is the same as above: 
 
+------+-------+------+------+ 
| col1 | col2  | col2 | col3 | 
+------+-------+------+------+ 
|    1 | trudy |    2 | y    | 
|    5 | david | NULL | NULL | 
+------+-------+------+------+ 
2 rows in set (0.00 sec)
[16 Apr 2005 7:37] Igor Babaev
This problem is related to one reported #6106. The same patch resolves it (see comments of #6106).
[21 Apr 2005 1:18] Paul Dubois
Noted in 5.0.5 changelog.