Fixed bug #27827.

ON conditions from JOIN expression were ignored at CHECK OPTION
check when updating a multi-table view with CHECK OPTION.

The st_table_list::prep_check_option function has been
modified to to take into account ON conditions at CHECK OPTION check
It was also changed to build the check option condition only once
for any update used in PS/SP.


sql/table.h:
  Fixed bug #27827.
  The st_table_list::check_option_processed variable has been added
  to build the check option condition only once for any update used
  in PS/SP.
sql/table.cc:
  Fixed bug #27827.
  The st_table_list::prep_check_option function has been
  modified to to take into account ON conditions at CHECK OPTION check
  It was also changed to build the check option condition only once
  for any update used in PS/SP.
mysql-test/t/view.test:
  Updated test case for bug #27827.
mysql-test/r/view.result:
  Updated test case for bug #27827.

1 files changed, 52 insertions, 0 deletions

diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 3275ba0a687..60b10de01f0 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -3233,4 +3233,56 @@ CREATE VIEW v1 AS SELECT CAST(1.23456789 AS DECIMAL(8,0)) AS col;
 SHOW CREATE VIEW v1;
 DROP VIEW v1;
 
+#
+# Bug #27827: CHECK OPTION ignores ON conditions when updating
+#             a multi-table view with CHECK OPTION.
+#
+
+CREATE TABLE t1 (a1 INT, c INT DEFAULT 0);
+CREATE TABLE t2 (a2 INT);
+CREATE TABLE t3 (a3 INT);
+CREATE TABLE t4 (a4 INT);
+INSERT INTO t1 (a1) VALUES (1),(2);
+INSERT INTO t2 (a2) VALUES (1),(2);
+INSERT INTO t3 (a3) VALUES (1),(2);
+INSERT INTO t4 (a4) VALUES (1),(2);
+
+CREATE VIEW v1 AS
+  SELECT t1.a1, t1.c FROM t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3
+    WITH CHECK OPTION;
+SELECT * FROM v1;
+--error 1369
+UPDATE v1 SET c=3;
+PREPARE t FROM 'UPDATE v1 SET c=3';
+--error 1369
+EXECUTE t;
+--error 1369
+EXECUTE t;
+--error 1369
+INSERT INTO v1(a1, c) VALUES (3, 3);
+UPDATE v1 SET c=1 WHERE a1=1;
+SELECT * FROM v1;
+SELECT * FROM t1;
+
+CREATE VIEW v2 AS SELECT t1.a1, t1.c
+  FROM (t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3)
+  JOIN (t3 JOIN t4 ON t3.a3=t4.a4)
+    ON t2.a2=t3.a3 WITH CHECK OPTION;
+SELECT * FROM v2;
+--error 1369
+UPDATE v2 SET c=3;
+PREPARE t FROM 'UPDATE v2 SET c=3';
+--error 1369
+EXECUTE t;
+--error 1369
+EXECUTE t;
+--error 1369
+INSERT INTO v2(a1, c) VALUES (3, 3);
+UPDATE v2 SET c=2 WHERE a1=1;
+SELECT * FROM v2;
+SELECT * FROM t1;
+
+DROP VIEW v1,v2;
+DROP TABLE t1,t2,t3,t4;
+
 --echo End of 5.0 tests.