summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/view.result60
-rw-r--r--mysql-test/t/view.test58
-rw-r--r--sql/item.cc16
-rw-r--r--sql/sql_base.cc5
-rw-r--r--sql/sql_delete.cc7
-rw-r--r--sql/sql_derived.cc2
-rw-r--r--sql/sql_insert.cc19
-rw-r--r--sql/sql_load.cc2
-rw-r--r--sql/sql_prepare.cc4
-rw-r--r--sql/sql_update.cc4
-rw-r--r--sql/table.cc25
-rw-r--r--sql/table.h3
12 files changed, 183 insertions, 22 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;
diff --git a/sql/item.cc b/sql/item.cc
index 8bd5b6fe1a4..f9bbc4aeead 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -9143,18 +9143,22 @@ void Item_ref::update_used_tables()
}
-table_map Item_direct_view_ref::used_tables() const
+table_map Item_direct_view_ref::used_tables() const
{
- return get_depended_from() ?
+ return get_depended_from() ?
OUTER_REF_TABLE_BIT :
- (view->merged ? (*ref)->used_tables() : view->table->map);
+ ((view->merged || !view->table) ?
+ (*ref)->used_tables() :
+ view->table->map);
}
-table_map Item_direct_view_ref::not_null_tables() const
+table_map Item_direct_view_ref::not_null_tables() const
{
- return get_depended_from() ?
+ return get_depended_from() ?
0 :
- (view->merged ? (*ref)->not_null_tables() : view->table->map);
+ ((view->merged || !view->table) ?
+ (*ref)->not_null_tables() :
+ view->table->map);
}
/*
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 6dd329f8c77..9c5d251c728 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -7927,7 +7927,8 @@ bool setup_tables(THD *thd, Name_resolution_context *context,
while ((table_list= ti++))
{
TABLE *table= table_list->table;
- table->pos_in_table_list= table_list;
+ if (table)
+ table->pos_in_table_list= table_list;
if (first_select_table &&
table_list->top_table() == first_select_table)
{
@@ -7940,7 +7941,7 @@ bool setup_tables(THD *thd, Name_resolution_context *context,
{
table_list->jtbm_table_no= tablenr;
}
- else
+ else if (table)
{
table->pos_in_table_list= table_list;
setup_table_map(table, table_list, tablenr);
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 3ce375190a7..84e88196f20 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -68,7 +68,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
if (mysql_handle_list_of_derived(thd->lex, table_list, DT_PREPARE))
DBUG_RETURN(TRUE);
- if (!table_list->updatable)
+ if (!table_list->single_table_updatable())
{
my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "DELETE");
DBUG_RETURN(TRUE);
@@ -526,7 +526,8 @@ int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds)
setup_conds(thd, table_list, select_lex->leaf_tables, conds) ||
setup_ftfuncs(select_lex))
DBUG_RETURN(TRUE);
- if (!table_list->updatable || check_key_in_view(thd, table_list))
+ if (!table_list->single_table_updatable() ||
+ check_key_in_view(thd, table_list))
{
my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "DELETE");
DBUG_RETURN(TRUE);
@@ -622,7 +623,7 @@ int mysql_multi_delete_prepare(THD *thd)
DBUG_RETURN(TRUE);
}
- if (!target_tbl->correspondent_table->updatable ||
+ if (!target_tbl->correspondent_table->single_table_updatable() ||
check_key_in_view(thd, target_tbl->correspondent_table))
{
my_error(ER_NON_UPDATABLE_TABLE, MYF(0),
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 0955f9c0982..02a26254336 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -483,7 +483,7 @@ bool mysql_derived_merge_for_insert(THD *thd, LEX *lex, TABLE_LIST *derived)
return mysql_derived_prepare(thd, lex, derived);
if (!derived->is_multitable())
{
- if (!derived->updatable)
+ if (!derived->single_table_updatable())
return derived->create_field_translation(thd);
if (derived->merge_underlying_list)
{
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index d01a594708e..a4943d5e13a 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -103,7 +103,8 @@ static bool check_view_insertability(THD *thd, TABLE_LIST *view);
*/
bool check_view_single_update(List<Item> &fields, List<Item> *values,
- TABLE_LIST *view, table_map *map)
+ TABLE_LIST *view, table_map *map,
+ bool insert)
{
/* it is join view => we need to find the table for update */
List_iterator_fast<Item> it(fields);
@@ -140,6 +141,14 @@ bool check_view_single_update(List<Item> &fields, List<Item> *values,
*/
tbl->table->insert_values= view->table->insert_values;
view->table= tbl->table;
+ if (!tbl->single_table_updatable())
+ {
+ if (insert)
+ my_error(ER_NON_INSERTABLE_TABLE, MYF(0), view->alias, "INSERT");
+ else
+ my_error(ER_NON_UPDATABLE_TABLE, MYF(0), view->alias, "UPDATE");
+ return TRUE;
+ }
*map= tables;
return FALSE;
@@ -184,7 +193,7 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
{
TABLE *table= table_list->table;
- if (!table_list->updatable)
+ if (!table_list->single_table_updatable())
{
my_error(ER_NON_INSERTABLE_TABLE, MYF(0), table_list->alias, "INSERT");
return -1;
@@ -260,7 +269,7 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
if (check_view_single_update(fields,
fields_and_values_from_different_maps ?
(List<Item>*) 0 : &values,
- table_list, map))
+ table_list, map, true))
return -1;
table= table_list->table;
}
@@ -347,7 +356,7 @@ static int check_update_fields(THD *thd, TABLE_LIST *insert_table_list,
if (insert_table_list->is_view() &&
insert_table_list->is_merged_derived() &&
check_view_single_update(update_fields, &update_values,
- insert_table_list, map))
+ insert_table_list, map, false))
return -1;
if (table->timestamp_field)
@@ -1159,7 +1168,7 @@ static bool mysql_prepare_insert_check_table(THD *thd, TABLE_LIST *table_list,
bool insert_into_view= (table_list->view != 0);
DBUG_ENTER("mysql_prepare_insert_check_table");
- if (!table_list->updatable)
+ if (!table_list->single_table_updatable())
{
my_error(ER_NON_INSERTABLE_TABLE, MYF(0), table_list->alias, "INSERT");
DBUG_RETURN(TRUE);
diff --git a/sql/sql_load.cc b/sql/sql_load.cc
index 42a8f001c6f..7fcb52a7b9c 100644
--- a/sql/sql_load.cc
+++ b/sql/sql_load.cc
@@ -187,7 +187,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
INSERT_ACL | UPDATE_ACL, FALSE))
DBUG_RETURN(-1);
if (!table_list->table || // do not suport join view
- !table_list->updatable || // and derived tables
+ !table_list->single_table_updatable() || // and derived tables
check_key_in_view(thd, table_list))
{
my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "LOAD");
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 611c8ddf944..32ce8dc6fc4 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1274,7 +1274,7 @@ static int mysql_test_update(Prepared_statement *stmt,
if (table_list->handle_derived(thd->lex, DT_PREPARE))
goto error;
- if (!table_list->updatable)
+ if (!table_list->single_table_updatable())
{
my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "UPDATE");
goto error;
@@ -1348,7 +1348,7 @@ static bool mysql_test_delete(Prepared_statement *stmt,
if (mysql_handle_derived(thd->lex, DT_PREPARE))
goto error;
- if (!table_list->updatable)
+ if (!table_list->single_table_updatable())
{
my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "DELETE");
goto error;
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 80eb823c346..a921a87884e 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -257,7 +257,7 @@ int mysql_update(THD *thd,
thd_proc_info(thd, "init");
table= table_list->table;
- if (!table_list->updatable)
+ if (!table_list->single_table_updatable())
{
my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "UPDATE");
DBUG_RETURN(1);
@@ -1090,7 +1090,7 @@ reopen_tables:
/* if table will be updated then check that it is unique */
if (table->map & tables_for_update)
{
- if (!tl->updatable || check_key_in_view(thd, tl))
+ if (!tl->single_table_updatable() || check_key_in_view(thd, tl))
{
my_error(ER_NON_UPDATABLE_TABLE, MYF(0), tl->alias, "UPDATE");
DBUG_RETURN(TRUE);
diff --git a/sql/table.cc b/sql/table.cc
index 5fe2d8afc42..f1ff353a8c4 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -3712,6 +3712,28 @@ bool TABLE_LIST::prep_where(THD *thd, Item **conds,
DBUG_RETURN(FALSE);
}
+/**
+ Check that table/view is updatable and if it has single
+ underlying tables/views it is also updatable
+
+ @return Result of the check.
+*/
+
+bool TABLE_LIST::single_table_updatable()
+{
+ if (!updatable)
+ return false;
+ if (view_tables && view_tables->elements == 1)
+ {
+ /*
+ We need to check deeply only single table views. Multi-table views
+ will be turned to multi-table updates and then checked by leaf tables
+ */
+ return view_tables->head()->single_table_updatable();
+ }
+ return true;
+}
+
/*
Merge ON expressions for a view
@@ -5188,6 +5210,9 @@ void st_table::mark_virtual_columns_for_write(bool insert_fl)
if (!vfield)
return;
+ if (!vfield)
+ return;
+
for (vfield_ptr= vfield; *vfield_ptr; vfield_ptr++)
{
tmp_vfield= *vfield_ptr;
diff --git a/sql/table.h b/sql/table.h
index 376aa9824dc..09230e40f7a 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1797,6 +1797,9 @@ struct TABLE_LIST
int fetch_number_of_rows();
bool change_refs_to_fields();
+
+ bool single_table_updatable();
+
private:
bool prep_check_option(THD *thd, uint8 check_opt_type);
bool prep_where(THD *thd, Item **conds, bool no_where_clause);
View Lorry Controller Status