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.

1 files changed, 34 insertions, 0 deletions

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 13921dc88d7..198233adf71 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2970,4 +2970,38 @@ UPDATE t1 SET i= f1();
 DROP FUNCTION f1;
 DROP VIEW v1;
 DROP TABLE t1;
+DROP VIEW IF EXISTS v1, v2;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT);
+CREATE VIEW v1 AS SELECT j FROM t1;
+CREATE VIEW v2 AS SELECT * FROM t1;
+INSERT INTO t1 (j) VALUES (1);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+1
+INSERT INTO v1 (j) VALUES (2);
+# LAST_INSERT_ID() should not change.
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+1
+INSERT INTO v2 (j) VALUES (3);
+# LAST_INSERT_ID() should be updated.
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+3
+INSERT INTO v1 (j) SELECT j FROM t1;
+# LAST_INSERT_ID() should not change.
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+3
+SELECT * FROM t1;
+i	j
+1	1
+2	2
+3	3
+4	1
+5	2
+6	3
+DROP VIEW v1, v2;
+DROP TABLE t1;
 End of 5.0 tests.