MySQL Bugs: #20327: incorrect results with views, server-side prepared stmts, correlated subquery

Bug #20327	incorrect results with views, server-side prepared stmts, correlated subquery
Submitted:	7 Jun 2006 23:14	Modified:	13 Dec 2006 19:03
Reporter:	Alex Burgel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	5.0.22	OS:	Linux (linux 2.4.21)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
i'm seeing incorrect results with a query that use a view, server-side prepared statements, and a correlated subquery.

i've tried to simplify it as much as possible.

How to repeat:
create table t1 ( id int );
create table t2 ( t1_id int, x int );
create table t3 ( t1_id int, a int );
create view v1 as select * from t1;

insert into t1 (id) values (1),(2),(3);
insert into t2 (t1_id,x) values (1, 4),(2, 4),(2, 5),(3, 4);
insert into t3 (t1_id,a) values (1, 343),(2, 234),(2, 876),(5, 876),(3, 676);

# regular statement
select t3.a from t1 join t3 on t3.t1_id=t1.id where exists (select t2.t1_id from t2 where t2.t1_id=t1.id and t2.x=4);

+------+
| a    |
+------+
|  343 |
|  234 |
|  876 |
|  676 |
+------+
4 rows in set (0.00 sec)

# server-side prepared statement
prepare stmt from 'select t3.a from t1 join t3 on t3.t1_id=t1.id where exists (select t2.t1_id from t2 where t2.t1_id=t1.id and t2.x=?)';
set @a=4;
execute stmt using @a;

+------+
| a    |
+------+
|  343 |
|  234 |
|  876 |
|  676 |
+------+
4 rows in set (0.00 sec)

# regular statement with view
select t3.a from v1 join t3 on t3.t1_id=v1.id where exists (select t2.t1_id from t2 where t2.t1_id=v1.id and t2.x=4);

+------+
| a    |
+------+
|  343 |
|  234 |
|  876 |
|  676 |
+------+
4 rows in set (0.00 sec)

# server-side prepared statement with view
prepare stmt from 'select t3.a from v1 join t3 on t3.t1_id=v1.id where exists (select t2.t1_id from t2 where t2.t1_id=v1.id and t2.x=?)';
set @a=4;
execute stmt using @a;

+------+
| a    |
+------+
|  676 |
+------+
1 row in set (0.00 sec)

Suggested fix:
the work around is not to use views or server-side prepared statements.

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/commits/13800

ChangeSet@1.2261, 2006-10-17 17:08:00+04:00, kroki@moonlight.intranet +4 -0
  BUG#20327: incorrect results with views, server-side prepared stmts,
  correlated subquery.
  
  A query with sub-SELECT that references fields of a VIEW from outer
  SELECT could return wrong result if used from prepared statement.
  
  The bug happened because during statement preparation fields of a view
  were merged, and on execution we marked such merged fields as dependent
  on outer select, though only fields of a view itself should be marked so.
  
  The solution is to not mark as dependent fields merged from a view.

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/commits/15576

ChangeSet@1.2302, 2006-11-20 23:32:10+03:00, evgen@moonbone.local +2 -0
  Bug#20327: Marking of a wrong field leads to a wrong result on select with view,
  prepared statement and subquery.
  
  When a field of a view from an outer select is resolved the find_field_in_view
  function creates an Item_direct_view_ref object that references the 
  corresponding view underlying field. After that the view_ref is marked
  as a dependent one. While resolving view underlying field it also get
  marked as a dependent one due to current_select still points to the subselect.
  Marking the view underlying field is wrong and lead to attaching conditions
  to a wrong table and thus to the wrong result of the whole statement.
  
  Now mark_select_range_as_dependent() function isn't called for fields from a 
  view underlying table.

Pushed in 5.0.32/5.1.14-beta

Noted in 5.0.32, 5.1.14 changelogs.

A query with a subquery that references columns of a view from the outer
SELECT could return an incorrect result if used from a prepared statement.