Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21164

DML in VIEW of VIEW inconsistency after replacing first view

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              midenok Aleksey Midenkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated: