Moving LP BUG#794005 to 5.3 + fixing INSERT of multi-table view.

2 files changed, 118 insertions, 0 deletions

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index d302df2f029..15ef0c088b1 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -3930,6 +3930,63 @@ drop table t1,t2;
 # -- End of 5.1 tests.
 # -----------------------------------------------------------------
 #
+# Bug #794005: crash in st_table::mark_virtual_columns_for_write
+#
+CREATE TABLE t1 (a int);
+insert into t1 values (1);
+CREATE TABLE t2 (a int);
+insert into t2 values (1);
+CREATE VIEW v2 AS SELECT * FROM t2;
+CREATE VIEW v1 AS SELECT * FROM v2;
+CREATE VIEW v3 AS SELECT t2.a,v1.a as b FROM t2,v1 where t2.a=v1.a;
+CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v2 AS SELECT * FROM t1;
+UPDATE v1 SET a = 10;
+ERROR HY000: The target table v1 of the UPDATE is not updatable
+REPLACE v1 SET a = 10;
+ERROR HY000: The target table v1 of the INSERT is not insertable-into
+INSERT into v1 values (20);
+ERROR HY000: The target table v1 of the INSERT is not insertable-into
+DELETE from v1;
+ERROR HY000: The target table v1 of the DELETE is not updatable
+UPDATE v3 SET b= 10;
+ERROR HY000: The target table v2 of the UPDATE is not updatable
+REPLACE v3 SET b= 10;
+ERROR HY000: The target table v3 of the INSERT is not insertable-into
+INSERT into v3(b) values (20);
+ERROR HY000: The target table v3 of the INSERT is not insertable-into
+DELETE from v3 where b=20;
+ERROR HY000: Can not delete from join view 'test.v3'
+DELETE from v3 where a=20;
+ERROR HY000: Can not delete from join view 'test.v3'
+DELETE v1 from v1,t1 where v1.a=t1.a;
+ERROR HY000: The target table v1 of the DELETE is not updatable
+UPDATE v3 SET a = 10;
+REPLACE v3 SET a = 11;
+INSERT INTO v3(a) values (20);
+select * from t1;
+a
+1
+select * from t2;
+a
+10
+11
+20
+CREATE OR REPLACE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM t2;
+DELETE from v1 where a=11;
+DELETE v1 from v1,t1 where v1.a=t1.a;
+select * from t1;
+a
+1
+select * from t2;
+a
+10
+20
+DROP VIEW v1,v2,v3;
+DROP TABLE t1,t2;
+# -----------------------------------------------------------------
+# -- End of 5.2 tests.
+# -----------------------------------------------------------------
+#
 # Bug #59696 Optimizer does not use equalities for conditions over view 
 #
 CREATE TABLE t1 (a int NOT NULL);
@@ -4376,4 +4433,7 @@ NULL	NULL	1	0
 NULL	NULL	1	0
 DROP VIEW v2;
 DROP TABLE t1, t2, t3;
+# -----------------------------------------------------------------
+# -- End of 5.3 tests.
+# -----------------------------------------------------------------
 SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index d4d5f2acc0b..2a9bfd89f3b 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -3981,6 +3981,60 @@ drop table t1,t2;
 --echo # -- End of 5.1 tests.
 --echo # -----------------------------------------------------------------
 --echo #
+--echo # Bug #794005: crash in st_table::mark_virtual_columns_for_write
+--echo #
+
+CREATE TABLE t1 (a int);
+insert into t1 values (1);
+CREATE TABLE t2 (a int);
+insert into t2 values (1);
+
+CREATE VIEW v2 AS SELECT * FROM t2;
+CREATE VIEW v1 AS SELECT * FROM v2;
+CREATE VIEW v3 AS SELECT t2.a,v1.a as b FROM t2,v1 where t2.a=v1.a;
+CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v2 AS SELECT * FROM t1;
+
+--error ER_NON_UPDATABLE_TABLE
+UPDATE v1 SET a = 10;
+--error ER_NON_INSERTABLE_TABLE
+REPLACE v1 SET a = 10;
+--error ER_NON_INSERTABLE_TABLE
+INSERT into v1 values (20);
+--error ER_NON_UPDATABLE_TABLE
+DELETE from v1;
+--error ER_NON_UPDATABLE_TABLE
+UPDATE v3 SET b= 10;
+--error ER_NON_INSERTABLE_TABLE
+REPLACE v3 SET b= 10;
+--error ER_NON_INSERTABLE_TABLE
+INSERT into v3(b) values (20);
+--error ER_VIEW_DELETE_MERGE_VIEW
+DELETE from v3 where b=20;
+--error ER_VIEW_DELETE_MERGE_VIEW
+DELETE from v3 where a=20;
+--error ER_NON_UPDATABLE_TABLE
+DELETE v1 from v1,t1 where v1.a=t1.a;
+UPDATE v3 SET a = 10;
+REPLACE v3 SET a = 11;
+INSERT INTO v3(a) values (20);
+
+select * from t1;
+select * from t2;
+
+CREATE OR REPLACE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM t2;
+DELETE from v1 where a=11;
+DELETE v1 from v1,t1 where v1.a=t1.a;
+select * from t1;
+select * from t2;
+
+DROP VIEW v1,v2,v3;
+DROP TABLE t1,t2;
+
+--echo # -----------------------------------------------------------------
+--echo # -- End of 5.2 tests.
+--echo # -----------------------------------------------------------------
+
+--echo #
 --echo # Bug #59696 Optimizer does not use equalities for conditions over view 
 --echo #
 
@@ -4311,4 +4365,8 @@ SELECT * FROM t1 RIGHT JOIN v2 ON ( v2.a = t1.a ) WHERE v2.b IN ( SELECT b FROM
 DROP VIEW v2;
 DROP TABLE t1, t2, t3;
 
+--echo # -----------------------------------------------------------------
+--echo # -- End of 5.3 tests.
+--echo # -----------------------------------------------------------------
+
 SET optimizer_switch=@save_optimizer_switch;