bk commit into 5.1 tree (kroki:1.2353) BUG#22584

[kroki]

Below is the list of changes that have just been committed into a local
5.1 repository of tomash. When tomash does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-10-20 18:31:08+04:00, kroki@stripped +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.

  mysql-test/r/view.result@stripped, 2006-10-20 18:31:03+04:00, kroki@stripped +34
-0
    Add result for bug#22584: last_insert_id not updated after inserting
    a record through a updatable view.

  mysql-test/t/view.test@stripped, 2006-10-20 18:31:03+04:00, kroki@stripped +38 -0
    Add test case for bug#22584: last_insert_id not updated after inserting
    a record through a updatable view.

  sql/sql_parse.cc@stripped, 2006-10-20 18:31:03+04:00, kroki@stripped +20 -4
    When we have inserted into a view, and AUTO_INCREMENT column is not
    accessed from this view, instead of setting LAST_INSERT_ID to zero set
    it to the value it had before this statement was executed.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	kroki
# Host:	moonlight.intranet
# Root:	/home/tomash/src/mysql_ab/mysql-5.1-bug22584

--- 1.587/sql/sql_parse.cc	2006-10-20 18:31:17 +04:00
+++ 1.588/sql/sql_parse.cc	2006-10-20 18:31:17 +04:00
@@ -3394,9 +3394,17 @@ end_with_restore_list:
     res= mysql_insert(thd, all_tables, lex->field_list, lex->many_values,
 		      lex->update_list, lex->value_list,
                       lex->duplicates, lex->ignore);
-    /* do not show last insert ID if VIEW does not have auto_inc */
+
+    /*
+      If we have inserted into a VIEW, and the base table has
+      AUTO_INCREMENT column, but this column is not accessible through
+      a view, then we should restore LAST_INSERT_ID to the value it
+      had before the statement.
+    */
     if (first_table->view && !first_table->contain_auto_increment)
-      thd->first_successful_insert_id_in_cur_stmt= 0;
+      thd->first_successful_insert_id_in_cur_stmt= 
+        thd->first_successful_insert_id_in_prev_stmt;
+
     break;
   }
   case SQLCOM_REPLACE_SELECT:
@@ -3456,9 +3464,17 @@ end_with_restore_list:
       /* revert changes for SP */
       select_lex->table_list.first= (byte*) first_table;
     }
-    /* do not show last insert ID if VIEW does not have auto_inc */
+
+    /*
+      If we have inserted into a VIEW, and the base table has
+      AUTO_INCREMENT column, but this column is not accessible through
+      a view, then we should restore LAST_INSERT_ID to the value it
+      had before the statement.
+    */
     if (first_table->view && !first_table->contain_auto_increment)
-      thd->first_successful_insert_id_in_cur_stmt= 0;
+      thd->first_successful_insert_id_in_cur_stmt= 
+        thd->first_successful_insert_id_in_prev_stmt;
+
     break;
   }
   case SQLCOM_TRUNCATE:

--- 1.186/mysql-test/r/view.result	2006-10-20 18:31:17 +04:00
+++ 1.187/mysql-test/r/view.result	2006-10-20 18:31:17 +04:00
@@ -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.

--- 1.168/mysql-test/t/view.test	2006-10-20 18:31:17 +04:00
+++ 1.169/mysql-test/t/view.test	2006-10-20 18:31:17 +04:00
@@ -2911,4 +2911,42 @@ DROP VIEW v1;
 DROP TABLE t1;
 
 
+#
+# BUG#22584: last_insert_id not updated after inserting a record
+# through a updatable view
+#
+# We still do not update LAST_INSERT_ID if AUTO_INCREMENT column is
+# not accessible through a view.  However, we do not reset the value
+# of LAST_INSERT_ID, but keep it unchanged.
+#
+--disable_warnings
+DROP VIEW IF EXISTS v1, v2;
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+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();
+
+INSERT INTO v1 (j) VALUES (2);
+--echo # LAST_INSERT_ID() should not change.
+SELECT LAST_INSERT_ID();
+
+INSERT INTO v2 (j) VALUES (3);
+--echo # LAST_INSERT_ID() should be updated.
+SELECT LAST_INSERT_ID();
+
+INSERT INTO v1 (j) SELECT j FROM t1;
+--echo # LAST_INSERT_ID() should not change.
+SELECT LAST_INSERT_ID();
+
+SELECT * FROM t1;
+
+DROP VIEW v1, v2;
+DROP TABLE t1;
+
+
 --echo End of 5.0 tests.