From 5389cc169abd3853145d7bb1d0f6dbce214eacea Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 29 Sep 2006 12:16:07 +0500 Subject: bug #16813 (WITH CHECK OPTION fails with UPDATE) We use the condition from CHECK OPTION twice handling UPDATE command. First we construnct 'update_cond' AND 'option_cond' condition to select records to be updated, then we check the 'option_cond' for the updated row. The problem is that first 'AND' condition is optimized during the 'select' which can break 'option_cond' structure, so it will be unusable for the sectond use - to check the updated row. Possible soultion is either use copy of the condition in the first use or to make optimization less traumatic for the operands. I picked the first one. mysql-test/r/view.result: result fixed mysql-test/t/view.test: testcase sql/table.cc: now we use the copy of the CHECK OPTION condition to construct the select's condition --- mysql-test/r/view.result | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'mysql-test/r') diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 9c05aba168c..f7b75374c8a 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2935,4 +2935,14 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 DROP VIEW v1; DROP TABLE t1; +CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL); +CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION; +INSERT INTO v1 (val) VALUES (2); +INSERT INTO v1 (val) VALUES (4); +INSERT INTO v1 (val) VALUES (6); +ERROR HY000: CHECK OPTION failed 'test.v1' +UPDATE v1 SET val=6 WHERE id=2; +ERROR HY000: CHECK OPTION failed 'test.v1' +DROP VIEW v1; +DROP TABLE t1; End of 5.0 tests. -- cgit v1.2.1