MySQL Bugs: #36002: Prepared statements: if a view used in a statement is replaced, bad data

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

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

Similar bug for stored procedures: Bug#33289
Similar bug for triggers: Bug#33000, Bug#33255

Thank you for the report.

Verified as described.

Workaround: deallocate statement.

Bug #50941 was marked as duplicate of this one.

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)

BTW: See also Bug#56468

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

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.