Debian Codegraph @

mysql-test/suite/rpl/r/rpl_not_null_innodb.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection master;
4 SET SQL_LOG_BIN= 0;
5 CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL,
6 `c` INT DEFAULT NULL,
7 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
8 CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL,
9 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
10 CREATE TABLE t3(`a` INT, `b` DATE DEFAULT NULL,
11 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
12 CREATE TABLE t4(`a` INT, `b` DATE DEFAULT NULL,
13 `c` INT DEFAULT NULL,
14 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
15 SET SQL_LOG_BIN= 1;
16 connection slave;
17 CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL,
18 `c` INT DEFAULT NULL,
19 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
20 CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL,
21 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
22 CREATE TABLE t3(`a` INT, `b` DATE DEFAULT '0000-00-00',
23 `c` INT DEFAULT 500,
24 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
25 CREATE TABLE t4(`a` INT, `b` DATE DEFAULT '0000-00-00',
26 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
27 ************* EXECUTION WITH INSERTS *************
28 connection master;
29 INSERT INTO t1(a,b,c) VALUES (1, null, 1);
30 INSERT INTO t1(a,b,c) VALUES (2,'1111-11-11', 2);
31 INSERT INTO t1(a,b) VALUES (3, null);
32 INSERT INTO t1(a,c) VALUES (4, 4);
33 INSERT INTO t1(a) VALUES (5);
34 INSERT INTO t2(a,b) VALUES (1, null);
35 INSERT INTO t2(a,b) VALUES (2,'1111-11-11');
36 INSERT INTO t2(a) VALUES (3);
37 INSERT INTO t3(a,b) VALUES (1, null);
38 INSERT INTO t3(a,b) VALUES (2,'1111-11-11');
39 INSERT INTO t3(a) VALUES (3);
40 INSERT INTO t4(a,b,c) VALUES (1, null, 1);
41 INSERT INTO t4(a,b,c) VALUES (2,'1111-11-11', 2);
42 INSERT INTO t4(a,b) VALUES (3, null);
43 INSERT INTO t4(a,c) VALUES (4, 4);
44 INSERT INTO t4(a) VALUES (5);
45 ************* SHOWING THE RESULT SETS WITH INSERTS *************
46 connection slave;
47 TABLES t1 and t2 must be equal otherwise an error will be thrown.
48 include/diff_tables.inc [master:t1, slave:t1]
49 include/diff_tables.inc [master:t2, slave:t2]
50 TABLES t2 and t3 must be different.
51 connection master;
52 SELECT * FROM t3 ORDER BY a;
53 a b
54 1 NULL
55 2 1111-11-11
56 3 NULL
57 connection slave;
58 SELECT * FROM t3 ORDER BY a;
59 a b c
60 1 NULL 500
61 2 1111-11-11 500
62 3 NULL 500
63 connection master;
64 SELECT * FROM t4 ORDER BY a;
65 a b c
66 1 NULL 1
67 2 1111-11-11 2
68 3 NULL NULL
69 4 NULL 4
70 5 NULL NULL
71 connection slave;
72 SELECT * FROM t4 ORDER BY a;
73 a b
74 1 NULL
75 2 1111-11-11
76 3 NULL
77 4 NULL
78 5 NULL
79 ************* EXECUTION WITH UPDATES and REPLACES *************
80 connection master;
81 DELETE FROM t1;
82 INSERT INTO t1(a,b,c) VALUES (1,'1111-11-11', 1);
83 REPLACE INTO t1(a,b,c) VALUES (2,'1111-11-11', 2);
84 UPDATE t1 set b= NULL, c= 300 where a= 1;
85 REPLACE INTO t1(a,b,c) VALUES (2, NULL, 300);
86 ************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES *************
87 connection slave;
88 TABLES t1 and t2 must be equal otherwise an error will be thrown.
89 include/diff_tables.inc [master:t1, slave:t1]
90 ************* CLEANING *************
91 connection master;
92 DROP TABLE t1;
93 DROP TABLE t2;
94 DROP TABLE t3;
95 DROP TABLE t4;
96 connection slave;
97 connection master;
98 SET SQL_LOG_BIN= 0;
99 CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT NULL, `c` BIT DEFAULT NULL,
100 PRIMARY KEY (`a`)) ENGINE= Innodb;
101 SET SQL_LOG_BIN= 1;
102 connection slave;
103 CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT b'01', `c` BIT DEFAULT NULL,
104 PRIMARY KEY (`a`)) ENGINE= Innodb;
105 ************* EXECUTION WITH INSERTS *************
106 connection master;
107 INSERT INTO t1(a,b,c) VALUES (1, null, b'01');
108 INSERT INTO t1(a,b,c) VALUES (2,b'00', b'01');
109 INSERT INTO t1(a,b) VALUES (3, null);
110 INSERT INTO t1(a,c) VALUES (4, b'01');
111 INSERT INTO t1(a) VALUES (5);
112 ************* SHOWING THE RESULT SETS WITH INSERTS *************
113 TABLES t1 and t2 must be different.
114 connection slave;
115 connection master;
116 SELECT a,b+0,c+0 FROM t1 ORDER BY a;
117 a b+0 c+0
118 1 NULL 1
119 2 0 1
120 3 NULL NULL
121 4 NULL 1
122 5 NULL NULL
123 connection slave;
124 SELECT a,b+0,c+0 FROM t1 ORDER BY a;
125 a b+0 c+0
126 1 NULL 1
127 2 0 1
128 3 NULL NULL
129 4 NULL 1
130 5 NULL NULL
131 ************* EXECUTION WITH UPDATES and REPLACES *************
132 connection master;
133 DELETE FROM t1;
134 INSERT INTO t1(a,b,c) VALUES (1,b'00', b'01');
135 REPLACE INTO t1(a,b,c) VALUES (2,b'00',b'01');
136 UPDATE t1 set b= NULL, c= b'00' where a= 1;
137 REPLACE INTO t1(a,b,c) VALUES (2, NULL, b'00');
138 ************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES *************
139 TABLES t1 and t2 must be equal otherwise an error will be thrown.
140 connection slave;
141 include/diff_tables.inc [master:t1, slave:t1]
142 connection master;
143 DROP TABLE t1;
144 connection slave;
145 ################################################################################
146 # NULL ---> NOT NULL (STRICT MODE)
147 # UNCOMMENT THIS AFTER FIXING BUG#43992
148 ################################################################################
149 ################################################################################
150 # NULL ---> NOT NULL (NON-STRICT MODE)
151 ################################################################################
152 connection master;
153 SET SQL_LOG_BIN= 0;
154 CREATE TABLE t1(`a` INT NOT NULL, `b` INT,
155 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
156 CREATE TABLE t2(`a` INT NOT NULL, `b` INT,
157 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
158 CREATE TABLE t3(`a` INT NOT NULL, `b` INT,
159 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
160 SET SQL_LOG_BIN= 1;
161 connection slave;
162 CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL,
163 `c` INT NOT NULL,
164 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
165 CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL,
166 `c` INT,
167 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
168 CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL,
169 `c` INT DEFAULT 500,
170 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
171 ************* EXECUTION WITH INSERTS *************
172 connection master;
173 INSERT INTO t1(a) VALUES (1);
174 INSERT INTO t1(a, b) VALUES (2, NULL);
175 INSERT INTO t1(a, b) VALUES (3, 1);
176 INSERT INTO t2(a) VALUES (1);
177 INSERT INTO t2(a, b) VALUES (2, NULL);
178 INSERT INTO t2(a, b) VALUES (3, 1);
179 INSERT INTO t3(a) VALUES (1);
180 INSERT INTO t3(a, b) VALUES (2, NULL);
181 INSERT INTO t3(a, b) VALUES (3, 1);
182 INSERT INTO t3(a, b) VALUES (4, 1);
183 REPLACE INTO t3(a, b) VALUES (5, null);
184 REPLACE INTO t3(a, b) VALUES (3, null);
185 UPDATE t3 SET b = NULL where a = 4;
186 ************* SHOWING THE RESULT SETS *************
187 connection master;
188 connection slave;
189 connection master;
190 SELECT * FROM t1 ORDER BY a;
191 a b
192 1 NULL
193 2 NULL
194 3 1
195 connection slave;
196 SELECT * FROM t1 ORDER BY a;
197 a b c
198 1 0 0
199 2 0 0
200 3 1 0
201 connection master;
202 SELECT * FROM t2 ORDER BY a;
203 a b
204 1 NULL
205 2 NULL
206 3 1
207 connection slave;
208 SELECT * FROM t2 ORDER BY a;
209 a b c
210 1 0 NULL
211 2 0 NULL
212 3 1 NULL
213 connection master;
214 SELECT * FROM t3 ORDER BY a;
215 a b
216 1 NULL
217 2 NULL
218 3 NULL
219 4 NULL
220 5 NULL
221 connection slave;
222 SELECT * FROM t3 ORDER BY a;
223 a b c
224 1 0 500
225 2 0 500
226 3 0 500
227 4 0 500
228 5 0 500
229 connection master;
230 DROP TABLE t1;
231 DROP TABLE t2;
232 DROP TABLE t3;
233 connection slave;
234 include/rpl_end.inc