1include/master-slave.inc 2[connection master] 3call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); 4 5-------- Test for BUG#9361 -------- 6CREATE TABLE t1 ( 7a int unsigned not null auto_increment primary key, 8b int unsigned 9) ENGINE=MyISAM; 10CREATE TABLE t2 ( 11a int unsigned not null auto_increment primary key, 12b int unsigned 13) ENGINE=MyISAM; 14INSERT INTO t1 VALUES (NULL, 0); 15INSERT INTO t1 SELECT NULL, 0 FROM t1; 16INSERT INTO t2 VALUES (NULL, 0), (NULL,1); 17SELECT * FROM t1 ORDER BY a; 18a b 191 0 202 0 21SELECT * FROM t2 ORDER BY a; 22a b 231 0 242 1 25UPDATE t2, (SELECT a FROM t1 ORDER BY a) AS t SET t2.b = t.a+5 ; 26SELECT * FROM t1 ORDER BY a; 27a b 281 0 292 0 30SELECT * FROM t2 ORDER BY a; 31a b 321 6 332 6 34connection slave; 35connection slave; 36SELECT * FROM t1 ORDER BY a; 37a b 381 0 392 0 40SELECT * FROM t2 ORDER BY a; 41a b 421 6 432 6 44connection master; 45drop table t1,t2; 46 47-------- Test 1 for BUG#9361 -------- 48connection master; 49DROP TABLE IF EXISTS t1; 50DROP TABLE IF EXISTS t2; 51CREATE TABLE t1 ( 52a1 char(30), 53a2 int, 54a3 int, 55a4 char(30), 56a5 char(30) 57); 58CREATE TABLE t2 ( 59b1 int, 60b2 char(30) 61); 62INSERT INTO t1 VALUES ('Yes', 1, NULL, 'foo', 'bar'); 63INSERT INTO t2 VALUES (1, 'baz'); 64UPDATE t1 a, t2 65SET a.a1 = 'No' 66WHERE a.a2 = 67(SELECT b1 68FROM t2 69WHERE b2 = 'baz') 70AND a.a3 IS NULL 71AND a.a4 = 'foo' 72AND a.a5 = 'bar'; 73connection slave; 74connection slave; 75SELECT * FROM t1; 76a1 a2 a3 a4 a5 77No 1 NULL foo bar 78SELECT * FROM t2; 79b1 b2 801 baz 81connection master; 82DROP TABLE t1, t2; 83 84-------- Test 2 for BUG#9361 -------- 85connection master; 86DROP TABLE IF EXISTS t1; 87DROP TABLE IF EXISTS t2; 88DROP TABLE IF EXISTS t3; 89CREATE TABLE t1 ( 90i INT, 91j INT, 92x INT, 93y INT, 94z INT 95); 96CREATE TABLE t2 ( 97i INT, 98k INT, 99x INT, 100y INT, 101z INT 102); 103CREATE TABLE t3 ( 104j INT, 105k INT, 106x INT, 107y INT, 108z INT 109); 110INSERT INTO t1 VALUES ( 1, 2,13,14,15); 111INSERT INTO t2 VALUES ( 1, 3,23,24,25); 112INSERT INTO t3 VALUES ( 2, 3, 1,34,35), ( 2, 3, 1,34,36); 113UPDATE t1 AS a 114INNER JOIN t2 AS b 115ON a.i = b.i 116INNER JOIN t3 AS c 117ON a.j = c.j AND b.k = c.k 118SET a.x = b.x, 119a.y = b.y, 120a.z = ( 121SELECT sum(z) 122FROM t3 123WHERE y = 34 124) 125WHERE b.x = 23; 126connection slave; 127connection slave; 128SELECT * FROM t1; 129i j x y z 1301 2 23 24 71 131connection master; 132DROP TABLE t1, t2, t3; 133DROP TABLE IF EXISTS t1; 134Warnings: 135Note 1051 Unknown table 'test.t1' 136DROP TABLE IF EXISTS t2; 137Warnings: 138Note 1051 Unknown table 'test.t2' 139CREATE TABLE t1 ( 140idp int(11) NOT NULL default '0', 141idpro int(11) default NULL, 142price decimal(19,4) default NULL, 143PRIMARY KEY (idp) 144); 145CREATE TABLE t2 ( 146idpro int(11) NOT NULL default '0', 147price decimal(19,4) default NULL, 148nbprice int(11) default NULL, 149PRIMARY KEY (idpro) 150); 151INSERT INTO t1 VALUES 152(1,1,'3.0000'), 153(2,2,'1.0000'), 154(3,1,'1.0000'), 155(4,1,'4.0000'), 156(5,3,'2.0000'), 157(6,2,'4.0000'); 158INSERT INTO t2 VALUES 159(1,'0.0000',0), 160(2,'0.0000',0), 161(3,'0.0000',0); 162update 163t2 164join 165( select idpro, min(price) as min_price, count(*) as nbr_price 166from t1 167where idpro>0 and price>0 168group by idpro 169) as table_price 170on t2.idpro = table_price.idpro 171set t2.price = table_price.min_price, 172t2.nbprice = table_price.nbr_price; 173select "-- MASTER AFTER JOIN --" as ""; 174 175-- MASTER AFTER JOIN -- 176select * from t1; 177idp idpro price 1781 1 3.0000 1792 2 1.0000 1803 1 1.0000 1814 1 4.0000 1825 3 2.0000 1836 2 4.0000 184select * from t2; 185idpro price nbprice 1861 1.0000 3 1872 1.0000 2 1883 2.0000 1 189connection slave; 190select "-- SLAVE AFTER JOIN --" as ""; 191 192-- SLAVE AFTER JOIN -- 193select * from t1; 194idp idpro price 1951 1 3.0000 1962 2 1.0000 1973 1 1.0000 1984 1 4.0000 1995 3 2.0000 2006 2 4.0000 201select * from t2; 202idpro price nbprice 2031 1.0000 3 2042 1.0000 2 2053 2.0000 1 206connection master; 207DROP TABLE t1, t2; 208include/rpl_end.inc 209