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

DML in VIEW of VIEW inconsistency after replacing first view

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21155 Assertion with versioned table upon DELETE from view of view after replacing first view

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            midenok Aleksey Midenkov created issue -
            midenok Aleksey Midenkov made changes -
            Field Original Value New Value
            Description h3. Reproduce 1
            {code:sql}
            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);

            h1. cleanup
            drop view v2;
            drop view v1;
            drop table t1, t2;
            {code}
            h3. Result 1
            {code}
            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
            {code}

            		h3. Reproduce 1: INSERT
            {code:sql}
            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;
            {code}
            h3. Result 1
            {code}
            mysqltest: At line 6: query 'update v2 set b= 3' failed: 1054: Unknown column 'b' in 'field list'
            {code}

            h3. Reproduce 3: REPLACE
            {code:sql}
            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;
            {code}
            h3. Result 3
            {code}
            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
            {code}

            h3. Reproduce 2: UPDATE
            {code:sql}
            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;
            {code}
            h3. Result 2
            {code}
            mysqltest: At line 6: query 'update v2 set b= 3' failed: 1054: Unknown column 'b' in 'field list'
            {code}

            midenok Aleksey Midenkov made changes -
            Description h3. Reproduce 1: INSERT
            {code:sql}
            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;
            {code}
            h3. Result 1
            {code}
            mysqltest: At line 6: query 'update v2 set b= 3' failed: 1054: Unknown column 'b' in 'field list'
            {code}

            h3. Reproduce 3: REPLACE
            {code:sql}
            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;
            {code}
            h3. Result 3
            {code}
            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
            {code}

            h3. Reproduce 2: UPDATE
            {code:sql}
            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;
            {code}
            h3. Result 2
            {code}
            mysqltest: At line 6: query 'update v2 set b= 3' failed: 1054: Unknown column 'b' in 'field list'
            {code}

            		INSERT, UPDATE, REPLACE don't work.

            h3. Reproduce 1: INSERT
            {code:sql}
            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;
            {code}
            h3. Result 1
            {code}
            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
            {code}

            h3. Reproduce 2: UPDATE
            {code:sql}
            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;
            {code}
            h3. Result 2
            {code}
            mysqltest: At line 6: query 'update v2 set b= 3' failed: 1054: Unknown column 'b' in 'field list'
            {code}

            h3. Reproduce 3: REPLACE
            {code:sql}
            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;
            {code}
            h3. Result 3
            {code}
            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
            {code}

            But DELETE works:

            h3. Good: DELETE
            {code:sql}
            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;
            {code}
            h3. Result
            {code}
            select * from t1;
            a
            1
            select * from t2;
            b
            {code}

            h3. 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*:
            {code}
            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
            {code}

            Thus DML that requires field names fails, but DML that does not require field names doesn't fail.
            midenok Aleksey Midenkov made changes -
            Description INSERT, UPDATE, REPLACE don't work.

            h3. Reproduce 1: INSERT
            {code:sql}
            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;
            {code}
            h3. Result 1
            {code}
            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
            {code}

            h3. Reproduce 2: UPDATE
            {code:sql}
            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;
            {code}
            h3. Result 2
            {code}
            mysqltest: At line 6: query 'update v2 set b= 3' failed: 1054: Unknown column 'b' in 'field list'
            {code}

            h3. Reproduce 3: REPLACE
            {code:sql}
            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;
            {code}
            h3. Result 3
            {code}
            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
            {code}

            But DELETE works:

            h3. Good: DELETE
            {code:sql}
            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;
            {code}
            h3. Result
            {code}
            select * from t1;
            a
            1
            select * from t2;
            b
            {code}

            h3. 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*:
            {code}
            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
            {code}

            Thus DML that requires field names fails, but DML that does not require field names doesn't fail.             		INSERT, UPDATE, REPLACE don't work.

            h3. Reproduce 1: INSERT
            {code:sql}
            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;
            {code}
            h3. Result 1
            {code}
            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
            {code}

            h3. Reproduce 2: UPDATE
            {code:sql}
            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;
            {code}
            h3. Result 2
            {code}
            mysqltest: At line 6: query 'update v2 set b= 3' failed: 1054: Unknown column 'b' in 'field list'
            {code}

            h3. Reproduce 3: REPLACE
            {code:sql}
            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;
            {code}
            h3. Result 3
            {code}
            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
            {code}

            h3. Good: DELETE
            But DELETE works:
            {code:sql}
            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;
            {code}
            h3. Result
            {code}
            select * from t1;
            a
            1
            select * from t2;
            b
            {code}

            h3. 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*:
            {code}
            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
            {code}

            Thus DML that requires field names fails, but DML that does not require field names doesn't fail.
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            Affects Version/s 10.1 [ 16100 ]
            Affects Version/s 10.3 [ 22126 ]
            midenok Aleksey Midenkov made changes -
            Affects Version/s 10.2 [ 14601 ]
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            elenst Elena Stepanova made changes -
            Fix Version/s 10.1 [ 16100 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Assignee Oleksandr Byelkin [ sanja ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.1 [ 16100 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 101453 ] MariaDB v4 [ 141668 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.2 [ 14601 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.3 [ 22126 ]
            alice Alice Sherepa made changes -
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.2 [ 28603 ]
            Fix Version/s 11.4 [ 29301 ]
            Fix Version/s 11.6 [ 29515 ]
            Fix Version/s 10.4(EOL) [ 22408 ]
            alice Alice Sherepa made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            alice Alice Sherepa made changes -
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6 [ 24028 ]
            Affects Version/s 10.11 [ 27614 ]
            Affects Version/s 11.2 [ 28603 ]
            Affects Version/s 11.4 [ 29301 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.6(EOL) [ 29515 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.2(EOL) [ 28603 ]

            People

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

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.