Debian Codegraph @

mysql-test/suite/vcol/inc/vcol_view.inc

raw
1 ################################################################################
2 # inc/vcol_view.inc #
3 # #
4 # Purpose: #
5 # Testing views defined on tables with virtual columns. #
6 # #
7 # #
8 # #
9 #------------------------------------------------------------------------------#
10 # Original Author: Andrey Zhakov #
11 # Original Date: 2008-09-04 #
12 # Change Author: Oleksandr Byelkin (Monty program Ab)
13 # Date: 2009-03-24
14 # Change: Syntax changed
15 ################################################################################
16
17
18
19 create table t1 (a int not null,
20 b int as (-a),
21 c int as (-a) persistent);
22 insert into t1 (a) values (1), (1), (2), (2), (3);
23
24 # simple view
25 create view v1 (d,e) as select abs(b), abs(c) from t1;
26 select d,e from v1;
27 select is_updatable from information_schema.views where table_name='v1';
28
29 # view with different algorithms (explain output differs)
30 explain extended select d,e from v1;
31 create algorithm=temptable view v2 (d,e) as select abs(b), abs(c) from t1;
32 show create view v2;
33 select d,e from v2;
34 explain extended select d,e from v2;
35
36 # VIEW on VIEW test
37 create view v3 (d,e) as select d*2, e*2 from v1;
38 select * from v3;
39 explain extended select * from v3;
40
41 drop view v1,v2,v3;
42 drop table t1;
43
44 #
45 # DISTINCT option for VIEW
46 #
47 create table t1 (a int not null,
48 b int as (-a),
49 c int as (-a) persistent);
50 insert into t1 (a) values (1), (2), (3), (1), (2), (3);
51 create view v1 as select distinct b from t1;
52 select * from v1;
53 explain select * from v1;
54 select * from t1;
55 drop view v1;
56 create view v1 as select distinct c from t1;
57 select * from v1;
58 explain select * from v1;
59 select * from t1;
60 drop view v1;
61 drop table t1;
62
63 #
64 # LIMIT clause test
65 #
66 create table t1 (a int not null,
67 b int as (-a),
68 c int as (-a) persistent);
69 insert into t1 (a) values (1), (2), (3), (4);
70 create view v1 as select b+1 from t1 order by 1 desc limit 2;
71 select * from v1;
72 --echo MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1.
73 --echo MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed
74 explain select * from v1;
75 drop view v1;
76 create view v1 as select c+1 from t1 order by 1 desc limit 2;
77 select * from v1;
78 --echo MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1.
79 --echo MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed
80 explain select * from v1;
81 drop view v1;
82 drop table t1;
83
84 #
85 # simple view + simple update, insert and delete
86 #
87 create table t1 (a int,
88 b int,
89 c int as (-a),
90 d int as (-a) persistent,
91 primary key(a));
92 insert into t1 (a,b) values (10,2), (20,3), (30,4), (40,5), (50,10);
93 create view v1 (a,e,f,g) as select a, b+1,c+1,d+1 from t1;
94 # updatable field of updateable view
95 update v1 set a=a+e;
96 select * from v1;
97 select * from t1;
98 delete from v1;
99 select * from v1;
100 select * from t1;
101 --error ER_NON_INSERTABLE_TABLE
102 insert into v1 (a,e) values (60,15);
103 drop table t1;
104 drop view v1;
105
106 #
107 # outer join based on VIEW with WHERE clause
108 #
109
110 SET @save_optimizer_switch=@@optimizer_switch;
111 SET optimizer_switch='outer_join_with_cache=off';
112
113 create table t1 (a int,
114 b int as (-a),
115 c int as (-a) persistent,
116 primary key(a));
117 insert into t1 (a) values (1), (2), (3);
118 create view v1 (x,y,z) as select a,b,c from t1 where b < -1;
119 select t1.a, v1.x, v1.y, v1.z from t1 left join v1 on (t1.b= v1.y);
120 drop view v1;
121 create view v1 (x,y,z) as select a,b,c from t1 where c < -1;
122 select t1.a, v1.x, v1.y, v1.z from t1 left join v1 on (t1.c= v1.z);
123 drop view v1;
124 drop table t1;
125
126 SET optimizer_switch=@save_optimizer_switch;
127
128 #
129 # VIEW built over UNION
130 #
131 create table t1 (a1 int,
132 b1 int as (-a1),
133 c1 int as (-a1) persistent);
134 create table t2 (a2 int,
135 b2 int as (-a2),
136 c2 int as (-a2) persistent);
137 insert into t1 (a1) values (1), (2);
138 insert into t2 (a2) values (2), (3);
139 create view v1 as select * from t1,t2 union all select * from t1,t2;
140 select * from v1;
141 drop view v1;
142 drop table t1, t2;
143
144 #
145 # Showing VIEW with VIEWs in subquery
146 #
147 create table t1 (a int,
148 b int as (-a),
149 c int as (-a) persistent);
150 create table t2 like t1;
151 create view v1 as select a,b,c from t1;
152 create view v2 as select a,b,c from t2 where b in (select b from v1);
153 show create view v2;
154 drop view v2, v1;
155 drop table t1, t2;
156
157 #
158 # TODO: VIEW with full text
159 #
160 #CREATE TABLE t1 (c1 int not null auto_increment primary key, c2 varchar(20), fulltext(c2));
161 #insert into t1 (c2) VALUES ('real Beer'),('Water'),('Kossu'),('Coca-Cola'),('Vodka'),('Wine'),('almost real Beer');
162 #select * from t1 WHERE match (c2) against ('Beer');
163 #CREATE VIEW v1 AS SELECT * from t1 WHERE match (c2) against ('Beer');
164 #select * from v1;
165 #drop view v1;
166 #drop table t1;
167
168 #
169 # distinct in temporary table with a VIEW
170 #
171 create table t1 (a int,
172 b int as (-a),
173 c int as (-a) persistent);
174 insert into t1 (a) values (1),(1),(2),(2),(3),(3);
175 create view v1 as select b from t1;
176 select distinct b from v1;
177 select distinct b from v1 limit 2;
178 select distinct b from t1 limit 2;
179 prepare stmt1 from "select distinct b from v1 limit 2";
180 execute stmt1;
181 execute stmt1;
182 deallocate prepare stmt1;
183 drop view v1;
184 create view v1 as select c from t1;
185 select distinct c from v1;
186 select distinct c from v1 limit 2;
187 select distinct c from t1 limit 2;
188 prepare stmt1 from "select distinct c from v1 limit 2";
189 execute stmt1;
190 execute stmt1;
191 deallocate prepare stmt1;
192 drop view v1;
193 drop table t1;
194
195 #
196 # WITH CHECK OPTION insert/update test
197 #
198 create table t1 (a int,
199 b int as (-a),
200 c int as (-a) persistent);
201 create view v1 as select * from t1 where b > -2 && c >-2 with check option;
202 # simple insert
203 insert into v1 (a) values (1);
204 -- error 1369
205 insert into v1 (a) values (3);
206 # simple insert with ignore
207 insert ignore into v1 (a) values (2),(3),(0);
208 select * from t1;
209 drop view v1;
210 drop table t1;
211