Bug #19789 VIEW with WITH CHECK OPTION allows invalid REPLACE
Submitted: 12 May 2006 22:41 Modified: 7 Jul 2006 20:25
Reporter: Paul Slezak Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.21 OS:Linux (Linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[12 May 2006 22:41] Paul Slezak
Description:
The WITH CHECK OPTION does not appear to be effective when using the REPLACE command.

How to repeat:
Your MySQL connection id is 249 to server version: 5.0.21-standard

mysql> create table testreplace (number int, filter int);
Query OK, 0 rows affected (0.00 sec)

mysql> create view filtertest as select filter from testreplace where filter=0 with check option;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into filtertest (filter) VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'newco1.filtertest'
mysql> insert into filtertest (filter) VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> update filtertest set filter=1;
ERROR 1369 (HY000): CHECK OPTION failed 'newco1.filtertest'

mysql> replace into filtertest (filter) VALUES (1);
Query OK, 1 row affected (0.00 sec)

Suggested fix:
Please make REPLACE work like INSERT and UPDATE when the WITH CHECK OPTION is specified on a view
[12 May 2006 23:47] Hartmut Holzgraefe
# MySQL Bugs #19789
# VIEW with WITH CHECK OPTION allows invalid REPLACE
# http://bugs.mysql.com/19879

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;
--enable_warnings

CREATE TABLE t1 (value INT PRIMARY KEY, filter INT);
INSERT INTO t1 VALUES (1, 0);
CREATE VIEW v1 AS 
  SELECT value, filter FROM t1 WHERE filter = 0 WITH CHECK OPTION;

--error 1369
INSERT INTO v1 (filter) VALUES (2);

--error 1369
UPDATE v1 SET filter = 2;

--error 1369
REPLACE INTO v1 (value, filter) VALUES (1, 2);

DROP TABLE t1;
DROP VIEW v1;
[13 May 2006 1:30] Paul Slezak
I am not sure what the previous comment is supposed to mean unless the commands are being done on a later version of MySQL.  Here is a verbatim cut/paste form the previous post with an added select

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 250 to server version: 5.0.21-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE t1 (value INT PRIMARY KEY, filter INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1, 0);
Query OK, 1 row affected (0.01 sec)

mysql> CREATE VIEW v1 AS
    ->   SELECT value, filter FROM t1 WHERE filter = 0 WITH CHECK OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO v1 (filter) VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'newco1.v1'
mysql> UPDATE v1 SET filter = 2;
ERROR 1369 (HY000): CHECK OPTION failed 'newco1.v1'
mysql> REPLACE INTO v1 (value, filter) VALUES (1, 2);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1;
+-------+--------+
| value | filter |
+-------+--------+
|     1 |      2 |
+-------+--------+
1 row in set (0.01 sec)

For what it is worth, might as well note that 2 rows affected was returned, even though only one row exists.
[7 Jun 2006 12:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7354
[19 Jun 2006 0:16] Evgeny Potemkin
Fixed in 4.1.21, 5.0.23, 5.1.12
[19 Jun 2006 0:22] Evgeny Potemkin
Sorry, fixed in 5.0 and 5.1 only
[20 Jun 2006 10:57] Evgeny Potemkin
The st_lex::which_check_option_applicable() function controls for which 
statements WITH CHECK OPTION clause should be taken into account. REPLACE and
REPLACE_SELECT wasn't in the list which results in allowing REPLACE to insert
wrong rows in a such view.
[7 Jul 2006 20:25] Mike Hillyer
Documented in 5.1.12 and 5.0.23 changelogs.

      <listitem>
        <para>
          The <literal>WITH CHECK OPTION</literal> was not enforced when
          a <literal>REPLACE</literal> statement was executed against a
          view. (Bug #19789)
        </para>
      </listitem>
[28 Aug 2013 21:44] Andrey Chirikba
As far as I can see, this is still present in 5.6.4, and I haven't seen any notes about it in further 5.6.x line releases.

I think the previous testcase should do, but I can supply my own one if needed.