1
############ mysql-test\t\updatable_views_with_limit_func.test #################
3
#Variable Name: updatable_views_with_limit #
5
#Access Type: Dynamic #
6
#Data Type: Enumeration #
11
#Creation Date: 2008-03-02 #
12
#Author: Sharique Abdullah #
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 #
18
#Reference: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#
19
#option_mysqld_updatable_views_with_limit #
21
################################################################################
27
SET @session_updatable_views_with_limit = @@Session.UPDATABLE_VIEWS_WITH_LIMIT;
31
DROP TABLE IF EXISTS t1;
34
################################
36
################################
38
CREATE TABLE t1 (a INT, b INT, c INT, PRIMARY KEY(a,b));
40
##################################
41
# Inserting values in the table #
42
##################################
45
INSERT INTO t1 VALUES (10,2,-1), (20,3,-2),
49
####################################
51
####################################
52
CREATE VIEW v1 (x,y) AS SELECT a, c FROM t1;
55
--echo ** Connecting test_con1 using username 'root' **
56
CONNECT (test_con1,localhost,root,,);
57
--echo ** Connection test_con1 **
60
SET @@Session.UPDATABLE_VIEWS_WITH_LIMIT=YES;
63
# Testing WITH a limit clause
66
--echo Warning expected, 'View does not contain complete key of the table'
67
UPDATE v1 SET x=x+6 LIMIT 1;
72
# Testing WITHOUT a limit clause
79
--echo ** Connecting test_con2 using username 'root' **
80
CONNECT (test_con2,localhost,root,,);
81
--echo ** Connection test_con2 **
84
SET @@Session.UPDATABLE_VIEWS_WITH_LIMIT=NO;
86
SELECT @@SESSION.UPDATABLE_VIEWS_WITH_LIMIT;
88
--ERROR ER_NON_UPDATABLE_TABLE
89
UPDATE v1 SET x=x+10 LIMIT 1;
90
--echo Expected error 'Non updatable table'
95
--echo '#---------------------FN_DYNVARS_039_01----------------------#'
96
######################################
97
# Setting value to NO #
98
######################################
100
SET UPDATABLE_VIEWS_WITH_LIMIT=NO;
102
-- error ER_NON_UPDATABLE_TABLE
103
UPDATE v1 SET x=x+1 LIMIT 1;
104
--echo Expected error 'Non updatable table'
106
SET UPDATABLE_VIEWS_WITH_LIMIT=0;
108
-- error ER_NON_UPDATABLE_TABLE
109
UPDATE v1 SET x=x+1 LIMIT 1;
110
--echo Expected error 'Non updatable table'
112
--echo '#---------------------FN_DYNVARS_039_02----------------------#'
113
######################################
114
# Setting value to Default #
115
######################################
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;
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;
130
--echo ** Connection default **
133
--echo ** Disconnecting test_con1, test_con2 **
134
disconnect test_con1;
135
disconnect test_con2;
137
SET @@SESSION.updatable_views_with_limit = @session_updatable_views_with_limit;
140
DROP VIEW IF EXISTS v1;
141
DROP TABLE IF EXISTS t1;