Debian Codegraph @

mysql-test/suite/sys_vars/r/updatable_views_with_limit_func.result

raw
1 SET @session_updatable_views_with_limit = @@Session.UPDATABLE_VIEWS_WITH_LIMIT;
2 DROP TABLE IF EXISTS t1;
3 CREATE TABLE t1 (a INT, b INT, c INT, PRIMARY KEY(a,b));
4 INSERT INTO t1 VALUES (10,2,-1), (20,3,-2),
5 (30,4,-3), (40,5,-4);
6 CREATE VIEW v1 (x,y) AS SELECT a, c FROM t1;
7 ** Connecting test_con1 using username 'root' **
8 ** Connection test_con1 **
9 SET @@Session.UPDATABLE_VIEWS_WITH_LIMIT=YES;
10 Warning expected, 'View does not contain complete key of the table'
11 UPDATE v1 SET x=x+6 LIMIT 1;
12 Warnings:
13 Note 1355 View being updated does not have complete key of underlying table in it
14 SELECT * FROM t1;
15 a b c
16 16 2 -1
17 20 3 -2
18 30 4 -3
19 40 5 -4
20 UPDATE v1 SET x=x+5;
21 SELECT * FROM t1;
22 a b c
23 21 2 -1
24 25 3 -2
25 35 4 -3
26 45 5 -4
27 ** Connecting test_con2 using username 'root' **
28 ** Connection test_con2 **
29 SET @@Session.UPDATABLE_VIEWS_WITH_LIMIT=NO;
30 SELECT @@SESSION.UPDATABLE_VIEWS_WITH_LIMIT;
31 @@SESSION.UPDATABLE_VIEWS_WITH_LIMIT
32 NO
33 UPDATE v1 SET x=x+10 LIMIT 1;
34 ERROR HY000: The target table v1 of the UPDATE is not updatable
35 Expected error 'Non updatable table'
36 SELECT * FROM t1;
37 a b c
38 21 2 -1
39 25 3 -2
40 35 4 -3
41 45 5 -4
42 '#---------------------FN_DYNVARS_039_01----------------------#'
43 SET UPDATABLE_VIEWS_WITH_LIMIT=NO;
44 UPDATE v1 SET x=x+1 LIMIT 1;
45 ERROR HY000: The target table v1 of the UPDATE is not updatable
46 Expected error 'Non updatable table'
47 SET UPDATABLE_VIEWS_WITH_LIMIT=0;
48 UPDATE v1 SET x=x+1 LIMIT 1;
49 ERROR HY000: The target table v1 of the UPDATE is not updatable
50 Expected error 'Non updatable table'
51 '#---------------------FN_DYNVARS_039_02----------------------#'
52 Warning expected, 'View does not contain complete key of the table'
53 SET UPDATABLE_VIEWS_WITH_LIMIT=DEFAULT;
54 UPDATE v1 SET x=x+1 LIMIT 1;
55 Warnings:
56 Note 1355 View being updated does not have complete key of underlying table in it
57 Warning expected, 'View does not contain complete key of the table'
58 SET UPDATABLE_VIEWS_WITH_LIMIT=YES;
59 UPDATE v1 SET x=x+2 LIMIT 1;
60 Warnings:
61 Note 1355 View being updated does not have complete key of underlying table in it
62 ** Connection default **
63 ** Disconnecting test_con1, test_con2 **
64 SET @@SESSION.updatable_views_with_limit = @session_updatable_views_with_limit;
65 DROP VIEW IF EXISTS v1;
66 DROP TABLE IF EXISTS t1;