Debian Codegraph @

mysql-test/suite/rpl/t/rpl_func_bitwise_ops.test

raw
1 ############################################################################################
2 # #
3 # The aim of this test is to set up replication between a master and slave and #
4 # test the modified behaviour of bitwise operations which was brought by WL#8699: #
5 # Bit-wise operations on binary data types. #
6 # #
7 # Creation Date: 2016-03-02 #
8 # Author: Deepa Dixit #
9 # #
10 ############################################################################################
11
12 --source include/master-slave.inc
13
14 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
15
16 --echo # Create table with columns of binary data types and insert values
17
18 --connection master
19
20 CREATE TABLE bitwise_table(id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, vbin1 VARBINARY(32), vbin2 VARBINARY(32));
21
22 INSERT INTO bitwise_table(vbin1, vbin2) VALUES
23 (x'59', x'6a'),
24 (x'5939', x'6ac3'),
25 (x'5939a998', x'6ac35d2a'),
26 (x'5939a99861154f35', x'6ac35d2a3ab34bda'),
27 (x'5939a99861154f3587d5440618e9b28b', x'6ac35d2a3ab34bda8ac412ea0141852c'),
28 (x'5939a99861154f3587d5440618e9b28b166181c5ca514ab1b8e9c970ae5e421a', x'6ac35d2a3ab34bda8ac412ea0141852c3c8e38bb19934a7092a40bb19db13a80'),
29 (x'5939a99861154f3587d5440618e9b28b', x'8ac412ea0141852c'),
30 (x'5939a99861154f35', x'6ac35d2a3ab34bda8ac412ea0141852c');
31
32 --disable_warnings
33 INSERT INTO bitwise_table(vbin1, vbin2)
34 SELECT (vbin1 & vbin2) , (vbin1 | vbin2) FROM bitwise_table WHERE id <= 6;
35
36 INSERT INTO bitwise_table(vbin1, vbin2)
37 SELECT (vbin1 ^ vbin2) , ( ~ vbin1 ) FROM bitwise_table WHERE id <= 6;
38
39 INSERT INTO bitwise_table(vbin1, vbin2)
40 SELECT (vbin1 >> 4 ) , ( vbin2 << 3 ) FROM bitwise_table WHERE id <= 6;
41
42 INSERT INTO bitwise_table(vbin1, vbin2)
43 SELECT (vbin1 ^ x'6ac3'), (vbin2 & x'5939') FROM bitwise_table WHERE id = 2;
44 --enable_warnings
45
46 --error ER_INVALID_BITWISE_OPERANDS_SIZE
47 INSERT INTO bitwise_table(vbin1, vbin2)
48 SELECT (vbin1 & vbin2) , (vbin1 | vbin2) FROM bitwise_table WHERE id = 7;
49
50 --source include/sync_slave_sql_with_master.inc
51 --echo [Connection Slave]
52
53 --echo # check whether table was created and values were inserted on slave
54
55 SELECT HEX(vbin1), BIT_COUNT(vbin1), HEX(vbin2), BIT_COUNT(vbin2) FROM bitwise_table;
56
57 SELECT (vbin1 | NULL ), (vbin2 & NULL) FROM bitwise_table;
58
59 SELECT HEX(vbin1), HEX(BIT_AND(vbin1)), HEX(BIT_OR(vbin1)), HEX(BIT_XOR(vbin1)), BIT_COUNT(vbin1) FROM bitwise_table GROUP BY id;
60
61 SELECT HEX(vbin2), HEX(BIT_AND(vbin2)), HEX(BIT_OR(vbin2)), HEX(BIT_XOR(vbin2)), BIT_COUNT(vbin2) FROM bitwise_table GROUP BY id;
62
63 --let $diff_tables = master:bitwise_table, slave:bitwise_table
64 --source include/diff_tables.inc
65
66 --echo [Connection Master]
67 --connection master
68
69 --echo # Update rows inside the table
70
71 UPDATE bitwise_table SET vbin1 = (vbin1 | vbin2) WHERE id <= 6;
72 UPDATE bitwise_table SET vbin2 = (vbin1 & vbin2) WHERE id <= 6;
73
74 --source include/sync_slave_sql_with_master.inc
75 --echo [Connection Slave]
76
77 --echo # Check whether rows are updated
78
79 SELECT HEX(vbin1), HEX(vbin2) FROM bitwise_table WHERE id <= 6;
80
81 --let $diff_tables = master:bitwise_table, slave:bitwise_table
82 --source include/diff_tables.inc
83
84 --echo [Connection Master]
85 --connection master
86
87 UPDATE bitwise_table SET vbin1 = (vbin1 ^ vbin2) WHERE id <= 6;
88 UPDATE bitwise_table SET vbin2 = (~ vbin1) WHERE id <= 6;
89
90 --source include/sync_slave_sql_with_master.inc
91 --echo [Connection Slave]
92
93 --echo # Check whether rows are updated
94
95 SELECT HEX(vbin1), HEX(vbin2) FROM bitwise_table WHERE id <= 6;
96
97 --let $diff_tables = master:bitwise_table, slave:bitwise_table
98 --source include/diff_tables.inc
99
100 --echo [Connection Master]
101 --connection master
102
103 --echo # Delete rows from the table
104
105 DELETE FROM bitwise_table WHERE BIT_COUNT(vbin1) <= 8;
106
107 --source include/sync_slave_sql_with_master.inc
108 --echo [Connection Slave]
109
110 --echo # check whether the rows are deleted
111
112 SELECT HEX(vbin1), HEX(vbin2) FROM bitwise_table;
113
114 --let $diff_tables = master:bitwise_table, slave:bitwise_table
115 --source include/diff_tables.inc
116
117
118 --echo [Connection Master]
119 --connection master
120
121 --echo # Drop tables and clean up
122
123 DROP TABLE bitwise_table;
124
125 --source include/sync_slave_sql_with_master.inc
126 --echo [Connection Slave]
127
128 --echo # Check whether table is dropped
129
130 --error ER_NO_SUCH_TABLE
131 SELECT * FROM bitwise_table;
132
133 --source include/rpl_end.inc
134