1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
--disable_warnings
drop table if exists t1,t2;
--enable_warnings
#
# Bug#601164: DELETE/UPDATE with ORDER BY index and LIMIT
#
create table t1 (a int, b int, v int as (a+1), index idx(b));
insert into t1(a, b) values
(4, 40), (3, 30), (5, 50), (7, 70), (8, 80), (2, 20), (1, 10);
select * from t1 order by b;
delete from t1 where v > 6 order by b limit 1;
select * from t1 order by b;
update t1 set a=v order by b limit 1;
select * from t1 order by b;
drop table t1;
#
# Bug#604549: Expression for virtual column returns row
#
-- error ER_ROW_EXPR_FOR_VCOL
CREATE TABLE t1 (
a int NOT NULL DEFAULT '0',
v double AS ((1, a)) VIRTUAL
);
#
# Bug#603654: Virtual column in ORDER BY, no other references of table columns
#
CREATE TABLE t1 (
a CHAR(255) BINARY NOT NULL DEFAULT 0,
b CHAR(255) BINARY NOT NULL DEFAULT 0,
v CHAR(255) BINARY AS (CONCAT(a,b)) VIRTUAL );
INSERT INTO t1(a,b) VALUES ('4','7'), ('4','6');
SELECT 1 AS C FROM t1 ORDER BY v;
DROP TABLE t1;
#
# Bug#603186: Insert into a table with stored vurtual columns
#
CREATE TABLE t1(a int, b int DEFAULT 0, v INT AS (b+10) PERSISTENT);
INSERT INTO t1(a) VALUES (1);
SELECT b, v FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a int DEFAULT 100, v int AS (a+1) PERSISTENT);
INSERT INTO t1 () VALUES ();
CREATE TABLE t2(a int DEFAULT 100 , v int AS (a+1));
INSERT INTO t2 () VALUES ();
SELECT a, v FROM t1;
SELECT a, v FROM t2;
DROP TABLE t1,t2;
#
# Bug#604503: Virtual column expression with datetime comparison
#
CREATE TABLE t1 (
a datetime NOT NULL DEFAULT '2000-01-01',
v boolean AS (a < '2001-01-01')
);
INSERT INTO t1(a) VALUES ('2002-02-15');
INSERT INTO t1(a) VALUES ('2000-10-15');
SELECT a, v FROM t1;
SELECT a, v FROM t1;
CREATE TABLE t2 (
a datetime NOT NULL DEFAULT '2000-01-01',
v boolean AS (a < '2001-01-01') PERSISTENT
);
INSERT INTO t2(a) VALUES ('2002-02-15');
INSERT INTO t2(a) VALUES ('2000-10-15');
SELECT * FROM t2;
DROP TABLE t1, t2;
|
