Bug #36002 Prepared statements: if a view used in a statement is replaced, bad data
Submitted: 11 Apr 2008 18:21 Modified: 27 Jan 2012 4:14
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.0,5.1,6.0 BK OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[11 Apr 2008 18:21] Konstantin Osipov
Description:
If a prepared statement uses a view, and the view definition changes between prepare and execute, the prepared statement continues to use the old definition, and thus may return incorrect results.

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop view if exists v1;
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into t1 values (10), (20), (30);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> create view v1 as select a, 2*a as b, 3*a as c from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|   10 |   20 |   30 | 
|   20 |   40 |   60 | 
|   30 |   60 |   90 | 
+------+------+------+
3 rows in set (0.00 sec)

mysql> 
mysql> prepare stmt from "select * from v1";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|   10 |   20 |   30 | 
|   20 |   40 |   60 | 
|   30 |   60 |   90 | 
+------+------+------+
3 rows in set (0.00 sec)

mysql> 
mysql> drop view v1;
Query OK, 0 rows affected (0.00 sec)

mysql> create view v1 as select a, 2*a as b, 5*a as c from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|   10 |   20 |   50 | 
|   20 |   40 |  100 | 
|   30 |   60 |  150 | 
+------+------+------+
3 rows in set (0.00 sec)

mysql> 
mysql> execute stmt;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|   10 |   20 |   30 | 
|   20 |   40 |   60 | 
|   30 |   60 |   90 | 
+------+------+------+
3 rows in set (0.00 sec)
-- sic - incorrect results
mysql> 
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop view v1;
Query OK, 0 rows affected (0.00 sec)

mysql> deallocate prepare stmt;
Query OK, 0 rows affected (0.00 sec)

How to repeat:
drop table if exists t1;
drop view if exists v1;
create table t1 (a int);
insert into t1 values (10), (20), (30);

create view v1 as select a, 2*a as b, 3*a as c from t1;
select * from v1;

prepare stmt from "select * from v1";
execute stmt;

drop view v1;
create view v1 as select a, 2*a as b, 5*a as c from t1;
select * from v1;

execute stmt;

drop table t1;
drop view v1;
deallocate prepare stmt;

Suggested fix:
WL#3726
[11 Apr 2008 18:22] Konstantin Osipov
Similar bug for stored procedures: Bug#33289
Similar bug for triggers: Bug#33000, Bug#33255
[11 Apr 2008 19:36] Sveta Smirnova
Thank you for the report.

Verified as described.

Workaround: deallocate statement.
[5 Feb 2010 17:48] Sveta Smirnova
Bug #50941 was marked as duplicate of this one.
[8 Sep 2010 13:13] Bernt Marius Johnsen
If query cache is enabled, the prepared statement executes correctly:

mysql> set global query_cache_size=1000000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop view if exists v1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> insert into t1 values (10), (20), (30);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> create view v1 as select a, 2*a as b, 3*a as c from t1;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|   10 |   20 |   30 | 
|   20 |   40 |   60 | 
|   30 |   60 |   90 | 
+------+------+------+
3 rows in set (0.00 sec)

mysql> 
mysql> prepare stmt from "select * from v1";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|   10 |   20 |   30 | 
|   20 |   40 |   60 | 
|   30 |   60 |   90 | 
+------+------+------+
3 rows in set (0.00 sec)

mysql> 
mysql> drop view v1;
Query OK, 0 rows affected (0.00 sec)

mysql> create view v1 as select a, 2*a as b, 5*a as c from t1;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|   10 |   20 |   50 | 
|   20 |   40 |  100 | 
|   30 |   60 |  150 | 
+------+------+------+
3 rows in set (0.00 sec)

mysql> 
mysql> execute stmt;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|   10 |   20 |   50 | 
|   20 |   40 |  100 | 
|   30 |   60 |  150 | 
+------+------+------+
3 rows in set (0.00 sec)

mysql> 
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop view v1;
Query OK, 0 rows affected (0.00 sec)

mysql> deallocate prepare stmt;
Query OK, 0 rows affected (0.00 sec)
[8 Sep 2010 13:15] Bernt Marius Johnsen
BTW: See also Bug#56468
[27 Jan 2012 4:14] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html
[27 Jan 2012 4:19] Jon Stephens
Documented fix in the 5.6.5 changelog as follows: 

        A prepared statement using a view whose definition changed
        between preparation and execution continued to use the old
        definition, which could cause the prepared statement to return
        incorrect results.

Closed.