Debian Codegraph @

mysql-test/suite/vcol/r/update_binlog.result

raw
1 set binlog_row_image="FULL";
2 set @@default_storage_engine="myisam";
3 create table t1 (a int, b int as (a+1), c int as (b+1) stored) charset=latin1;
4 insert t1 set a=1;
5 select * from t1;
6 a b c
7 1 2 3
8 update t1 set a=2;
9 select * from t1;
10 a b c
11 2 3 4
12 drop table t1;
13 create table t1 (a int, c int as(a), p varchar(20) as(rtrim(y)), y char(20), index (p,c)) charset=latin1;
14 show create table t1;
15 Table Create Table
16 t1 CREATE TABLE `t1` (
17 `a` int(11) DEFAULT NULL,
18 `c` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL,
19 `p` varchar(20) GENERATED ALWAYS AS (rtrim(`y`)) VIRTUAL,
20 `y` char(20) DEFAULT NULL,
21 KEY `p` (`p`,`c`)
22 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
23 insert into t1 (a,y) values(1, "yyy");
24 update t1 set a = 100 where a = 1;
25 check table t1;
26 Table Op Msg_type Msg_text
27 test.t1 check status OK
28 drop table t1;
29 create table t1 (
30 a varchar(10000),
31 b varchar(3000),
32 c varchar(14000) generated always as (concat(a,b)) virtual,
33 d varchar(5000) generated always as (b) virtual,
34 e int(11) generated always as (10) virtual,
35 h int(11) not null primary key,
36 index(c(100), d(20))) charset=latin1;
37 insert t1 (a,b,h) values (repeat('g', 10000), repeat('x', 2800), 1);
38 update t1 set a = repeat(cast(1 as char), 2000);
39 drop table t1;
40 create table t1 (
41 a varchar(10000),
42 b varchar(3000),
43 c varchar(14000) generated always as (concat(a,b)) virtual,
44 i varchar(5000) generated always as (b) virtual,
45 d varchar(5000) generated always as (i) virtual,
46 e int(11) generated always as (10) virtual,
47 h int(11) not null primary key,
48 index(c(100), d(20))) charset=latin1;
49 insert t1 (a,b,h) values (repeat('g', 10000), repeat('x', 2800), 1);
50 update t1 set a = repeat(cast(1 as char), 2000);
51 drop table t1;
52 create table t1(a blob not null, b int, c varbinary (10) generated always as (a) virtual, unique (c(9)));
53 insert t1 (a,b) values ('a', 1);
54 replace t1 set a = 'a',b =1;
55 insert t1 (a,b) values ('a', 1) on duplicate key update a='b', b=2;
56 select * from t1;
57 a b c
58 b 2 b
59 drop table t1;
60 create table t (a int primary key, b int, c int as (b), index (c));
61 insert t (a,b) values (9,0);
62 create table t2 select * from t;
63 update t, t2 set t.b=10 where t.a=t2.a;
64 check table t;
65 Table Op Msg_type Msg_text
66 test.t check status OK
67 select * from t;
68 a b c
69 9 10 10
70 drop table t, t2;
71 create table t1 (a int, b int, c int, d int, e int);
72 insert t1 values (1,2,3,4,5), (1,2,3,4,5);
73 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
74 create table t (a int primary key,
75 b int, c blob as (b), index (c(57)),
76 d blob, e blob as (d), index (e(57)))
77 replace select * from t1;
78 Warnings:
79 Warning 1906 The value specified for generated column 'c' in table 't' has been ignored
80 Warning 1906 The value specified for generated column 'e' in table 't' has been ignored
81 Warning 1906 The value specified for generated column 'c' in table 't' has been ignored
82 Warning 1906 The value specified for generated column 'e' in table 't' has been ignored
83 check table t;
84 Table Op Msg_type Msg_text
85 test.t check status OK
86 select * from t;
87 a b c d e
88 1 2 2 4 4
89 update t set a=10, b=1, d=1;
90 check table t;
91 Table Op Msg_type Msg_text
92 test.t check status OK
93 select * from t;
94 a b c d e
95 10 1 1 1 1
96 replace t (a,b,d) values (10,2,2);
97 check table t;
98 Table Op Msg_type Msg_text
99 test.t check status OK
100 select * from t;
101 a b c d e
102 10 2 2 2 2
103 insert t(a,b,d) values (10) on duplicate key update b=3;
104 ERROR 21S01: Column count doesn't match value count at row 1
105 insert t(a,b,d) values (10,2,2) on duplicate key update b=3, d=3;
106 check table t;
107 Table Op Msg_type Msg_text
108 test.t check status OK
109 select * from t;
110 a b c d e
111 10 3 3 3 3
112 replace t (a,b,d) select 10,4,4;
113 check table t;
114 Table Op Msg_type Msg_text
115 test.t check status OK
116 select * from t;
117 a b c d e
118 10 4 4 4 4
119 insert t(a,b,d) select 10,4,4 on duplicate key update b=5, d=5;
120 check table t;
121 Table Op Msg_type Msg_text
122 test.t check status OK
123 select * from t;
124 a b c d e
125 10 5 5 5 5
126 replace delayed t (a,b,d) values (10,6,6);
127 flush tables t;
128 check table t;
129 Table Op Msg_type Msg_text
130 test.t check status OK
131 select * from t;
132 a b c d e
133 10 6 6 6 6
134 insert delayed t(a,b,d) values (10,6,6) on duplicate key update b=7, d=7;
135 flush tables t;
136 check table t;
137 Table Op Msg_type Msg_text
138 test.t check status OK
139 select * from t;
140 a b c d e
141 10 7 7 7 7
142 load data infile 'MYSQLTEST_VARDIR/tmp/vblobs.txt' replace into table t;
143 check table t;
144 Table Op Msg_type Msg_text
145 test.t check status OK
146 select * from t;
147 a b c d e
148 10 8 8 8 8
149 update t set a=11, b=9, d=9 where a>5;
150 check table t;
151 Table Op Msg_type Msg_text
152 test.t check status OK
153 select * from t;
154 a b c d e
155 11 9 9 9 9
156 create table t2 select * from t;
157 update t, t2 set t.b=10, t.d=10 where t.a=t2.a;
158 check table t;
159 Table Op Msg_type Msg_text
160 test.t check status OK
161 select * from t;
162 a b c d e
163 11 10 10 10 10
164 update t, t tt set t.b=11, tt.d=11 where t.a=tt.a;
165 check table t;
166 Table Op Msg_type Msg_text
167 test.t check status OK
168 select * from t;
169 a b c d e
170 11 11 11 11 11
171 drop table t, t1, t2;
172 create table t (f1 int, f2 int, f3 int as (f1*2) virtual, key(f3,f2));
173 insert into t (f1,f2) values (1,1),(2,2);
174 create view v as
175 select a2.f1, a2.f2, a1.f3
176 from t a1, t a2
177 where a2.f3 <> 0
178 with local check option;
179 update v set f3 = 52;
180 drop view v;
181 drop table t;
182 set binlog_row_image="MINIMAL";
183 create table t1 (a int, b int as (a+1), c int as (b+1) stored) charset=latin1;
184 insert t1 set a=1;
185 select * from t1;
186 a b c
187 1 2 3
188 update t1 set a=2;
189 select * from t1;
190 a b c
191 2 3 4
192 drop table t1;
193 create table t1 (a int, c int as(a), p varchar(20) as(rtrim(y)), y char(20), index (p,c)) charset=latin1;
194 show create table t1;
195 Table Create Table
196 t1 CREATE TABLE `t1` (
197 `a` int(11) DEFAULT NULL,
198 `c` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL,
199 `p` varchar(20) GENERATED ALWAYS AS (rtrim(`y`)) VIRTUAL,
200 `y` char(20) DEFAULT NULL,
201 KEY `p` (`p`,`c`)
202 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
203 insert into t1 (a,y) values(1, "yyy");
204 update t1 set a = 100 where a = 1;
205 check table t1;
206 Table Op Msg_type Msg_text
207 test.t1 check status OK
208 drop table t1;
209 create table t1 (
210 a varchar(10000),
211 b varchar(3000),
212 c varchar(14000) generated always as (concat(a,b)) virtual,
213 d varchar(5000) generated always as (b) virtual,
214 e int(11) generated always as (10) virtual,
215 h int(11) not null primary key,
216 index(c(100), d(20))) charset=latin1;
217 insert t1 (a,b,h) values (repeat('g', 10000), repeat('x', 2800), 1);
218 update t1 set a = repeat(cast(1 as char), 2000);
219 drop table t1;
220 create table t1 (
221 a varchar(10000),
222 b varchar(3000),
223 c varchar(14000) generated always as (concat(a,b)) virtual,
224 i varchar(5000) generated always as (b) virtual,
225 d varchar(5000) generated always as (i) virtual,
226 e int(11) generated always as (10) virtual,
227 h int(11) not null primary key,
228 index(c(100), d(20))) charset=latin1;
229 insert t1 (a,b,h) values (repeat('g', 10000), repeat('x', 2800), 1);
230 update t1 set a = repeat(cast(1 as char), 2000);
231 drop table t1;
232 create table t1(a blob not null, b int, c varbinary (10) generated always as (a) virtual, unique (c(9)));
233 insert t1 (a,b) values ('a', 1);
234 replace t1 set a = 'a',b =1;
235 insert t1 (a,b) values ('a', 1) on duplicate key update a='b', b=2;
236 select * from t1;
237 a b c
238 b 2 b
239 drop table t1;
240 create table t (a int primary key, b int, c int as (b), index (c));
241 insert t (a,b) values (9,0);
242 create table t2 select * from t;
243 update t, t2 set t.b=10 where t.a=t2.a;
244 check table t;
245 Table Op Msg_type Msg_text
246 test.t check status OK
247 select * from t;
248 a b c
249 9 10 10
250 drop table t, t2;
251 create table t1 (a int, b int, c int, d int, e int);
252 insert t1 values (1,2,3,4,5), (1,2,3,4,5);
253 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
254 create table t (a int primary key,
255 b int, c blob as (b), index (c(57)),
256 d blob, e blob as (d), index (e(57)))
257 replace select * from t1;
258 Warnings:
259 Warning 1906 The value specified for generated column 'c' in table 't' has been ignored
260 Warning 1906 The value specified for generated column 'e' in table 't' has been ignored
261 Warning 1906 The value specified for generated column 'c' in table 't' has been ignored
262 Warning 1906 The value specified for generated column 'e' in table 't' has been ignored
263 check table t;
264 Table Op Msg_type Msg_text
265 test.t check status OK
266 select * from t;
267 a b c d e
268 1 2 2 4 4
269 update t set a=10, b=1, d=1;
270 check table t;
271 Table Op Msg_type Msg_text
272 test.t check status OK
273 select * from t;
274 a b c d e
275 10 1 1 1 1
276 replace t (a,b,d) values (10,2,2);
277 check table t;
278 Table Op Msg_type Msg_text
279 test.t check status OK
280 select * from t;
281 a b c d e
282 10 2 2 2 2
283 insert t(a,b,d) values (10) on duplicate key update b=3;
284 ERROR 21S01: Column count doesn't match value count at row 1
285 insert t(a,b,d) values (10,2,2) on duplicate key update b=3, d=3;
286 check table t;
287 Table Op Msg_type Msg_text
288 test.t check status OK
289 select * from t;
290 a b c d e
291 10 3 3 3 3
292 replace t (a,b,d) select 10,4,4;
293 check table t;
294 Table Op Msg_type Msg_text
295 test.t check status OK
296 select * from t;
297 a b c d e
298 10 4 4 4 4
299 insert t(a,b,d) select 10,4,4 on duplicate key update b=5, d=5;
300 check table t;
301 Table Op Msg_type Msg_text
302 test.t check status OK
303 select * from t;
304 a b c d e
305 10 5 5 5 5
306 replace delayed t (a,b,d) values (10,6,6);
307 flush tables t;
308 check table t;
309 Table Op Msg_type Msg_text
310 test.t check status OK
311 select * from t;
312 a b c d e
313 10 6 6 6 6
314 insert delayed t(a,b,d) values (10,6,6) on duplicate key update b=7, d=7;
315 flush tables t;
316 check table t;
317 Table Op Msg_type Msg_text
318 test.t check status OK
319 select * from t;
320 a b c d e
321 10 7 7 7 7
322 load data infile 'MYSQLTEST_VARDIR/tmp/vblobs.txt' replace into table t;
323 check table t;
324 Table Op Msg_type Msg_text
325 test.t check status OK
326 select * from t;
327 a b c d e
328 10 8 8 8 8
329 update t set a=11, b=9, d=9 where a>5;
330 check table t;
331 Table Op Msg_type Msg_text
332 test.t check status OK
333 select * from t;
334 a b c d e
335 11 9 9 9 9
336 create table t2 select * from t;
337 update t, t2 set t.b=10, t.d=10 where t.a=t2.a;
338 check table t;
339 Table Op Msg_type Msg_text
340 test.t check status OK
341 select * from t;
342 a b c d e
343 11 10 10 10 10
344 update t, t tt set t.b=11, tt.d=11 where t.a=tt.a;
345 check table t;
346 Table Op Msg_type Msg_text
347 test.t check status OK
348 select * from t;
349 a b c d e
350 11 11 11 11 11
351 drop table t, t1, t2;
352 create table t (f1 int, f2 int, f3 int as (f1*2) virtual, key(f3,f2));
353 insert into t (f1,f2) values (1,1),(2,2);
354 create view v as
355 select a2.f1, a2.f2, a1.f3
356 from t a1, t a2
357 where a2.f3 <> 0
358 with local check option;
359 update v set f3 = 52;
360 drop view v;
361 drop table t;