Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5
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.