MySQL Bugs: #30020: Wrong IS_UPDATABLE value in I_S.VIEWS for a non updatable view (regression)

Bug #30020	Wrong IS_UPDATABLE value in I_S.VIEWS for a non updatable view (regression)
Submitted:	24 Jul 2007 23:02	Modified:	3 Aug 2007 15:52
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.0.46	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
A view that is based on distinct values of one table can not be updatable and it isn't. However the INFORMATION_SCHEMA.VIEWS table shows as if it is updatable.

See the following:

omer@linux:~/ent-5.0.46-com/mysql-test> ../bin/mysql --user=root --port=9306 --protocol=tcp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.46-enterprise-log MySQL Enterprise Server (Commercial)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database db_test;
Query OK, 1 row affected (0.01 sec)

mysql> use db_test
Database changed

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

mysql> insert into t_data values (1), (2), (1), (3), (2), (4);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t_data;
+------+
| i    |
+------+
|    1 |
|    2 |
|    1 |
|    3 |
|    2 |
|    4 |
+------+
6 rows in set (0.00 sec)

mysql> create view distinct_view as select distinct i from t_data;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from distinct_view;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

# Error os expected
mysql> insert into distinct_view values (99);
ERROR 1471 (HY000): The target table distinct_view of the INSERT is not insertable-into

# Error is expected
mysql> update distinct_view set i=7 where i=1;
ERROR 1288 (HY000): The target table distinct_view of the UPDATE is not updatable

# Should show the value as 'NO'
mysql> select table_name, is_updatable from information_schema.views where table_schema='db_test';
+---------------+--------------+
| table_name    | is_updatable |
+---------------+--------------+
| distinct_view | YES          |  <-- This is wromg
+---------------+--------------+
1 row in set (0.00 sec)

How to repeat:
Run the following from the mysql client:

create database db_test;
use db_test;
create table t_data (i int);
insert into t_data values (1), (2), (1), (3), (2), (4);
create view distinct_view as select distinct i from t_data;
select * from distinct_view;
insert into distinct_view values (99);  
update distinct_view set i=7 where i=1; 
select table_name, is_updatable 
   from information_schema.views 
   where table_schema='db_test';

Suggested fix:
The IS_UPDATABLE should have the valuse of 'NO'

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/commits/31763

ChangeSet@1.2483, 2007-07-28 16:02:29+04:00, evgen@moonbone.local +3 -0
  Bug#30020: Insufficient check led to a wrong info provided by the
  information schema table.
  
  The get_schema_views_record() function fills records in the view table of
  the informations schema with data about given views. Among other info
  the is_updatable flag is set. But the check whether the view is updatable or
  not wasn't covering all cases thus sometimes providing wrong info.
  This might led to a user confusion.
  
  Now the get_schema_views_record function additionally calls to the 
  view->can_be_merge() function to find out whether the view can be updated or
  not.

Pushed into 5.1.21-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.21 changelogs.

The IS_UPDATABLE column in the INFORMATION_SCHEMA.VIEWS table was not
always set correctly.