1 include/master-slave.inc
4 CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL,
6 PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
7 CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL,
8 PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
9 CREATE TABLE t3(`a` INT, `b` DATE DEFAULT NULL,
10 PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
11 CREATE TABLE t4(`a` INT, `b` DATE DEFAULT NULL,
13 PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
15 CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL,
17 PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
18 CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL,
19 PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
20 CREATE TABLE t3(`a` INT, `b` DATE DEFAULT '0000-00-00',
22 PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
23 CREATE TABLE t4(`a` INT, `b` DATE DEFAULT '0000-00-00',
24 PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
25 ************* EXECUTION WITH INSERTS *************
26 INSERT INTO t1(a,b,c) VALUES (1, null, 1);
27 INSERT INTO t1(a,b,c) VALUES (2,'1111-11-11', 2);
28 INSERT INTO t1(a,b) VALUES (3, null);
29 INSERT INTO t1(a,c) VALUES (4, 4);
30 INSERT INTO t1(a) VALUES (5);
31 INSERT INTO t2(a,b) VALUES (1, null);
32 INSERT INTO t2(a,b) VALUES (2,'1111-11-11');
33 INSERT INTO t2(a) VALUES (3);
34 INSERT INTO t3(a,b) VALUES (1, null);
35 INSERT INTO t3(a,b) VALUES (2,'1111-11-11');
36 INSERT INTO t3(a) VALUES (3);
37 INSERT INTO t4(a,b,c) VALUES (1, null, 1);
38 INSERT INTO t4(a,b,c) VALUES (2,'1111-11-11', 2);
39 INSERT INTO t4(a,b) VALUES (3, null);
40 INSERT INTO t4(a,c) VALUES (4, 4);
41 INSERT INTO t4(a) VALUES (5);
42 ************* SHOWING THE RESULT SETS WITH INSERTS *************
43 TABLES t1 and t2 must be equal otherwise an error will be thrown.
44 include/diff_tables.inc [master:t1, slave:t1]
45 include/diff_tables.inc [master:t2, slave:t2]
46 TABLES t2 and t3 must be different.
47 SELECT * FROM t3 ORDER BY a;
52 SELECT * FROM t3 ORDER BY a;
57 SELECT * FROM t4 ORDER BY a;
64 SELECT * FROM t4 ORDER BY a;
71 ************* EXECUTION WITH UPDATES and REPLACES *************
73 INSERT INTO t1(a,b,c) VALUES (1,'1111-11-11', 1);
74 REPLACE INTO t1(a,b,c) VALUES (2,'1111-11-11', 2);
75 UPDATE t1 set b= NULL, c= 300 where a= 1;
76 REPLACE INTO t1(a,b,c) VALUES (2, NULL, 300);
77 ************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES *************
78 TABLES t1 and t2 must be equal otherwise an error will be thrown.
79 include/diff_tables.inc [master:t1, slave:t1]
80 ************* CLEANING *************
86 CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT NULL, `c` BIT DEFAULT NULL,
87 PRIMARY KEY (`a`)) ENGINE= MyISAM;
89 CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT b'01', `c` BIT DEFAULT NULL,
90 PRIMARY KEY (`a`)) ENGINE= MyISAM;
91 ************* EXECUTION WITH INSERTS *************
92 INSERT INTO t1(a,b,c) VALUES (1, null, b'01');
93 INSERT INTO t1(a,b,c) VALUES (2,b'00', b'01');
94 INSERT INTO t1(a,b) VALUES (3, null);
95 INSERT INTO t1(a,c) VALUES (4, b'01');
96 INSERT INTO t1(a) VALUES (5);
97 ************* SHOWING THE RESULT SETS WITH INSERTS *************
98 TABLES t1 and t2 must be different.
99 SELECT a,b+0,c+0 FROM t1 ORDER BY a;
106 SELECT a,b+0,c+0 FROM t1 ORDER BY a;
113 ************* EXECUTION WITH UPDATES and REPLACES *************
115 INSERT INTO t1(a,b,c) VALUES (1,b'00', b'01');
116 REPLACE INTO t1(a,b,c) VALUES (2,b'00',b'01');
117 UPDATE t1 set b= NULL, c= b'00' where a= 1;
118 REPLACE INTO t1(a,b,c) VALUES (2, NULL, b'00');
119 ************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES *************
120 TABLES t1 and t2 must be equal otherwise an error will be thrown.
121 include/diff_tables.inc [master:t1, slave:t1]
123 ################################################################################
124 # NULL ---> NOT NULL (STRICT MODE)
125 # UNCOMMENT THIS AFTER FIXING BUG#43992
126 ################################################################################
127 ################################################################################
128 # NULL ---> NOT NULL (NON-STRICT MODE)
129 ################################################################################
131 CREATE TABLE t1(`a` INT NOT NULL, `b` INT,
132 PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
133 CREATE TABLE t2(`a` INT NOT NULL, `b` INT,
134 PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
135 CREATE TABLE t3(`a` INT NOT NULL, `b` INT,
136 PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
138 CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL,
140 PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
141 CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL,
143 PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
144 CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL,
146 PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
147 ************* EXECUTION WITH INSERTS *************
148 INSERT INTO t1(a) VALUES (1);
149 INSERT INTO t1(a, b) VALUES (2, NULL);
150 INSERT INTO t1(a, b) VALUES (3, 1);
151 INSERT INTO t2(a) VALUES (1);
152 INSERT INTO t2(a, b) VALUES (2, NULL);
153 INSERT INTO t2(a, b) VALUES (3, 1);
154 INSERT INTO t3(a) VALUES (1);
155 INSERT INTO t3(a, b) VALUES (2, NULL);
156 INSERT INTO t3(a, b) VALUES (3, 1);
157 INSERT INTO t3(a, b) VALUES (4, 1);
158 REPLACE INTO t3(a, b) VALUES (5, null);
159 REPLACE INTO t3(a, b) VALUES (3, null);
160 UPDATE t3 SET b = NULL where a = 4;
161 ************* SHOWING THE RESULT SETS *************
162 SELECT * FROM t1 ORDER BY a;
167 SELECT * FROM t1 ORDER BY a;
172 SELECT * FROM t2 ORDER BY a;
177 SELECT * FROM t2 ORDER BY a;
182 SELECT * FROM t3 ORDER BY a;
189 SELECT * FROM t3 ORDER BY a;