Debian Codegraph @

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

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