diff options
author | Aleksey Midenkov <midenok@gmail.com> | 2019-12-02 11:48:37 +0300 |
---|---|---|
committer | Aleksey Midenkov <midenok@gmail.com> | 2019-12-02 11:48:37 +0300 |
commit | 6dd41e008eb2e384913d970e79aa01cd886891ec (patch) | |
tree | 5767ac99dab47612131b29985efd48b903c55dd3 | |
parent | 97aa07abf544870faa0956784f33f158b092b2e5 (diff) | |
download | mariadb-git-6dd41e008eb2e384913d970e79aa01cd886891ec.tar.gz |
MDEV-21155 Assertion with versioned table upon DELETE from view of view after replacing first view
When view is merged by DT_MERGE_FOR_INSERT it is then skipped from
processing and doesn't update WHERE clause with
vers_setup_conds(). Note that view itself cannot work in
vers_setup_conds() because it doesn't have row_start, row_end
fields. Thus it is required to descend down to material TABLE_LIST
through calls of mysql_derived_prepare() and run vers_setup_conds()
from there. Luckily, all views (views of views, views of views of
views, etc.) are linked in one list through next_global pointer, so we
can skip all views of views and get straight to non-view TABLE_LIST by
checking its merge_underlying_list property for zero value (it is
assigned by DT_MERGE_FOR_INSERT for merged derived tables).
We have to do that only for UPDATE and DELETE. Other DML commands
don't use WHERE clause.
MDEV-21146 Assertion `m_lock_type == 2' in handler::ha_drop_table upon LOAD DATA
LOAD DATA does not use WHERE and the above call of vers_setup_conds()
is not needed. unit->prepare() led to wrongly locked temporary table.
-rw-r--r-- | mysql-test/suite/versioning/r/view.result | 66 | ||||
-rw-r--r-- | mysql-test/suite/versioning/t/view.test | 58 | ||||
-rw-r--r-- | sql/sql_derived.cc | 23 | ||||
-rw-r--r-- | sql/table.h | 2 |
4 files changed, 143 insertions, 6 deletions
diff --git a/mysql-test/suite/versioning/r/view.result b/mysql-test/suite/versioning/r/view.result index 8b23e87d6a4..1cb68ea5b93 100644 --- a/mysql-test/suite/versioning/r/view.result +++ b/mysql-test/suite/versioning/r/view.result @@ -160,6 +160,7 @@ period for system_time (row_start, row_end) ) with system versioning; insert into t1 values (1), (2); create or replace view v1 as select * from t1 where x > 1; +# update, delete update v1 set x= x + 1; select *, check_row(row_start, row_end) from t1 for system_time all order by x; x check_row(row_start, row_end) @@ -211,5 +212,70 @@ x check_row(row_start, row_end) 1 CURRENT ROW 2 HISTORICAL ROW 3 HISTORICAL ROW +# replace +create or replace table t1 ( +x int primary key, y int, +row_start SYS_DATATYPE as row start invisible, +row_end SYS_DATATYPE as row end invisible, +period for system_time (row_start, row_end) +) with system versioning; +insert into t1 values (1, 0), (2, 0); +create or replace view v1 as select * from t1 where x > 1; +replace v1 values (1, 1); +replace v1 values (2, 1); +replace v1 values (3, 1); +# REPLACE ignores VIEW condition because itself doesn't use WHERE +select *, check_row(row_start, row_end) from t1 for system_time all order by x, row_end; +x y check_row(row_start, row_end) +1 0 HISTORICAL ROW +1 1 CURRENT ROW +2 0 HISTORICAL ROW +2 1 CURRENT ROW +3 1 CURRENT ROW +# insert-select, on duplicate key +insert v1 select * from t1 where x = 1 on duplicate key update x = v1.x - 1; +select *, check_row(row_start, row_end) from t1 for system_time all order by x, row_end; +x y check_row(row_start, row_end) +0 1 CURRENT ROW +1 0 HISTORICAL ROW +1 1 HISTORICAL ROW +2 0 HISTORICAL ROW +2 1 CURRENT ROW +3 1 CURRENT ROW drop view v1, v2; drop tables t1, t2; +# +# MDEV-21146 Assertion `m_lock_type == 2' in handler::ha_drop_table upon LOAD DATA +# +create table t1 (a int); +create view v1 as select * from t1; +create or replace table t1 (b int) with system versioning; +load data infile 'xx' into table v1; +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them +drop view v1; +drop table t1; +# +# MDEV-21155 Assertion with versioned table upon DELETE from view of view after replacing first view +# +create table t1 (a int); +insert into t1 values (1); +create table t2 ( +b int, +row_start SYS_DATATYPE as row start invisible, +row_end SYS_DATATYPE as row end invisible, +period for system_time (row_start, row_end) +) with system versioning; +insert into t2 values (2); +create view v1 as select * from t1; +create view v2 as select * from v1; +create or replace view v1 as select * from t2; +delete from v2; +select * from t1; +a +1 +select *, check_row(row_start, row_end) from t2 for system_time all; +b check_row(row_start, row_end) +2 HISTORICAL ROW +drop view v2; +drop view v1; +drop table t1, t2; diff --git a/mysql-test/suite/versioning/t/view.test b/mysql-test/suite/versioning/t/view.test index c05fbfd3866..288f1eb6e21 100644 --- a/mysql-test/suite/versioning/t/view.test +++ b/mysql-test/suite/versioning/t/view.test @@ -136,6 +136,7 @@ eval create or replace table t1 ( ) with system versioning; insert into t1 values (1), (2); create or replace view v1 as select * from t1 where x > 1; +--echo # update, delete update v1 set x= x + 1; select *, check_row(row_start, row_end) from t1 for system_time all order by x; insert v1 values (4); @@ -153,8 +154,63 @@ select *, check_row(row_start, row_end) from t2 for system_time all order by x; delete v1, v2 from v1 join v2 where v1.x = v2.x + 2; select *, check_row(row_start, row_end) from t1 for system_time all order by x; select *, check_row(row_start, row_end) from t2 for system_time all order by x; - +--echo # replace +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create or replace table t1 ( + x int primary key, y int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end) +) with system versioning; +insert into t1 values (1, 0), (2, 0); +create or replace view v1 as select * from t1 where x > 1; +replace v1 values (1, 1); +replace v1 values (2, 1); +replace v1 values (3, 1); +--echo # REPLACE ignores VIEW condition because itself doesn't use WHERE +select *, check_row(row_start, row_end) from t1 for system_time all order by x, row_end; +--echo # insert-select, on duplicate key +insert v1 select * from t1 where x = 1 on duplicate key update x = v1.x - 1; +select *, check_row(row_start, row_end) from t1 for system_time all order by x, row_end; drop view v1, v2; drop tables t1, t2; +--echo # +--echo # MDEV-21146 Assertion `m_lock_type == 2' in handler::ha_drop_table upon LOAD DATA +--echo # +create table t1 (a int); +create view v1 as select * from t1; +create or replace table t1 (b int) with system versioning; +--error ER_VIEW_INVALID +load data infile 'xx' into table v1; + +# cleanup +drop view v1; +drop table t1; + +--echo # +--echo # MDEV-21155 Assertion with versioned table upon DELETE from view of view after replacing first view +--echo # +create table t1 (a int); +insert into t1 values (1); +--replace_result $sys_datatype_expl SYS_DATATYPE +eval create table t2 ( + b int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end) +) with system versioning; +insert into t2 values (2); +create view v1 as select * from t1; +create view v2 as select * from v1; +create or replace view v1 as select * from t2; +delete from v2; +select * from t1; +select *, check_row(row_start, row_end) from t2 for system_time all; + +# cleanup +drop view v2; +drop view v1; +drop table t1, t2; + --source suite/versioning/common_finish.inc diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 44595746614..652353fb722 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -692,12 +692,27 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) { /* System versioned tables may still require to get versioning conditions - (when updating view). See vers_setup_conds(). + when modifying view (see vers_setup_conds()). Only UPDATE and DELETE are + affected because they use WHERE condition. */ if (!unit->prepared && derived->table->versioned() && - (res= unit->prepare(derived, derived->derived_result, 0))) - goto exit; + derived->merge_underlying_list && + /* choose only those merged views that do not select from other views */ + !derived->merge_underlying_list->merge_underlying_list) + { + switch (thd->lex->sql_command) + { + case SQLCOM_DELETE: + case SQLCOM_DELETE_MULTI: + case SQLCOM_UPDATE: + case SQLCOM_UPDATE_MULTI: + if ((res= unit->prepare(derived, derived->derived_result, 0))) + goto exit; + default: + break; + } + } DBUG_RETURN(FALSE); } @@ -817,7 +832,7 @@ exit: { if (!derived->is_with_table_recursive_reference()) { - if (derived->table) + if (derived->table && derived->table->s->tmp_table) free_tmp_table(thd, derived->table); delete derived->derived_result; } diff --git a/sql/table.h b/sql/table.h index ca5565250de..86e30034449 100644 --- a/sql/table.h +++ b/sql/table.h @@ -321,7 +321,7 @@ typedef struct st_grant_info enum tmp_table_type { - NO_TMP_TABLE, NON_TRANSACTIONAL_TMP_TABLE, TRANSACTIONAL_TMP_TABLE, + NO_TMP_TABLE= 0, NON_TRANSACTIONAL_TMP_TABLE, TRANSACTIONAL_TMP_TABLE, INTERNAL_TMP_TABLE, SYSTEM_TMP_TABLE }; enum release_type { RELEASE_NORMAL, RELEASE_WAIT_FOR_DROP }; |