#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    17
    Rep Power
    0

    Why am I able to insert into updated view but not able to retrieve it.


    Hi,

    my version is as follow

    Code:
    root@localhost[test1]>select * from v1;
    Empty set (0.00 sec)
    
    root@localhost[test1]>select * from v2;
    Empty set (0.00 sec)
    
    root@localhost[test1]>select * from v3;
    Empty set (0.00 sec)
    
    root@localhost[test1]>select * from t1;
    +------+
    | a    |
    +------+
    |    2 |
    from http://dev.mysql.com/doc/refman/5.0/en/view-updatability.html

    mysql> CREATE TABLE t1 (a INT);
    mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
    -> WITH CHECK OPTION;
    mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
    -> WITH LOCAL CHECK OPTION;
    mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
    -> WITH CASCADED CHECK OPTION;
    it works accordingly to what is being documented.

    but when I discovered something

    Code:
    root@localhost[test1]>CREATE TABLE t1 (a INT);
    Query OK, 0 rows affected (0.00 sec)
    
    root@localhost[test1]>CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2 WITH CHECK OPTION;
    Query OK, 0 rows affected (0.00 sec)
    
    root@localhost[test1]>CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0 WITH LOCAL CHECK OPTION;
    Query OK, 0 rows affected (0.01 sec)
    
    root@localhost[test1]>CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0 WITH CASCADED CHECK OPTION;
    Query OK, 0 rows affected (0.00 sec)
    
    root@localhost[test1]>INSERT INTO v2 VALUES (2);
    Query OK, 1 row affected (0.00 sec)
    
    root@localhost[test1]>INSERT INTO v3 VALUES (2);
    ERROR 1369 (HY000): CHECK OPTION failed 'test1.v3'
    root@localhost[test1]>INSERT INTO v1 VALUES (2);
    ERROR 1369 (HY000): CHECK OPTION failed 'test1.v1'

    retrieving

    Code:
    root@localhost[test1]>select * from v1;
    Empty set (0.00 sec)
    
    root@localhost[test1]>select * from v2;
    Empty set (0.00 sec)
    
    root@localhost[test1]>select * from v3;
    Empty set (0.00 sec)
    
    root@localhost[test1]>select * from t1;
    +------+
    | a    |
    +------+
    |    2 |
    Note I was able to retrieve from test yet I was not able to retrieve from v2. Why is this so?

    thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    36
    because there is nothing in v2
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    17
    Rep Power
    0
    why there's nothing in v2?

    note I was able to insert v2 yet I was not able to retrieve from v2.

    Code:
    root@localhost[test1]>CREATE TABLE t1 (a INT);
    Query OK, 0 rows affected (0.00 sec)
    
    root@localhost[test1]>CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2 WITH CHECK OPTION;
    Query OK, 0 rows affected (0.00 sec)
    
    root@localhost[test1]>CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0 WITH LOCAL CHECK OPTION;
    Query OK, 0 rows affected (0.01 sec)
    
    root@localhost[test1]>INSERT INTO v2 VALUES (2);
    Query OK, 1 row affected (0.00 sec)
    Note v2 is declare with local check option.

    thanks
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    36
    probably because

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

    says less than 2 not less than or equal to 2

    if you insert 1 into v2 then it will be entered into the table t1 and you'll be able to see it in your view.

    Comments on this post

    • mysqlnoob1 agrees : you are correct but I don't know why I'm not given any option for the number of points
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    17
    Rep Power
    0
    thanks you are right I was able to insert 1 into v2 and view from all the table and views.

IMN logo majestic logo threadwatch logo seochat tools logo