[tomato.git] / release / src / router / mysql / mysql-test / suite / engines / funcs / t / ps_string_null.test
| blob | 5e0919d7ca1f80fd6a34dcd7f9d7311914eb8982 |
1 --disable_warnings
2 DROP TABLE IF EXISTS t1;
3 --enable_warnings
4 CREATE TABLE t1(c1 CHAR(100) NULL);
5 PREPARE stmt1 FROM 'INSERT INTO t1 (c1) VALUES(?)';
6 SET @a=NULL;
7 EXECUTE stmt1 USING @a;
8 SET @a='';
9 EXECUTE stmt1 USING @a;
10 SET @a='x';
11 EXECUTE stmt1 USING @a;
12 SET @a='123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.';
13 EXECUTE stmt1 USING @a;
14 DEALLOCATE PREPARE stmt1;
15 SELECT * FROM t1;
16 SELECT COUNT(c1) AS total_rows FROM t1;
17 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
18 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
19 PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE c1 = ?';
20 SET @a=NULL;
21 EXECUTE stmt1 USING @a;
22 SET @a='';
23 EXECUTE stmt1 USING @a;
24 SET @a='x';
25 EXECUTE stmt1 USING @a;
26 DEALLOCATE PREPARE stmt1;
27 PREPARE stmt1 FROM 'UPDATE t1 SET c1 = ? WHERE c1 = ?';
28 SET @a=NULL;
29 SET @b='a';
30 EXECUTE stmt1 USING @a,@b;
31 SET @a='';
32 SET @b='b';
33 EXECUTE stmt1 USING @a,@b;
34 SET @a='x';
35 SET @b='c';
36 EXECUTE stmt1 USING @a,@c;
37 DEALLOCATE PREPARE stmt1;
38 SELECT * FROM t1;
39 SELECT COUNT(c1) AS total_rows FROM t1;
40 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
41 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
42 PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 = ?';
43 SET @a='a';
44 EXECUTE stmt1 USING @a;
45 SET @a='b';
46 EXECUTE stmt1 USING @a;
47 SET @a='c';
48 EXECUTE stmt1 USING @a;
49 DEALLOCATE PREPARE stmt1;
50 SELECT * FROM t1;
51 SELECT COUNT(c1) AS total_rows FROM t1;
52 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
53 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
54 DROP TABLE t1;
55 CREATE TABLE t1(c1 VARCHAR(100) NULL);
56 PREPARE stmt1 FROM 'INSERT INTO t1 (c1) VALUES(?)';
57 SET @a=NULL;
58 EXECUTE stmt1 USING @a;
59 SET @a='';
60 EXECUTE stmt1 USING @a;
61 SET @a='x';
62 EXECUTE stmt1 USING @a;
63 SET @a='123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.';
64 EXECUTE stmt1 USING @a;
65 DEALLOCATE PREPARE stmt1;
66 SELECT * FROM t1;
67 SELECT COUNT(c1) AS total_rows FROM t1;
68 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
69 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
70 PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE c1 = ?';
71 SET @a=NULL;
72 EXECUTE stmt1 USING @a;
73 SET @a='';
74 EXECUTE stmt1 USING @a;
75 SET @a='x';
76 EXECUTE stmt1 USING @a;
77 DEALLOCATE PREPARE stmt1;
78 PREPARE stmt1 FROM 'UPDATE t1 SET c1 = ? WHERE c1 = ?';
79 SET @a=NULL;
80 SET @b='a';
81 EXECUTE stmt1 USING @a,@b;
82 SET @a='';
83 SET @b='b';
84 EXECUTE stmt1 USING @a,@b;
85 SET @a='x';
86 SET @b='c';
87 EXECUTE stmt1 USING @a,@c;
88 DEALLOCATE PREPARE stmt1;
89 SELECT * FROM t1;
90 SELECT COUNT(c1) AS total_rows FROM t1;
91 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
92 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
93 PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 = ?';
94 SET @a='a';
95 EXECUTE stmt1 USING @a;
96 SET @a='b';
97 EXECUTE stmt1 USING @a;
98 SET @a='c';
99 EXECUTE stmt1 USING @a;
100 DEALLOCATE PREPARE stmt1;
101 SELECT * FROM t1;
102 SELECT COUNT(c1) AS total_rows FROM t1;
103 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
104 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
105 DROP TABLE t1;
106 CREATE TABLE t1(c1 BINARY(100) NULL);
107 PREPARE stmt1 FROM 'INSERT INTO t1 (c1) VALUES(?)';
108 SET @a=NULL;
109 EXECUTE stmt1 USING @a;
110 SET @a='';
111 EXECUTE stmt1 USING @a;
112 SET @a='x';
113 EXECUTE stmt1 USING @a;
114 SET @a='123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.';
115 EXECUTE stmt1 USING @a;
116 DEALLOCATE PREPARE stmt1;
117 SELECT * FROM t1;
118 SELECT COUNT(c1) AS total_rows FROM t1;
119 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
120 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
121 PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE c1 = ?';
122 SET @a=NULL;
123 EXECUTE stmt1 USING @a;
124 SET @a='';
125 EXECUTE stmt1 USING @a;
126 SET @a='x';
127 EXECUTE stmt1 USING @a;
128 DEALLOCATE PREPARE stmt1;
129 PREPARE stmt1 FROM 'UPDATE t1 SET c1 = ? WHERE c1 = ?';
130 SET @a=NULL;
131 SET @b='a';
132 EXECUTE stmt1 USING @a,@b;
133 SET @a='';
134 SET @b='b';
135 EXECUTE stmt1 USING @a,@b;
136 SET @a='x';
137 SET @b='c';
138 EXECUTE stmt1 USING @a,@c;
139 DEALLOCATE PREPARE stmt1;
140 SELECT * FROM t1;
141 SELECT COUNT(c1) AS total_rows FROM t1;
142 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
143 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
144 PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 = ?';
145 SET @a='a';
146 EXECUTE stmt1 USING @a;
147 SET @a='b';
148 EXECUTE stmt1 USING @a;
149 SET @a='c';
150 EXECUTE stmt1 USING @a;
151 DEALLOCATE PREPARE stmt1;
152 SELECT * FROM t1;
153 SELECT COUNT(c1) AS total_rows FROM t1;
154 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
155 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
156 DROP TABLE t1;
157 CREATE TABLE t1(c1 VARBINARY(100) NULL);
158 PREPARE stmt1 FROM 'INSERT INTO t1 (c1) VALUES(?)';
159 SET @a=NULL;
160 EXECUTE stmt1 USING @a;
161 SET @a='';
162 EXECUTE stmt1 USING @a;
163 SET @a='x';
164 EXECUTE stmt1 USING @a;
165 SET @a='123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.';
166 EXECUTE stmt1 USING @a;
167 DEALLOCATE PREPARE stmt1;
168 SELECT * FROM t1;
169 SELECT COUNT(c1) AS total_rows FROM t1;
170 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
171 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
172 PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE c1 = ?';
173 SET @a=NULL;
174 EXECUTE stmt1 USING @a;
175 SET @a='';
176 EXECUTE stmt1 USING @a;
177 SET @a='x';
178 EXECUTE stmt1 USING @a;
179 DEALLOCATE PREPARE stmt1;
180 PREPARE stmt1 FROM 'UPDATE t1 SET c1 = ? WHERE c1 = ?';
181 SET @a=NULL;
182 SET @b='a';
183 EXECUTE stmt1 USING @a,@b;
184 SET @a='';
185 SET @b='b';
186 EXECUTE stmt1 USING @a,@b;
187 SET @a='x';
188 SET @b='c';
189 EXECUTE stmt1 USING @a,@c;
190 DEALLOCATE PREPARE stmt1;
191 SELECT * FROM t1;
192 SELECT COUNT(c1) AS total_rows FROM t1;
193 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
194 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
195 PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 = ?';
196 SET @a='a';
197 EXECUTE stmt1 USING @a;
198 SET @a='b';
199 EXECUTE stmt1 USING @a;
200 SET @a='c';
201 EXECUTE stmt1 USING @a;
202 DEALLOCATE PREPARE stmt1;
203 SELECT * FROM t1;
204 SELECT COUNT(c1) AS total_rows FROM t1;
205 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
206 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
207 DROP TABLE t1;
208 CREATE TABLE t1(c1 BLOB(100) NULL);
209 PREPARE stmt1 FROM 'INSERT INTO t1 (c1) VALUES(?)';
210 SET @a=NULL;
211 EXECUTE stmt1 USING @a;
212 SET @a='';
213 EXECUTE stmt1 USING @a;
214 SET @a='x';
215 EXECUTE stmt1 USING @a;
216 SET @a='123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.';
217 EXECUTE stmt1 USING @a;
218 DEALLOCATE PREPARE stmt1;
219 SELECT * FROM t1;
220 SELECT COUNT(c1) AS total_rows FROM t1;
221 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
222 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
223 PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE c1 = ?';
224 SET @a=NULL;
225 EXECUTE stmt1 USING @a;
226 SET @a='';
227 EXECUTE stmt1 USING @a;
228 SET @a='x';
229 EXECUTE stmt1 USING @a;
230 DEALLOCATE PREPARE stmt1;
231 PREPARE stmt1 FROM 'UPDATE t1 SET c1 = ? WHERE c1 = ?';
232 SET @a=NULL;
233 SET @b='a';
234 EXECUTE stmt1 USING @a,@b;
235 SET @a='';
236 SET @b='b';
237 EXECUTE stmt1 USING @a,@b;
238 SET @a='x';
239 SET @b='c';
240 EXECUTE stmt1 USING @a,@c;
241 DEALLOCATE PREPARE stmt1;
242 SELECT * FROM t1;
243 SELECT COUNT(c1) AS total_rows FROM t1;
244 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
245 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
246 PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 = ?';
247 SET @a='a';
248 EXECUTE stmt1 USING @a;
249 SET @a='b';
250 EXECUTE stmt1 USING @a;
251 SET @a='c';
252 EXECUTE stmt1 USING @a;
253 DEALLOCATE PREPARE stmt1;
254 SELECT * FROM t1;
255 SELECT COUNT(c1) AS total_rows FROM t1;
256 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
257 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
258 DROP TABLE t1;
259 CREATE TABLE t1(c1 TEXT(100) NULL);
260 PREPARE stmt1 FROM 'INSERT INTO t1 (c1) VALUES(?)';
261 SET @a=NULL;
262 EXECUTE stmt1 USING @a;
263 SET @a='';
264 EXECUTE stmt1 USING @a;
265 SET @a='x';
266 EXECUTE stmt1 USING @a;
267 SET @a='123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.';
268 EXECUTE stmt1 USING @a;
269 DEALLOCATE PREPARE stmt1;
270 SELECT * FROM t1;
271 SELECT COUNT(c1) AS total_rows FROM t1;
272 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
273 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
274 PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE c1 = ?';
275 SET @a=NULL;
276 EXECUTE stmt1 USING @a;
277 SET @a='';
278 EXECUTE stmt1 USING @a;
279 SET @a='x';
280 EXECUTE stmt1 USING @a;
281 DEALLOCATE PREPARE stmt1;
282 PREPARE stmt1 FROM 'UPDATE t1 SET c1 = ? WHERE c1 = ?';
283 SET @a=NULL;
284 SET @b='a';
285 EXECUTE stmt1 USING @a,@b;
286 SET @a='';
287 SET @b='b';
288 EXECUTE stmt1 USING @a,@b;
289 SET @a='x';
290 SET @b='c';
291 EXECUTE stmt1 USING @a,@c;
292 DEALLOCATE PREPARE stmt1;
293 SELECT * FROM t1;
294 SELECT COUNT(c1) AS total_rows FROM t1;
295 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
296 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
297 PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 = ?';
298 SET @a='a';
299 EXECUTE stmt1 USING @a;
300 SET @a='b';
301 EXECUTE stmt1 USING @a;
302 SET @a='c';
303 EXECUTE stmt1 USING @a;
304 DEALLOCATE PREPARE stmt1;
305 SELECT * FROM t1;
306 SELECT COUNT(c1) AS total_rows FROM t1;
307 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
308 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
309 DROP TABLE t1;