MySQL Bugs: #50941: Changes in view are not reflected in prepared statements

Bug #50941	Changes in view are not reflected in prepared statements
Submitted:	5 Feb 2010 12:00	Modified:	5 Feb 2010 17:48
Reporter:	Roy Lyseng	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	5.1,6.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If a query against a is prepared, and the view is changed between two executions, the updated view definition is not reflected in subsequent executions of the prepared query.

Two test cases are provided, one that change an expression in the SELECT list of the view and one that change the WHERE clause of the view. Neither of the updates are reflected in the execution.

How to repeat:
CREATE TABLE t1(a INT PRIMARY KEY, b INT);
INSERT INTO t1 VALUES(1,10),(2,20);
CREATE VIEW v1 AS SELECT a AS va FROM t1;
PREPARE stmt FROM "
SELECT va FROM v1;
";
EXECUTE stmt;
DROP VIEW v1;
CREATE VIEW v1 AS SELECT a+b AS va FROM t1;
EXECUTE stmt;

PREPARE stmt FROM "
SELECT va FROM v1;
";
EXECUTE stmt;
DROP VIEW v1;
CREATE VIEW v1 AS SELECT a+b AS va FROM t1 WHERE b<20;
EXECUTE stmt;

Thank you for the bug report. Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.43-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t1(a INT PRIMARY KEY, b INT);
Query OK, 0 rows affected (0.92 sec)

mysql> INSERT INTO t1 VALUES(1,10),(2,20);
Query OK, 2 rows affected (0.19 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW v1 AS SELECT a AS va FROM t1;
Query OK, 0 rows affected (0.13 sec)

mysql> PREPARE stmt FROM "
    "> SELECT va FROM v1;
    "> ";
Query OK, 0 rows affected (0.09 sec)
Statement prepared

mysql> EXECUTE stmt;
+----+
| va |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.03 sec)

mysql> DROP VIEW v1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v1 AS SELECT a+b AS va FROM t1;
Query OK, 0 rows affected (0.09 sec)

mysql> EXECUTE stmt;
+----+
| va |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> PREPARE stmt FROM "
    "> SELECT va FROM v1;
    "> ";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
+----+
| va |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> DROP VIEW v1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v1 AS SELECT a+b AS va FROM t1 WHERE b<20;
Query OK, 0 rows affected (0.03 sec)

mysql> EXECUTE stmt;
+------+
| va   |
+------+
|   11 |
|   22 |
+------+
2 rows in set (0.00 sec)

Reprepare does NOT happen:

mysql> show session status like 'Com_stmt_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Com_stmt_close          | 1     |
| Com_stmt_execute        | 4     |
| Com_stmt_fetch          | 0     |
| Com_stmt_prepare        | 2     |
| Com_stmt_reprepare      | 0     |
| Com_stmt_reset          | 0     |
| Com_stmt_send_long_data | 0     |
+-------------------------+-------+
7 rows in set (0.05 sec)

mysql> DROP VIEW v1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v1 AS SELECT a+b AS va FROM t1;
Query OK, 0 rows affected (0.03 sec)

mysql> EXECUTE stmt;
+------+
| va   |
+------+
|   11 |
|   22 |
+------+
2 rows in set (0.00 sec)

mysql> show session status like 'Com_stmt_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Com_stmt_close          | 1     |
| Com_stmt_execute        | 5     |
| Com_stmt_fetch          | 0     |
| Com_stmt_prepare        | 2     |
| Com_stmt_reprepare      | 0     |
| Com_stmt_reset          | 0     |
| Com_stmt_send_long_data | 0     |
+-------------------------+-------+
7 rows in set (0.00 sec)

Valeriy, it's a duplicate of a well-known bug -- could you please at least mention that?

Duplicate of bug #36002