Debian Codegraph @

mysql-test/suite/rpl_ndb/r/rpl_ndb_multi_update3.result

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