MySQL Bugs: #23464: A view with an ORDER BY clause becomes non-updatable

Bug #23464	A view with an ORDER BY clause becomes non-updatable
Submitted:	19 Oct 2006 14:02	Modified:	9 Feb 2007 20:20
Reporter:	Giuseppe Maxia	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.27-BK, 5.0.26 and 5.1.12	OS:	Any (all)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	updatable, Views

Description:
A view that should be updatabale according to all the requirements listed in the manual becomes non updatable if it includes a ORDER BY clause.

If the documentation is right, then it's a bug. If this is expected behavior, then the documentation is incomplete. 

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop view if exists v1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop view if exists v2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (i int);
Query OK, 0 rows affected (0.32 sec)

mysql> create view v1 as select i from t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create view v2 as select i from t1 order by i;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into v1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into v2 values (2);
ERROR 1288 (HY000): The target table v2 of the INSERT is not updatable

How to repeat:
drop table if exists t1;
drop view if exists v1;
drop view if exists v2;
create table t1 (i int);
create view v1 as select i from t1;
create view v2 as select i from t1 order by i;
insert into v1 values (1); # success
insert into v2 values (2); # failure

Suggested fix:
If the documentation is right, then it's a bug. If this is expected behavior, then the documentation is incomplete.

Verified as described on 5.1.13-beta

expected behavior IMHO

this is due to the following limitation: 

* "algorithm=temptable"

an updatable view reqires that the view can be resolved using the MERGE algorithm, the v2 only works with TEMPTABLE though (due to the ORDER BY):

mysql> drop view if exists v2;
Query OK, 0 rows affected (0.00 sec)

mysql> create algorithm=merge view v2 as select i from t1 order by i;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------+
| Level   | Code | Message                                                                       |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1354 | View merge algorithm can't be used here for now (assumed undefined algorithm) | 
+---------+------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop view if exists v2;
Query OK, 0 rows affected (0.00 sec)

mysql> create algorithm=temptable view v2 as select i from t1 order by i;
Query OK, 0 rows affected (0.01 sec)

mysql> show warnings;
Empty set (0.00 sec)

Duplicate of bug#12122.