1
drop table if exists t1,t2;
2
create table t1 (a int, b int, v int as (a+1), index idx(b));
3
insert into t1(a, b) values
4
(4, 40), (3, 30), (5, 50), (7, 70), (8, 80), (2, 20), (1, 10);
5
select * from t1 order by b;
14
delete from t1 where v > 6 order by b limit 1;
15
select * from t1 order by b;
23
update t1 set a=v order by b limit 1;
24
select * from t1 order by b;
34
a int NOT NULL DEFAULT '0',
35
v double AS ((1, a)) VIRTUAL
37
ERROR HY000: Expression for computed column cannot return a row
39
a CHAR(255) BINARY NOT NULL DEFAULT 0,
40
b CHAR(255) BINARY NOT NULL DEFAULT 0,
41
v CHAR(255) BINARY AS (CONCAT(a,b)) VIRTUAL );
42
INSERT INTO t1(a,b) VALUES ('4','7'), ('4','6');
43
SELECT 1 AS C FROM t1 ORDER BY v;
48
CREATE TABLE t1(a int, b int DEFAULT 0, v INT AS (b+10) PERSISTENT);
49
INSERT INTO t1(a) VALUES (1);
54
CREATE TABLE t1(a int DEFAULT 100, v int AS (a+1) PERSISTENT);
55
INSERT INTO t1 () VALUES ();
56
CREATE TABLE t2(a int DEFAULT 100 , v int AS (a+1));
57
INSERT INTO t2 () VALUES ();
66
a datetime NOT NULL DEFAULT '2000-01-01',
67
v boolean AS (a < '2001-01-01')
69
INSERT INTO t1(a) VALUES ('2002-02-15');
70
INSERT INTO t1(a) VALUES ('2000-10-15');
80
a datetime NOT NULL DEFAULT '2000-01-01',
81
v boolean AS (a < '2001-01-01') PERSISTENT
83
INSERT INTO t2(a) VALUES ('2002-02-15');
84
INSERT INTO t2(a) VALUES ('2000-10-15');
91
a char(255), b char(255), c char(255), d char(255),
92
v char(255) AS (CONCAT(c,d) ) VIRTUAL
94
INSERT INTO t1(a,b,c,d) VALUES ('w','x','y','z'), ('W','X','Y','Z');
95
SELECT v FROM t1 ORDER BY CONCAT(a,b);
100
CREATE TABLE t1 (f1 INTEGER, v1 INTEGER AS (f1) VIRTUAL);
101
CREATE TABLE t2 AS SELECT v1 FROM t1;
102
SHOW CREATE TABLE t2;
104
t2 CREATE TABLE `t2` (
105
`v1` int(11) DEFAULT NULL
106
) ENGINE=MyISAM DEFAULT CHARSET=latin1
108
CREATE TABLE t1 (p int, a double NOT NULL, v double AS (ROUND(a,p)) VIRTUAL);
109
INSERT INTO t1 VALUES (0,1,0);
111
Warning 1906 The value specified for computed column 'v' in table 't1' ignored
112
INSERT INTO t1 VALUES (NULL,0,0);
114
Warning 1906 The value specified for computed column 'v' in table 't1' ignored
115
SELECT a, p, v, ROUND(a,p), ROUND(a,p+NULL) FROM t1;
116
a p v ROUND(a,p) ROUND(a,p+NULL)
118
0 NULL NULL NULL NULL
120
CREATE TABLE t1 (p int, a double NOT NULL);
121
INSERT INTO t1(p,a) VALUES (0,1);
122
INSERT INTO t1(p,a) VALUES (NULL,0);
123
SELECT a, p, ROUND(a,p), ROUND(a,p+NULL) FROM t1;
124
a p ROUND(a,p) ROUND(a,p+NULL)
128
CREATE TABLE t1 (a char(32), v char(32) CHARACTER SET ucs2 AS (a) VIRTUAL);
129
SHOW CREATE TABLE t1;
131
t1 CREATE TABLE `t1` (
132
`a` char(32) DEFAULT NULL,
133
`v` char(32) CHARACTER SET ucs2 AS (a) VIRTUAL
134
) ENGINE=MyISAM DEFAULT CHARSET=latin1
136
CREATE TABLE t1 (a int, b int);
137
CREATE TABLE t2 (a int, b int as (a+1) VIRTUAL);
138
SELECT table_schema, table_name, column_name, column_type, extra
139
FROM information_schema.columns WHERE table_name = 't1';
140
table_schema table_name column_name column_type extra
143
SELECT table_schema, table_name, column_name, column_type, extra
144
FROM information_schema.columns WHERE table_name = 't2';
145
table_schema table_name column_name column_type extra
147
test t2 b int(11) VIRTUAL
150
a int not null, b char(2) not null,
151
c enum('Y','N') as (case when b = 'aa' then 'Y' else 'N' end) persistent
153
show create table t1;
155
t1 CREATE TABLE `t1` (
156
`a` int(11) NOT NULL,
157
`b` char(2) NOT NULL,
158
`c` enum('Y','N') AS (case when b = 'aa' then 'Y' else 'N' end) PERSISTENT
159
) ENGINE=MyISAM DEFAULT CHARSET=latin1
160
insert into t1(a,b) values (1,'bb'), (2,'aa'), (3,'cc');
169
as (if(a=0,if(b=0,('n,n'),('n,y')),if(b=0,('y,n'),('y,y')))) persistent
171
show create table t2;
173
t2 CREATE TABLE `t2` (
174
`a` int(11) DEFAULT NULL,
175
`b` int(11) DEFAULT NULL,
176
`c` set('y','n') AS (if(a=0,if(b=0,('n,n'),('n,y')),if(b=0,('y,n'),('y,y')))) PERSISTENT
177
) ENGINE=MyISAM DEFAULT CHARSET=latin1
178
insert into t2(a,b) values (7,0), (2,3), (0,1);
187
tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL
189
INSERT INTO t1 (tsv) VALUES (DEFAULT);
191
Warning 1292 Incorrect datetime value: '0000-00-00'
192
INSERT DELAYED INTO t1 (tsv) VALUES (DEFAULT);
194
Warning 1292 Incorrect datetime value: '0000-00-00'
196
SELECT COUNT(*) FROM t1;
200
create table t1 (a int, b int);
201
insert into t1 values (3, 30), (4, 20), (1, 20);
202
create table t2 (c int, d int, v int as (d+1), index idx(c));
203
insert into t2(c,d) values
204
(20, 100), (20, 300), (30, 100), (30, 200), (40, 500),
205
(70, 100), (40, 300), (60, 100), (40, 100), (70, 100);
206
insert into t2(c,d) values
207
(120, 100), (150, 300), (130, 100), (130, 200), (140, 500),
208
(170, 100), (180, 300), (160, 100), (40, 100), (170, 100);
209
set join_cache_level=6;
211
select * from t1,t2 where t1.b=t2.c and d <= 100;
212
id select_type table type possible_keys key key_len ref rows Extra
213
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where
214
1 SIMPLE t2 ref idx idx 5 test.t1.b 2 Using where
215
select * from t1,t2 where t1.b=t2.c and d <= 100;
220
set join_cache_level=default;
222
create table t1 (a bigint, b bigint as (a > '2'));
223
show create table t1;
225
t1 CREATE TABLE `t1` (
226
`a` bigint(20) DEFAULT NULL,
227
`b` bigint(20) AS (a > '2') VIRTUAL
228
) ENGINE=MyISAM DEFAULT CHARSET=latin1
229
insert into t1 (a) values (1),(3);
239
create table t1 (a bigint, b bigint as (a between 0 and 2));
240
show create table t1;
242
t1 CREATE TABLE `t1` (
243
`a` bigint(20) DEFAULT NULL,
244
`b` bigint(20) AS (a between 0 and 2) VIRTUAL
245
) ENGINE=MyISAM DEFAULT CHARSET=latin1
246
insert into t1 (a) values (1),(3);
256
create table t1 (a char(10), b char(10) as (a between 0 and 2));
257
show create table t1;
259
t1 CREATE TABLE `t1` (
260
`a` char(10) DEFAULT NULL,
261
`b` char(10) AS (a between 0 and 2) VIRTUAL
262
) ENGINE=MyISAM DEFAULT CHARSET=latin1
263
insert into t1 (a) values (1),(3);
274
`a` int(11) NOT NULL,
275
`b` varchar(32) DEFAULT NULL,
276
`c` int(11) AS (a MOD 10) VIRTUAL,
277
`d` varchar(5) AS (LEFT(b,5)) PERSISTENT
279
show create table t1;
281
t1 CREATE TABLE `t1` (
282
`a` int(11) NOT NULL,
283
`b` varchar(32) DEFAULT NULL,
284
`c` int(11) AS (a MOD 10) VIRTUAL,
285
`d` varchar(5) AS (LEFT(b,5)) PERSISTENT
286
) ENGINE=MyISAM DEFAULT CHARSET=latin1
287
show columns from t1;
288
Field Type Null Key Default Extra
290
b varchar(32) YES NULL
291
c int(11) YES NULL VIRTUAL
292
d varchar(5) YES NULL PERSISTENT
293
show full columns from t1;
294
Field Type Collation Null Key Default Extra Privileges Comment
295
a int(11) NULL NO NULL #
296
b varchar(32) latin1_swedish_ci YES NULL #
297
c int(11) NULL YES NULL VIRTUAL #
298
d varchar(5) latin1_swedish_ci YES NULL PERSISTENT #
299
INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,NULL);
300
UPDATE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND `d`='a';
302
Warning 1906 The value specified for computed column 'd' in table 't1' ignored
303
INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a');
305
Warning 1906 The value specified for computed column 'd' in table 't1' ignored
306
set sql_mode='strict_all_tables';
307
UPDATE `test`.`t1` SET `d`='b' WHERE `a`='1' AND `b`='a' AND `c`='1' AND `d`='a';
308
ERROR HY000: The value specified for computed column 'd' in table 't1' ignored
309
INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a');
310
ERROR HY000: The value specified for computed column 'd' in table 't1' ignored