Description:
If one creates a view (e.g. v1) based on a single underlying table (e.g. t1), a correlated subquery on the view returns an incorrect result. The same query on the underlying table returns the correct result, so the problem must be related to view code.
Note:
With version 4.1, the same query on the underlying table also returns the correct result.
For example:
The query on the table correctly returns {'p1','p2','p4'}:
mysql> select distinct first.col2 from t1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1);
+------+
| col2 |
+------+
| p1 |
| p2 |
| p4 |
+------+
3 rows in set (0.01 sec)
mysql> select distinct first.col2 from v1 first where first.col2 in (select second.col2 from v1 second where second.col1<>first.col1);
Empty set (0.85 sec)
Query translation: For each row in the first copy of the table, return the col2 value if (and only if) that col2 value also appears in a row of a second copy of the table in which the col1 value is not in the row of the first copy.
How to repeat:
create table t1 (col1 char(5),col2 char(5));
create view v1 (col1,col2) as select col1,col2 from t1;
insert into v1 values('s1','p1');
insert into v1 values('s1','p2');
insert into v1 values('s1','p3');
insert into v1 values('s1','p4');
insert into v1 values('s2','p1');
insert into v1 values('s3','p2');
insert into v1 values('s4','p4');
select distinct first.col2 from t1 first where first.col2 in
(select second.col2 from t1 second where second.col1<>first.col1);
select distinct first.col2 from v1 first where first.col2 in
(select second.col2 from v1 second where second.col1<>first.col1);
Description: If one creates a view (e.g. v1) based on a single underlying table (e.g. t1), a correlated subquery on the view returns an incorrect result. The same query on the underlying table returns the correct result, so the problem must be related to view code. Note: With version 4.1, the same query on the underlying table also returns the correct result. For example: The query on the table correctly returns {'p1','p2','p4'}: mysql> select distinct first.col2 from t1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1); +------+ | col2 | +------+ | p1 | | p2 | | p4 | +------+ 3 rows in set (0.01 sec) mysql> select distinct first.col2 from v1 first where first.col2 in (select second.col2 from v1 second where second.col1<>first.col1); Empty set (0.85 sec) Query translation: For each row in the first copy of the table, return the col2 value if (and only if) that col2 value also appears in a row of a second copy of the table in which the col1 value is not in the row of the first copy. How to repeat: create table t1 (col1 char(5),col2 char(5)); create view v1 (col1,col2) as select col1,col2 from t1; insert into v1 values('s1','p1'); insert into v1 values('s1','p2'); insert into v1 values('s1','p3'); insert into v1 values('s1','p4'); insert into v1 values('s2','p1'); insert into v1 values('s3','p2'); insert into v1 values('s4','p4'); select distinct first.col2 from t1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1); select distinct first.col2 from v1 first where first.col2 in (select second.col2 from v1 second where second.col1<>first.col1);