MySQL Bugs: #19789: VIEW with WITH CHECK OPTION allows invalid REPLACE

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

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

# 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;

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.

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

Fixed in 4.1.21, 5.0.23, 5.1.12

Sorry, fixed in 5.0 and 5.1 only

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.

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>

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.