Bug #6120 Stored procedure unaffected by ALTER VIEW
Submitted: 15 Oct 2004 19:25 Modified: 26 Jul 2005 20:21
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.2-alpha-debug OS:Linux (SuSE 8.2)
Assigned to: Igor Babaev CPU Architecture:Any

[15 Oct 2004 19:25] Peter Gulutzan
Description:
I have a procedure that depends on a view. I alter the view. I call the procedure. It's using 
the old definition of the view, as it was before the alter. Only after I do a data-change 
operation on the view, does it get through to the stored procedure that the view is 
different. 
 

How to repeat:
mysql> create table t (s1 int, s2 int)// 
Query OK, 0 rows affected (0.42 sec) 
 
mysql> insert into t values (1,2)// 
Query OK, 1 row affected (0.00 sec) 
 
mysql> create view v as select s2 as s1, s1 as s2 from t// 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> select * from v// 
+------+------+ 
| s1   | s2   | 
+------+------+ 
|    2 |    1 | 
+------+------+ 
1 row in set (0.00 sec) 
 
mysql> create procedure p () select * from v// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p()// 
+------+------+ 
| s1   | s2   | 
+------+------+ 
|    2 |    1 | 
+------+------+ 
1 row in set (0.00 sec) 
 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> alter view v as select s1 as s1, s2 as s2 from t// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p()// 
+------+------+ 
| s1   | s2   | 
+------+------+ 
|    2 |    1 | 
+------+------+ 
1 row in set (0.00 sec) 
 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into v values (1,2); 
    -> // 
Query OK, 1 row affected (0.00 sec) 
 
mysql> call p()// 
+------+------+ 
| s1   | s2   | 
+------+------+ 
|    1 |    2 | 
|    1 |    2 | 
+------+------+ 
2 rows in set (0.00 sec) 
 
Query OK, 0 rows affected (0.00 sec) 
 
 

Suggested fix:
Don't support ALTER VIEW!
[6 Jul 2005 16:00] Bugs System
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/internals/26727
[11 Jul 2005 16:44] Oleksandr Byelkin
after adding invalidation to DROP VIEW, it is OK
[12 Jul 2005 15:35] Bugs System
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/internals/26949
[12 Jul 2005 16:44] Igor Babaev
As Sanja noticed in his review a similar problem exists for the DROP VIEW / CREATE VIEW scenario.

 ChangeSet
  1.1898 05/07/06 09:00:17 igor@igor-inspiron.creware.com +3 -0
  view.result, view.test:
    Added a test case for bug #6120.
  sql_view.cc:
    Fixed bug #6120.
    The SP cache must be invalidated when a view is altered.

ChangeSet
  1.1915 05/07/12 08:34:57 igor@igor-inspiron.creware.com +3 -0
  view.test, view.result:
    Expanded the test case for bug #6120 to cover
    DROP VIEW / CREATE VIEW scenario.
  sql_view.cc:
    Expanded the fix for bug #6120 to cover the case of
    DROP VIEW / CREATE_VIEW.

The fix will appear in 5.0.10
[26 Jul 2005 20:21] Paul Dubois
Noted in 5.0.10 changelog.