Debian Codegraph @

mysql-test/suite/gcol/inc/gcol_non_stored_columns.inc

raw
1 ################################################################################
2 # inc/gcol_non_stored_columns.inc #
3 # #
4 # Purpose: #
5 # Ensure that MySQL behaviour is consistent irrelevant of #
6 # - the place of a non-stored column among other columns, #
7 # - the total number of non-stored fields. #
8 # #
9 #------------------------------------------------------------------------------#
10 # Original Author: Andrey Zhakov #
11 # Original Date: 2008-09-04 #
12 # Change Author: #
13 # Change Date: #
14 # Change: #
15 ################################################################################
16
17 --echo # Case 1. All non-stored columns.
18 eval create $opt_tmp table t1 (a int generated always as (2+3) virtual);
19 insert into t1 values (default);
20 select * from t1;
21 insert into t1 values (default);
22 select * from t1;
23 drop table t1;
24 --echo # Case 2. CREATE
25 --echo # - Column1: "real"
26 --echo # - Column 2: virtual non-stored
27 eval create $opt_tmp table t1 (a int, b int generated always as (-a) virtual);
28 insert into t1 values (1,default);
29 select * from t1;
30 insert into t1 values (2,default);
31 select * from t1 order by a;
32 drop table t1;
33
34 --echo # Case 3. CREATE
35 --echo # - Column1: "real"
36 --echo # - Column 2: virtual stored
37 eval create $opt_tmp table t1 (a int, b int generated always as (-a) stored);
38 insert into t1 values (1,default);
39 select * from t1;
40 insert into t1 values (2,default);
41 select * from t1 order by a;
42 drop table t1;
43
44 --echo # Case 4. CREATE
45 --echo # - Column1: virtual non-stored
46 --echo # - Column2: "real"
47 eval create $opt_tmp table t1 (a int generated always as (-b) virtual, b int);
48 insert into t1 values (default,1);
49 select * from t1;
50 insert into t1 values (default,2);
51 select * from t1 order by a;
52 drop table t1;
53
54 --echo # Case 5. CREATE
55 --echo # - Column1: virtual stored
56 --echo # - Column2: "real"
57 eval create $opt_tmp table t1 (a int generated always as (-b) stored, b int);
58 insert into t1 values (default,1);
59 select * from t1;
60 insert into t1 values (default,2);
61 select * from t1 order by a;
62 drop table t1;
63
64 --echo # Case 6. CREATE
65 --echo # - Column1: "real"
66 --echo # - Column2: virtual non-stored
67 --echo # - Column3: virtual stored
68 eval create $opt_tmp table t1 (a int, b int generated always as (-a), c int generated always as (-a) stored);
69 insert into t1 values (1,default,default);
70 select * from t1;
71 insert into t1 values (2,default,default);
72 select * from t1 order by a;
73 drop table t1;
74
75 --echo # Case 7. ALTER. Modify virtual stored -> virtual non-stored
76 eval create $opt_tmp table t1 (a int, b int generated always as (a % 2) stored);
77 --error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
78 alter table t1 modify b int generated always as (a % 2) virtual;
79 show create table t1;
80 drop table t1;
81
82 --echo # Case 8. ALTER. Modify virtual non-stored -> virtual stored
83 eval create $opt_tmp table t1 (a int, b int generated always as (a % 2) virtual);
84 --error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
85 alter table t1 modify b int generated always as (a % 2) stored;
86 show create table t1;
87 drop table t1;
88
89 --echo # Case 9. CREATE LIKE
90 --echo # - Column1: "real"
91 --echo # - Column2: virtual non-stored
92 --echo # - Column3: virtual stored
93 eval create $opt_tmp table t1 (a int, b int generated always as (-a), c int generated always as (-a) stored);
94 eval create $opt_tmp table t2 like t1;
95 insert into t2 values (1,default,default);
96 select * from t2;
97 insert into t2 values (2,default,default);
98 select * from t2 order by a;
99 drop table t2;
100 drop table t1;
101
102 --echo # Case 10. ALTER. Dropping a virtual non-stored column.
103 --echo # - Column1: virtual non-stored
104 --echo # - Column2: "real"
105 eval create $opt_tmp table t1 (a int generated always as (-b) virtual, b int, c varchar(5));
106 insert into t1 values (default,1,'v1');
107 insert into t1 values (default,2,'v2');
108 select * from t1 order by b;
109 alter table t1 drop column a;
110 select * from t1 order by b;
111 show create table t1;
112 drop table t1;
113
114 --echo # Case 11. ALTER. Dropping a virtual stored column.
115 --echo # - Column1: virtual stored
116 --echo # - Column2: "real"
117 eval create $opt_tmp table t1 (a int generated always as (-b) stored, b int, c char(5));
118 insert into t1 values (default,1,'v1');
119 insert into t1 values (default,2,'v2');
120 select * from t1 order by b;
121 alter table t1 drop column a;
122 select * from t1 order by b;
123 show create table t1;
124 drop table t1;
125
126 --echo # Case 12. ALTER. Adding a new virtual non-stored column.
127 eval create $opt_tmp table t1 (a int, b datetime);
128 insert into t1 values (1,'2008-09-04');
129 insert into t1 values (2,'2008-09-05');
130 select * from t1 order by a;
131 alter table t1 add column c int generated always as (dayofyear(b)) virtual after a;
132 select * from t1 order by a;
133 show create table t1;
134 drop table t1;
135
136 --echo # Case 13. ALTER. Adding a new virtual stored column.
137 eval create $opt_tmp table t1 (a int, b datetime);
138 insert into t1 values (1,'2008-09-04');
139 insert into t1 values (2,'2008-09-05');
140 select * from t1 order by a;
141 alter table t1 add column c int generated always as (dayofyear(b)) stored after a;
142 select * from t1 order by a;
143 show create table t1;
144 drop table t1;
145
146 --echo # Case 14. ALTER. Changing the expression of a virtual stored column.
147 eval create $opt_tmp table t1 (a int, b datetime, c int generated always as (week(b)) stored);
148 insert into t1 values (1,'2008-09-04',default);
149 insert into t1 values (2,'2008-09-05',default);
150 select * from t1 order by a;
151 alter table t1 change column c c int generated always as (week(b,1)) stored;
152 select * from t1 order by a;
153 show create table t1;
154 drop table t1;
155
156 --echo # Case 15. ALTER. Changing the expression of a virtual non-stored column.
157 eval create $opt_tmp table t1 (a int, b datetime, c int generated always as (week(b)) virtual);
158 insert into t1 values (1,'2008-09-04',default);
159 insert into t1 values (2,'2008-09-05',default);
160 select * from t1 order by a;
161 alter table t1 change column c c int generated always as (week(b,1)) virtual;
162 select * from t1 order by a;
163 show create table t1;
164 drop table t1;
165