Debian Codegraph @

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

raw
1 create table t1 (a int, b int as (a+1), c int as (b+1) stored) charset=latin1;
2 insert t1 set a=1;
3 select * from t1;
4 a b c
5 1 2 3
6 update t1 set a=2;
7 select * from t1;
8 a b c
9 2 3 4
10 drop table t1;
11 create table t1 (a int, c int as(a), p varchar(20) as(rtrim(y)), y char(20), index (p,c)) charset=latin1;
12 show create table t1;
13 Table Create Table
14 t1 CREATE TABLE `t1` (
15 `a` int(11) DEFAULT NULL,
16 `c` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL,
17 `p` varchar(20) GENERATED ALWAYS AS (rtrim(`y`)) VIRTUAL,
18 `y` char(20) DEFAULT NULL,
19 KEY `p` (`p`,`c`)
20 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
21 insert into t1 (a,y) values(1, "yyy");
22 update t1 set a = 100 where a = 1;
23 check table t1;
24 Table Op Msg_type Msg_text
25 test.t1 check status OK
26 drop table t1;
27 create table t1 (
28 a varchar(10000),
29 b varchar(3000),
30 c varchar(14000) generated always as (concat(a,b)) virtual,
31 d varchar(5000) generated always as (b) virtual,
32 e int(11) generated always as (10) virtual,
33 h int(11) not null primary key,
34 index(c(100), d(20))) charset=latin1;
35 insert t1 (a,b,h) values (repeat('g', 10000), repeat('x', 2800), 1);
36 update t1 set a = repeat(cast(1 as char), 2000);
37 drop table t1;
38 create table t1 (
39 a varchar(10000),
40 b varchar(3000),
41 c varchar(14000) generated always as (concat(a,b)) virtual,
42 i varchar(5000) generated always as (b) virtual,
43 d varchar(5000) generated always as (i) virtual,
44 e int(11) generated always as (10) virtual,
45 h int(11) not null primary key,
46 index(c(100), d(20))) charset=latin1;
47 insert t1 (a,b,h) values (repeat('g', 10000), repeat('x', 2800), 1);
48 update t1 set a = repeat(cast(1 as char), 2000);
49 drop table t1;
50 create table t1(a blob not null, b int, c varbinary (10) generated always as (a) virtual, unique (c(9)));
51 insert t1 (a,b) values ('a', 1);
52 replace t1 set a = 'a',b =1;
53 insert t1 (a,b) values ('a', 1) on duplicate key update a='b', b=2;
54 select * from t1;
55 a b c
56 b 2 b
57 drop table t1;
58 create table t (a int primary key, b int, c int as (b), index (c));
59 insert t (a,b) values (9,0);
60 create table t2 select * from t;
61 update t, t2 set t.b=10 where t.a=t2.a;
62 check table t;
63 Table Op Msg_type Msg_text
64 test.t check status OK
65 select * from t;
66 a b c
67 9 10 10
68 drop table t, t2;
69 create table t1 (a int, b int, c int, d int, e int);
70 insert t1 values (1,2,3,4,5), (1,2,3,4,5);
71 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
72 create table t (a int primary key,
73 b int, c blob as (b), index (c(57)),
74 d blob, e blob as (d), index (e(57)))
75 replace select * from t1;
76 Warnings:
77 Warning 1906 The value specified for generated column 'c' in table 't' has been ignored
78 Warning 1906 The value specified for generated column 'e' in table 't' has been ignored
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 check table t;
82 Table Op Msg_type Msg_text
83 test.t check status OK
84 select * from t;
85 a b c d e
86 1 2 2 4 4
87 update t set a=10, b=1, d=1;
88 check table t;
89 Table Op Msg_type Msg_text
90 test.t check status OK
91 select * from t;
92 a b c d e
93 10 1 1 1 1
94 replace t (a,b,d) values (10,2,2);
95 check table t;
96 Table Op Msg_type Msg_text
97 test.t check status OK
98 select * from t;
99 a b c d e
100 10 2 2 2 2
101 insert t(a,b,d) values (10) on duplicate key update b=3;
102 ERROR 21S01: Column count doesn't match value count at row 1
103 insert t(a,b,d) values (10,2,2) on duplicate key update b=3, d=3;
104 check table t;
105 Table Op Msg_type Msg_text
106 test.t check status OK
107 select * from t;
108 a b c d e
109 10 3 3 3 3
110 replace t (a,b,d) select 10,4,4;
111 check table t;
112 Table Op Msg_type Msg_text
113 test.t check status OK
114 select * from t;
115 a b c d e
116 10 4 4 4 4
117 insert t(a,b,d) select 10,4,4 on duplicate key update b=5, d=5;
118 check table t;
119 Table Op Msg_type Msg_text
120 test.t check status OK
121 select * from t;
122 a b c d e
123 10 5 5 5 5
124 replace delayed t (a,b,d) values (10,6,6);
125 flush tables t;
126 check table t;
127 Table Op Msg_type Msg_text
128 test.t check status OK
129 select * from t;
130 a b c d e
131 10 6 6 6 6
132 insert delayed t(a,b,d) values (10,6,6) on duplicate key update b=7, d=7;
133 flush tables t;
134 check table t;
135 Table Op Msg_type Msg_text
136 test.t check status OK
137 select * from t;
138 a b c d e
139 10 7 7 7 7
140 load data infile 'MYSQLTEST_VARDIR/tmp/vblobs.txt' replace into table t;
141 check table t;
142 Table Op Msg_type Msg_text
143 test.t check status OK
144 select * from t;
145 a b c d e
146 10 8 8 8 8
147 update t set a=11, b=9, d=9 where a>5;
148 check table t;
149 Table Op Msg_type Msg_text
150 test.t check status OK
151 select * from t;
152 a b c d e
153 11 9 9 9 9
154 create table t2 select * from t;
155 update t, t2 set t.b=10, t.d=10 where t.a=t2.a;
156 check table t;
157 Table Op Msg_type Msg_text
158 test.t check status OK
159 select * from t;
160 a b c d e
161 11 10 10 10 10
162 update t, t tt set t.b=11, tt.d=11 where t.a=tt.a;
163 check table t;
164 Table Op Msg_type Msg_text
165 test.t check status OK
166 select * from t;
167 a b c d e
168 11 11 11 11 11
169 drop table t, t1, t2;
170 create table t (f1 int, f2 int, f3 int as (f1*2) virtual, key(f3,f2));
171 insert into t (f1,f2) values (1,1),(2,2);
172 create view v as
173 select a2.f1, a2.f2, a1.f3
174 from t a1, t a2
175 where a2.f3 <> 0
176 with local check option;
177 update v set f3 = 52;
178 drop view v;
179 drop table t;