Bug #6404 wrong behaviour, nested updatable VIEWs, CHECK OPTION

• Submitted: 3 Nov 2004 15:39
• Modified: 21 Jun 2005 23:28
• Reporter: Matthias Leich
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[3 Nov 2004 15:39] Matthias Leich

Description:
If I perform an UPDATE or INSERT into a VIEW B (defined without CHECK OPTION) but
based on a VIEW A (defined with CHECK OPTION), the CHECK OPTION of VIEW A is
ignored.
This behaviour
- is not covered by the manual and
- violates the SQL Core SQL-99 standard and the expectations of the NIST tests.

Example:
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 (a INT);

CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
WITH CHECK OPTION;
CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0;

# This INSERT should fail, because the row violates the CHECK OPTION of VIEW v1
INSERT INTO v2 VALUES (2);

My environment:
   - Intel PC with Linux(SuSE 9.1)
   - MySQL 5.0 compiled from source
ChangeSet@1.1684.1.1, 2004-11-02 17:45:26+03:00

How to repeat:
Please use the statements above.

Suggested fix:
please implement the SQL  standard behaviour

[21 Jun 2005 19:06] Trudy Pelzer

This is not a bug. In standard SQL, the CHECK OPTION cascades upward,
not downward. That is, if view2 has no CHECK OPTION as part of its
definition (as in the example here), then the conditions for any view 
upon which it is based have no effect on view2. If view2 has a LOCAL
CHECK OPTION, then the conditions for any view upon which it is based 
also have no effect on view2. Only if view2 has a [CASCADED] CHECK OPTION
do the conditions of view1 have any effect on data changes to view2.