Bug #6032 Views: should not be updatable for tables in subqueries
Submitted: 11 Oct 2004 15:44 Modified: 8 Nov 2004 9:57
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: Oleksandr Byelkin CPU Architecture:Any

[11 Oct 2004 15:44] Peter Gulutzan 
Description:
If I create a view of a table t1, and the view's SELECT contains a subquery which refers 
to t1, then the view should not be updatable. MySQL would not be able to handle the 
SELECT's WHERE, which could be false before the update but true after the update. 

How to repeat:
mysql> create table t (s1 smallint); 
Query OK, 0 rows affected (0.29 sec) 
 
mysql> create view v as select * from t 
           where 20 < (select (s1) from t) with check option; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> insert into v values (30); 
ERROR 1369 (HY000): CHECK OPTION failed 'db11.v'
[11 Oct 2004 16:01] MySQL Verification Team 
Verified on latest BK source tree.
[25 Oct 2004 14:38] Oleksandr Byelkin 
ChangeSet 
  1.1674 04/10/25 17:32:28 bell@sanja.is.com.ua +10 -0 
  fixed detection of updating table on which we select (BUG#6032)
[8 Nov 2004 9:57] Oleksandr Byelkin 
Thank you for bug report! Bug is fixed patch is pushed into source repository.