Debian Codegraph @

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

raw
1 ############# mysql-test\t\sql_mode_func.test ######################################
2 # #
3 # Variable Name: sql_mode #
4 # Scope: GLOBAL & SESSION #
5 # Access Type: Dynamic #
6 # Data Type: enumeration #
7 # Values: ALLOW_INVALID_DATES, ANSI_QUOTES, ERROR_FOR_DIVISION_BY_ZERO, #
8 # HIGH_NOT_PRECEDENCE, IGNORE_SPACE, #
9 # NO_AUTO_VALUE_ON_ZERO, NO_BACKSLASH_ESCAPES, NO_DIR_IN_CREATE, #
10 # NO_ENGINE_SUBSTITUTION, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, #
11 # NO_TABLE_OPTIONS, NO_UNSIGNED_SUBTRACTION, NO_ZERO_DATE, #
12 # NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY, PIPES_AS_CONCAT, REAL_AS_FLOAT#
13 # STRICT_ALL_TABLES, STRICT_TRANS_TABLES, OFF #
14 # #
15 # #
16 # Creation Date: 2008-02-22 #
17 # Author: Sharique Abdullah #
18 # #
19 # Description: Test Cases of Dynamic System Variable "sql_mode" #
20 # that checks behavior of this variable in the following ways #
21 # * Functionality based on different values #
22 # #
23 # Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
24 # server-system-variables.html#option_mysqld_sql_mode #
25 # #
26 ####################################################################################
27
28 --echo ** Setup **
29 --echo
30 #
31 # Setup
32 #
33
34 #
35 # Save initial value
36 #
37
38 SET @sql_mode_session = @@SESSION.sql_mode;
39 SET @sql_mode_global = @@GLOBAL.sql_mode;
40
41 #
42 # Create tables
43 #
44
45 CREATE TEMPORARY TABLE t1(a varchar(20) not null, b varchar(20));
46 CREATE TEMPORARY TABLE t2(a varchar(20) null, b varchar(20));
47
48 --echo '#--------------------FN_DYNVARS_153_01-------------------------#'
49 #
50 #ANSI mode
51 #
52 --echo ANSI mode
53
54
55 SET SESSION sql_mode = ANSI;
56
57 SELECT @@SESSION.sql_mode;
58
59 INSERT INTO t1 VALUES('t1a1','t1b1');
60 INSERT INTO t1 VALUES('t1a2','t1b2');
61 INSERT INTO t1 VALUES('t1a3','t1b3');
62 INSERT INTO t1 VALUES('t1a4','t1b4');
63
64 INSERT INTO t2 VALUES('t2a1','t2b1');
65 INSERT INTO t2 VALUES('t2a2','t2b2');
66 INSERT INTO t2 VALUES('t2a3','t2b3');
67 INSERT INTO t2 VALUES('t2a4','t2b4');
68
69 --error ER_INVALID_GROUP_FUNC_USE
70 SELECT * FROM t1 WHERE t1.a IN (SELECT MAX(t1.b) FROM t2);
71 --echo Expected error for invalid group function
72
73 #
74 # Reset
75 #
76
77 SET SESSION sql_mode = @sql_mode_session;
78 SET GLOBAL sql_mode = @sql_mode_global;
79
80 --echo '#--------------------FN_DYNVARS_153_02-------------------------#'
81 #
82 #STRICT_TRANS_TABLES mode
83 #
84 --echo STRICT_TRANS_TABLES mode
85
86 DELETE FROM t1;
87 DELETE FROM t2;
88
89 SET SESSION sql_mode = STRICT_TRANS_TABLES;
90
91 SELECT @@SESSION.sql_mode;
92
93 INSERT INTO t1 VALUES('t1a1','t1b1');
94 INSERT INTO t1 VALUES('t1a2','t1b2');
95 INSERT INTO t1 VALUES('t1a3','t1b3');
96 INSERT INTO t1 VALUES('t1a4','t1b4');
97
98 INSERT INTO t2 VALUES(NULL,'t2b1');
99 INSERT INTO t2 VALUES('t2a2','t2b2');
100 INSERT INTO t2 VALUES('t2a3','t2b3');
101 INSERT INTO t2 VALUES('t2a4','t2b4');
102
103 --error ER_BAD_NULL_ERROR
104 INSERT INTO t1 SELECT * FROM t2;
105 --echo Expected error Bad NULL value
106
107 SELECT * FROM t1 WHERE a = 't2a2';
108 --echo Should return no rows in columns a,b;
109
110 #
111 # Reset
112 #
113
114 SET SESSION sql_mode = @sql_mode_session;
115 SET GLOBAL sql_mode = @sql_mode_global;
116
117 --echo '#--------------------FN_DYNVARS_153_03-------------------------#'
118 #
119 #TRADITIONAL mode
120 #
121 --echo TRADITIONAL mode
122
123 SET SESSION sql_mode = TRADITIONAL;
124
125 SELECT @@SESSION.sql_mode;
126
127 INSERT INTO t1 VALUES('t1a1','t1b1');
128 INSERT INTO t1 VALUES('t1a2','t1b2');
129 INSERT INTO t1 VALUES('t1a3','t1b3');
130 INSERT INTO t1 VALUES('t1a4','t1b4');
131
132 INSERT INTO t2 VALUES(NULL,'t2b1');
133 INSERT INTO t2 VALUES('t2a2','t2b2');
134 INSERT INTO t2 VALUES('t2a3','t2b3');
135 INSERT INTO t2 VALUES('t2a4','t2b4');
136
137 --error ER_BAD_NULL_ERROR
138 INSERT INTO t1 SELECT * FROM t2;
139 --echo Expected error Bad NULL value
140
141 SELECT * FROM t1 WHERE a = 't2a2';
142 --echo Should return no rows in columns a,b;
143
144
145 --echo '#-------------------FN_DYNVARS_153_04-------------------------#'
146 #
147 # Session data integrity check & GLOBAL Value check
148 #
149
150 SET GLOBAL sql_mode = '';
151 --echo ** Connecting con_int1 using root **
152 connect (con_int1,localhost,root,,);
153
154 --echo ** Connection con_int1 **
155 connection con_int1;
156 SELECT @@SESSION.sql_mode;
157 --echo '' Expected
158
159 SET SESSION sql_mode = 'TRADITIONAL';
160
161 --echo ** Connecting con_int2 using root **
162 connect (con_int2,localhost,root,,);
163
164 --echo ** Connection con_int2 **
165 connection con_int2;
166 SELECT @@SESSION.sql_mode;
167 --echo '' Expected
168
169 SET SESSION sql_mode = 'ANSI';
170
171 --echo ** Connection con_int2 **
172 connection con_int2;
173 SELECT @@SESSION.sql_mode;
174 --echo 'REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ANSI' Expected
175
176 --echo ** Connection con_int1 **
177 connection con_int1;
178 SELECT @@SESSION.sql_mode;
179 --echo 'STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL' Expected
180
181
182 SELECT @@GLOBAL.sql_mode;
183 --echo '' Expected
184
185 --echo ** Connection default **
186 connection default;
187
188 --echo Disconnecting Connections con_int1, con_int2
189 disconnect con_int1;
190 disconnect con_int2;
191
192 #
193 # Cleanup
194 #
195
196 SET SESSION sql_mode = @sql_mode_session;
197 SET GLOBAL sql_mode = @sql_mode_global;
198
199 DROP TABLE t1;
200 DROP TABLE t2;