1
############## mysql-test\t\completion_type_basic.test #########################
3
# Variable Name: completion_type #
4
# Scope: GLOBAL & SESSION #
5
# Access Type: Dynamic #
8
# Valid Values: 0,1 & 2 #
11
# Creation Date: 2008-02-07 #
12
# Author: Salman Rawala #
14
# Description: Test Cases of Dynamic System Variable "completion_type" #
15
# that checks behavior of this variable in the following ways #
17
# * Valid & Invalid values #
18
# * Scope & Access method #
19
# * Data Integrity . #
21
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
22
# server-system-variables.html#option_mysqld_completion_type #
24
################################################################################
26
--source include/load_sysvars.inc
28
###############################################################
29
# START OF completion_type TESTS #
30
###############################################################
33
#############################################################
34
# Save initial value #
35
#############################################################
37
SET @start_global_value = @@global.completion_type;
38
SELECT @start_global_value;
39
SET @start_session_value = @@session.completion_type;
40
SELECT @start_session_value;
42
--echo '#--------------------FN_DYNVARS_017_01-------------------------#'
43
###############################################################
44
# Display the DEFAULT value of completion_type #
45
###############################################################
47
SET @@global.completion_type = 2;
48
SET @@global.completion_type = DEFAULT;
49
SELECT @@global.completion_type;
51
SET @@session.completion_type = 1;
52
SET @@session.completion_type = DEFAULT;
53
SELECT @@session.completion_type;
56
--echo '#--------------------FN_DYNVARS_017_02-------------------------#'
57
###############################################################
58
# Check the DEFAULT value of completion_type #
59
###############################################################
61
SET @@global.completion_type = @start_global_value;
62
SELECT @@global.completion_type = 0;
64
SET @@session.completion_type = @start_session_value;
65
SELECT @@session.completion_type = 0;
68
--echo '#--------------------FN_DYNVARS_017_03-------------------------#'
69
#########################################################################
70
# Change the value of completion_type to a valid value for GLOBAL Scope #
71
#########################################################################
73
SET @@global.completion_type = 0;
74
SELECT @@global.completion_type;
75
SET @@global.completion_type = 1;
76
SELECT @@global.completion_type;
77
SET @@global.completion_type = 2;
78
SELECT @@global.completion_type;
81
--echo '#--------------------FN_DYNVARS_017_04-------------------------#'
82
##########################################################################
83
# Change the value of completion_type to a valid value for SESSION Scope #
84
##########################################################################
86
SET @@session.completion_type = 0;
87
SELECT @@session.completion_type;
88
SET @@session.completion_type = 1;
89
SELECT @@session.completion_type;
90
SET @@session.completion_type = 2;
91
SELECT @@session.completion_type;
94
--echo '#------------------FN_DYNVARS_017_05-----------------------#'
95
###########################################################
96
# Change the value of completion_type to an invalid value #
97
###########################################################
99
--Error ER_WRONG_VALUE_FOR_VAR
100
SET @@global.completion_type = 10;
101
--Error ER_WRONG_VALUE_FOR_VAR
102
SET @@global.completion_type = -1024;
103
--Error ER_WRONG_TYPE_FOR_VAR
104
SET @@global.completion_type = 2.4;
105
--Error ER_WRONG_TYPE_FOR_VAR
106
SET @@global.completion_type = OFF;
107
--Error ER_WRONG_VALUE_FOR_VAR
108
SET @@session.completion_type = 10;
109
--Error ER_WRONG_VALUE_FOR_VAR
110
SET @@session.completion_type = -2;
111
--Error ER_WRONG_TYPE_FOR_VAR
112
SET @@session.completion_type = 1.2;
113
--Error ER_WRONG_TYPE_FOR_VAR
114
SET @@session.completion_type = ON;
117
--echo '#------------------FN_DYNVARS_017_06-----------------------#'
118
###############################################################################
119
# Check if the value in GLOBAL & SESSION Tables matches value in variable #
120
###############################################################################
122
SELECT @@global.completion_type = VARIABLE_VALUE
123
FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
124
WHERE VARIABLE_NAME='completion_type';
126
SELECT @@session.completion_type = VARIABLE_VALUE
127
FROM INFORMATION_SCHEMA.SESSION_VARIABLES
128
WHERE VARIABLE_NAME='completion_type';
131
--echo '#------------------FN_DYNVARS_017_07-----------------------#'
132
####################################################################
133
# Check if TRUE and FALSE values can be used on variable #
134
####################################################################
136
SET @@global.completion_type = TRUE;
137
SELECT @@global.completion_type;
138
SET @@global.completion_type = FALSE;
139
SELECT @@global.completion_type;
142
--echo '#---------------------FN_DYNVARS_001_08----------------------#'
143
##############################################################################
144
# Check if accessing variable with SESSION,LOCAL and without SCOPE points #
145
# to same session variable #
146
##############################################################################
148
SET @@completion_type = 1;
149
SELECT @@completion_type = @@local.completion_type;
150
SELECT @@local.completion_type = @@session.completion_type;
153
--echo '#---------------------FN_DYNVARS_001_09----------------------#'
154
##########################################################################
155
# Check if completion_type can be accessed with and without @@ sign #
156
##########################################################################
158
SET completion_type = 1;
159
SELECT @@completion_type;
160
--Error ER_PARSE_ERROR
161
SET local.completion_type = 1;
162
--Error ER_UNKNOWN_TABLE
163
SELECT local.completion_type;
164
--Error ER_PARSE_ERROR
165
SET session.completion_type = 1;
166
--Error ER_UNKNOWN_TABLE
167
SELECT session.completion_type;
168
--Error ER_BAD_FIELD_ERROR
169
SELECT completion_type = @@session.completion_type;
172
####################################
173
# Restore initial value #
174
####################################
176
SET @@global.completion_type = @start_global_value;
177
SELECT @@global.completion_type;
178
SET @@session.completion_type = @start_session_value;
179
SELECT @@session.completion_type;
181
############################################
182
# END OF completion_type TESTS #
183
############################################