Debian Codegraph @

mysql-test/suite/sys_vars/t/updatable_views_with_limit_func.test

raw
1 ############ mysql-test\t\updatable_views_with_limit_func.test #################
2 # #
3 #Variable Name: updatable_views_with_limit #
4 #Scope: SESSION #
5 #Access Type: Dynamic #
6 #Data Type: Enumeration #
7 #Default Value: - #
8 #Values: - #
9 # #
10 # #
11 #Creation Date: 2008-03-02 #
12 #Author: Sharique Abdullah #
13 # #
14 #Description: Test Cases of Dynamic System Variable "updatable_views_with_limit#
15 # that checks behavior of this variable in the following ways #
16 # * Functionality based on different values #
17 # #
18 #Reference: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#
19 #option_mysqld_updatable_views_with_limit #
20 # #
21 ################################################################################
22
23 #
24 # Setup
25 #
26
27 SET @session_updatable_views_with_limit = @@Session.UPDATABLE_VIEWS_WITH_LIMIT;
28
29
30 --disable_warnings
31 DROP TABLE IF EXISTS t1;
32 --enable_warnings
33
34 ################################
35 # Creating table #
36 ################################
37
38 CREATE TABLE t1 (a INT, b INT, c INT, PRIMARY KEY(a,b));
39
40 ##################################
41 # Inserting values in the table #
42 ##################################
43
44
45 INSERT INTO t1 VALUES (10,2,-1), (20,3,-2),
46 (30,4,-3), (40,5,-4);
47
48
49 ####################################
50 # Creating views #
51 ####################################
52 CREATE VIEW v1 (x,y) AS SELECT a, c FROM t1;
53
54
55 --echo ** Connecting test_con1 using username 'root' **
56 CONNECT (test_con1,localhost,root,,);
57 --echo ** Connection test_con1 **
58 CONNECTION test_con1;
59
60 SET @@Session.UPDATABLE_VIEWS_WITH_LIMIT=YES;
61
62 #
63 # Testing WITH a limit clause
64 #
65
66 --echo Warning expected, 'View does not contain complete key of the table'
67 UPDATE v1 SET x=x+6 LIMIT 1;
68
69 SELECT * FROM t1;
70
71 #
72 # Testing WITHOUT a limit clause
73 #
74
75 UPDATE v1 SET x=x+5;
76
77 SELECT * FROM t1;
78
79 --echo ** Connecting test_con2 using username 'root' **
80 CONNECT (test_con2,localhost,root,,);
81 --echo ** Connection test_con2 **
82 CONNECTION test_con2;
83
84 SET @@Session.UPDATABLE_VIEWS_WITH_LIMIT=NO;
85
86 SELECT @@SESSION.UPDATABLE_VIEWS_WITH_LIMIT;
87
88 --ERROR ER_NON_UPDATABLE_TABLE
89 UPDATE v1 SET x=x+10 LIMIT 1;
90 --echo Expected error 'Non updatable table'
91
92 SELECT * FROM t1;
93
94
95 --echo '#---------------------FN_DYNVARS_039_01----------------------#'
96 ######################################
97 # Setting value to NO #
98 ######################################
99
100 SET UPDATABLE_VIEWS_WITH_LIMIT=NO;
101
102 -- error ER_NON_UPDATABLE_TABLE
103 UPDATE v1 SET x=x+1 LIMIT 1;
104 --echo Expected error 'Non updatable table'
105
106 SET UPDATABLE_VIEWS_WITH_LIMIT=0;
107
108 -- error ER_NON_UPDATABLE_TABLE
109 UPDATE v1 SET x=x+1 LIMIT 1;
110 --echo Expected error 'Non updatable table'
111
112 --echo '#---------------------FN_DYNVARS_039_02----------------------#'
113 ######################################
114 # Setting value to Default #
115 ######################################
116
117 --echo Warning expected, 'View does not contain complete key of the table'
118 SET UPDATABLE_VIEWS_WITH_LIMIT=DEFAULT;
119 UPDATE v1 SET x=x+1 LIMIT 1;
120
121
122 --echo Warning expected, 'View does not contain complete key of the table'
123 SET UPDATABLE_VIEWS_WITH_LIMIT=YES;
124 UPDATE v1 SET x=x+2 LIMIT 1;
125
126 #
127 # Cleanup
128 #
129
130 --echo ** Connection default **
131 connection default;
132
133 --echo ** Disconnecting test_con1, test_con2 **
134 disconnect test_con1;
135 disconnect test_con2;
136
137 SET @@SESSION.updatable_views_with_limit = @session_updatable_views_with_limit;
138
139 --disable_warnings
140 DROP VIEW IF EXISTS v1;
141 DROP TABLE IF EXISTS t1;
142 --enable_warnings
143