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