|
INSERT, UPDATE, REPLACE don't work.
Reproduce 1: INSERT
create table t1 (a int primary key);
|
create table t2 (b int primary key);
|
create view v1 as select * from t1;
|
create view v2 as select * from v1;
|
create or replace view v1 as select * from t2;
|
insert v2 values (3);
|
|
# cleanup
|
drop view v2;
|
drop view v1;
|
drop table t1, t2;
|
Result 1
mysqltest: At line 6: query 'insert v2 values (3)' failed: 1356: View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
Reproduce 2: UPDATE
create table t1 (a int primary key);
|
create table t2 (b int primary key);
|
create view v1 as select * from t1;
|
create view v2 as select * from v1;
|
create or replace view v1 as select * from t2;
|
update v2 set b= 3;
|
|
# cleanup
|
drop view v2;
|
drop view v1;
|
drop table t1, t2;
|
Result 2
mysqltest: At line 6: query 'update v2 set b= 3' failed: 1054: Unknown column 'b' in 'field list'
|
Reproduce 3: REPLACE
create table t1 (a int primary key);
|
create table t2 (b int primary key);
|
create view v1 as select * from t1;
|
create view v2 as select * from v1;
|
create or replace view v1 as select * from t2;
|
replace v2 values (3);
|
|
# cleanup
|
drop view v2;
|
drop view v1;
|
drop table t1, t2;
|
Result 3
mysqltest: At line 6: query 'replace v2 values (3)' failed: 1356: View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
Good: DELETE
But DELETE works:
create table t1 (a int);
|
insert into t1 values (1);
|
create table t2 (b int);
|
insert into t2 values (1);
|
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 * from t2;
|
|
# cleanup
|
drop view v2;
|
drop view v1;
|
drop table t1, t2;
|
Result
select * from t1;
|
a
|
1
|
select * from t2;
|
b
|
The root of the problem
The root of the problem is that VIEW frm contains wildcard expanded and v2.frm contains fields from old v1:
TYPE=VIEW
|
query=select `v1`.`a` AS `a` from `test`.`v1`
|
md5=00a5b2c376dacce85423d0523f68684c
|
updatable=1
|
algorithm=0
|
definer_user=root
|
definer_host=localhost
|
suid=2
|
with_check_option=0
|
timestamp=2019-11-28 06:47:07
|
create-version=2
|
source=select * from v1
|
client_cs_name=utf8
|
connection_cl_name=utf8_general_ci
|
view_body_utf8=select `v1`.`a` AS `a` from `test`.`v1`
|
mariadb-version=100321
|
Thus DML that requires field names fails, but DML that does not require field names doesn't fail.
|