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