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)
