Debian Codegraph @

mysql-test/suite/versioning/r/view.result

raw
1 create or replace table t1 (x int) with system versioning;
2 insert into t1 values (1);
3 select now(6) into @t1;
4 update t1 set x= 2;
5 select now(6) into @t2;
6 delete from t1;
7 set @vt1= concat("create or replace view vt1 as select * from t1 for system_time as of timestamp '", @t1, "'");
8 prepare stmt from @vt1;
9 execute stmt;
10 drop prepare stmt;
11 set @vt2= concat("create or replace view vt2 as select *, row_end from t1 for system_time as of timestamp '", @t2, "'");
12 prepare stmt from @vt2;
13 execute stmt;
14 drop prepare stmt;
15 select * from t1;
16 x
17 create or replace view vt1 as select * from t1;
18 show create view vt1;
19 View Create View character_set_client collation_connection
20 vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`x` AS `x` from `t1` latin1 latin1_swedish_ci
21 drop view vt1;
22 drop view vt2;
23 create or replace view vt1 as select * from t1 for system_time all;
24 select * from vt1;
25 x
26 2
27 1
28 prepare stmt from 'select * from vt1';
29 execute stmt;
30 x
31 2
32 1
33 drop prepare stmt;
34 set @str= concat('create or replace view vt1 as
35 select * from t1 for system_time as of timestamp "', @t1, '"');
36 prepare stmt from @str;
37 execute stmt;
38 drop prepare stmt;
39 select * from t1 for system_time as of timestamp @t1;
40 x
41 1
42 select * from vt1;
43 x
44 1
45 insert into vt1 values (3);
46 select * from t1;
47 x
48 3
49 select * from vt1;
50 x
51 1
52 create or replace table t1 (x int) with system versioning;
53 insert into t1 values (1), (2);
54 set @t1=now(6);
55 delete from t1 where x=2;
56 set @t2=now(6);
57 delete from t1 where x=1;
58 set @t3=now(6);
59 set @tmp= concat("create or replace view vt1 as select * from t1 for system_time as of timestamp '", @t1, "'");
60 prepare stmt from @tmp;
61 execute stmt;
62 drop prepare stmt;
63 select * from vt1;
64 x
65 1
66 2
67 # VIEW with parameters [tempesta-tech/mariadb#151]
68 create or replace table t1 (x int) with system versioning;
69 create or replace view vt1(c) as select x from t1;
70 show create view vt1;
71 View Create View character_set_client collation_connection
72 vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`x` AS `c` from `t1` latin1 latin1_swedish_ci
73 # VIEW over JOIN of versioned tables [tempesta-tech/mariadb#153]
74 create or replace table t1 (a int) with system versioning;
75 create or replace table t2 (b int) with system versioning;
76 insert into t1 values (1);
77 insert into t2 values (2);
78 create or replace view vt12 as select * from t1 cross join t2;
79 select * from vt12;
80 a b
81 1 2
82 create or replace view vt12 as select * from t1 for system_time as of timestamp ('1970-01-01 00:00') cross join t2;
83 select * from vt12;
84 a b
85 # VIEW improvements [tempesta-tech/mariadb#183]
86 create or replace table t3 (x int);
87 create or replace view vt1 as select * from t1, t2, t3;
88 show create view vt1;
89 View Create View character_set_client collation_connection
90 vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`b` AS `b`,`t3`.`x` AS `x` from ((`t1` join `t2`) join `t3`) latin1 latin1_swedish_ci
91 create or replace view vt1 as select * from t3, t2, t1;
92 show create view vt1;
93 View Create View character_set_client collation_connection
94 vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t3`.`x` AS `x`,`t2`.`b` AS `b`,`t1`.`a` AS `a` from ((`t3` join `t2`) join `t1`) latin1 latin1_swedish_ci
95 create or replace view vt1 as select a, t2.row_end as endo from t3, t1, t2;
96 show create view vt1;
97 View Create View character_set_client collation_connection
98 vt1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vt1` AS select `t1`.`a` AS `a`,`t2`.`row_end` AS `endo` from ((`t3` join `t1`) join `t2`) latin1 latin1_swedish_ci
99 # VIEW over UNION [tempesta-tech/mariadb#269]
100 create or replace view vt1 as select * from t1 union select * from t1;
101 select * from vt1;
102 a
103 1
104 # VIEW over UNION with non-versioned [tempesta-tech/mariadb#393]
105 create or replace table t2 (a int);
106 create or replace view vt1 as select * from t1 union select * from t2;
107 select * from vt1;
108 a
109 1
110 #
111 # MDEV-14689 crash on second PS execute
112 #
113 create or replace table t1 (a int);
114 create or replace view v1 as select * from t1;
115 create or replace table t2 (b int) with system versioning;
116 prepare stmt from 'select a from v1 inner join t2 group by a order by a';
117 execute stmt;
118 a
119 execute stmt;
120 a
121 drop view v1;
122 drop tables t1, t2;
123 #
124 # MDEV-15146 SQLError[4122]: View is not system versioned
125 #
126 create or replace table t1 (a int) with system versioning;
127 insert t1 values (1),(2);
128 set @a=now(6);
129 create or replace view v1 as select * from t1;
130 delete from t1;
131 select * from v1;
132 a
133 select * from v1 for system_time as of @a;
134 a
135 1
136 2
137 show create view v1;
138 View Create View character_set_client collation_connection
139 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci
140 #
141 # MDEV-15378 Valid query causes invalid view definition due to syntax limitation in FOR SYSTEM_TIME
142 #
143 create or replace table t1 (i int) with system versioning;
144 select * from t1 for system_time as of now() - interval 6 second;
145 i
146 create or replace view v1 as select * from t1 for system_time as of date_sub(now(), interval 6 second);
147 show create view v1;
148 View Create View character_set_client collation_connection
149 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` FOR SYSTEM_TIME AS OF TIMESTAMP current_timestamp() - interval 6 second latin1 latin1_swedish_ci
150 drop view v1, vt1, vt12;
151 drop tables t1, t3;
152 #
153 # MDEV-18727 improve DML operation of System Versioning
154 #
155 create or replace table t1 (
156 x int,
157 row_start SYS_DATATYPE as row start invisible,
158 row_end SYS_DATATYPE as row end invisible,
159 period for system_time (row_start, row_end)
160 ) with system versioning;
161 insert into t1 values (1), (2);
162 create or replace view v1 as select * from t1 where x > 1;
163 # update, delete
164 update v1 set x= x + 1;
165 select *, check_row(row_start, row_end) from t1 for system_time all order by x;
166 x check_row(row_start, row_end)
167 1 CURRENT ROW
168 2 HISTORICAL ROW
169 3 CURRENT ROW
170 insert v1 values (4);
171 select *, check_row(row_start, row_end) from t1 for system_time all order by x;
172 x check_row(row_start, row_end)
173 1 CURRENT ROW
174 2 HISTORICAL ROW
175 3 CURRENT ROW
176 4 CURRENT ROW
177 delete from v1 where x < 4;
178 select *, check_row(row_start, row_end) from t1 for system_time all order by x;
179 x check_row(row_start, row_end)
180 1 CURRENT ROW
181 2 HISTORICAL ROW
182 3 HISTORICAL ROW
183 4 CURRENT ROW
184 # multi-update
185 create or replace table t2 like t1;
186 insert into t2 values (1), (2);
187 create or replace view v2 as select * from t2 where x > 1;
188 update v1, v2 set v1.x= v1.x + 1, v2.x= v2.x + 1 where v1.x = v2.x + 2;
189 select *, check_row(row_start, row_end) from t1 for system_time all order by x;
190 x check_row(row_start, row_end)
191 1 CURRENT ROW
192 2 HISTORICAL ROW
193 3 HISTORICAL ROW
194 4 HISTORICAL ROW
195 5 CURRENT ROW
196 select *, check_row(row_start, row_end) from t2 for system_time all order by x;
197 x check_row(row_start, row_end)
198 1 CURRENT ROW
199 2 HISTORICAL ROW
200 3 CURRENT ROW
201 # multi-delete
202 delete v1, v2 from v1 join v2 where v1.x = v2.x + 2;
203 select *, check_row(row_start, row_end) from t1 for system_time all order by x;
204 x check_row(row_start, row_end)
205 1 CURRENT ROW
206 2 HISTORICAL ROW
207 3 HISTORICAL ROW
208 4 HISTORICAL ROW
209 5 HISTORICAL ROW
210 select *, check_row(row_start, row_end) from t2 for system_time all order by x;
211 x check_row(row_start, row_end)
212 1 CURRENT ROW
213 2 HISTORICAL ROW
214 3 HISTORICAL ROW
215 # replace
216 create or replace table t1 (
217 x int primary key, y int,
218 row_start SYS_DATATYPE as row start invisible,
219 row_end SYS_DATATYPE as row end invisible,
220 period for system_time (row_start, row_end)
221 ) with system versioning;
222 insert into t1 values (1, 0), (2, 0);
223 create or replace view v1 as select * from t1 where x > 1;
224 replace v1 values (1, 1);
225 replace v1 values (2, 1);
226 replace v1 values (3, 1);
227 # REPLACE ignores VIEW condition because itself doesn't use WHERE
228 select *, check_row(row_start, row_end) from t1 for system_time all order by x, row_end;
229 x y check_row(row_start, row_end)
230 1 0 HISTORICAL ROW
231 1 1 CURRENT ROW
232 2 0 HISTORICAL ROW
233 2 1 CURRENT ROW
234 3 1 CURRENT ROW
235 # insert-select, on duplicate key
236 insert v1 select * from t1 where x = 1 on duplicate key update x = v1.x - 1;
237 select *, check_row(row_start, row_end) from t1 for system_time all order by x, row_end;
238 x y check_row(row_start, row_end)
239 0 1 CURRENT ROW
240 1 0 HISTORICAL ROW
241 1 1 HISTORICAL ROW
242 2 0 HISTORICAL ROW
243 2 1 CURRENT ROW
244 3 1 CURRENT ROW
245 drop view v1, v2;
246 drop tables t1, t2;
247 #
248 # MDEV-21146 Assertion `m_lock_type == 2' in handler::ha_drop_table upon LOAD DATA
249 #
250 create table t1 (a int);
251 create view v1 as select * from t1;
252 create or replace table t1 (b int) with system versioning;
253 load data infile 'xx' into table v1;
254 ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
255 drop view v1;
256 drop table t1;
257 #
258 # MDEV-21155 Assertion with versioned table upon DELETE from view of view after replacing first view
259 #
260 create table t1 (a int);
261 insert into t1 values (1);
262 create table t2 (
263 b int,
264 row_start SYS_DATATYPE as row start invisible,
265 row_end SYS_DATATYPE as row end invisible,
266 period for system_time (row_start, row_end)
267 ) with system versioning;
268 insert into t2 values (2);
269 create view v1 as select * from t1;
270 create view v2 as select * from v1;
271 create or replace view v1 as select * from t2;
272 delete from v2;
273 select * from t1;
274 a
275 1
276 select *, check_row(row_start, row_end) from t2 for system_time all;
277 b check_row(row_start, row_end)
278 2 HISTORICAL ROW
279 drop view v2;
280 drop view v1;
281 drop table t1, t2;
282 #
283 # MDEV-23779 Error upon querying the view, that selecting from versioned table with partitions
284 #
285 create table t1 (i int) with system versioning
286 partition by system_time (partition p0 history, partition pn current);
287 create view v1 as select * from t1 partition (pn);
288 show create view v1;
289 View Create View character_set_client collation_connection
290 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`i` AS `i` from `t1` PARTITION (`pn`) latin1 latin1_swedish_ci
291 drop view v1;
292 drop table t1;