| Bug #9863 | Views:Even if updatable_views_with_limit is set, update to view does not work. | ||
|---|---|---|---|
| Submitted: | 13 Apr 2005 8:23 | Modified: | 17 Apr 2005 18:07 |
| Reporter: | Disha | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.3 | OS: | Any (All) |
| Assigned to: | CPU Architecture: | Any | |
[13 Apr 2005 21:54]
Jorge del Conde
Tested w/5.0.4 from bk
[17 Apr 2005 13:57]
Oleksandr Byelkin
MySQL do not support views with LIMIT clause for update, because MERGE algorithm can't be aplicable in this case in general. Here is example: create view v1 as select * from t1 limit 23; select * from v1,t1 where t1.a=v1.a limit 2; i.e. LIMIT of subquery should be applied first (there are mistake in our documentation that such operation lead to unclown results, we will fix it soon). Above operaion can be allowed in theory but it is feature request.
[17 Apr 2005 18:07]
Sergei Golubchik
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: updatable_views_with_limit does not allow updates to views with limit. It only allows to update a view (that does not contain a primary key in the underlying table) if UPDATE statement itself contains a LIMIT clause, not if a view contains LIMIT clause. As written in the manual: `updatable_views_with_limit' This variable controls whether updates can be made using a view that does not contain a primary key in the underlying table, if the update contains a `LIMIT' clause.
Description: System variable updatable_views_with_limit is used to allow or disallow updates to views with limits. Even if is set to YES, update to view is blocked. In the Repro steps, find 2 views. v1 has limit set and v2 does not. However v2 is updated with limit set during the update statement. Regardless of the updatable_views_with_limit is set or not, update to v1 is blocked and update to v2 is not. How to repeat: 1. Set set updatable_views_with_limit=YES// 2. drop table if exists t1// drop table if exists t2// create table t1(f1 char, f3 char ascii)// create table t2(f1 char, f3 char ascii)// 3. insert into t1 values('a','b'),('c','d'),('e','f')// insert into t2 values('g','h'),('i','j'),('k','l')// 4. drop view if exists v1// create view v1 as select * from t1 limit 1,1// drop view if exists v2// create view v2 as select * from t2// 5. Update v2 set f1='z' limit 1// 6. Update v1 set f1='y'// ACTUAL RESULT: After step 6, following error is observed. ERROR 1288 (HY000): The target table v1 of the UPDATE is not updatable EXPECTED RESULT: Update should be allowed.