Debian Codegraph @

mysql-test/suite/binlog/include/mysqlbinlog_row_engine.inc

raw
1 # mysqlbinlog_row.test
2 #
3 # Show that mysqlbinlog displays human readable comments to
4 # row-based log events.
5 #
6 # Procedure:
7 # Create a table that represents all-known types in 5.1.
8 # Write rows that contain the mins, maxes, and NULL for each type.
9 # Write a random or "problematic" value (i.e. one that might require
10 # escaping if it's represented as a string-y type) for each type.
11 # Update each of these rows.
12 # Delete each of these rows.
13 # Inspect the binlog.
14 #
15 # Bug#31455 - mysqlbinlog don't print user readable info about RBR events
16 #
17
18 --source include/have_log_bin.inc
19 set sql_mode="";
20
21 SET NAMES 'utf8';
22 #SHOW VARIABLES LIKE 'character_set%';
23
24
25 --echo #
26 --echo # Preparatory cleanup.
27 --echo #
28 --disable_warnings
29 DROP TABLE IF EXISTS t1, t2, t3;
30 --enable_warnings
31
32 --echo #
33 --echo # We need a fixed timestamp to avoid varying results.
34 --echo #
35 SET timestamp=1000000000;
36
37 --echo #
38 --echo # ===================================================
39 --echo # Test #1 - Insert/update/delete with all data types.
40 --echo # ===================================================
41 --echo #
42 --echo # Delete all existing binary logs.
43 --echo #
44 RESET MASTER;
45
46 --echo #
47 --echo # Create a test table with all data types.
48 --echo #
49 eval CREATE TABLE t1 (
50 c01 BIT,
51 c02 BIT(64),
52 c03 TINYINT,
53 c04 TINYINT UNSIGNED,
54 c05 TINYINT ZEROFILL,
55 c06 BOOL,
56 c07 SMALLINT,
57 c08 SMALLINT UNSIGNED,
58 c09 SMALLINT ZEROFILL,
59 c10 MEDIUMINT,
60 c11 MEDIUMINT UNSIGNED,
61 c12 MEDIUMINT ZEROFILL,
62 c13 INT,
63 c14 INT UNSIGNED,
64 c15 INT ZEROFILL,
65 c16 BIGINT,
66 c17 BIGINT UNSIGNED,
67 c18 BIGINT ZEROFILL,
68 c19 FLOAT,
69 c20 FLOAT UNSIGNED,
70 c21 FLOAT ZEROFILL,
71 c22 DOUBLE,
72 c23 DOUBLE UNSIGNED,
73 c24 DOUBLE ZEROFILL,
74 c25 DECIMAL,
75 c26 DECIMAL UNSIGNED,
76 c27 DECIMAL ZEROFILL,
77 #
78 c28 DATE,
79 c29 DATETIME,
80 c30 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
81 c31 TIME,
82 c32 YEAR,
83 #
84 c33 CHAR,
85 c34 CHAR(0),
86 c35 CHAR(1),
87 c36 CHAR(255),
88 c37 NATIONAL CHAR,
89 c38 NATIONAL CHAR(0),
90 c39 NATIONAL CHAR(1),
91 c40 NATIONAL CHAR(255),
92 c41 CHAR CHARACTER SET UCS2,
93 c42 CHAR(0) CHARACTER SET UCS2,
94 c43 CHAR(1) CHARACTER SET UCS2,
95 c44 CHAR(255) CHARACTER SET UCS2,
96 #
97 c45 VARCHAR(0),
98 c46 VARCHAR(1),
99 c47 VARCHAR(255),
100 c48 VARCHAR(261),
101 c49 NATIONAL VARCHAR(0),
102 c50 NATIONAL VARCHAR(1),
103 c51 NATIONAL VARCHAR(255),
104 c52 NATIONAL VARCHAR(261),
105 c53 VARCHAR(0) CHARACTER SET UCS2,
106 c54 VARCHAR(1) CHARACTER SET UCS2,
107 c55 VARCHAR(255) CHARACTER SET UCS2,
108 c56 VARCHAR(261) CHARACTER SET UCS2,
109 #
110 c57 BINARY,
111 c58 BINARY(0),
112 c59 BINARY(1),
113 c60 BINARY(255),
114 #
115 c61 VARBINARY(0),
116 c62 VARBINARY(1),
117 c63 VARBINARY(255),
118 c64 VARBINARY(261),
119 #
120 c65 TINYBLOB,
121 c66 TINYTEXT,
122 c67 TINYTEXT CHARACTER SET UCS2,
123 c68 BLOB,
124 c69 TEXT,
125 c70 TEXT CHARACTER SET UCS2,
126 c71 MEDIUMBLOB,
127 c72 MEDIUMTEXT,
128 c73 MEDIUMTEXT CHARACTER SET UCS2,
129 c74 LONGBLOB,
130 c75 LONGTEXT,
131 c76 LONGTEXT CHARACTER SET UCS2,
132 #
133 c77 ENUM('a','b','c'),
134 c78 SET('a','b','c'),
135 #
136 crn INT -- row number
137 ) ENGINE=$engine_type DEFAULT CHARSET latin1;
138
139 --echo #
140 --echo # Insert minimum values.
141 --echo #
142 INSERT INTO t1 VALUES (
143 b'0', -- c01
144 b'0000000000000000000000000000000000000000000000000000000000000000', -- c02
145 -128, -- c03
146 0, -- c04
147 000, -- c05
148 false, -- c06
149 -32768, -- c07
150 0, -- c08
151 00000, -- c09
152 -8388608, -- c10
153 0, -- c11
154 00000000, -- c12
155 -2147483648, -- c13
156 0, -- c14
157 0000000000, -- c15
158 -9223372036854775808, -- c16
159 0, -- c17
160 00000000000000000000, -- c18
161 -3.402823466E+38, -- c19
162 1.175494351E-38, -- c20
163 000000000000, -- c21
164 -1.7976931348623E+308, -- c22 three digits cut for ps-protocol
165 2.2250738585072E-308, -- c23 three digits cut for ps-protocol
166 0000000000000000000000, -- c24
167 -9999999999, -- c25
168 0, -- c26
169 0000000000, -- c27
170 #
171 '1000-01-01', -- c28
172 '1000-01-01 00:00:00', -- c29
173 '1970-01-02 00:00:01', -- c30 one day later due to timezone issues
174 '-838:59:59', -- c31
175 '1901', -- c32
176 #
177 '', -- c33
178 '', -- c34
179 '', -- c35
180 '', -- c36
181 '', -- c37
182 '', -- c38
183 '', -- c39
184 '', -- c40
185 '', -- c41
186 '', -- c42
187 '', -- c43
188 '', -- c44
189 #
190 '', -- c45
191 '', -- c46
192 '', -- c47
193 '', -- c48
194 '', -- c49
195 '', -- c50
196 '', -- c51
197 '', -- c52
198 '', -- c53
199 '', -- c54
200 '', -- c55
201 '', -- c56
202 #
203 '', -- c57
204 '', -- c58
205 '', -- c59
206 '', -- c60
207 #
208 '', -- c61
209 '', -- c62
210 '', -- c63
211 '', -- c64
212 #
213 '', -- c65
214 '', -- c66
215 '', -- c67
216 '', -- c68
217 '', -- c69
218 '', -- c70
219 '', -- c71
220 '', -- c72
221 '', -- c73
222 '', -- c74
223 '', -- c75
224 '', -- c76
225 #
226 'a', -- c77
227 '', -- c78
228 #
229 1 -- crn -- row number
230 );
231
232 --echo #
233 --echo # Insert maximum values.
234 --echo #
235 INSERT INTO t1 VALUES (
236 b'1', -- c01
237 b'1111111111111111111111111111111111111111111111111111111111111111', -- c02
238 127, -- c03
239 255, -- c04
240 255, -- c05
241 true, -- c06
242 32767, -- c07
243 65535, -- c08
244 65535, -- c09
245 8388607, -- c10
246 16777215, -- c11
247 16777215, -- c12
248 2147483647, -- c13
249 4294967295, -- c14
250 4294967295, -- c15
251 9223372036854775807, -- c16
252 18446744073709551615, -- c17
253 18446744073709551615, -- c18
254 3.402823466E+38, -- c19
255 3.402823466E+38, -- c20
256 3.402823466E+38, -- c21
257 1.7976931348623E+308, -- c22 three digits cut for ps-protocol
258 1.7976931348623E+308, -- c23 three digits cut for ps-protocol
259 1.7976931348623E+308, -- c24 three digits cut for ps-protocol
260 9999999999, -- c25
261 9999999999, -- c26
262 9999999999, -- c27
263 #
264 '9999-12-31', -- c28
265 '9999-12-31 23:59:59', -- c29
266 '2038-01-08 03:14:07', -- c30 one day earlier due to timezone issues
267 '838:59:59', -- c31
268 '2155', -- c32
269 #
270 x'ff', -- c33
271 '', -- c34
272 x'ff', -- c35
273 REPEAT(x'ff',255), -- c36
274 _utf8 x'efbfbf', -- c37
275 '', -- c38
276 _utf8 x'efbfbf', -- c39
277 REPEAT(_utf8 x'efbfbf',255), -- c40
278 _ucs2 x'ffff', -- c41
279 '', -- c42
280 _ucs2 x'ffff', -- c43
281 REPEAT(_ucs2 x'ffff',255), -- c44
282 #
283 '', -- c45
284 x'ff', -- c46
285 REPEAT(x'ff',255), -- c47
286 REPEAT(x'ff',261), -- c48
287 '', -- c49
288 _utf8 x'efbfbf', -- c50
289 REPEAT(_utf8 x'efbfbf',255), -- c51
290 REPEAT(_utf8 x'efbfbf',261), -- c52
291 '', -- c53
292 _ucs2 x'ffff', -- c54
293 REPEAT(_ucs2 x'ffff',255), -- c55
294 REPEAT(_ucs2 x'ffff',261), -- c56
295 #
296 x'ff', -- c57
297 '', -- c58
298 x'ff', -- c59
299 REPEAT(x'ff',255), -- c60
300 #
301 '', -- c61
302 x'ff', -- c62
303 REPEAT(x'ff',255), -- c63
304 REPEAT(x'ff',261), -- c64
305 #
306 'tinyblob', -- c65 not using maximum value here
307 'tinytext', -- c66 not using maximum value here
308 'tinytext-ucs2', -- c67 not using maximum value here
309 'blob', -- c68 not using maximum value here
310 'text', -- c69 not using maximum value here
311 'text-ucs2', -- c70 not using maximum value here
312 'mediumblob', -- c71 not using maximum value here
313 'mediumtext', -- c72 not using maximum value here
314 'mediumtext-ucs2', -- c73 not using maximum value here
315 'longblob', -- c74 not using maximum value here
316 'longtext', -- c75 not using maximum value here
317 'longtext-ucs2', -- c76 not using maximum value here
318 #
319 'c', -- c77
320 'a,b,c', -- c78
321 #
322 2 -- crn -- row number
323 );
324
325 --echo #
326 --echo # Insert a row with NULL values and one with arbitrary values.
327 --echo #
328 INSERT INTO t1 VALUES (
329 NULL, -- c01
330 NULL, -- c02
331 NULL, -- c03
332 NULL, -- c04
333 NULL, -- c05
334 NULL, -- c06
335 NULL, -- c07
336 NULL, -- c08
337 NULL, -- c09
338 NULL, -- c10
339 NULL, -- c11
340 NULL, -- c12
341 NULL, -- c13
342 NULL, -- c14
343 NULL, -- c15
344 NULL, -- c16
345 NULL, -- c17
346 NULL, -- c18
347 NULL, -- c19
348 NULL, -- c20
349 NULL, -- c21
350 NULL, -- c22
351 NULL, -- c23
352 NULL, -- c24
353 NULL, -- c25
354 NULL, -- c26
355 NULL, -- c27
356 #
357 NULL, -- c28
358 NULL, -- c29
359 NULL, -- c30
360 NULL, -- c31
361 NULL, -- c32
362 #
363 NULL, -- c33
364 NULL, -- c34
365 NULL, -- c35
366 NULL, -- c36
367 NULL, -- c37
368 NULL, -- c38
369 NULL, -- c39
370 NULL, -- c40
371 NULL, -- c41
372 NULL, -- c42
373 NULL, -- c43
374 NULL, -- c44
375 #
376 NULL, -- c45
377 NULL, -- c46
378 NULL, -- c47
379 NULL, -- c48
380 NULL, -- c49
381 NULL, -- c50
382 NULL, -- c51
383 NULL, -- c52
384 NULL, -- c53
385 NULL, -- c54
386 NULL, -- c55
387 NULL, -- c56
388 #
389 NULL, -- c57
390 NULL, -- c58
391 NULL, -- c59
392 NULL, -- c60
393 #
394 NULL, -- c61
395 NULL, -- c62
396 NULL, -- c63
397 NULL, -- c64
398 #
399 NULL, -- c65
400 NULL, -- c66
401 NULL, -- c67
402 NULL, -- c68
403 NULL, -- c69
404 NULL, -- c70
405 NULL, -- c71
406 NULL, -- c72
407 NULL, -- c73
408 NULL, -- c74
409 NULL, -- c75
410 NULL, -- c76
411 #
412 NULL, -- c77
413 NULL, -- c78
414 #
415 3 -- crn -- row number
416 ), (
417 b'1', -- c01
418 b'1111111111111111111111111111111111111111111111111111111111111111', -- c02
419 127, -- c03
420 0, -- c04
421 001, -- c05
422 true, -- c06
423 32767, -- c07
424 0, -- c08
425 00001, -- c09
426 8388607, -- c10
427 0, -- c11
428 00000001, -- c12
429 2147483647, -- c13
430 0, -- c14
431 0000000001, -- c15
432 9223372036854775807, -- c16
433 0, -- c17
434 00000000000000000001, -- c18
435 -1.175494351E-38, -- c19
436 1.175494351E-38, -- c20
437 000000000000001, -- c21
438 -2.2250738585072E-308, -- c22
439 2.2250738585072E-308, -- c23
440 00000000000000000000001, -- c24
441 -9999999999, -- c25
442 9999999999, -- c26
443 0000000001, -- c27
444 #
445 '2008-08-04', -- c28
446 '2008-08-04 16:18:06', -- c29
447 '2008-08-04 16:18:24', -- c30
448 '16:18:47', -- c31
449 '2008', -- c32
450 #
451 'a', -- c33
452 '', -- c34
453 'e', -- c35
454 REPEAT('i',255), -- c36
455 _utf8 x'c3a4', -- c37
456 '', -- c38
457 _utf8 x'c3b6', -- c39
458 REPEAT(_utf8 x'c3bc',255), -- c40
459 _ucs2 x'00e4', -- c41
460 '', -- c42
461 _ucs2 x'00f6', -- c43
462 REPEAT(_ucs2 x'00fc',255), -- c44
463 #
464 '', -- c45
465 'a', -- c46
466 REPEAT('e',255), -- c47
467 REPEAT('i',261), -- c48
468 '', -- c49
469 _utf8 x'c3a4', -- c50
470 REPEAT(_utf8 x'c3b6',255), -- c51
471 REPEAT(_utf8 x'c3bc',261), -- c52
472 '', -- c53
473 _ucs2 x'00e4', -- c54
474 REPEAT(_ucs2 x'00f6',255), -- c55
475 REPEAT(_ucs2 x'00fc',261), -- c56
476 #
477 '0', -- c57
478 '', -- c58
479 '1', -- c59
480 REPEAT('1',255), -- c60
481 #
482 '', -- c61
483 'b', -- c62
484 REPEAT('c',255), -- c63
485 REPEAT('\'',261), -- c64
486 #
487 'tinyblob', -- c65
488 'tinytext', -- c66
489 'tinytext-ucs2', -- c67
490 'blob', -- c68
491 'text', -- c69
492 'text-ucs2', -- c70
493 'mediumblob', -- c71
494 'mediumtext', -- c72
495 'mediumtext-ucs2', -- c73
496 'longblob', -- c74
497 'longtext', -- c75
498 'longtext-ucs2', -- c76
499 #
500 'b', -- c77
501 'b,c', -- c78
502 #
503 4 -- crn -- row number
504 );
505
506 --echo #
507 --echo # Show what we have in the table.
508 --echo # Do not display bit type output. It's binary and confuses diff.
509 --echo # Also BINARY with nul-bytes should be avoided.
510 --echo #
511 --replace_column 1 # 2 # 57 # 58 # 59 # 60 #
512 query_vertical SELECT * FROM t1;
513
514 --echo #
515 --echo # NOTE: For matching FLOAT and DOUBLE values in WHERE conditions,
516 --echo # don't use exact match, but < or > and tweak the numbers a bit.
517 --echo #
518 --echo # Show how much rows are affected by each statement.
519 --echo #
520 --enable_info
521
522 --echo #
523 --echo # Update min values to max values.
524 --echo #
525 UPDATE t1 SET
526 c01 = b'1',
527 c02 = b'1111111111111111111111111111111111111111111111111111111111111111',
528 c03 = 127,
529 c04 = 255,
530 c05 = 255,
531 c06 = true,
532 c07 = 32767,
533 c08 = 65535,
534 c09 = 65535,
535 c10 = 8388607,
536 c11 = 16777215,
537 c12 = 16777215,
538 c13 = 2147483647,
539 c14 = 4294967295,
540 c15 = 4294967295,
541 c16 = 9223372036854775807,
542 c17 = 18446744073709551615,
543 c18 = 18446744073709551615,
544 c19 = 3.402823466E+38,
545 c20 = 3.402823466E+38,
546 c21 = 3.402823466E+38,
547 c22 = 1.7976931348623E+308,
548 c23 = 1.7976931348623E+308,
549 c24 = 1.7976931348623E+308,
550 c25 = 9999999999,
551 c26 = 9999999999,
552 c27 = 9999999999,
553 #
554 c28 = '9999-12-31',
555 c29 = '9999-12-31 23:59:59',
556 c30 = '2038-01-08 03:14:07',
557 c31 = '838:59:59',
558 c32 = '2155',
559 #
560 c33 = x'ff',
561 c34 = '',
562 c35 = x'ff',
563 c36 = REPEAT(x'ff',255),
564 c37 = _utf8 x'efbfbf',
565 c38 = '',
566 c39 = _utf8 x'efbfbf',
567 c40 = REPEAT(_utf8 x'efbfbf',255),
568 c41 = _ucs2 x'ffff',
569 c42 = '',
570 c43 = _ucs2 x'ffff',
571 c44 = REPEAT(_ucs2 x'ffff',255),
572 #
573 c45 = '',
574 c46 = x'ff',
575 c47 = REPEAT(x'ff',255),
576 c48 = REPEAT(x'ff',261),
577 c49 = '',
578 c50 = _utf8 x'efbfbf',
579 c51 = REPEAT(_utf8 x'efbfbf',255),
580 c52 = REPEAT(_utf8 x'efbfbf',261),
581 c53 = '',
582 c54 = _ucs2 x'ffff',
583 c55 = REPEAT(_ucs2 x'ffff',255),
584 c56 = REPEAT(_ucs2 x'ffff',261),
585 #
586 c57 = x'ff',
587 c58 = '',
588 c59 = x'ff',
589 c60 = REPEAT(x'ff',255),
590 #
591 c61 = '',
592 c62 = x'ff',
593 c63 = REPEAT(x'ff',255),
594 c64 = REPEAT(x'ff',261),
595 #
596 c65 = 'tinyblob',
597 c66 = 'tinytext',
598 c67 = 'tinytext-ucs2',
599 c68 = 'blob',
600 c69 = 'text',
601 c70 = 'text-ucs2',
602 c71 = 'mediumblob',
603 c72 = 'mediumtext',
604 c73 = 'mediumtext-ucs2',
605 c74 = 'longblob',
606 c75 = 'longtext',
607 c76 = 'longtext-ucs2',
608 #
609 c77 = 'c',
610 c78 = 'a,b,c',
611 #
612 crn = crn
613 #
614 WHERE
615 #
616 c01 = b'0' AND
617 c02 = b'0000000000000000000000000000000000000000000000000000000000000000' AND
618 c03 = -128 AND
619 c04 = 0 AND
620 c05 = 000 AND
621 c06 = false AND
622 c07 = -32768 AND
623 c08 = 0 AND
624 c09 = 00000 AND
625 c10 = -8388608 AND
626 c11 = 0 AND
627 c12 = 00000000 AND
628 c13 = -2147483648 AND
629 c14 = 0 AND
630 c15 = 0000000000 AND
631 c16 = -9223372036854775808 AND
632 c17 = 0 AND
633 c18 = 00000000000000000000 AND
634 c19 < -3.402823465E+38 AND
635 c20 < 1.175494352E-38 AND
636 c21 = 000000000000 AND
637 c22 < -1.7976931348622E+308 AND
638 c23 < 2.2250738585073E-308 AND
639 c24 = 0000000000000000000000 AND
640 c25 = -9999999999 AND
641 c26 = 0 AND
642 c27 = 0000000000 AND
643 #
644 c28 = '1000-01-01' AND
645 c29 = '1000-01-01 00:00:00' AND
646 c30 = '1970-01-02 00:00:01' AND
647 c31 = '-838:59:59' AND
648 c32 = '1901' AND
649 #
650 c33 = '' AND
651 c34 = '' AND
652 c35 = '' AND
653 c36 = '' AND
654 c37 = '' AND
655 c38 = '' AND
656 c39 = '' AND
657 c40 = '' AND
658 c41 = '' AND
659 c42 = '' AND
660 c43 = '' AND
661 c44 = '' AND
662 #
663 c45 = '' AND
664 c46 = '' AND
665 c47 = '' AND
666 c48 = '' AND
667 c49 = '' AND
668 c50 = '' AND
669 c51 = '' AND
670 c52 = '' AND
671 c53 = '' AND
672 c54 = '' AND
673 c55 = '' AND
674 c56 = '' AND
675 #
676 # this does not reproduce the inserted value: c57 = '' AND
677 c58 = '' AND
678 # this does not reproduce the inserted value: c59 = '' AND
679 # this does not reproduce the inserted value: c60 = '' AND
680 #
681 c61 = '' AND
682 c62 = '' AND
683 c63 = '' AND
684 c64 = '' AND
685 #
686 c65 = '' AND
687 c66 = '' AND
688 c67 = '' AND
689 c68 = '' AND
690 c69 = '' AND
691 c70 = '' AND
692 c71 = '' AND
693 c72 = '' AND
694 c73 = '' AND
695 c74 = '' AND
696 c75 = '' AND
697 c76 = '' AND
698 #
699 c77 = 'a' AND
700 c78 = '' AND
701 #
702 crn = 1;
703
704 --echo #
705 --echo # Update max values to min values.
706 --echo #
707 UPDATE t1 SET
708 c01 = b'0',
709 c02 = b'0000000000000000000000000000000000000000000000000000000000000000',
710 c03 = -128,
711 c04 = 0,
712 c05 = 000,
713 c06 = false,
714 c07 = -32768,
715 c08 = 0,
716 c09 = 00000,
717 c10 = -8388608,
718 c11 = 0,
719 c12 = 00000000,
720 c13 = -2147483648,
721 c14 = 0,
722 c15 = 0000000000,
723 c16 = -9223372036854775808,
724 c17 = 0,
725 c18 = 00000000000000000000,
726 c19 = -3.402823466E+38,
727 c20 = 1.175494351E-38,
728 c21 = 000000000000,
729 c22 = -1.7976931348623E+308,
730 c23 = 2.2250738585072E-308,
731 c24 = 0000000000000000000000,
732 c25 = -9999999999,
733 c26 = 0,
734 c27 = 0000000000,
735 #
736 c28 = '1000-01-01',
737 c29 = '1000-01-01 00:00:00',
738 c30 = '1970-01-02 00:00:01',
739 c31 = '-838:59:59',
740 c32 = '1901',
741 #
742 c33 = '',
743 c34 = '',
744 c35 = '',
745 c36 = '',
746 c37 = '',
747 c38 = '',
748 c39 = '',
749 c40 = '',
750 c41 = '',
751 c42 = '',
752 c43 = '',
753 c44 = '',
754 #
755 c45 = '',
756 c46 = '',
757 c47 = '',
758 c48 = '',
759 c49 = '',
760 c50 = '',
761 c51 = '',
762 c52 = '',
763 c53 = '',
764 c54 = '',
765 c55 = '',
766 c56 = '',
767 #
768 c57 = '',
769 c58 = '',
770 c59 = '',
771 c60 = '',
772 #
773 c61 = '',
774 c62 = '',
775 c63 = '',
776 c64 = '',
777 #
778 c65 = '',
779 c66 = '',
780 c67 = '',
781 c68 = '',
782 c69 = '',
783 c70 = '',
784 c71 = '',
785 c72 = '',
786 c73 = '',
787 c74 = '',
788 c75 = '',
789 c76 = '',
790 #
791 c77 = 'a',
792 c78 = '',
793 #
794 crn = crn
795 #
796 WHERE
797 #
798 c01 = b'1' AND
799 # the below does not reproduce the inserted value:
800 #c02 = b'1111111111111111111111111111111111111111111111111111111111111111' AND
801 c03 = 127 AND
802 c04 = 255 AND
803 c05 = 255 AND
804 c06 = true AND
805 c07 = 32767 AND
806 c08 = 65535 AND
807 c09 = 65535 AND
808 c10 = 8388607 AND
809 c11 = 16777215 AND
810 c12 = 16777215 AND
811 c13 = 2147483647 AND
812 c14 = 4294967295 AND
813 c15 = 4294967295 AND
814 c16 = 9223372036854775807 AND
815 c17 = 18446744073709551615 AND
816 c18 = 18446744073709551615 AND
817 c19 > 3.402823465E+38 AND
818 c20 > 3.402823465E+38 AND
819 c21 > 3.402823465E+38 AND
820 c22 > 1.7976931348622E+308 AND
821 c23 > 1.7976931348622E+308 AND
822 c24 > 1.7976931348622E+308 AND
823 c25 = 9999999999 AND
824 c26 = 9999999999 AND
825 c27 = 9999999999 AND
826 #
827 c28 = '9999-12-31' AND
828 c29 = '9999-12-31 23:59:59' AND
829 c30 = '2038-01-08 03:14:07' AND
830 c31 = '838:59:59' AND
831 c32 = '2155' AND
832 #
833 c33 = x'ff' AND
834 c34 = '' AND
835 c35 = x'ff' AND
836 c36 = REPEAT(x'ff',255) AND
837 c37 = _utf8 x'efbfbf' AND
838 c38 = '' AND
839 c39 = _utf8 x'efbfbf' AND
840 c40 = REPEAT(_utf8 x'efbfbf',255) AND
841 c41 = _ucs2 x'ffff' AND
842 c42 = '' AND
843 c43 = _ucs2 x'ffff' AND
844 c44 = REPEAT(_ucs2 x'ffff',255) AND
845 #
846 c45 = '' AND
847 c46 = x'ff' AND
848 c47 = REPEAT(x'ff',255) AND
849 c48 = REPEAT(x'ff',261) AND
850 c49 = '' AND
851 c50 = _utf8 x'efbfbf' AND
852 c51 = REPEAT(_utf8 x'efbfbf',255) AND
853 c52 = REPEAT(_utf8 x'efbfbf',261) AND
854 c53 = '' AND
855 c54 = _ucs2 x'ffff' AND
856 c55 = REPEAT(_ucs2 x'ffff',255) AND
857 c56 = REPEAT(_ucs2 x'ffff',261) AND
858 #
859 c57 = x'ff' AND
860 c58 = '' AND
861 c59 = x'ff' AND
862 c60 = REPEAT(x'ff',255) AND
863 #
864 c61 = '' AND
865 c62 = x'ff' AND
866 c63 = REPEAT(x'ff',255) AND
867 c64 = REPEAT(x'ff',261) AND
868 #
869 c65 = 'tinyblob' AND
870 c66 = 'tinytext' AND
871 c67 = 'tinytext-ucs2' AND
872 c68 = 'blob' AND
873 c69 = 'text' AND
874 c70 = 'text-ucs2' AND
875 c71 = 'mediumblob' AND
876 c72 = 'mediumtext' AND
877 c73 = 'mediumtext-ucs2' AND
878 c74 = 'longblob' AND
879 c75 = 'longtext' AND
880 c76 = 'longtext-ucs2' AND
881 #
882 c77 = 'c' AND
883 c78 = 'a,b,c' AND
884 #
885 crn = 2;
886
887 --echo #
888 --echo # Update NULL values to arbitrary values.
889 --echo #
890 UPDATE t1 SET
891 c01 = b'1',
892 c02 = b'1111111111111111111111111111111111111111111111111111111111111111',
893 c03 = 127,
894 c04 = 0,
895 c05 = 001,
896 c06 = true,
897 c07 = 32767,
898 c08 = 0,
899 c09 = 00001,
900 c10 = 8388607,
901 c11 = 0,
902 c12 = 00000001,
903 c13 = 2147483647,
904 c14 = 0,
905 c15 = 0000000001,
906 c16 = 9223372036854775807,
907 c17 = 0,
908 c18 = 00000000000000000001,
909 c19 = -1.175494351E-38,
910 c20 = 1.175494351E-38,
911 c21 = 000000000000001,
912 c22 = -2.2250738585072E-308,
913 c23 = 2.2250738585072E-308,
914 c24 = 00000000000000000000001,
915 c25 = -9999999999,
916 c26 = 9999999999,
917 c27 = 0000000001,
918 #
919 c28 = '2008-08-04',
920 c29 = '2008-08-04 16:18:06',
921 c30 = '2008-08-04 16:18:24',
922 c31 = '16:18:47',
923 c32 = '2008',
924 #
925 c33 = 'a',
926 c34 = '',
927 c35 = 'e',
928 c36 = REPEAT('i',255),
929 c37 = _utf8 x'c3a4',
930 c38 = '',
931 c39 = _utf8 x'c3b6',
932 c40 = REPEAT(_utf8 x'c3bc',255),
933 c41 = _ucs2 x'00e4',
934 c42 = '',
935 c43 = _ucs2 x'00f6',
936 c44 = REPEAT(_ucs2 x'00fc',255),
937 #
938 c45 = '',
939 c46 = 'a',
940 c47 = REPEAT('e',255),
941 c48 = REPEAT('i',261),
942 c49 = '',
943 c50 = _utf8 x'c3a4',
944 c51 = REPEAT(_utf8 x'c3b6',255),
945 c52 = REPEAT(_utf8 x'c3bc',261),
946 c53 = '',
947 c54 = _ucs2 x'00e4',
948 c55 = REPEAT(_ucs2 x'00f6',255),
949 c56 = REPEAT(_ucs2 x'00fc',261),
950 #
951 c57 = '0',
952 c58 = '',
953 c59 = '1',
954 c60 = REPEAT('1',255),
955 #
956 c61 = '',
957 c62 = 'b',
958 c63 = REPEAT('c',255),
959 c64 = REPEAT('\'',261),
960 #
961 c65 = 'tinyblob',
962 c66 = 'tinytext',
963 c67 = 'tinytext-ucs2',
964 c68 = 'blob',
965 c69 = 'text',
966 c70 = 'text-ucs2',
967 c71 = 'mediumblob',
968 c72 = 'mediumtext',
969 c73 = 'mediumtext-ucs2',
970 c74 = 'longblob',
971 c75 = 'longtext',
972 c76 = 'longtext-ucs2',
973 #
974 c77 = 'b',
975 c78 = 'b,c',
976 #
977 crn = crn
978 #
979 WHERE
980 #
981 c01 IS NULL AND
982 c02 IS NULL AND
983 c03 IS NULL AND
984 c04 IS NULL AND
985 c05 IS NULL AND
986 c06 IS NULL AND
987 c07 IS NULL AND
988 c08 IS NULL AND
989 c09 IS NULL AND
990 c10 IS NULL AND
991 c11 IS NULL AND
992 c12 IS NULL AND
993 c13 IS NULL AND
994 c14 IS NULL AND
995 c15 IS NULL AND
996 c16 IS NULL AND
997 c17 IS NULL AND
998 c18 IS NULL AND
999 c19 IS NULL AND
1000 c20 IS NULL AND
1001 c21 IS NULL AND
1002 c22 IS NULL AND
1003 c23 IS NULL AND
1004 c24 IS NULL AND
1005 c25 IS NULL AND
1006 c26 IS NULL AND
1007 c27 IS NULL AND
1008 #
1009 c28 IS NULL AND
1010 c29 IS NULL AND
1011 # this got a timestamp instead of NULL: c30 IS NULL AND
1012 c31 IS NULL AND
1013 c32 IS NULL AND
1014 #
1015 c33 IS NULL AND
1016 c34 IS NULL AND
1017 c35 IS NULL AND
1018 c36 IS NULL AND
1019 c37 IS NULL AND
1020 c38 IS NULL AND
1021 c39 IS NULL AND
1022 c40 IS NULL AND
1023 c41 IS NULL AND
1024 c42 IS NULL AND
1025 c43 IS NULL AND
1026 c44 IS NULL AND
1027 #
1028 c45 IS NULL AND
1029 c46 IS NULL AND
1030 c47 IS NULL AND
1031 c48 IS NULL AND
1032 c49 IS NULL AND
1033 c50 IS NULL AND
1034 c51 IS NULL AND
1035 c52 IS NULL AND
1036 c53 IS NULL AND
1037 c54 IS NULL AND
1038 c55 IS NULL AND
1039 c56 IS NULL AND
1040 #
1041 c57 IS NULL AND
1042 c58 IS NULL AND
1043 c59 IS NULL AND
1044 c60 IS NULL AND
1045 #
1046 c61 IS NULL AND
1047 c62 IS NULL AND
1048 c63 IS NULL AND
1049 c64 IS NULL AND
1050 #
1051 c65 IS NULL AND
1052 c66 IS NULL AND
1053 c67 IS NULL AND
1054 c68 IS NULL AND
1055 c69 IS NULL AND
1056 c70 IS NULL AND
1057 c71 IS NULL AND
1058 c72 IS NULL AND
1059 c73 IS NULL AND
1060 c74 IS NULL AND
1061 c75 IS NULL AND
1062 c76 IS NULL AND
1063 #
1064 c77 IS NULL AND
1065 c78 IS NULL AND
1066 #
1067 crn = 3;
1068
1069 --echo #
1070 --echo # Update arbitrary values to NULL values.
1071 --echo #
1072 UPDATE t1 SET
1073 c01 = NULL,
1074 c02 = NULL,
1075 c03 = NULL,
1076 c04 = NULL,
1077 c05 = NULL,
1078 c06 = NULL,
1079 c07 = NULL,
1080 c08 = NULL,
1081 c09 = NULL,
1082 c10 = NULL,
1083 c11 = NULL,
1084 c12 = NULL,
1085 c13 = NULL,
1086 c14 = NULL,
1087 c15 = NULL,
1088 c16 = NULL,
1089 c17 = NULL,
1090 c18 = NULL,
1091 c19 = NULL,
1092 c20 = NULL,
1093 c21 = NULL,
1094 c22 = NULL,
1095 c23 = NULL,
1096 c24 = NULL,
1097 c25 = NULL,
1098 c26 = NULL,
1099 c27 = NULL,
1100 #
1101 c28 = NULL,
1102 c29 = NULL,
1103 c30 = NULL,
1104 c31 = NULL,
1105 c32 = NULL,
1106 #
1107 c33 = NULL,
1108 c34 = NULL,
1109 c35 = NULL,
1110 c36 = NULL,
1111 c37 = NULL,
1112 c38 = NULL,
1113 c39 = NULL,
1114 c40 = NULL,
1115 c41 = NULL,
1116 c42 = NULL,
1117 c43 = NULL,
1118 c44 = NULL,
1119 #
1120 c45 = NULL,
1121 c46 = NULL,
1122 c47 = NULL,
1123 c48 = NULL,
1124 c49 = NULL,
1125 c50 = NULL,
1126 c51 = NULL,
1127 c52 = NULL,
1128 c53 = NULL,
1129 c54 = NULL,
1130 c55 = NULL,
1131 c56 = NULL,
1132 #
1133 c57 = NULL,
1134 c58 = NULL,
1135 c59 = NULL,
1136 c60 = NULL,
1137 #
1138 c61 = NULL,
1139 c62 = NULL,
1140 c63 = NULL,
1141 c64 = NULL,
1142 #
1143 c65 = NULL,
1144 c66 = NULL,
1145 c67 = NULL,
1146 c68 = NULL,
1147 c69 = NULL,
1148 c70 = NULL,
1149 c71 = NULL,
1150 c72 = NULL,
1151 c73 = NULL,
1152 c74 = NULL,
1153 c75 = NULL,
1154 c76 = NULL,
1155 #
1156 c77 = NULL,
1157 c78 = NULL,
1158 #
1159 crn = crn
1160 #
1161 WHERE
1162 #
1163 c01 = b'1' AND
1164 # the below does not reproduce the inserted value:
1165 #c02 = b'1111111111111111111111111111111111111111111111111111111111111111' AND
1166 c03 = 127 AND
1167 c04 = 0 AND
1168 c05 = 001 AND
1169 c06 = true AND
1170 c07 = 32767 AND
1171 c08 = 0 AND
1172 c09 = 00001 AND
1173 c10 = 8388607 AND
1174 c11 = 0 AND
1175 c12 = 00000001 AND
1176 c13 = 2147483647 AND
1177 c14 = 0 AND
1178 c15 = 0000000001 AND
1179 c16 = 9223372036854775807 AND
1180 c17 = 0 AND
1181 c18 = 00000000000000000001 AND
1182 c19 > -1.175494352E-38 AND
1183 c20 < 1.175494352E-38 AND
1184 c21 = 000000000000001 AND
1185 c22 > -2.2250738585073E-308 AND
1186 c23 < 2.2250738585073E-308 AND
1187 c24 = 00000000000000000000001 AND
1188 c25 = -9999999999 AND
1189 c26 = 9999999999 AND
1190 c27 = 0000000001 AND
1191 #
1192 c28 = '2008-08-04' AND
1193 c29 = '2008-08-04 16:18:06' AND
1194 c30 = '2008-08-04 16:18:24' AND
1195 c31 = '16:18:47' AND
1196 c32 = '2008' AND
1197 #
1198 c33 = 'a' AND
1199 c34 = '' AND
1200 c35 = 'e' AND
1201 c36 = REPEAT('i',255) AND
1202 c37 = _utf8 x'c3a4' AND
1203 c38 = '' AND
1204 c39 = _utf8 x'c3b6' AND
1205 c40 = REPEAT(_utf8 x'c3bc',255) AND
1206 c41 = _ucs2 x'00e4' AND
1207 c42 = '' AND
1208 c43 = _ucs2 x'00f6' AND
1209 c44 = REPEAT(_ucs2 x'00fc',255) AND
1210 #
1211 c45 = '' AND
1212 c46 = 'a' AND
1213 c47 = REPEAT('e',255) AND
1214 c48 = REPEAT('i',261) AND
1215 c49 = '' AND
1216 c50 = _utf8 x'c3a4' AND
1217 c51 = REPEAT(_utf8 x'c3b6',255) AND
1218 c52 = REPEAT(_utf8 x'c3bc',261) AND
1219 c53 = '' AND
1220 c54 = _ucs2 x'00e4' AND
1221 c55 = REPEAT(_ucs2 x'00f6',255) AND
1222 c56 = REPEAT(_ucs2 x'00fc',261) AND
1223 #
1224 c57 = '0' AND
1225 c58 = '' AND
1226 c59 = '1' AND
1227 c60 = REPEAT('1',255) AND
1228 #
1229 c61 = '' AND
1230 c62 = 'b' AND
1231 c63 = REPEAT('c',255) AND
1232 c64 = REPEAT('\'',261) AND
1233 #
1234 c65 = 'tinyblob' AND
1235 c66 = 'tinytext' AND
1236 c67 = 'tinytext-ucs2' AND
1237 c68 = 'blob' AND
1238 c69 = 'text' AND
1239 c70 = 'text-ucs2' AND
1240 c71 = 'mediumblob' AND
1241 c72 = 'mediumtext' AND
1242 c73 = 'mediumtext-ucs2' AND
1243 c74 = 'longblob' AND
1244 c75 = 'longtext' AND
1245 c76 = 'longtext-ucs2' AND
1246 #
1247 c77 = 'b' AND
1248 c78 = 'b,c' AND
1249 #
1250 crn = 4;
1251
1252 --echo #
1253 --echo # Show what we have in the table.
1254 --echo # Do not display bit type output. It's binary and confuses diff.
1255 --echo # Also BINARY with nul-bytes should be avoided.
1256 --echo #
1257 --replace_column 1 # 2 # 57 # 58 # 59 # 60 #
1258 query_vertical SELECT * FROM t1;
1259
1260 --echo #
1261 --echo # Delete the row that has max values now.
1262 --echo #
1263 DELETE FROM t1 WHERE
1264 #
1265 c01 = b'1' AND
1266 # the below does not reproduce the inserted value:
1267 #c02 = b'1111111111111111111111111111111111111111111111111111111111111111' AND
1268 c03 = 127 AND
1269 c04 = 255 AND
1270 c05 = 255 AND
1271 c06 = true AND
1272 c07 = 32767 AND
1273 c08 = 65535 AND
1274 c09 = 65535 AND
1275 c10 = 8388607 AND
1276 c11 = 16777215 AND
1277 c12 = 16777215 AND
1278 c13 = 2147483647 AND
1279 c14 = 4294967295 AND
1280 c15 = 4294967295 AND
1281 c16 = 9223372036854775807 AND
1282 c17 = 18446744073709551615 AND
1283 c18 = 18446744073709551615 AND
1284 c19 > 3.402823465E+38 AND
1285 c20 > 3.402823465E+38 AND
1286 c21 > 3.402823465E+38 AND
1287 c22 > 1.7976931348622E+308 AND
1288 c23 > 1.7976931348622E+308 AND
1289 c24 > 1.7976931348622E+308 AND
1290 c25 = 9999999999 AND
1291 c26 = 9999999999 AND
1292 c27 = 9999999999 AND
1293 #
1294 c28 = '9999-12-31' AND
1295 c29 = '9999-12-31 23:59:59' AND
1296 c30 = '2038-01-08 03:14:07' AND
1297 c31 = '838:59:59' AND
1298 c32 = '2155' AND
1299 #
1300 c33 = x'ff' AND
1301 c34 = '' AND
1302 c35 = x'ff' AND
1303 c36 = REPEAT(x'ff',255) AND
1304 c37 = _utf8 x'efbfbf' AND
1305 c38 = '' AND
1306 c39 = _utf8 x'efbfbf' AND
1307 c40 = REPEAT(_utf8 x'efbfbf',255) AND
1308 c41 = _ucs2 x'ffff' AND
1309 c42 = '' AND
1310 c43 = _ucs2 x'ffff' AND
1311 c44 = REPEAT(_ucs2 x'ffff',255) AND
1312 #
1313 c45 = '' AND
1314 c46 = x'ff' AND
1315 c47 = REPEAT(x'ff',255) AND
1316 c48 = REPEAT(x'ff',261) AND
1317 c49 = '' AND
1318 c50 = _utf8 x'efbfbf' AND
1319 c51 = REPEAT(_utf8 x'efbfbf',255) AND
1320 c52 = REPEAT(_utf8 x'efbfbf',261) AND
1321 c53 = '' AND
1322 c54 = _ucs2 x'ffff' AND
1323 c55 = REPEAT(_ucs2 x'ffff',255) AND
1324 c56 = REPEAT(_ucs2 x'ffff',261) AND
1325 #
1326 c57 = x'ff' AND
1327 c58 = '' AND
1328 c59 = x'ff' AND
1329 c60 = REPEAT(x'ff',255) AND
1330 #
1331 c61 = '' AND
1332 c62 = x'ff' AND
1333 c63 = REPEAT(x'ff',255) AND
1334 c64 = REPEAT(x'ff',261) AND
1335 #
1336 c65 = 'tinyblob' AND
1337 c66 = 'tinytext' AND
1338 c67 = 'tinytext-ucs2' AND
1339 c68 = 'blob' AND
1340 c69 = 'text' AND
1341 c70 = 'text-ucs2' AND
1342 c71 = 'mediumblob' AND
1343 c72 = 'mediumtext' AND
1344 c73 = 'mediumtext-ucs2' AND
1345 c74 = 'longblob' AND
1346 c75 = 'longtext' AND
1347 c76 = 'longtext-ucs2' AND
1348 #
1349 c77 = 'c' AND
1350 c78 = 'a,b,c' AND
1351 #
1352 crn = 1;
1353
1354 --echo #
1355 --echo # Delete the row that has min values now.
1356 --echo #
1357 DELETE FROM t1 WHERE
1358 #
1359 c01 = b'0' AND
1360 c02 = b'0000000000000000000000000000000000000000000000000000000000000000' AND
1361 c03 = -128 AND
1362 c04 = 0 AND
1363 c05 = 000 AND
1364 c06 = false AND
1365 c07 = -32768 AND
1366 c08 = 0 AND
1367 c09 = 00000 AND
1368 c10 = -8388608 AND
1369 c11 = 0 AND
1370 c12 = 00000000 AND
1371 c13 = -2147483648 AND
1372 c14 = 0 AND
1373 c15 = 0000000000 AND
1374 c16 = -9223372036854775808 AND
1375 c17 = 0 AND
1376 c18 = 00000000000000000000 AND
1377 c19 < -3.402823465E+38 AND
1378 c20 < 1.175494352E-38 AND
1379 c21 = 000000000000 AND
1380 c22 < -1.7976931348622E+308 AND
1381 c23 < 2.2250738585073E-308 AND
1382 c24 = 0000000000000000000000 AND
1383 c25 = -9999999999 AND
1384 c26 = 0 AND
1385 c27 = 0000000000 AND
1386 #
1387 c28 = '1000-01-01' AND
1388 c29 = '1000-01-01 00:00:00' AND
1389 c30 = '1970-01-02 00:00:01' AND
1390 c31 = '-838:59:59' AND
1391 c32 = '1901' AND
1392 #
1393 c33 = '' AND
1394 c34 = '' AND
1395 c35 = '' AND
1396 c36 = '' AND
1397 c37 = '' AND
1398 c38 = '' AND
1399 c39 = '' AND
1400 c40 = '' AND
1401 c41 = '' AND
1402 c42 = '' AND
1403 c43 = '' AND
1404 c44 = '' AND
1405 #
1406 c45 = '' AND
1407 c46 = '' AND
1408 c47 = '' AND
1409 c48 = '' AND
1410 c49 = '' AND
1411 c50 = '' AND
1412 c51 = '' AND
1413 c52 = '' AND
1414 c53 = '' AND
1415 c54 = '' AND
1416 c55 = '' AND
1417 c56 = '' AND
1418 #
1419 # this does not reproduce the inserted value: c57 = '' AND
1420 c58 = '' AND
1421 # this does not reproduce the inserted value: c59 = '' AND
1422 # this does not reproduce the inserted value: c60 = '' AND
1423 #
1424 c61 = '' AND
1425 c62 = '' AND
1426 c63 = '' AND
1427 c64 = '' AND
1428 #
1429 c65 = '' AND
1430 c66 = '' AND
1431 c67 = '' AND
1432 c68 = '' AND
1433 c69 = '' AND
1434 c70 = '' AND
1435 c71 = '' AND
1436 c72 = '' AND
1437 c73 = '' AND
1438 c74 = '' AND
1439 c75 = '' AND
1440 c76 = '' AND
1441 #
1442 c77 = 'a' AND
1443 c78 = '' AND
1444 #
1445 crn = 2;
1446
1447 --echo #
1448 --echo # Delete the row that has arbitrary values now.
1449 --echo #
1450 DELETE FROM t1 WHERE
1451 #
1452 c01 = b'1' AND
1453 # the below does not reproduce the inserted value:
1454 #c02 = b'1111111111111111111111111111111111111111111111111111111111111111' AND
1455 c03 = 127 AND
1456 c04 = 0 AND
1457 c05 = 001 AND
1458 c06 = true AND
1459 c07 = 32767 AND
1460 c08 = 0 AND
1461 c09 = 00001 AND
1462 c10 = 8388607 AND
1463 c11 = 0 AND
1464 c12 = 00000001 AND
1465 c13 = 2147483647 AND
1466 c14 = 0 AND
1467 c15 = 0000000001 AND
1468 c16 = 9223372036854775807 AND
1469 c17 = 0 AND
1470 c18 = 00000000000000000001 AND
1471 c19 > -1.175494352E-38 AND
1472 c20 < 1.175494352E-38 AND
1473 c21 = 000000000000001 AND
1474 c22 > -2.2250738585073E-308 AND
1475 c23 < 2.2250738585073E-308 AND
1476 c24 = 00000000000000000000001 AND
1477 c25 = -9999999999 AND
1478 c26 = 9999999999 AND
1479 c27 = 0000000001 AND
1480 #
1481 c28 = '2008-08-04' AND
1482 c29 = '2008-08-04 16:18:06' AND
1483 c30 = '2008-08-04 16:18:24' AND
1484 c31 = '16:18:47' AND
1485 c32 = '2008' AND
1486 #
1487 c33 = 'a' AND
1488 c34 = '' AND
1489 c35 = 'e' AND
1490 c36 = REPEAT('i',255) AND
1491 c37 = _utf8 x'c3a4' AND
1492 c38 = '' AND
1493 c39 = _utf8 x'c3b6' AND
1494 c40 = REPEAT(_utf8 x'c3bc',255) AND
1495 c41 = _ucs2 x'00e4' AND
1496 c42 = '' AND
1497 c43 = _ucs2 x'00f6' AND
1498 c44 = REPEAT(_ucs2 x'00fc',255) AND
1499 #
1500 c45 = '' AND
1501 c46 = 'a' AND
1502 c47 = REPEAT('e',255) AND
1503 c48 = REPEAT('i',261) AND
1504 c49 = '' AND
1505 c50 = _utf8 x'c3a4' AND
1506 c51 = REPEAT(_utf8 x'c3b6',255) AND
1507 c52 = REPEAT(_utf8 x'c3bc',261) AND
1508 c53 = '' AND
1509 c54 = _ucs2 x'00e4' AND
1510 c55 = REPEAT(_ucs2 x'00f6',255) AND
1511 c56 = REPEAT(_ucs2 x'00fc',261) AND
1512 #
1513 c57 = '0' AND
1514 c58 = '' AND
1515 c59 = '1' AND
1516 c60 = REPEAT('1',255) AND
1517 #
1518 c61 = '' AND
1519 c62 = 'b' AND
1520 c63 = REPEAT('c',255) AND
1521 c64 = REPEAT('\'',261) AND
1522 #
1523 c65 = 'tinyblob' AND
1524 c66 = 'tinytext' AND
1525 c67 = 'tinytext-ucs2' AND
1526 c68 = 'blob' AND
1527 c69 = 'text' AND
1528 c70 = 'text-ucs2' AND
1529 c71 = 'mediumblob' AND
1530 c72 = 'mediumtext' AND
1531 c73 = 'mediumtext-ucs2' AND
1532 c74 = 'longblob' AND
1533 c75 = 'longtext' AND
1534 c76 = 'longtext-ucs2' AND
1535 #
1536 c77 = 'b' AND
1537 c78 = 'b,c' AND
1538 #
1539 crn = 3;
1540
1541 --echo #
1542 --echo # Delete the row that has NULL values now.
1543 --echo #
1544 DELETE FROM t1 WHERE
1545 #
1546 c01 IS NULL AND
1547 c02 IS NULL AND
1548 c03 IS NULL AND
1549 c04 IS NULL AND
1550 c05 IS NULL AND
1551 c06 IS NULL AND
1552 c07 IS NULL AND
1553 c08 IS NULL AND
1554 c09 IS NULL AND
1555 c10 IS NULL AND
1556 c11 IS NULL AND
1557 c12 IS NULL AND
1558 c13 IS NULL AND
1559 c14 IS NULL AND
1560 c15 IS NULL AND
1561 c16 IS NULL AND
1562 c17 IS NULL AND
1563 c18 IS NULL AND
1564 c19 IS NULL AND
1565 c20 IS NULL AND
1566 c21 IS NULL AND
1567 c22 IS NULL AND
1568 c23 IS NULL AND
1569 c24 IS NULL AND
1570 c25 IS NULL AND
1571 c26 IS NULL AND
1572 c27 IS NULL AND
1573 #
1574 c28 IS NULL AND
1575 c29 IS NULL AND
1576 # this got a timestamp instead of NULL: c30 IS NULL AND
1577 c31 IS NULL AND
1578 c32 IS NULL AND
1579 #
1580 c33 IS NULL AND
1581 c34 IS NULL AND
1582 c35 IS NULL AND
1583 c36 IS NULL AND
1584 c37 IS NULL AND
1585 c38 IS NULL AND
1586 c39 IS NULL AND
1587 c40 IS NULL AND
1588 c41 IS NULL AND
1589 c42 IS NULL AND
1590 c43 IS NULL AND
1591 c44 IS NULL AND
1592 #
1593 c45 IS NULL AND
1594 c46 IS NULL AND
1595 c47 IS NULL AND
1596 c48 IS NULL AND
1597 c49 IS NULL AND
1598 c50 IS NULL AND
1599 c51 IS NULL AND
1600 c52 IS NULL AND
1601 c53 IS NULL AND
1602 c54 IS NULL AND
1603 c55 IS NULL AND
1604 c56 IS NULL AND
1605 #
1606 c57 IS NULL AND
1607 c58 IS NULL AND
1608 c59 IS NULL AND
1609 c60 IS NULL AND
1610 #
1611 c61 IS NULL AND
1612 c62 IS NULL AND
1613 c63 IS NULL AND
1614 c64 IS NULL AND
1615 #
1616 c65 IS NULL AND
1617 c66 IS NULL AND
1618 c67 IS NULL AND
1619 c68 IS NULL AND
1620 c69 IS NULL AND
1621 c70 IS NULL AND
1622 c71 IS NULL AND
1623 c72 IS NULL AND
1624 c73 IS NULL AND
1625 c74 IS NULL AND
1626 c75 IS NULL AND
1627 c76 IS NULL AND
1628 #
1629 c77 IS NULL AND
1630 c78 IS NULL AND
1631 #
1632 crn = 4;
1633
1634 --echo #
1635 --echo # Show what we have in the table. Should be empty now.
1636 --echo #
1637 query_vertical SELECT * FROM t1;
1638
1639 --echo #
1640 --echo # Hide how much rows are affected by each statement.
1641 --echo #
1642 --disable_info
1643
1644 --echo #
1645 --echo # Flush all log buffers to the log file.
1646 --echo #
1647 FLUSH LOGS;
1648
1649 --echo #
1650 --echo # Call mysqlbinlog to display the log file contents.
1651 --echo #
1652 let $MYSQLD_DATADIR= `select @@datadir`;
1653 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
1654 --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /Xid = [0-9]*/Xid = #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /(@[0-9]*=[0-9-]*[.][0-9]{1,3})[0-9e+-]*[^ ]*[ ]*(.*(FLOAT|DOUBLE).*[*].)/\1... \2/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /collation_server=[0-9]+/collation_server=X/ /xid=\d*/xid=<xid>/
1655 --exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001
1656
1657 --echo #
1658 --echo # Cleanup.
1659 --echo #
1660 DROP TABLE t1;
1661
1662 --echo #
1663 --echo # =========================================
1664 --echo # Test #2 - Multi-row insert/update/delete.
1665 --echo # =========================================
1666 --echo #
1667 --echo # Delete all existing binary logs.
1668 --echo #
1669 RESET MASTER;
1670
1671 --echo #
1672 --echo # Create a test table with selected data types.
1673 --echo #
1674 eval CREATE TABLE t1 (
1675 c28 DATE,
1676 c47 VARCHAR(24),
1677 crn INT -- row number
1678 ) ENGINE=$engine_type DEFAULT CHARSET latin1;
1679
1680 --echo #
1681 --echo # Show how much rows are affected by each statement.
1682 --echo #
1683 --enable_info
1684
1685 --echo #
1686 --echo # Multi-row insert.
1687 --echo #
1688 INSERT INTO t1 VALUES
1689 ('2008-08-01','VARCHAR-01',1),
1690 ('2008-08-02','VARCHAR-02',2),
1691 ('2008-08-03','VARCHAR-03',3),
1692 ('2008-08-04','VARCHAR-04',4),
1693 ('2008-08-05','VARCHAR-05',5),
1694 ('2008-08-06','VARCHAR-06',6),
1695 ('2008-08-07','VARCHAR-07',7),
1696 ('2008-08-08','VARCHAR-08',8),
1697 ('2008-08-09','VARCHAR-09',9);
1698
1699 --echo #
1700 --echo # Multi-row update.
1701 --echo #
1702 UPDATE t1 SET c28 = ADDDATE(c28,10) WHERE crn < 8;
1703
1704 --echo #
1705 --echo # Show what we have in the table.
1706 --echo #
1707 SELECT * FROM t1;
1708
1709 --echo #
1710 --echo # Multi-row delete.
1711 --echo #
1712 DELETE FROM t1 WHERE crn < 8;
1713
1714 --echo #
1715 --echo # Show what we have in the table.
1716 --echo #
1717 SELECT * FROM t1;
1718
1719 --echo #
1720 --echo # Hide how much rows are affected by each statement.
1721 --echo #
1722 --disable_info
1723
1724 --echo #
1725 --echo # Flush all log buffers to the log file.
1726 --echo #
1727 FLUSH LOGS;
1728
1729 --echo #
1730 --echo # Call mysqlbinlog to display the log file contents.
1731 --echo #
1732 let $MYSQLD_DATADIR= `select @@datadir`;
1733 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
1734 --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /Xid = [0-9]*/Xid = #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /(@[0-9]*=[0-9-]*[.][0-9]{1,3})[0-9e+-]*[^ ]*[ ]*(.*(FLOAT|DOUBLE).*[*].)/\1... \2/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /collation_server=[0-9]+/collation_server=X/ /xid=\d*/xid=<xid>/
1735 --exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001
1736
1737 --echo #
1738 --echo # Cleanup.
1739 --echo #
1740 DROP TABLE t1;
1741
1742 --echo #
1743 --echo # ====================================
1744 --echo # Test #3 - Multi-table update/delete.
1745 --echo # ====================================
1746 --echo #
1747 --echo # Delete all existing binary logs.
1748 --echo #
1749 RESET MASTER;
1750
1751 --echo #
1752 --echo # Create test tables with selected data types.
1753 --echo #
1754 eval CREATE TABLE t1 (
1755 c_1_1 DATE,
1756 c_1_2 VARCHAR(255),
1757 c_1_n INT -- row number
1758 ) ENGINE=$engine_type DEFAULT CHARSET latin1;
1759 #
1760 eval CREATE TABLE t2 (
1761 c_2_1 DATE,
1762 c_2_2 VARCHAR(255),
1763 c_2_n INT -- row number
1764 ) ENGINE=$engine_type DEFAULT CHARSET latin1;
1765 #
1766 eval CREATE TABLE t3 (
1767 c_3_1 DATE,
1768 c_3_2 VARCHAR(255),
1769 c_3_n INT -- row number
1770 ) ENGINE=$engine_type DEFAULT CHARSET latin1;
1771
1772 --echo #
1773 --echo # Show how much rows are affected by each statement.
1774 --echo #
1775 --enable_info
1776
1777 --echo #
1778 --echo # Insert data.
1779 --echo #
1780 INSERT INTO t1 VALUES
1781 ('2008-01-01','VARCHAR-01-01',11),
1782 ('2008-01-02','VARCHAR-01-02',2),
1783 ('2008-01-03','VARCHAR-01-03',3),
1784 ('2008-01-04','VARCHAR-01-04',4),
1785 ('2008-01-05','VARCHAR-01-05',5),
1786 ('2008-01-06','VARCHAR-01-06',6),
1787 ('2008-01-07','VARCHAR-01-07',7),
1788 ('2008-01-08','VARCHAR-01-08',18),
1789 ('2008-01-09','VARCHAR-01-09',19);
1790 #
1791 INSERT INTO t2 VALUES
1792 ('2008-02-01','VARCHAR-02-01',21),
1793 ('2008-02-02','VARCHAR-02-02',2),
1794 ('2008-02-03','VARCHAR-02-03',3),
1795 ('2008-02-04','VARCHAR-02-04',4),
1796 ('2008-02-05','VARCHAR-02-05',5),
1797 ('2008-02-06','VARCHAR-02-06',6),
1798 ('2008-02-07','VARCHAR-02-07',7),
1799 ('2008-02-08','VARCHAR-02-08',28),
1800 ('2008-02-09','VARCHAR-02-09',29);
1801 #
1802 INSERT INTO t3 VALUES
1803 ('2008-03-01','VARCHAR-03-01',31),
1804 ('2008-03-02','VARCHAR-03-02',2),
1805 ('2008-03-03','VARCHAR-03-03',3),
1806 ('2008-03-04','VARCHAR-03-04',4),
1807 ('2008-03-05','VARCHAR-03-05',5),
1808 ('2008-03-06','VARCHAR-03-06',6),
1809 ('2008-03-07','VARCHAR-03-07',7),
1810 ('2008-03-08','VARCHAR-03-08',38),
1811 ('2008-03-09','VARCHAR-03-09',39);
1812
1813 --echo #
1814 --echo # Multi-table update.
1815 --echo #
1816 UPDATE t1,t2,t3 SET
1817 c_1_1 = ADDDATE(c_1_1,INTERVAL 10 YEAR),
1818 c_2_1 = ADDDATE(c_2_1,INTERVAL 20 YEAR),
1819 c_3_1 = ADDDATE(c_3_1,INTERVAL 30 YEAR)
1820 WHERE c_1_n = c_2_n AND c_2_n = c_3_n;
1821
1822 --echo #
1823 --echo # Show what we have in the tables.
1824 --echo #
1825 SELECT * FROM t1;
1826 SELECT * FROM t2;
1827 SELECT * FROM t3;
1828
1829 --echo #
1830 --echo # Multi-table delete.
1831 --echo #
1832 DELETE FROM t1,t2,t3 USING t1 INNER JOIN t2 INNER JOIN t3
1833 WHERE c_1_n = c_2_n AND c_2_n = c_3_n;
1834
1835 --echo #
1836 --echo # Show what we have in the tables.
1837 --echo #
1838 SELECT * FROM t1;
1839 SELECT * FROM t2;
1840 SELECT * FROM t3;
1841
1842 --echo #
1843 --echo # Hide how much rows are affected by each statement.
1844 --echo #
1845 --disable_info
1846
1847 --echo #
1848 --echo # Flush all log buffers to the log file.
1849 --echo #
1850 FLUSH LOGS;
1851
1852 --echo #
1853 --echo # Call mysqlbinlog to display the log file contents.
1854 --echo #
1855 let $MYSQLD_DATADIR= `select @@datadir`;
1856 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
1857 --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /Xid = [0-9]*/Xid = #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /(@[0-9]*=[0-9-]*[.][0-9]{1,3})[0-9e+-]*[^ ]*[ ]*(.*(FLOAT|DOUBLE).*[*].)/\1... \2/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /collation_server=[0-9]+/collation_server=X/ /xid=\d*/xid=<xid>/
1858 --exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001
1859
1860 --echo #
1861 --echo # Cleanup.
1862 --echo #
1863 DROP TABLE t1, t2, t3;
1864
1865 --echo #
1866 --echo # ===========================
1867 --echo # Test #4 - LOAD DATA INFILE.
1868 --echo # ===========================
1869 --echo #
1870 --echo # Delete all existing binary logs.
1871 --echo #
1872 RESET MASTER;
1873
1874 --echo #
1875 --echo # Create a test table with selected data types.
1876 --echo #
1877 eval CREATE TABLE t1 (
1878 c1 INT DEFAULT 100,
1879 c2 INT,
1880 c3 VARCHAR(60)
1881 ) ENGINE=$engine_type DEFAULT CHARSET latin1;
1882
1883 --echo #
1884 --echo # Show how much rows are affected by each statement.
1885 --echo #
1886 --enable_info
1887
1888 --echo #
1889 --echo # Load data.
1890 --echo #
1891 LOAD DATA INFILE '../../std_data/loaddata5.dat'
1892 INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (c1,c2)
1893 SET c3 = 'Wow';
1894
1895 --echo #
1896 --echo # Show what we have in the table.
1897 --echo #
1898 SELECT * FROM t1;
1899
1900 --echo #
1901 --echo # Hide how much rows are affected by each statement.
1902 --echo #
1903 --disable_info
1904
1905 --echo #
1906 --echo # Flush all log buffers to the log file.
1907 --echo #
1908 FLUSH LOGS;
1909
1910 --echo #
1911 --echo # Call mysqlbinlog to display the log file contents.
1912 --echo #
1913 let $MYSQLD_DATADIR= `select @@datadir`;
1914 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
1915 --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /Xid = [0-9]*/Xid = #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /(@[0-9]*=[0-9-]*[.][0-9]{1,3})[0-9e+-]*[^ ]*[ ]*(.*(FLOAT|DOUBLE).*[*].)/\1... \2/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /collation_server=[0-9]+/collation_server=X/ /xid=\d*/xid=<xid>/
1916 --exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001
1917
1918 --echo #
1919 --echo # Cleanup.
1920 --echo #
1921 DROP TABLE t1;
1922
1923
1924 --echo #
1925 --echo # Beginning of 10.2 test
1926 --echo #
1927 --echo # MDEV-25460: Assertion `!is_set() || (m_status == DA_OK_BULK &&
1928 --echo # is_bulk_op())' failed in Diagnostics_area::set_ok_status in my_ok
1929 --echo # from mysql_sql_stmt_prepare
1930 --echo #
1931
1932 CREATE TEMPORARY TABLE a (c INT) ENGINE=InnoDB;
1933 CREATE TABLE b (c INT) ENGINE=InnoDB;
1934
1935 --error ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR
1936 PREPARE s FROM 'SET STATEMENT binlog_format=ROW FOR SELECT * FROM b';
1937
1938 DROP TABLE b;
1939 DROP TEMPORARY TABLE a;
1940
1941 CREATE TEMPORARY TABLE t (c INT);
1942 --error ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR
1943 PREPARE s FROM 'SET STATEMENT binlog_format=ROW FOR SELECT 1';
1944 DROP TEMPORARY TABLE t;
1945
1946 --echo #
1947 --echo # End of 10.2 test
1948 --echo #