Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.11, 11.2(EOL), 11.4
-
None
Description
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.
Attachments
Issue Links
- relates to
-
MDEV-21155 Assertion with versioned table upon DELETE from view of view after replacing first view
- Closed