Bug #22584 last_insert_id not updated after inserting a record through a updatable view
Submitted: 22 Sep 2006 9:12 Modified: 15 Nov 2006 18:42
Reporter: Giuseppe Maxia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.26-BK, 5.0.24 and 5.1.12 OS:Linux (Linux)
Assigned to: Tomash Brechko CPU Architecture:Any
Tags: auto_increment, LAST_INSERT_ID, Views

[22 Sep 2006 9:12] Giuseppe Maxia
Description:
When a updatable view references some fields from a table EXCEPT the auto_increment column, an insertion to such view will succeed, but the LAST_INSERT_ID id not updated.

This bug may be related to  bug #21812
(LAST_INSERT_ID not updated after insert inside a stored function)

Here is a sample run:

+-------------------+
| version()         |
+-------------------+
| 5.1.12-beta-debug |
+-------------------+

--------------
drop table if exists t1
--------------

--------------
create table t1 (id int not null auto_increment primary key, name char(10))
--------------

--------------
create or replace view v1 (t1_name) as select t1.name from t1
--------------

--------------
desc t1
--------------

+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
--------------
desc v1
--------------

+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| t1_name | char(10) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
--------------
insert into v1 (t1_name) values ('xxx')
--------------

--------------
select 'in view (1)' as op, 1 as expected, last_insert_id()
--------------

+-------------+----------+------------------+
| op          | expected | last_insert_id() |
+-------------+----------+------------------+
| in view (1) |        1 |                0 |
+-------------+----------+------------------+
--------------
select * from t1
--------------

+----+------+
| id | name |
+----+------+
|  1 | xxx  |
+----+------+
--------------
select auto_increment from information_schema.tables where table_name='t1' and table_schema='test'
--------------

+----------------+
| auto_increment |
+----------------+
|              2 |
+----------------+
--------------
insert into v1 (t1_name) values ('yyy')
--------------

--------------
select 'in view (1)' as op, 2 as expected, last_insert_id()
--------------

+-------------+----------+------------------+
| op          | expected | last_insert_id() |
+-------------+----------+------------------+
| in view (1) |        2 |                0 |
+-------------+----------+------------------+
--------------
select * from t1
--------------

+----+------+
| id | name |
+----+------+
|  1 | xxx  |
|  2 | yyy  |
+----+------+
--------------
select auto_increment from information_schema.tables where table_name='t1' and table_schema='test'
--------------

+----------------+
| auto_increment |
+----------------+
|              3 |
+----------------+

How to repeat:
select version();
create schema if not exists test;
use test;

select 'this is the bug' as `test description`
union select 'a view NOT referencing the auto_increment field'
union select 'of the underlying table';

drop table if exists t1;
create table t1 (id int not null auto_increment primary key, name char(10));
create or replace view v1 (t1_name) as select t1.name from t1 ;

desc t1;
desc v1;

insert into v1 (t1_name) values ('xxx');
select 'in view (1)' as op, 1 as expected, last_insert_id();
select * from t1;
select auto_increment from information_schema.tables where table_name='t1' and table_schema='test';
insert into v1 (t1_name) values ('yyy');
select 'in view (1)' as op, 2 as expected, last_insert_id();
select * from t1;
select auto_increment from information_schema.tables where table_name='t1' and table_schema='test';

select 'this works fine' as `test description`
union select 'a view referencing the auto_increment field '
union select 'of the underlying table';;
drop table if exists t1;
create table t1 (id int not null auto_increment primary key, name char(10));
create or replace view v1 (t1_id, t1_name) as select t1.id, t1.name from t1 ;

desc t1;
desc v1;
insert into v1 (t1_name) values ('xxx');
select 'in view (2)' as op, 1 as expected, last_insert_id();
select * from t1;
select auto_increment from information_schema.tables where table_name='t1' and table_schema='test';
insert into v1 (t1_name) values ('yyy');
select 'in view (2)' as op, 2 as expected, last_insert_id();
select * from t1;
select auto_increment from information_schema.tables where table_name='t1' and table_schema='test';

select 'this also works fine' as `test description`
union select 'inserting data directly to a table';

drop table if exists t1;
create table t1 (id int not null auto_increment primary key, name char(10));
insert into t1 (name) values ('xxx');
select 'in table ' as op, 1 as expected, last_insert_id();
select auto_increment from information_schema.tables where table_name='t1' and table_schema='test';
select * from t1;
insert into t1 (name) values ('yyy');
select 'in table ' as op, 2 as expected, last_insert_id();
select * from t1;
select auto_increment from information_schema.tables where table_name='t1' and table_schema='test';

Suggested fix:
As a workaround, every updatable view must include the auto_increment column
[22 Sep 2006 10:17] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.26-BK on Linux. May be also related to bug #21726.
[20 Oct 2006 14:33] 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/commits/14073

ChangeSet@1.2353, 2006-10-20 18:31:08+04:00, kroki@moonlight.intranet +3 -0
  BUG#22584: last_insert_id not updated after inserting a record through
  a updatable view.
  
  When there's a VIEW on a base table that have AUTO_INCREMENT column, and
  this VIEW doesn't provide an access such column, after INSERT to such
  VIEW LAST_INSERT_ID() did not return the value just generated.
  
  This behaviour is intended and correct, because if the VIEW doesn't list
  some columns then these columns are effectively hidden from the user,
  and so any side effects of inserting default values to them.
  
  However, there was a bug that such statement inserting into a view would
  reset LAST_INSERT_ID() instead of leaving it unchanged.
  
  This patch restores the original value of LAST_INSERT_ID() instead of
  resetting it to zero.
[26 Oct 2006 12:13] Konstantin Osipov
The patch was approved by email.
Notice that the patch does not make the value of LAST_INSERT_ID() available: rather, it makes sure it's not modified by an insert through a view.
The logic is that if you do not see a base table column in a view, you do not see any side effects caused by modification of that column through an insert into that view. This needs to be documented in the manual.
[27 Oct 2006 9: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/commits/14460

ChangeSet@1.2291, 2006-10-27 13:32:41+04:00, kroki@moonlight.intranet +3 -0
  BUG#22584: last_insert_id not updated after inserting a record through
  a updatable view.
  
  When there's a VIEW on a base table that have AUTO_INCREMENT column, and
  this VIEW doesn't provide an access such column, after INSERT to such
  VIEW LAST_INSERT_ID() did not return the value just generated.
  
  This behaviour is intended and correct, because if the VIEW doesn't list
  some columns then these columns are effectively hidden from the user,
  and so any side effects of inserting default values to them.
  
  However, there was a bug that such statement inserting into a view would
  reset LAST_INSERT_ID() instead of leaving it unchanged.
  
  This patch restores the original value of LAST_INSERT_ID() instead of
  resetting it to zero.
[27 Oct 2006 12:15] Tomash Brechko
Queued to 5.0-runtime and 5.1-runtime.
[2 Nov 2006 17:43] Dmitry Lenev
Fix was pushed into 5.0.29 and 5.1.13
[15 Nov 2006 18:42] Paul DuBois
Noted in 5.0.30 (not 5.0.29), 5.1.13 changelogs.

If a table contains an AUTO_INCREMENT column, inserting into an
insertable view on the table that does not include the AUTO_INCREMENT
column should not change the value of LAST_INSERT_ID(), because the
side effects of inserting default values into columns not part of the
view should not be visible. MySQL was incorrectly setting
LAST_INSERT_ID() to zero.

Also pointed this out in the CREATE VIEW section.