Debian Codegraph @

mysql-test/suite/engines/funcs/r/ps_string_null.result

raw
1 DROP TABLE IF EXISTS t1;
2 CREATE TABLE t1(c1 CHAR(100) NULL);
3 PREPARE stmt1 FROM 'INSERT INTO t1 (c1) VALUES(?)';
4 SET @a=NULL;
5 EXECUTE stmt1 USING @a;
6 SET @a='';
7 EXECUTE stmt1 USING @a;
8 SET @a='x';
9 EXECUTE stmt1 USING @a;
10 SET @a='123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.';
11 EXECUTE stmt1 USING @a;
12 DEALLOCATE PREPARE stmt1;
13 SELECT * FROM t1;
14 c1
15 NULL
16
17 x
18 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
19 SELECT COUNT(c1) AS total_rows FROM t1;
20 total_rows
21 3
22 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
23 null_rows
24 0
25 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
26 not_null_rows
27 3
28 PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE c1 = ?';
29 SET @a=NULL;
30 EXECUTE stmt1 USING @a;
31 c1
32 SET @a='';
33 EXECUTE stmt1 USING @a;
34 c1
35
36 SET @a='x';
37 EXECUTE stmt1 USING @a;
38 c1
39 x
40 DEALLOCATE PREPARE stmt1;
41 PREPARE stmt1 FROM 'UPDATE t1 SET c1 = ? WHERE c1 = ?';
42 SET @a=NULL;
43 SET @b='a';
44 EXECUTE stmt1 USING @a,@b;
45 SET @a='';
46 SET @b='b';
47 EXECUTE stmt1 USING @a,@b;
48 SET @a='x';
49 SET @b='c';
50 EXECUTE stmt1 USING @a,@c;
51 DEALLOCATE PREPARE stmt1;
52 SELECT * FROM t1;
53 c1
54 NULL
55
56 x
57 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
58 SELECT COUNT(c1) AS total_rows FROM t1;
59 total_rows
60 3
61 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
62 null_rows
63 0
64 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
65 not_null_rows
66 3
67 PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 = ?';
68 SET @a='a';
69 EXECUTE stmt1 USING @a;
70 SET @a='b';
71 EXECUTE stmt1 USING @a;
72 SET @a='c';
73 EXECUTE stmt1 USING @a;
74 DEALLOCATE PREPARE stmt1;
75 SELECT * FROM t1;
76 c1
77 NULL
78
79 x
80 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
81 SELECT COUNT(c1) AS total_rows FROM t1;
82 total_rows
83 3
84 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
85 null_rows
86 0
87 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
88 not_null_rows
89 3
90 DROP TABLE t1;
91 CREATE TABLE t1(c1 VARCHAR(100) NULL);
92 PREPARE stmt1 FROM 'INSERT INTO t1 (c1) VALUES(?)';
93 SET @a=NULL;
94 EXECUTE stmt1 USING @a;
95 SET @a='';
96 EXECUTE stmt1 USING @a;
97 SET @a='x';
98 EXECUTE stmt1 USING @a;
99 SET @a='123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.';
100 EXECUTE stmt1 USING @a;
101 DEALLOCATE PREPARE stmt1;
102 SELECT * FROM t1;
103 c1
104 NULL
105
106 x
107 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
108 SELECT COUNT(c1) AS total_rows FROM t1;
109 total_rows
110 3
111 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
112 null_rows
113 0
114 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
115 not_null_rows
116 3
117 PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE c1 = ?';
118 SET @a=NULL;
119 EXECUTE stmt1 USING @a;
120 c1
121 SET @a='';
122 EXECUTE stmt1 USING @a;
123 c1
124
125 SET @a='x';
126 EXECUTE stmt1 USING @a;
127 c1
128 x
129 DEALLOCATE PREPARE stmt1;
130 PREPARE stmt1 FROM 'UPDATE t1 SET c1 = ? WHERE c1 = ?';
131 SET @a=NULL;
132 SET @b='a';
133 EXECUTE stmt1 USING @a,@b;
134 SET @a='';
135 SET @b='b';
136 EXECUTE stmt1 USING @a,@b;
137 SET @a='x';
138 SET @b='c';
139 EXECUTE stmt1 USING @a,@c;
140 DEALLOCATE PREPARE stmt1;
141 SELECT * FROM t1;
142 c1
143 NULL
144
145 x
146 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
147 SELECT COUNT(c1) AS total_rows FROM t1;
148 total_rows
149 3
150 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
151 null_rows
152 0
153 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
154 not_null_rows
155 3
156 PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 = ?';
157 SET @a='a';
158 EXECUTE stmt1 USING @a;
159 SET @a='b';
160 EXECUTE stmt1 USING @a;
161 SET @a='c';
162 EXECUTE stmt1 USING @a;
163 DEALLOCATE PREPARE stmt1;
164 SELECT * FROM t1;
165 c1
166 NULL
167
168 x
169 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
170 SELECT COUNT(c1) AS total_rows FROM t1;
171 total_rows
172 3
173 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
174 null_rows
175 0
176 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
177 not_null_rows
178 3
179 DROP TABLE t1;
180 CREATE TABLE t1(c1 BINARY(100) NULL);
181 PREPARE stmt1 FROM 'INSERT INTO t1 (c1) VALUES(?)';
182 SET @a=NULL;
183 EXECUTE stmt1 USING @a;
184 SET @a='';
185 EXECUTE stmt1 USING @a;
186 SET @a='x';
187 EXECUTE stmt1 USING @a;
188 SET @a='123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.';
189 EXECUTE stmt1 USING @a;
190 DEALLOCATE PREPARE stmt1;
191 SELECT * FROM t1;
192 c1
193 NULL
194
195 x
196 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
197 SELECT COUNT(c1) AS total_rows FROM t1;
198 total_rows
199 3
200 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
201 null_rows
202 0
203 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
204 not_null_rows
205 3
206 PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE c1 = ?';
207 SET @a=NULL;
208 EXECUTE stmt1 USING @a;
209 c1
210 SET @a='';
211 EXECUTE stmt1 USING @a;
212 c1
213 SET @a='x';
214 EXECUTE stmt1 USING @a;
215 c1
216 DEALLOCATE PREPARE stmt1;
217 PREPARE stmt1 FROM 'UPDATE t1 SET c1 = ? WHERE c1 = ?';
218 SET @a=NULL;
219 SET @b='a';
220 EXECUTE stmt1 USING @a,@b;
221 SET @a='';
222 SET @b='b';
223 EXECUTE stmt1 USING @a,@b;
224 SET @a='x';
225 SET @b='c';
226 EXECUTE stmt1 USING @a,@c;
227 DEALLOCATE PREPARE stmt1;
228 SELECT * FROM t1;
229 c1
230 NULL
231
232 x
233 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
234 SELECT COUNT(c1) AS total_rows FROM t1;
235 total_rows
236 3
237 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
238 null_rows
239 0
240 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
241 not_null_rows
242 3
243 PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 = ?';
244 SET @a='a';
245 EXECUTE stmt1 USING @a;
246 SET @a='b';
247 EXECUTE stmt1 USING @a;
248 SET @a='c';
249 EXECUTE stmt1 USING @a;
250 DEALLOCATE PREPARE stmt1;
251 SELECT * FROM t1;
252 c1
253 NULL
254
255 x
256 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
257 SELECT COUNT(c1) AS total_rows FROM t1;
258 total_rows
259 3
260 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
261 null_rows
262 0
263 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
264 not_null_rows
265 3
266 DROP TABLE t1;
267 CREATE TABLE t1(c1 VARBINARY(100) NULL);
268 PREPARE stmt1 FROM 'INSERT INTO t1 (c1) VALUES(?)';
269 SET @a=NULL;
270 EXECUTE stmt1 USING @a;
271 SET @a='';
272 EXECUTE stmt1 USING @a;
273 SET @a='x';
274 EXECUTE stmt1 USING @a;
275 SET @a='123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.';
276 EXECUTE stmt1 USING @a;
277 DEALLOCATE PREPARE stmt1;
278 SELECT * FROM t1;
279 c1
280 NULL
281
282 x
283 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
284 SELECT COUNT(c1) AS total_rows FROM t1;
285 total_rows
286 3
287 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
288 null_rows
289 0
290 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
291 not_null_rows
292 3
293 PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE c1 = ?';
294 SET @a=NULL;
295 EXECUTE stmt1 USING @a;
296 c1
297 SET @a='';
298 EXECUTE stmt1 USING @a;
299 c1
300
301 SET @a='x';
302 EXECUTE stmt1 USING @a;
303 c1
304 x
305 DEALLOCATE PREPARE stmt1;
306 PREPARE stmt1 FROM 'UPDATE t1 SET c1 = ? WHERE c1 = ?';
307 SET @a=NULL;
308 SET @b='a';
309 EXECUTE stmt1 USING @a,@b;
310 SET @a='';
311 SET @b='b';
312 EXECUTE stmt1 USING @a,@b;
313 SET @a='x';
314 SET @b='c';
315 EXECUTE stmt1 USING @a,@c;
316 DEALLOCATE PREPARE stmt1;
317 SELECT * FROM t1;
318 c1
319 NULL
320
321 x
322 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
323 SELECT COUNT(c1) AS total_rows FROM t1;
324 total_rows
325 3
326 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
327 null_rows
328 0
329 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
330 not_null_rows
331 3
332 PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 = ?';
333 SET @a='a';
334 EXECUTE stmt1 USING @a;
335 SET @a='b';
336 EXECUTE stmt1 USING @a;
337 SET @a='c';
338 EXECUTE stmt1 USING @a;
339 DEALLOCATE PREPARE stmt1;
340 SELECT * FROM t1;
341 c1
342 NULL
343
344 x
345 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
346 SELECT COUNT(c1) AS total_rows FROM t1;
347 total_rows
348 3
349 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
350 null_rows
351 0
352 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
353 not_null_rows
354 3
355 DROP TABLE t1;
356 CREATE TABLE t1(c1 BLOB(100) NULL);
357 PREPARE stmt1 FROM 'INSERT INTO t1 (c1) VALUES(?)';
358 SET @a=NULL;
359 EXECUTE stmt1 USING @a;
360 SET @a='';
361 EXECUTE stmt1 USING @a;
362 SET @a='x';
363 EXECUTE stmt1 USING @a;
364 SET @a='123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.';
365 EXECUTE stmt1 USING @a;
366 DEALLOCATE PREPARE stmt1;
367 SELECT * FROM t1;
368 c1
369 NULL
370
371 x
372 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
373 SELECT COUNT(c1) AS total_rows FROM t1;
374 total_rows
375 3
376 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
377 null_rows
378 0
379 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
380 not_null_rows
381 3
382 PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE c1 = ?';
383 SET @a=NULL;
384 EXECUTE stmt1 USING @a;
385 c1
386 SET @a='';
387 EXECUTE stmt1 USING @a;
388 c1
389
390 SET @a='x';
391 EXECUTE stmt1 USING @a;
392 c1
393 x
394 DEALLOCATE PREPARE stmt1;
395 PREPARE stmt1 FROM 'UPDATE t1 SET c1 = ? WHERE c1 = ?';
396 SET @a=NULL;
397 SET @b='a';
398 EXECUTE stmt1 USING @a,@b;
399 SET @a='';
400 SET @b='b';
401 EXECUTE stmt1 USING @a,@b;
402 SET @a='x';
403 SET @b='c';
404 EXECUTE stmt1 USING @a,@c;
405 DEALLOCATE PREPARE stmt1;
406 SELECT * FROM t1;
407 c1
408 NULL
409
410 x
411 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
412 SELECT COUNT(c1) AS total_rows FROM t1;
413 total_rows
414 3
415 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
416 null_rows
417 0
418 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
419 not_null_rows
420 3
421 PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 = ?';
422 SET @a='a';
423 EXECUTE stmt1 USING @a;
424 SET @a='b';
425 EXECUTE stmt1 USING @a;
426 SET @a='c';
427 EXECUTE stmt1 USING @a;
428 DEALLOCATE PREPARE stmt1;
429 SELECT * FROM t1;
430 c1
431 NULL
432
433 x
434 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
435 SELECT COUNT(c1) AS total_rows FROM t1;
436 total_rows
437 3
438 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
439 null_rows
440 0
441 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
442 not_null_rows
443 3
444 DROP TABLE t1;
445 CREATE TABLE t1(c1 TEXT(100) NULL);
446 PREPARE stmt1 FROM 'INSERT INTO t1 (c1) VALUES(?)';
447 SET @a=NULL;
448 EXECUTE stmt1 USING @a;
449 SET @a='';
450 EXECUTE stmt1 USING @a;
451 SET @a='x';
452 EXECUTE stmt1 USING @a;
453 SET @a='123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.';
454 EXECUTE stmt1 USING @a;
455 DEALLOCATE PREPARE stmt1;
456 SELECT * FROM t1;
457 c1
458 NULL
459
460 x
461 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
462 SELECT COUNT(c1) AS total_rows FROM t1;
463 total_rows
464 3
465 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
466 null_rows
467 0
468 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
469 not_null_rows
470 3
471 PREPARE stmt1 FROM 'SELECT * FROM t1 WHERE c1 = ?';
472 SET @a=NULL;
473 EXECUTE stmt1 USING @a;
474 c1
475 SET @a='';
476 EXECUTE stmt1 USING @a;
477 c1
478
479 SET @a='x';
480 EXECUTE stmt1 USING @a;
481 c1
482 x
483 DEALLOCATE PREPARE stmt1;
484 PREPARE stmt1 FROM 'UPDATE t1 SET c1 = ? WHERE c1 = ?';
485 SET @a=NULL;
486 SET @b='a';
487 EXECUTE stmt1 USING @a,@b;
488 SET @a='';
489 SET @b='b';
490 EXECUTE stmt1 USING @a,@b;
491 SET @a='x';
492 SET @b='c';
493 EXECUTE stmt1 USING @a,@c;
494 DEALLOCATE PREPARE stmt1;
495 SELECT * FROM t1;
496 c1
497 NULL
498
499 x
500 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
501 SELECT COUNT(c1) AS total_rows FROM t1;
502 total_rows
503 3
504 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
505 null_rows
506 0
507 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
508 not_null_rows
509 3
510 PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 = ?';
511 SET @a='a';
512 EXECUTE stmt1 USING @a;
513 SET @a='b';
514 EXECUTE stmt1 USING @a;
515 SET @a='c';
516 EXECUTE stmt1 USING @a;
517 DEALLOCATE PREPARE stmt1;
518 SELECT * FROM t1;
519 c1
520 NULL
521
522 x
523 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
524 SELECT COUNT(c1) AS total_rows FROM t1;
525 total_rows
526 3
527 SELECT COUNT(c1) AS null_rows FROM t1 WHERE c1 IS NULL;
528 null_rows
529 0
530 SELECT COUNT(c1) AS not_null_rows FROM t1 WHERE c1 IS NOT NULL;
531 not_null_rows
532 3
533 DROP TABLE t1;