Bug #5247 Query with a correlated sub-query and using view returns a wrong result
Submitted: 27 Aug 2004 10:34 Modified: 6 Sep 2004 13:52
Reporter: Igor Babaev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.2 OS:Any (all)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[27 Aug 2004 10:34] Igor Babaev
Description:
The result of a query that uses a view and contains a correlated sub-query mostly is wrong.   

How to repeat:
Run the following statements:
create table t1 (a int, b int);
insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10);
create view v1(c) as select a+1 from t1 where b >= 4;
select c from v1 where exists (select * from t1 where a=2 and b=c);

You'll get

mysql> create table t1 (a int, b int);
Query OK, 0 rows affected (2.25 sec)

mysql> insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10);
Query OK, 5 rows affected (1.65 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> create view v1(c) as select a+1 from t1 where b >= 4;
Query OK, 0 rows affected (3.88 sec)

mysql> select c from v1 where exists (select * from t1 where a=2 and b=c);
+------+
| c    |
+------+
|    3 |
|    3 |
|    4 |
+------+
3 rows in set (26.74 sec)

(or, sometimes you'll get
mysql> select c from v1 where exists (select * from t1 where a=2 and b=c);
Empty set (20.53 sec)
)

The equivalent query that does not use the view returns the correct result:

mysql> select a+1 as c from t1 where exists (select * from t1 where a=2 and b=c) and b >=4;
+------+
| c    |
+------+
|    4 |
+------+
1 row in set (19.29 sec)
[1 Sep 2004 22:30] Oleksandr Byelkin
ChangeSet 
  1.1744 04/09/01 23:27:40 bell@sanja.is.com.ua +3 -0 
  fixed staistic of subquery if outer field resolved in merged view (BUG#5247)
[6 Sep 2004 13:52] Oleksandr Byelkin
Thank you for bugreport! Bug is fixed, patch is pushed intou nour source repositoy.