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

Views WITH CHECK OPTION work inconsistently for different engines and key structures and produces bogus ER_KEY_NOT_FOUND

    Details

      Description

      If a view is created WITH CHECK OPTION, INSERTs which violate the WHERE condition and UPDATEs which update visible rows to invisible are supposed to be rejected with ER_VIEW_CHECK_FAILED.
      MariaDB documentation (could use some improvement)
      MySQL manual

      In reality, it's all over the place – sometimes such UPDATEs indeed produce ER_VIEW_CHECK_FAILED, sometimes other errors, sometimes they even succeed.

      Test case 1 with MyISAM

      CREATE TABLE t1 (f1 INT, f2 INT, UNIQUE(f2)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1,0),(2,3);
       
      CREATE ALGORITHM = MERGE VIEW v1 AS SELECT a1.f1, a1.f2 FROM t1 AS a1, t1 AS a2 WHERE a2.f2 = 0 WITH CHECK OPTION;
      --error ER_VIEW_CHECK_FAILED
      UPDATE v1 SET f2=1 WHERE f2=0;
      

      In this case UPDATE fails with ER_KEY_NOT_FOUND instead of ER_VIEW_CHECK_FAILED (and ER_KEY_NOT_FOUND is also written to the error log, thus producing a bogus alert about corruption).

      10.4 2465d3e00b2f

      mysqltest: At line 8: query 'UPDATE v1 SET f2=1 WHERE f2=0' failed with wrong errno 1032: 'Can't find record in 't1'', instead of 1369...
      

      Test case 1 with InnoDB

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (f1 INT, f2 INT, UNIQUE(f2)) ENGINE=InnoDB;
      INSERT INTO t1 VALUES (1,0),(2,3);
       
      CREATE ALGORITHM = MERGE VIEW v1 AS SELECT a1.f1, a1.f2 FROM t1 AS a1, t1 AS a2 WHERE a2.f2 = 0 WITH CHECK OPTION;
      --error ER_VIEW_CHECK_FAILED
      UPDATE v1 SET f2=1 WHERE f2=0;
      

      The same test case, but with InnoDB, allows UPDATE to proceed, thus removing the row with f2=0 from the view:

      10.4 2465d3e00b2f

      mysqltest: At line 8: query 'UPDATE v1 SET f2=1 WHERE f2=0' succeeded - should have failed with errno 1369...
      

      A similar test case, but with a non-unique key instead of unique on f2, allows the UPDATE to proceed with both MyISAM and InnoDB:

      Test case 2 with MyISAM

      CREATE TABLE t1 (f1 INT, f2 INT, KEY(f2)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1,0),(2,3);
       
      CREATE ALGORITHM = MERGE VIEW v1 AS SELECT a1.f1, a1.f2 FROM t1 AS a1, t1 AS a2 WHERE a2.f2 = 0 WITH CHECK OPTION;
      --error ER_VIEW_CHECK_FAILED
      UPDATE v1 SET f2=1 WHERE f2=0;
      

      mysqltest: At line 8: query 'UPDATE v1 SET f2=1 WHERE f2=0' succeeded - should have failed with errno 1369...
      

      The same test case as test case 2, but without a WHERE clause in UPDATE, works as expected with both MyISAM and InnoDB:

      Test case 3

      CREATE TABLE t1 (f1 INT, f2 INT, KEY(f2));
      INSERT INTO t1 VALUES (1,0),(2,3);
       
      CREATE ALGORITHM = MERGE VIEW v1 AS SELECT a1.f1, a1.f2 FROM t1 AS a1, t1 AS a2 WHERE a2.f2 = 0 WITH CHECK OPTION;
      --error ER_VIEW_CHECK_FAILED
      UPDATE v1 SET f2=1;
      

      etc. – there are many variations.
      Reproducible on all versions of MariaDB and MySQL.
      Maybe it's just something to document, but I cannot figure the common rule.

        Attachments

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              elenst Elena Stepanova
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: