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 CONNECT (test_con1,localhost,root,,);
56 CONNECTION test_con1;
57
58 SET @@Session.UPDATABLE_VIEWS_WITH_LIMIT=YES;
59
60 #
61 # Testing WITH a limit clause
62 #
63
64 --echo Warning expected, 'View does not contain complete key of the table'
65 UPDATE v1 SET x=x+6 LIMIT 1;
66
67 SELECT * FROM t1;
68
69 #
70 # Testing WITHOUT a limit clause
71 #
72
73 UPDATE v1 SET x=x+5;
74
75 SELECT * FROM t1;
76
77 CONNECT (test_con2,localhost,root,,);
78 CONNECTION test_con2;
79
80 SET @@Session.UPDATABLE_VIEWS_WITH_LIMIT=NO;
81
82 SELECT @@SESSION.UPDATABLE_VIEWS_WITH_LIMIT;
83
84 --ERROR ER_NON_UPDATABLE_TABLE
85 UPDATE v1 SET x=x+10 LIMIT 1;
86 --echo Expected error 'Non updatable table'
87
88 SELECT * FROM t1;
89
90
91 --echo '#---------------------FN_DYNVARS_039_01----------------------#'
92 ######################################
93 # Setting value to NO #
94 ######################################
95
96 SET UPDATABLE_VIEWS_WITH_LIMIT=NO;
97
98 -- error ER_NON_UPDATABLE_TABLE
99 UPDATE v1 SET x=x+1 LIMIT 1;
100 --echo Expected error 'Non updatable table'
101
102 SET UPDATABLE_VIEWS_WITH_LIMIT=0;
103
104 -- error ER_NON_UPDATABLE_TABLE
105 UPDATE v1 SET x=x+1 LIMIT 1;
106 --echo Expected error 'Non updatable table'
107
108 --echo '#---------------------FN_DYNVARS_039_02----------------------#'
109 ######################################
110 # Setting value to Default #
111 ######################################
112
113 --echo Warning expected, 'View does not contain complete key of the table'
114 SET UPDATABLE_VIEWS_WITH_LIMIT=DEFAULT;
115 UPDATE v1 SET x=x+1 LIMIT 1;
116
117
118 --echo Warning expected, 'View does not contain complete key of the table'
119 SET UPDATABLE_VIEWS_WITH_LIMIT=YES;
120 UPDATE v1 SET x=x+2 LIMIT 1;
121
122 #
123 # Cleanup
124 #
125
126 connection default;
127
128 disconnect test_con1;
129 disconnect test_con2;
130
131 SET @@SESSION.updatable_views_with_limit = @session_updatable_views_with_limit;
132
133 --disable_warnings
134 DROP VIEW IF EXISTS v1;
135 DROP TABLE IF EXISTS t1;
136 --enable_warnings