[#MDEV-18158] Views WITH CHECK OPTION work inconsistently for different engines and key structures and produces bogus ER_KEY_NOT

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.

[MDEV-18158] Views WITH CHECK OPTION work inconsistently for different engines and key structures and produces bogus ER_KEY_NOT_FOUND Created: 2019-01-06 Updated: 2023-04-27
Status:	Open
Project:	MariaDB Server
Component/s:	Views
Affects Version/s:	5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s:	10.4, 10.5, 10.6

[MDEV-18158] Views WITH CHECK OPTION work inconsistently for different engines and key structures and produces bogus ER_KEY_NOT_FOUND Created: 2019-01-06 Updated: 2023-04-27