2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
8
CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL,
10
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
11
CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL,
12
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
13
CREATE TABLE t3(`a` INT, `b` DATE DEFAULT NULL,
14
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
15
CREATE TABLE t4(`a` INT, `b` DATE DEFAULT NULL,
17
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
19
CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL,
21
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
22
CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL,
23
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
24
CREATE TABLE t3(`a` INT, `b` DATE DEFAULT '0000-00-00',
26
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
27
CREATE TABLE t4(`a` INT, `b` DATE DEFAULT '0000-00-00',
28
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
29
************* EXECUTION WITH INSERTS *************
30
INSERT INTO t1(a,b,c) VALUES (1, null, 1);
31
INSERT INTO t1(a,b,c) VALUES (2,'1111-11-11', 2);
32
INSERT INTO t1(a,b) VALUES (3, null);
33
INSERT INTO t1(a,c) VALUES (4, 4);
34
INSERT INTO t1(a) VALUES (5);
35
INSERT INTO t2(a,b) VALUES (1, null);
36
INSERT INTO t2(a,b) VALUES (2,'1111-11-11');
37
INSERT INTO t2(a) VALUES (3);
38
INSERT INTO t3(a,b) VALUES (1, null);
39
INSERT INTO t3(a,b) VALUES (2,'1111-11-11');
40
INSERT INTO t3(a) VALUES (3);
41
INSERT INTO t4(a,b,c) VALUES (1, null, 1);
42
INSERT INTO t4(a,b,c) VALUES (2,'1111-11-11', 2);
43
INSERT INTO t4(a,b) VALUES (3, null);
44
INSERT INTO t4(a,c) VALUES (4, 4);
45
INSERT INTO t4(a) VALUES (5);
46
************* SHOWING THE RESULT SETS WITH INSERTS *************
47
TABLES t1 and t2 must be equal otherwise an error will be thrown.
48
Comparing tables master:test.t1 and slave:test.t1
49
Comparing tables master:test.t2 and slave:test.t2
50
TABLES t2 and t3 must be different.
51
SELECT * FROM t3 ORDER BY a;
56
SELECT * FROM t3 ORDER BY a;
61
SELECT * FROM t4 ORDER BY a;
68
SELECT * FROM t4 ORDER BY a;
75
************* EXECUTION WITH UPDATES and REPLACES *************
77
INSERT INTO t1(a,b,c) VALUES (1,'1111-11-11', 1);
78
REPLACE INTO t1(a,b,c) VALUES (2,'1111-11-11', 2);
79
UPDATE t1 set b= NULL, c= 300 where a= 1;
80
REPLACE INTO t1(a,b,c) VALUES (2, NULL, 300);
81
************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES *************
82
TABLES t1 and t2 must be equal otherwise an error will be thrown.
83
Comparing tables master:test.t1 and slave:test.t1
84
************* CLEANING *************
90
CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT NULL, `c` BIT DEFAULT NULL,
91
PRIMARY KEY (`a`)) ENGINE= MyISAM;
93
CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT b'01', `c` BIT DEFAULT NULL,
94
PRIMARY KEY (`a`)) ENGINE= MyISAM;
95
************* EXECUTION WITH INSERTS *************
96
INSERT INTO t1(a,b,c) VALUES (1, null, b'01');
97
INSERT INTO t1(a,b,c) VALUES (2,b'00', b'01');
98
INSERT INTO t1(a,b) VALUES (3, null);
99
INSERT INTO t1(a,c) VALUES (4, b'01');
100
INSERT INTO t1(a) VALUES (5);
101
************* SHOWING THE RESULT SETS WITH INSERTS *************
102
TABLES t1 and t2 must be different.
103
SELECT a,b+0,c+0 FROM t1 ORDER BY a;
110
SELECT a,b+0,c+0 FROM t1 ORDER BY a;
117
************* EXECUTION WITH UPDATES and REPLACES *************
119
INSERT INTO t1(a,b,c) VALUES (1,b'00', b'01');
120
REPLACE INTO t1(a,b,c) VALUES (2,b'00',b'01');
121
UPDATE t1 set b= NULL, c= b'00' where a= 1;
122
REPLACE INTO t1(a,b,c) VALUES (2, NULL, b'00');
123
************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES *************
124
TABLES t1 and t2 must be equal otherwise an error will be thrown.
125
Comparing tables master:test.t1 and slave:test.t1
127
################################################################################
128
# NULL ---> NOT NULL (STRICT MODE)
129
# UNCOMMENT THIS AFTER FIXING BUG#43992
130
################################################################################
131
################################################################################
132
# NULL ---> NOT NULL (NON-STRICT MODE)
133
################################################################################
135
CREATE TABLE t1(`a` INT NOT NULL, `b` INT,
136
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
137
CREATE TABLE t2(`a` INT NOT NULL, `b` INT,
138
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
139
CREATE TABLE t3(`a` INT NOT NULL, `b` INT,
140
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
142
CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL,
144
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
145
CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL,
147
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
148
CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL,
150
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
151
************* EXECUTION WITH INSERTS *************
152
INSERT INTO t1(a) VALUES (1);
153
INSERT INTO t1(a, b) VALUES (2, NULL);
154
INSERT INTO t1(a, b) VALUES (3, 1);
155
INSERT INTO t2(a) VALUES (1);
156
INSERT INTO t2(a, b) VALUES (2, NULL);
157
INSERT INTO t2(a, b) VALUES (3, 1);
158
INSERT INTO t3(a) VALUES (1);
159
INSERT INTO t3(a, b) VALUES (2, NULL);
160
INSERT INTO t3(a, b) VALUES (3, 1);
161
INSERT INTO t3(a, b) VALUES (4, 1);
162
REPLACE INTO t3(a, b) VALUES (5, null);
163
REPLACE INTO t3(a, b) VALUES (3, null);
164
UPDATE t3 SET b = NULL where a = 4;
165
************* SHOWING THE RESULT SETS *************
166
SELECT * FROM t1 ORDER BY a;
171
SELECT * FROM t1 ORDER BY a;
176
SELECT * FROM t2 ORDER BY a;
181
SELECT * FROM t2 ORDER BY a;
186
SELECT * FROM t3 ORDER BY a;
193
SELECT * FROM t3 ORDER BY a;