1SET @session_updatable_views_with_limit = @@Session.UPDATABLE_VIEWS_WITH_LIMIT; 2DROP TABLE IF EXISTS t1; 3CREATE TABLE t1 (a INT, b INT, c INT, PRIMARY KEY(a,b)); 4INSERT INTO t1 VALUES (10,2,-1), (20,3,-2), 5(30,4,-3), (40,5,-4); 6CREATE VIEW v1 (x,y) AS SELECT a, c FROM t1; 7CONNECT test_con1,localhost,root,,; 8connection test_con1; 9SET @@Session.UPDATABLE_VIEWS_WITH_LIMIT=YES; 10Warning expected, 'View does not contain complete key of the table' 11UPDATE v1 SET x=x+6 LIMIT 1; 12Warnings: 13Note 1355 View being updated does not have complete key of underlying table in it 14SELECT * FROM t1; 15a b c 1616 2 -1 1720 3 -2 1830 4 -3 1940 5 -4 20UPDATE v1 SET x=x+5; 21SELECT * FROM t1; 22a b c 2321 2 -1 2425 3 -2 2535 4 -3 2645 5 -4 27CONNECT test_con2,localhost,root,,; 28connection test_con2; 29SET @@Session.UPDATABLE_VIEWS_WITH_LIMIT=NO; 30SELECT @@SESSION.UPDATABLE_VIEWS_WITH_LIMIT; 31@@SESSION.UPDATABLE_VIEWS_WITH_LIMIT 32NO 33UPDATE v1 SET x=x+10 LIMIT 1; 34ERROR HY000: The target table v1 of the UPDATE is not updatable 35Expected error 'Non updatable table' 36SELECT * FROM t1; 37a b c 3821 2 -1 3925 3 -2 4035 4 -3 4145 5 -4 42'#---------------------FN_DYNVARS_039_01----------------------#' 43SET UPDATABLE_VIEWS_WITH_LIMIT=NO; 44UPDATE v1 SET x=x+1 LIMIT 1; 45ERROR HY000: The target table v1 of the UPDATE is not updatable 46Expected error 'Non updatable table' 47SET UPDATABLE_VIEWS_WITH_LIMIT=0; 48UPDATE v1 SET x=x+1 LIMIT 1; 49ERROR HY000: The target table v1 of the UPDATE is not updatable 50Expected error 'Non updatable table' 51'#---------------------FN_DYNVARS_039_02----------------------#' 52Warning expected, 'View does not contain complete key of the table' 53SET UPDATABLE_VIEWS_WITH_LIMIT=DEFAULT; 54UPDATE v1 SET x=x+1 LIMIT 1; 55Warnings: 56Note 1355 View being updated does not have complete key of underlying table in it 57Warning expected, 'View does not contain complete key of the table' 58SET UPDATABLE_VIEWS_WITH_LIMIT=YES; 59UPDATE v1 SET x=x+2 LIMIT 1; 60Warnings: 61Note 1355 View being updated does not have complete key of underlying table in it 62connection default; 63disconnect test_con1; 64disconnect test_con2; 65SET @@SESSION.updatable_views_with_limit = @session_updatable_views_with_limit; 66DROP VIEW IF EXISTS v1; 67DROP TABLE IF EXISTS t1; 68