Edit File: rpl_not_null_innodb.result
include/master-slave.inc [connection master] SET SQL_LOG_BIN= 0; CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL, `c` INT DEFAULT NULL, PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL, PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; CREATE TABLE t3(`a` INT, `b` DATE DEFAULT NULL, PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; CREATE TABLE t4(`a` INT, `b` DATE DEFAULT NULL, `c` INT DEFAULT NULL, PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; SET SQL_LOG_BIN= 1; CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL, `c` INT DEFAULT NULL, PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL, PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; CREATE TABLE t3(`a` INT, `b` DATE DEFAULT '0000-00-00', `c` INT DEFAULT 500, PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; CREATE TABLE t4(`a` INT, `b` DATE DEFAULT '0000-00-00', PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; ************* EXECUTION WITH INSERTS ************* INSERT INTO t1(a,b,c) VALUES (1, null, 1); INSERT INTO t1(a,b,c) VALUES (2,'1111-11-11', 2); INSERT INTO t1(a,b) VALUES (3, null); INSERT INTO t1(a,c) VALUES (4, 4); INSERT INTO t1(a) VALUES (5); INSERT INTO t2(a,b) VALUES (1, null); INSERT INTO t2(a,b) VALUES (2,'1111-11-11'); INSERT INTO t2(a) VALUES (3); INSERT INTO t3(a,b) VALUES (1, null); INSERT INTO t3(a,b) VALUES (2,'1111-11-11'); INSERT INTO t3(a) VALUES (3); INSERT INTO t4(a,b,c) VALUES (1, null, 1); INSERT INTO t4(a,b,c) VALUES (2,'1111-11-11', 2); INSERT INTO t4(a,b) VALUES (3, null); INSERT INTO t4(a,c) VALUES (4, 4); INSERT INTO t4(a) VALUES (5); ************* SHOWING THE RESULT SETS WITH INSERTS ************* TABLES t1 and t2 must be equal otherwise an error will be thrown. include/diff_tables.inc [master:t1, slave:t1] include/diff_tables.inc [master:t2, slave:t2] TABLES t2 and t3 must be different. SELECT * FROM t3 ORDER BY a; a b 1 NULL 2 1111-11-11 3 NULL SELECT * FROM t3 ORDER BY a; a b c 1 NULL 500 2 1111-11-11 500 3 NULL 500 SELECT * FROM t4 ORDER BY a; a b c 1 NULL 1 2 1111-11-11 2 3 NULL NULL 4 NULL 4 5 NULL NULL SELECT * FROM t4 ORDER BY a; a b 1 NULL 2 1111-11-11 3 NULL 4 NULL 5 NULL ************* EXECUTION WITH UPDATES and REPLACES ************* DELETE FROM t1; INSERT INTO t1(a,b,c) VALUES (1,'1111-11-11', 1); REPLACE INTO t1(a,b,c) VALUES (2,'1111-11-11', 2); UPDATE t1 set b= NULL, c= 300 where a= 1; REPLACE INTO t1(a,b,c) VALUES (2, NULL, 300); ************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES ************* TABLES t1 and t2 must be equal otherwise an error will be thrown. include/diff_tables.inc [master:t1, slave:t1] ************* CLEANING ************* DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; SET SQL_LOG_BIN= 0; CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT NULL, `c` BIT DEFAULT NULL, PRIMARY KEY (`a`)) ENGINE= Innodb; SET SQL_LOG_BIN= 1; CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT b'01', `c` BIT DEFAULT NULL, PRIMARY KEY (`a`)) ENGINE= Innodb; ************* EXECUTION WITH INSERTS ************* INSERT INTO t1(a,b,c) VALUES (1, null, b'01'); INSERT INTO t1(a,b,c) VALUES (2,b'00', b'01'); INSERT INTO t1(a,b) VALUES (3, null); INSERT INTO t1(a,c) VALUES (4, b'01'); INSERT INTO t1(a) VALUES (5); ************* SHOWING THE RESULT SETS WITH INSERTS ************* TABLES t1 and t2 must be different. SELECT a,b+0,c+0 FROM t1 ORDER BY a; a b+0 c+0 1 NULL 1 2 0 1 3 NULL NULL 4 NULL 1 5 NULL NULL SELECT a,b+0,c+0 FROM t1 ORDER BY a; a b+0 c+0 1 NULL 1 2 0 1 3 NULL NULL 4 NULL 1 5 NULL NULL ************* EXECUTION WITH UPDATES and REPLACES ************* DELETE FROM t1; INSERT INTO t1(a,b,c) VALUES (1,b'00', b'01'); REPLACE INTO t1(a,b,c) VALUES (2,b'00',b'01'); UPDATE t1 set b= NULL, c= b'00' where a= 1; REPLACE INTO t1(a,b,c) VALUES (2, NULL, b'00'); ************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES ************* TABLES t1 and t2 must be equal otherwise an error will be thrown. include/diff_tables.inc [master:t1, slave:t1] DROP TABLE t1; ################################################################################ # NULL ---> NOT NULL (STRICT MODE) # UNCOMMENT THIS AFTER FIXING BUG#43992 ################################################################################ ################################################################################ # NULL ---> NOT NULL (NON-STRICT MODE) ################################################################################ SET SQL_LOG_BIN= 0; CREATE TABLE t1(`a` INT NOT NULL, `b` INT, PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; CREATE TABLE t2(`a` INT NOT NULL, `b` INT, PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; CREATE TABLE t3(`a` INT NOT NULL, `b` INT, PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; SET SQL_LOG_BIN= 1; CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL, `c` INT NOT NULL, PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL, `c` INT, PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL, `c` INT DEFAULT 500, PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; ************* EXECUTION WITH INSERTS ************* INSERT INTO t1(a) VALUES (1); INSERT INTO t1(a, b) VALUES (2, NULL); INSERT INTO t1(a, b) VALUES (3, 1); INSERT INTO t2(a) VALUES (1); INSERT INTO t2(a, b) VALUES (2, NULL); INSERT INTO t2(a, b) VALUES (3, 1); INSERT INTO t3(a) VALUES (1); INSERT INTO t3(a, b) VALUES (2, NULL); INSERT INTO t3(a, b) VALUES (3, 1); INSERT INTO t3(a, b) VALUES (4, 1); REPLACE INTO t3(a, b) VALUES (5, null); REPLACE INTO t3(a, b) VALUES (3, null); UPDATE t3 SET b = NULL where a = 4; ************* SHOWING THE RESULT SETS ************* SELECT * FROM t1 ORDER BY a; a b 1 NULL 2 NULL 3 1 SELECT * FROM t1 ORDER BY a; a b c 1 0 0 2 0 0 3 1 0 SELECT * FROM t2 ORDER BY a; a b 1 NULL 2 NULL 3 1 SELECT * FROM t2 ORDER BY a; a b c 1 0 NULL 2 0 NULL 3 1 NULL SELECT * FROM t3 ORDER BY a; a b 1 NULL 2 NULL 3 NULL 4 NULL 5 NULL SELECT * FROM t3 ORDER BY a; a b c 1 0 500 2 0 500 3 0 500 4 0 500 5 0 500 DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; include/rpl_end.inc
Back to File Manager