Debian Codegraph @

mysql-test/suite/versioning/t/select2.test

raw
1 --source suite/versioning/engines.inc
2 --source suite/versioning/common.inc
3
4 replace_result $default_engine ENGINE $sys_datatype_expl SYS_TYPE;
5 eval create table t1(
6 x int unsigned,
7 y int unsigned,
8 sys_start $sys_datatype_expl as row start invisible,
9 sys_end $sys_datatype_expl as row end invisible,
10 period for system_time (sys_start, sys_end))
11 with system versioning engine=$default_engine;
12
13 insert into t1 (x, y) values
14 (0, 100),
15 (1, 101),
16 (2, 102),
17 (3, 103),
18 (4, 104),
19 (5, 105),
20 (6, 106),
21 (7, 107),
22 (8, 108),
23 (9, 109);
24 set @t0= now(6);
25 --disable_cursor_protocol
26 --enable_prepare_warnings
27 select sys_start from t1 limit 1 into @x0;
28 --disable_prepare_warnings
29 --enable_cursor_protocol
30
31 delete from t1 where x = 3;
32 delete from t1 where x > 7;
33
34 insert into t1(x, y) values(3, 33);
35 --disable_cursor_protocol
36 --enable_prepare_warnings
37 select sys_start from t1 where x = 3 and y = 33 into @t1;
38 --disable_prepare_warnings
39 if($MTR_COMBINATION_TRX_ID) {
40 set @x1= @t1;
41 select trt_commit_ts(@x1) into @t1;
42 }
43 --enable_cursor_protocol
44
45 select x, y from t1;
46 select x as ASOF_x, y from t1 for system_time as of timestamp @t0;
47 select x as FROMTO_x, y from t1 for system_time from '1970-01-01 00:00' to timestamp @t1;
48 select x as BETWAND_x, y from t1 for system_time between '1970-01-01 00:00' and timestamp @t1;
49 select x as ALL_x, y from t1 for system_time all;
50
51 if($MTR_COMBINATION_TRX_ID) {
52 select x as ASOF2_x, y from t1 for system_time as of transaction @x0;
53 select x as FROMTO2_x, y from t1 for system_time from transaction @x0 to transaction @x1;
54 select x as BETWAND2_x, y from t1 for system_time between transaction @x0 and transaction @x1;
55 }
56 if(!$MTR_COMBINATION_TRX_ID) {
57 select x as ASOF2_x, y from t1 for system_time as of @t0;
58 select x as FROMTO2_x, y from t1 for system_time from '1970-01-01 00:00' to @t1;
59 select x as BETWAND2_x, y from t1 for system_time between '1970-01-01 00:00' and timestamp @t1;
60 }
61
62 drop table t1;
63
64 replace_result $default_engine ENGINE $sys_datatype_expl SYS_TYPE;
65 eval create table t1(
66 x int,
67 y int,
68 sys_start $sys_datatype_expl as row start invisible,
69 sys_end $sys_datatype_expl as row end invisible,
70 period for system_time (sys_start, sys_end))
71 with system versioning engine=$default_engine;
72
73 create table t2 like t1;
74
75 insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5);
76 insert into t2 values (1, 2), (2, 1), (3, 1);
77 set @t0= now(6);
78
79 select t1.x as IJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x;
80 select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x;
81 select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x;
82
83 delete from t1;
84 delete from t2;
85
86 select IJ2_x1,y1,x2,y2 from (select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x)
87 for system_time as of timestamp @t0 as t;
88 select LJ2_x1,y1,x2,y2 from (select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x)
89 for system_time as of timestamp @t0 as t;
90 select RJ2_x1,y1,x2,y2 from (select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x)
91 for system_time as of timestamp @t0 as t;
92
93 drop table t1;
94 drop table t2;
95
96 --echo # MDEV-14686 Server crashes in Item_field::used_tables on 2nd call of SP [#422]
97 create or replace table t1 (called int, bad int) with system versioning;
98 create or replace procedure bad() select * from t1 where bad in (select called from t1);
99 --disable_query_log
100 call bad; call bad; call bad; call bad; call bad; call bad; call bad; call bad;
101 drop procedure bad;
102 --enable_query_log
103 --echo # bad() is good.
104
105 --echo # MDEV-14751 Server crashes in TABLE::versioned on 2nd execution of SP [#431]
106 create or replace table t1 (called_bad int);
107 create or replace table t2 (b int);
108 create or replace procedure bad() select * from t1 where ( 5, 6 ) in ( select b, b from t2 ) and called_bad in ( select max(b) from t2 );
109 --disable_query_log
110 call bad; call bad; call bad; call bad; call bad; call bad; call bad; call bad;
111 drop procedure bad;
112 --enable_query_log
113 --echo # bad() is good.
114
115 --echo # MDEV-14786 Server crashes in Item_cond::transform on 2nd execution of SP querying from a view [#436]
116 create or replace table t1 (called_bad int) with system versioning;
117 create or replace view v1 as select called_bad from t1 where called_bad < 5;
118 create or replace procedure bad() select called_bad from v1;
119 --disable_query_log
120 call bad; call bad; call bad; call bad; call bad; call bad; call bad; call bad;
121 drop procedure bad;
122 --enable_query_log
123 --echo # bad() is good.
124
125 --echo # wildcard expansion on hidden fields.
126 create or replace table t1(
127 A int
128 ) with system versioning;
129 insert into t1 values(1);
130 select * from t1;
131
132 create or replace table t1 (x int);
133 insert into t1 values (1);
134 --error ER_VERS_NOT_VERSIONED
135 select * from t1 for system_time all;
136
137 create or replace table t1 (x int) with system versioning;
138 insert into t1 values (1);
139 --error ER_TABLE_NOT_LOCKED_FOR_WRITE
140 select * from t1 for system_time as of now() for update;
141
142 create or replace table t1 (a int not null auto_increment primary key) with system versioning;
143 select * from (t1 as t2 left join t1 as t3 using (a)) natural left join t1;
144
145 create or replace table t1 (a int) with system versioning;
146 create or replace table t2 (a int) with system versioning;
147 insert into t1 values(1);
148 insert into t2 values(1);
149 create or replace view v1 as select * from t2 inner join t1 using (a);
150 select * from v1;
151 drop view v1;
152
153 create or replace table t1 (a int) with system versioning;
154 insert into t1 values (1);
155 create view vt1 as select a from t1;
156 select * from t1 natural join vt1;
157 drop view vt1;
158
159 create or replace table t1(x int) with system versioning;
160 select * from (t1 as r left join t1 as u using (x)), t1;
161
162 # @end should be max
163 create or replace table t1 (a int) with system versioning;
164 insert into t1 values (1);
165 create trigger read_end after update on t1
166 for each row set @end = old.row_end;
167 update t1 set a=2;
168 --replace_result 18446744073709551615 MAX_RESULT $sys_time_max_replace MAX_RESULT
169 select @end;
170
171 create or replace table t1 (a int) with system versioning;
172 create or replace table t2 (b int) with system versioning;
173 insert into t1 values (1);
174 insert into t2 values (2);
175 select * from (select * from t1 cross join t2) as tmp;
176 select * from (select * from (select * from t1 cross join t2) as tmp1) as tmp2;
177 select * from (select * from t1 cross join t2 for system_time as of timestamp ('1970-01-01 00:00')) as tmp;
178
179 create or replace table t1(a1 int) with system versioning;
180 create or replace table t2(a2 int) with system versioning;
181 insert into t1 values(1),(2);
182 insert into t2 values(1),(2);
183 select * from t1 for system_time all natural left join t2 for system_time all;
184
185 # natural join of a view and table
186 create or replace table t1(a1 int) with system versioning;
187 create or replace table t2(a2 int) with system versioning;
188 insert into t1 values(1),(2);
189 insert into t2 values(1),(2);
190 create or replace view v1 as select a1 from t1;
191
192 select * from v1 natural join t2;
193 select * from v1 natural left join t2;
194 select * from v1 natural right join t2;
195
196 create or replace table t1 (a int) with system versioning;
197 insert into t1 values (1);
198 insert into t1 values (2);
199 insert into t1 values (3);
200 select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
201
202 create or replace table t1 (x int) with system versioning;
203 create or replace table t2 (y int) with system versioning;
204 insert into t1 values (1), (2), (3);
205 delete from t1 where x = 3;
206 insert into t2 values (1);
207 select * from t1, t2 for system_time all;
208
209 --error ER_VERS_NOT_VERSIONED
210 select * from (select * from t1 for system_time all, t2 for system_time all) for system_time all as t;
211 --error ER_PARSE_ERROR
212 select * from (t1 for system_time all join t2 for system_time all) for system_time all;
213
214 --echo # MDEV-16043 Assertion thd->Item_change_list::is_empty() failed in mysql_parse upon SELECT from a view reading from a versioned table
215 create or replace table t1 (a int) with system versioning;
216 create or replace view v1 as select * from t1;
217 prepare stmt from "select * from t1 where exp( '20010609211642053929' )";
218 --error ER_DATA_OUT_OF_RANGE
219 execute stmt;
220 select a from v1;
221
222 drop view v1;
223 drop table t1, t2;
224
225 -- source suite/versioning/common_finish.inc