[tomato.git] / release / src / router / mysql / mysql-test / suite / engines / funcs / t / ps_number_null.test
| blob | 7c45a841cd7f54a217971644fce49b933c063ce7 |
1 --disable_warnings
2 DROP TABLE IF EXISTS t6;
3 --enable_warnings
4 CREATE TABLE t6(c1 TINYINT UNSIGNED NULL);
5 PREPARE stmt6 FROM 'INSERT INTO t6 (c1) VALUES(?)';
6 SET @a=NULL;
7 EXECUTE stmt6 USING @a;
8 SET @a=0;
9 EXECUTE stmt6 USING @a;
10 SET @a=255;
11 EXECUTE stmt6 USING @a;
12 --sorted_result
13 SELECT * FROM t6;
14 --sorted_result
15 SELECT COUNT(c1) AS total_rows FROM t6;
16 --sorted_result
17 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
18 --sorted_result
19 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
20 --sorted_result
21 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
22 DEALLOCATE PREPARE stmt6;
23 --sorted_result
24 PREPARE stmt6 FROM 'SELECT * FROM t6 WHERE c1 = ?';
25 SET @a=NULL;
26 EXECUTE stmt6 USING @a;
27 SET @a=0;
28 EXECUTE stmt6 USING @a;
29 SET @a=255;
30 EXECUTE stmt6 USING @a;
31 DEALLOCATE PREPARE stmt6;
32 PREPARE stmt6 FROM 'UPDATE t6 SET c1 = ? WHERE c1 = ?';
33 SET @a=NULL;
34 SET @b=0;
35 EXECUTE stmt6 USING @a,@b;
36 SET @a=0;
37 SET @b=NULL;
38 EXECUTE stmt6 USING @a,@b;
39 SET @a=255;
40 SET @a=201;
41 EXECUTE stmt6 USING @a,@b;
42 --sorted_result
43 SELECT * FROM t6;
44 --sorted_result
45 SELECT COUNT(c1) AS total_rows FROM t6;
46 --sorted_result
47 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
48 --sorted_result
49 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
50 --sorted_result
51 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
52 DEALLOCATE PREPARE stmt6;
53 PREPARE stmt6 FROM 'DELETE FROM t6 WHERE c1 = ?';
54 SET @a=NULL;
55 EXECUTE stmt6 USING @a;
56 SET @a=0;
57 EXECUTE stmt6 USING @a;
58 SET @a=255;
59 EXECUTE stmt6 USING @a;
60 --sorted_result
61 SELECT * FROM t6;
62 --sorted_result
63 SELECT COUNT(c1) AS total_rows FROM t6;
64 --sorted_result
65 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
66 --sorted_result
67 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
68 --sorted_result
69 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
70 DEALLOCATE PREPARE stmt6;
71 DROP TABLE t6;
72 CREATE TABLE t6(c1 SMALLINT UNSIGNED NULL);
73 PREPARE stmt6 FROM 'INSERT INTO t6 (c1) VALUES(?)';
74 SET @a=NULL;
75 EXECUTE stmt6 USING @a;
76 SET @a=0;
77 EXECUTE stmt6 USING @a;
78 SET @a=255;
79 EXECUTE stmt6 USING @a;
80 --sorted_result
81 SELECT * FROM t6;
82 --sorted_result
83 SELECT COUNT(c1) AS total_rows FROM t6;
84 --sorted_result
85 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
86 --sorted_result
87 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
88 --sorted_result
89 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
90 DEALLOCATE PREPARE stmt6;
91 --sorted_result
92 PREPARE stmt6 FROM 'SELECT * FROM t6 WHERE c1 = ?';
93 SET @a=NULL;
94 EXECUTE stmt6 USING @a;
95 SET @a=0;
96 EXECUTE stmt6 USING @a;
97 SET @a=255;
98 EXECUTE stmt6 USING @a;
99 DEALLOCATE PREPARE stmt6;
100 PREPARE stmt6 FROM 'UPDATE t6 SET c1 = ? WHERE c1 = ?';
101 SET @a=NULL;
102 SET @b=0;
103 EXECUTE stmt6 USING @a,@b;
104 SET @a=0;
105 SET @b=NULL;
106 EXECUTE stmt6 USING @a,@b;
107 SET @a=255;
108 SET @a=201;
109 EXECUTE stmt6 USING @a,@b;
110 --sorted_result
111 SELECT * FROM t6;
112 --sorted_result
113 SELECT COUNT(c1) AS total_rows FROM t6;
114 --sorted_result
115 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
116 --sorted_result
117 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
118 --sorted_result
119 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
120 DEALLOCATE PREPARE stmt6;
121 PREPARE stmt6 FROM 'DELETE FROM t6 WHERE c1 = ?';
122 SET @a=NULL;
123 EXECUTE stmt6 USING @a;
124 SET @a=0;
125 EXECUTE stmt6 USING @a;
126 SET @a=255;
127 EXECUTE stmt6 USING @a;
128 --sorted_result
129 SELECT * FROM t6;
130 --sorted_result
131 SELECT COUNT(c1) AS total_rows FROM t6;
132 --sorted_result
133 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
134 --sorted_result
135 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
136 --sorted_result
137 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
138 DEALLOCATE PREPARE stmt6;
139 DROP TABLE t6;
140 CREATE TABLE t6(c1 MEDIUMINT UNSIGNED NULL);
141 PREPARE stmt6 FROM 'INSERT INTO t6 (c1) VALUES(?)';
142 SET @a=NULL;
143 EXECUTE stmt6 USING @a;
144 SET @a=0;
145 EXECUTE stmt6 USING @a;
146 SET @a=255;
147 EXECUTE stmt6 USING @a;
148 --sorted_result
149 SELECT * FROM t6;
150 --sorted_result
151 SELECT COUNT(c1) AS total_rows FROM t6;
152 --sorted_result
153 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
154 --sorted_result
155 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
156 --sorted_result
157 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
158 DEALLOCATE PREPARE stmt6;
159 --sorted_result
160 PREPARE stmt6 FROM 'SELECT * FROM t6 WHERE c1 = ?';
161 SET @a=NULL;
162 EXECUTE stmt6 USING @a;
163 SET @a=0;
164 EXECUTE stmt6 USING @a;
165 SET @a=255;
166 EXECUTE stmt6 USING @a;
167 DEALLOCATE PREPARE stmt6;
168 PREPARE stmt6 FROM 'UPDATE t6 SET c1 = ? WHERE c1 = ?';
169 SET @a=NULL;
170 SET @b=0;
171 EXECUTE stmt6 USING @a,@b;
172 SET @a=0;
173 SET @b=NULL;
174 EXECUTE stmt6 USING @a,@b;
175 SET @a=255;
176 SET @a=201;
177 EXECUTE stmt6 USING @a,@b;
178 --sorted_result
179 SELECT * FROM t6;
180 --sorted_result
181 SELECT COUNT(c1) AS total_rows FROM t6;
182 --sorted_result
183 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
184 --sorted_result
185 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
186 --sorted_result
187 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
188 DEALLOCATE PREPARE stmt6;
189 PREPARE stmt6 FROM 'DELETE FROM t6 WHERE c1 = ?';
190 SET @a=NULL;
191 EXECUTE stmt6 USING @a;
192 SET @a=0;
193 EXECUTE stmt6 USING @a;
194 SET @a=255;
195 EXECUTE stmt6 USING @a;
196 --sorted_result
197 SELECT * FROM t6;
198 --sorted_result
199 SELECT COUNT(c1) AS total_rows FROM t6;
200 --sorted_result
201 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
202 --sorted_result
203 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
204 --sorted_result
205 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
206 DEALLOCATE PREPARE stmt6;
207 DROP TABLE t6;
208 CREATE TABLE t6(c1 INT UNSIGNED NULL);
209 PREPARE stmt6 FROM 'INSERT INTO t6 (c1) VALUES(?)';
210 SET @a=NULL;
211 EXECUTE stmt6 USING @a;
212 SET @a=0;
213 EXECUTE stmt6 USING @a;
214 SET @a=255;
215 EXECUTE stmt6 USING @a;
216 --sorted_result
217 SELECT * FROM t6;
218 --sorted_result
219 SELECT COUNT(c1) AS total_rows FROM t6;
220 --sorted_result
221 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
222 --sorted_result
223 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
224 --sorted_result
225 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
226 DEALLOCATE PREPARE stmt6;
227 --sorted_result
228 PREPARE stmt6 FROM 'SELECT * FROM t6 WHERE c1 = ?';
229 SET @a=NULL;
230 EXECUTE stmt6 USING @a;
231 SET @a=0;
232 EXECUTE stmt6 USING @a;
233 SET @a=255;
234 EXECUTE stmt6 USING @a;
235 DEALLOCATE PREPARE stmt6;
236 PREPARE stmt6 FROM 'UPDATE t6 SET c1 = ? WHERE c1 = ?';
237 SET @a=NULL;
238 SET @b=0;
239 EXECUTE stmt6 USING @a,@b;
240 SET @a=0;
241 SET @b=NULL;
242 EXECUTE stmt6 USING @a,@b;
243 SET @a=255;
244 SET @a=201;
245 EXECUTE stmt6 USING @a,@b;
246 --sorted_result
247 SELECT * FROM t6;
248 --sorted_result
249 SELECT COUNT(c1) AS total_rows FROM t6;
250 --sorted_result
251 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
252 --sorted_result
253 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
254 --sorted_result
255 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
256 DEALLOCATE PREPARE stmt6;
257 PREPARE stmt6 FROM 'DELETE FROM t6 WHERE c1 = ?';
258 SET @a=NULL;
259 EXECUTE stmt6 USING @a;
260 SET @a=0;
261 EXECUTE stmt6 USING @a;
262 SET @a=255;
263 EXECUTE stmt6 USING @a;
264 --sorted_result
265 SELECT * FROM t6;
266 --sorted_result
267 SELECT COUNT(c1) AS total_rows FROM t6;
268 --sorted_result
269 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
270 --sorted_result
271 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
272 --sorted_result
273 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
274 DEALLOCATE PREPARE stmt6;
275 DROP TABLE t6;
276 CREATE TABLE t6(c1 INTEGER UNSIGNED NULL);
277 PREPARE stmt6 FROM 'INSERT INTO t6 (c1) VALUES(?)';
278 SET @a=NULL;
279 EXECUTE stmt6 USING @a;
280 SET @a=0;
281 EXECUTE stmt6 USING @a;
282 SET @a=255;
283 EXECUTE stmt6 USING @a;
284 --sorted_result
285 SELECT * FROM t6;
286 --sorted_result
287 SELECT COUNT(c1) AS total_rows FROM t6;
288 --sorted_result
289 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
290 --sorted_result
291 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
292 --sorted_result
293 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
294 DEALLOCATE PREPARE stmt6;
295 --sorted_result
296 PREPARE stmt6 FROM 'SELECT * FROM t6 WHERE c1 = ?';
297 SET @a=NULL;
298 EXECUTE stmt6 USING @a;
299 SET @a=0;
300 EXECUTE stmt6 USING @a;
301 SET @a=255;
302 EXECUTE stmt6 USING @a;
303 DEALLOCATE PREPARE stmt6;
304 PREPARE stmt6 FROM 'UPDATE t6 SET c1 = ? WHERE c1 = ?';
305 SET @a=NULL;
306 SET @b=0;
307 EXECUTE stmt6 USING @a,@b;
308 SET @a=0;
309 SET @b=NULL;
310 EXECUTE stmt6 USING @a,@b;
311 SET @a=255;
312 SET @a=201;
313 EXECUTE stmt6 USING @a,@b;
314 --sorted_result
315 SELECT * FROM t6;
316 --sorted_result
317 SELECT COUNT(c1) AS total_rows FROM t6;
318 --sorted_result
319 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
320 --sorted_result
321 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
322 --sorted_result
323 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
324 DEALLOCATE PREPARE stmt6;
325 PREPARE stmt6 FROM 'DELETE FROM t6 WHERE c1 = ?';
326 SET @a=NULL;
327 EXECUTE stmt6 USING @a;
328 SET @a=0;
329 EXECUTE stmt6 USING @a;
330 SET @a=255;
331 EXECUTE stmt6 USING @a;
332 --sorted_result
333 SELECT * FROM t6;
334 --sorted_result
335 SELECT COUNT(c1) AS total_rows FROM t6;
336 --sorted_result
337 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
338 --sorted_result
339 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
340 --sorted_result
341 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
342 DEALLOCATE PREPARE stmt6;
343 DROP TABLE t6;
344 CREATE TABLE t6(c1 BIGINT UNSIGNED NULL);
345 PREPARE stmt6 FROM 'INSERT INTO t6 (c1) VALUES(?)';
346 SET @a=NULL;
347 EXECUTE stmt6 USING @a;
348 SET @a=0;
349 EXECUTE stmt6 USING @a;
350 SET @a=255;
351 EXECUTE stmt6 USING @a;
352 --sorted_result
353 SELECT * FROM t6;
354 --sorted_result
355 SELECT COUNT(c1) AS total_rows FROM t6;
356 --sorted_result
357 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
358 --sorted_result
359 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
360 --sorted_result
361 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
362 DEALLOCATE PREPARE stmt6;
363 --sorted_result
364 PREPARE stmt6 FROM 'SELECT * FROM t6 WHERE c1 = ?';
365 SET @a=NULL;
366 EXECUTE stmt6 USING @a;
367 SET @a=0;
368 EXECUTE stmt6 USING @a;
369 SET @a=255;
370 EXECUTE stmt6 USING @a;
371 DEALLOCATE PREPARE stmt6;
372 PREPARE stmt6 FROM 'UPDATE t6 SET c1 = ? WHERE c1 = ?';
373 SET @a=NULL;
374 SET @b=0;
375 EXECUTE stmt6 USING @a,@b;
376 SET @a=0;
377 SET @b=NULL;
378 EXECUTE stmt6 USING @a,@b;
379 SET @a=255;
380 SET @a=201;
381 EXECUTE stmt6 USING @a,@b;
382 --sorted_result
383 SELECT * FROM t6;
384 --sorted_result
385 SELECT COUNT(c1) AS total_rows FROM t6;
386 --sorted_result
387 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
388 --sorted_result
389 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
390 --sorted_result
391 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
392 DEALLOCATE PREPARE stmt6;
393 PREPARE stmt6 FROM 'DELETE FROM t6 WHERE c1 = ?';
394 SET @a=NULL;
395 EXECUTE stmt6 USING @a;
396 SET @a=0;
397 EXECUTE stmt6 USING @a;
398 SET @a=255;
399 EXECUTE stmt6 USING @a;
400 --sorted_result
401 SELECT * FROM t6;
402 --sorted_result
403 SELECT COUNT(c1) AS total_rows FROM t6;
404 --sorted_result
405 SELECT COUNT(c1) AS positive_rows FROM t6 WHERE c1 > 0;
406 --sorted_result
407 SELECT COUNT(c1) AS null_rows FROM t6 WHERE c1 IS NULL;
408 --sorted_result
409 SELECT COUNT(c1) AS not_null_rows FROM t6 WHERE c1 IS NOT NULL;
410 DEALLOCATE PREPARE stmt6;
411 DROP TABLE t6;