Debian Codegraph @

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

raw
1 create table emp
2 (
3 emp_id int,
4 name varchar(127),
5 mgr int
6 ) with system versioning;
7 insert into emp values (1, 'bill', 0),
8 (2, 'bill', 1),
9 (3, 'kate', 1);
10 set @ts=now(6);
11 delete from emp;
12 insert into emp values (4, 'john', 1);
13 with ancestors as (select * from emp) select * from ancestors;
14 emp_id name mgr
15 4 john 1
16 set @tmp= "with ancestors as (select * from emp) select * from ancestors";
17 prepare stmt from @tmp;
18 execute stmt;
19 emp_id name mgr
20 4 john 1
21 drop prepare stmt;
22 with ancestors as (select * from emp for system_time all) select * from ancestors;
23 emp_id name mgr
24 1 bill 0
25 2 bill 1
26 3 kate 1
27 4 john 1
28 set @tmp= "with ancestors as (select * from emp for system_time all) select * from ancestors";
29 prepare stmt from @tmp;
30 execute stmt;
31 emp_id name mgr
32 1 bill 0
33 2 bill 1
34 3 kate 1
35 4 john 1
36 drop prepare stmt;
37 with recursive ancestors as (select * from emp) select * from ancestors;
38 emp_id name mgr
39 4 john 1
40 set @tmp= "with recursive ancestors as (select * from emp) select * from ancestors";
41 prepare stmt from @tmp;
42 execute stmt;
43 emp_id name mgr
44 4 john 1
45 drop prepare stmt;
46 select emp_id from (select emp_id from emp where row_end>'2031-1-1') as tmp;
47 emp_id
48 4
49 set @tmp= "select emp_id from (select emp_id from emp where row_end>'2031-1-1') as tmp";
50 prepare stmt from @tmp;
51 execute stmt;
52 emp_id
53 4
54 drop prepare stmt;
55 with recursive
56 ancestors
57 as
58 (
59 select e.emp_id, e.name, e.mgr
60 from emp as e
61 where name = 'john'
62 union
63 select ee.emp_id, ee.name, ee.mgr
64 from emp as ee, ancestors as a
65 where ee.mgr = a.emp_id
66 )
67 select * from ancestors;
68 emp_id name mgr
69 4 john 1
70 set @tmp= "
71 with recursive
72 ancestors
73 as
74 (
75 select e.emp_id, e.name, e.mgr
76 from emp as e
77 where name = 'john'
78 union
79 select ee.emp_id, ee.name, ee.mgr
80 from emp as ee, ancestors as a
81 where ee.mgr = a.emp_id
82 )
83 select * from ancestors";
84 prepare stmt from @tmp;
85 execute stmt;
86 emp_id name mgr
87 4 john 1
88 drop prepare stmt;
89 with recursive
90 ancestors
91 as
92 (
93 select e.emp_id, e.name, e.mgr
94 from emp for system_time as of timestamp @ts as e
95 where name = 'bill'
96 union
97 select ee.emp_id, ee.name, ee.mgr
98 from emp for system_time as of timestamp @ts as ee,
99 ancestors as a
100 where ee.mgr = a.emp_id
101 )
102 select * from ancestors;
103 emp_id name mgr
104 1 bill 0
105 2 bill 1
106 3 kate 1
107 set @tmp= "
108 with recursive
109 ancestors
110 as
111 (
112 select e.emp_id, e.name, e.mgr
113 from emp for system_time as of timestamp @ts as e
114 where name = 'bill'
115 union
116 select ee.emp_id, ee.name, ee.mgr
117 from emp for system_time as of timestamp @ts as ee,
118 ancestors as a
119 where ee.mgr = a.emp_id
120 )
121 select * from ancestors";
122 prepare stmt from @tmp;
123 execute stmt;
124 emp_id name mgr
125 1 bill 0
126 2 bill 1
127 3 kate 1
128 drop prepare stmt;
129 drop table emp;
130 create or replace table t1 (x int) with system versioning;
131 create or replace table t2 (y int) with system versioning;
132 insert into t1 values (1);
133 set @t0= now(6);
134 delete from t1;
135 insert into t1 values (2);
136 insert into t2 values (10);
137 select * from (select *, t1.row_end, t1.row_end as endo from t1) as s0;
138 x row_end endo
139 2 # #
140 select * from (select *, t1.row_end, t2.row_start from t1, t2) as s0;
141 x y row_end row_start
142 2 10 # #
143 # SYSTEM_TIME propagation from inner to outer
144 select * from (select * from t1 for system_time as of timestamp @t0, t2) as s0;
145 x y
146 1 10
147 with s1 as (select * from t1 for system_time as of timestamp @t0, t2) select * from s1;
148 x y
149 1 10
150 # leading table selection
151 select * from (select *, t1.row_end from t2, t1 for system_time as of timestamp @t0) as s2;
152 y x row_end
153 10 1 #
154 with s3 as (select *, t1.row_end from t2, t1 for system_time as of timestamp @t0) select * from s3;
155 y x row_end
156 10 1 #
157 ### VIEW instead of t1
158 set @q= concat("create view vt1 as select * from t1 for system_time as of timestamp '", @t0, "'");
159 prepare q from @q;
160 execute q;
161 drop prepare q;
162 create view vt2 as select * from t1;
163 # SYSTEM_TIME propagation from view
164 select * from vt1;
165 x
166 1
167 # SYSTEM_TIME propagation from inner to outer
168 select * from (select * from vt1, t2) as s0;
169 x y
170 1 10
171 ### SYSTEM_TIME clash
172 select * from (select * from t1 for system_time all) for system_time all as dt0;
173 ERROR HY000: Table `dt0` is not system-versioned
174 select * from vt1 for system_time all;
175 ERROR HY000: Table `vt1` is not system-versioned
176 with dt1 as (select * from t1 for system_time all)
177 select * from dt1 for system_time all;
178 ERROR HY000: Table `dt1` is not system-versioned
179 ### UNION
180 set @t1= now(6);
181 delete from t2;
182 insert into t2 values (3);
183 # SYSTEM_TIME is not propagated
184 select x from t1 union
185 select y from t2;
186 x
187 2
188 3
189 select x from t1 for system_time as of @t0 union
190 select y from t2;
191 x
192 1
193 3
194 select x from t1 union
195 select y from t2 for system_time as of @t1;
196 x
197 2
198 10
199 select x from t1 for system_time as of @t0 union
200 select y from t2 for system_time as of @t1;
201 x
202 1
203 10
204 # LEFT/RIGHT JOIN
205 create or replace table t1 (x int, y int) with system versioning;
206 create or replace table t2 (x int, y int) with system versioning;
207 insert into t1 values (1, 1), (1, 2), (1, 3), (4, 4), (5, 5);
208 insert into t2 values (1, 2), (2, 1), (3, 1);
209 ## Outer or inner SYSTEM_TIME produces same expression
210 id select_type table type possible_keys key key_len ref rows filtered Extra
211 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where
212 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
213 Query A:
214 Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME AS OF TIMESTAMP current_timestamp(6) join `test`.`t2` FOR SYSTEM_TIME AS OF TIMESTAMP current_timestamp(6) where `test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t2`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`t2`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`t1`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`t1`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu'
215 id select_type table type possible_keys key key_len ref rows filtered Extra
216 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where
217 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
218 Query B:
219 Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME AS OF TIMESTAMP current_timestamp(6) join `test`.`t2` FOR SYSTEM_TIME AS OF TIMESTAMP current_timestamp(6) where `test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t2`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`t2`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`t1`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`t1`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu'
220 Fine result: queries A and B are equal.
221 ## LEFT JOIN: t1, t2 versioned
222 select * from (
223 select t1.x as LJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2
224 from t1 left join t2 on t1.x = t2.x)
225 as derived;
226 LJ1_x1 y1 x2 y2
227 1 1 1 2
228 1 2 1 2
229 1 3 1 2
230 4 4 NULL NULL
231 5 5 NULL NULL
232 alter table t2 drop system versioning;
233 ## LEFT JOIN: t1 versioned
234 select * from (
235 select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2
236 from t1 left join t2 on t1.x = t2.x)
237 as derived;
238 LJ2_x1 y1 x2 y2
239 1 1 1 2
240 1 2 1 2
241 1 3 1 2
242 4 4 NULL NULL
243 5 5 NULL NULL
244 alter table t1 drop system versioning;
245 alter table t2 add system versioning;
246 ## LEFT JOIN: t2 versioned
247 select * from (
248 select t1.x as LJ3_x1, t1.y as y1, t2.x as x2, t2.y as y2
249 from t1 left join t2 on t1.x = t2.x)
250 as derived;
251 LJ3_x1 y1 x2 y2
252 1 1 1 2
253 1 2 1 2
254 1 3 1 2
255 4 4 NULL NULL
256 5 5 NULL NULL
257 alter table t1 add system versioning;
258 ## RIGHT JOIN: t1, t2 versioned
259 select * from (
260 select t1.x as RJ1_x1, t1.y as y1, t2.x as x2, t2.y as y2
261 from t1 right join t2 on t1.x = t2.x)
262 as derived;
263 RJ1_x1 y1 x2 y2
264 1 1 1 2
265 1 2 1 2
266 1 3 1 2
267 NULL NULL 2 1
268 NULL NULL 3 1
269 alter table t2 drop system versioning;
270 ## RIGHT JOIN: t1 versioned
271 select * from (
272 select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2
273 from t1 right join t2 on t1.x = t2.x)
274 as derived;
275 RJ2_x1 y1 x2 y2
276 1 1 1 2
277 1 2 1 2
278 1 3 1 2
279 NULL NULL 2 1
280 NULL NULL 3 1
281 alter table t1 drop system versioning;
282 alter table t2 add system versioning;
283 ## RIGHT JOIN: t2 versioned
284 select * from (
285 select t1.x as RJ3_x1, t1.y as y1, t2.x as x2, t2.y as y2
286 from t1 right join t2 on t1.x = t2.x)
287 as derived;
288 RJ3_x1 y1 x2 y2
289 1 1 1 2
290 1 2 1 2
291 1 3 1 2
292 NULL NULL 2 1
293 NULL NULL 3 1
294 drop table t1, t2;
295 drop view vt1, vt2;