Debian Codegraph @

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

raw
1 --source include/default_optimizer_switch.inc
2
3 --let $replace_regex_tsltz6= /(TIMESTAMP..WITH LOCAL TIME ZONE..'....-..-.. ..:..:..[.]......'|<cache>\(current_timestamp\(6\)\))/TIMESTAMP\/*WITH LOCAL TIME ZONE*\/'YYYY-MM-DD hh:ss:mm:.uuuuuu'/
4
5 create table emp
6 (
7 emp_id int,
8 name varchar(127),
9 mgr int
10 ) with system versioning;
11
12 insert into emp values (1, 'bill', 0),
13 (2, 'bill', 1),
14 (3, 'kate', 1);
15 set @ts=now(6);
16 delete from emp;
17 insert into emp values (4, 'john', 1);
18
19 with ancestors as (select * from emp) select * from ancestors;
20 set @tmp= "with ancestors as (select * from emp) select * from ancestors";
21 prepare stmt from @tmp; execute stmt; drop prepare stmt;
22
23 with ancestors as (select * from emp for system_time all) select * from ancestors;
24 set @tmp= "with ancestors as (select * from emp for system_time all) select * from ancestors";
25 prepare stmt from @tmp; execute stmt; drop prepare stmt;
26
27 with recursive ancestors as (select * from emp) select * from ancestors;
28 set @tmp= "with recursive ancestors as (select * from emp) select * from ancestors";
29 prepare stmt from @tmp; execute stmt; drop prepare stmt;
30
31 select emp_id from (select emp_id from emp where row_end>'2031-1-1') as tmp;
32 set @tmp= "select emp_id from (select emp_id from emp where row_end>'2031-1-1') as tmp";
33 prepare stmt from @tmp; execute stmt; drop prepare stmt;
34
35 with recursive
36 ancestors
37 as
38 (
39 select e.emp_id, e.name, e.mgr
40 from emp as e
41 where name = 'john'
42 union
43 select ee.emp_id, ee.name, ee.mgr
44 from emp as ee, ancestors as a
45 where ee.mgr = a.emp_id
46 )
47 select * from ancestors;
48 set @tmp= "
49 with recursive
50 ancestors
51 as
52 (
53 select e.emp_id, e.name, e.mgr
54 from emp as e
55 where name = 'john'
56 union
57 select ee.emp_id, ee.name, ee.mgr
58 from emp as ee, ancestors as a
59 where ee.mgr = a.emp_id
60 )
61 select * from ancestors";
62 prepare stmt from @tmp; execute stmt; drop prepare stmt;
63
64 #385
65 with recursive
66 ancestors
67 as
68 (
69 select e.emp_id, e.name, e.mgr
70 from emp for system_time as of timestamp @ts as e
71 where name = 'bill'
72 union
73 select ee.emp_id, ee.name, ee.mgr
74 from emp for system_time as of timestamp @ts as ee,
75 ancestors as a
76 where ee.mgr = a.emp_id
77 )
78 select * from ancestors;
79 set @tmp= "
80 with recursive
81 ancestors
82 as
83 (
84 select e.emp_id, e.name, e.mgr
85 from emp for system_time as of timestamp @ts as e
86 where name = 'bill'
87 union
88 select ee.emp_id, ee.name, ee.mgr
89 from emp for system_time as of timestamp @ts as ee,
90 ancestors as a
91 where ee.mgr = a.emp_id
92 )
93 select * from ancestors";
94 prepare stmt from @tmp; execute stmt; drop prepare stmt;
95
96 drop table emp;
97
98 create or replace table t1 (x int) with system versioning;
99 create or replace table t2 (y int) with system versioning;
100 insert into t1 values (1);
101 set @t0= now(6);
102 delete from t1;
103 insert into t1 values (2);
104 insert into t2 values (10);
105
106 --replace_column 2 # 3 #
107 select * from (select *, t1.row_end, t1.row_end as endo from t1) as s0;
108 --replace_column 3 # 4 #
109 select * from (select *, t1.row_end, t2.row_start from t1, t2) as s0;
110
111 --echo # SYSTEM_TIME propagation from inner to outer
112 select * from (select * from t1 for system_time as of timestamp @t0, t2) as s0;
113 with s1 as (select * from t1 for system_time as of timestamp @t0, t2) select * from s1;
114 --echo # leading table selection
115 --replace_column 3 #
116 select * from (select *, t1.row_end from t2, t1 for system_time as of timestamp @t0) as s2;
117 --replace_column 3 #
118 with s3 as (select *, t1.row_end from t2, t1 for system_time as of timestamp @t0) select * from s3;
119
120 --echo ### VIEW instead of t1
121 set @q= concat("create view vt1 as select * from t1 for system_time as of timestamp '", @t0, "'");
122 prepare q from @q; execute q; drop prepare q;
123 create view vt2 as select * from t1;
124
125 --echo # SYSTEM_TIME propagation from view
126 select * from vt1;
127 --echo # SYSTEM_TIME propagation from inner to outer
128 select * from (select * from vt1, t2) as s0;
129
130 --echo ### SYSTEM_TIME clash
131 --error ER_VERS_NOT_VERSIONED
132 select * from (select * from t1 for system_time all) for system_time all as dt0;
133 --error ER_VERS_NOT_VERSIONED
134 select * from vt1 for system_time all;
135 --error ER_VERS_NOT_VERSIONED
136 with dt1 as (select * from t1 for system_time all)
137 select * from dt1 for system_time all;
138
139 --echo ### UNION
140 set @t1= now(6);
141 delete from t2;
142 insert into t2 values (3);
143 --echo # SYSTEM_TIME is not propagated
144 select x from t1 union
145 select y from t2;
146 select x from t1 for system_time as of @t0 union
147 select y from t2;
148 select x from t1 union
149 select y from t2 for system_time as of @t1;
150 select x from t1 for system_time as of @t0 union
151 select y from t2 for system_time as of @t1;
152
153 --echo # LEFT/RIGHT JOIN
154 create or replace table t1 (x int, y int) with system versioning;
155 create or replace table t2 (x int, y int) with system versioning;
156
157 insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5);
158 insert into t2 values (1, 2), (2, 1), (3, 1);
159
160 --echo ## Outer or inner SYSTEM_TIME produces same expression
161
162 --disable_warnings
163 --disable_query_log
164 explain extended
165 select * from (
166 select t1.x, t1.y as y1, t2.x as x2, t2.y as y2
167 from t1 join t2 on t1.x = t2.x) for system_time as of now() as t;
168
169 --replace_regex $replace_regex_tsltz6
170 let $a=`show warnings`;
171 --echo Query A:
172 echo $a;
173
174 explain extended
175 select * from (
176 select t1.x, t1.y as y1, t2.x as x2, t2.y as y2
177 from t1 for system_time as of now()
178 join t2 for system_time as of now() on t1.x = t2.x) as t;
179
180 --replace_regex $replace_regex_tsltz6
181 let $b=`show warnings`;
182 --echo Query B:
183 echo $b;
184
185 if ($a == $b)
186 {
187 --echo Fine result: queries A and B are equal.
188 }
189 --enable_query_log
190 --enable_warnings
191
192 --echo ## LEFT JOIN: t1, t2 versioned
193 select * from (
194 select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2
195 from t1 left join t2 on t1.x = t2.x)
196 as derived;
197
198 alter table t2 drop system versioning;
199
200 --echo ## LEFT JOIN: t1 versioned
201 select * from (
202 select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2
203 from t1 left join t2 on t1.x = t2.x)
204 as derived;
205
206 alter table t1 drop system versioning;
207 alter table t2 add system versioning;
208
209 --echo ## LEFT JOIN: t2 versioned
210 select * from (
211 select t1.x as LJ3_x1, t1.y as y1, t2.x as x2, t2.y as y2
212 from t1 left join t2 on t1.x = t2.x)
213 as derived;
214
215 alter table t1 add system versioning;
216
217 --echo ## RIGHT JOIN: t1, t2 versioned
218 select * from (
219 select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2
220 from t1 right join t2 on t1.x = t2.x)
221 as derived;
222
223 alter table t2 drop system versioning;
224
225 --echo ## RIGHT JOIN: t1 versioned
226 select * from (
227 select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2
228 from t1 right join t2 on t1.x = t2.x)
229 as derived;
230
231 alter table t1 drop system versioning;
232 alter table t2 add system versioning;
233
234 --echo ## RIGHT JOIN: t2 versioned
235 select * from (
236 select t1.x as RJ3_x1, t1.y as y1, t2.x as x2, t2.y as y2
237 from t1 right join t2 on t1.x = t2.x)
238 as derived;
239
240 drop table t1, t2;
241 drop view vt1, vt2;
242