Debian Codegraph @

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

raw
1 SET @saved_stats_persistent = @@GLOBAL.innodb_stats_persistent;
2 SET GLOBAL innodb_stats_persistent = OFF;
3 set time_zone="+00:00";
4 set default_storage_engine=innodb;
5 create or replace table dept (
6 dept_id int(10) primary key,
7 name varchar(100)
8 ) with system versioning;
9 create or replace table emp (
10 emp_id int(10) primary key,
11 dept_id int(10) not null,
12 name varchar(100) not null,
13 mgr int(10),
14 salary int(10) not null,
15 constraint `dept-emp-fk`
16 foreign key (dept_id) references dept (dept_id)
17 on delete cascade
18 on update restrict,
19 constraint `mgr-fk`
20 foreign key (mgr) references emp (emp_id)
21 on delete restrict
22 on update restrict
23 ) with system versioning;
24 insert into dept (dept_id, name) values (10, "accounting");
25 insert into emp (emp_id, name, salary, dept_id, mgr) values
26 (1, "bill", 1000, 10, null),
27 (20, "john", 500, 10, 1),
28 (30, "jane", 750, 10,1 );
29 select row_start into @ts_1 from emp where name="jane";
30 update emp set mgr=30 where name ="john";
31 explain extended
32 with ancestors as (
33 select e.emp_id, e.name, e.mgr, e.salary from emp as e where name = 'bill'
34 union
35 select e.emp_id, e.name, e.mgr, e.salary from emp as e
36 ) select * from ancestors for system_time as of @ts_1;
37 id select_type table type possible_keys key key_len ref rows filtered Extra
38 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8 100.00
39 2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where
40 3 UNION e ALL NULL NULL NULL NULL 4 100.00 Using where
41 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
42 Warnings:
43 Note 1003 with ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME AS OF TIMESTAMP @`ts_1` `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`e`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME AS OF TIMESTAMP @`ts_1` `e` where `test`.`e`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`e`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu')/* select#1 */ select `ancestors`.`emp_id` AS `emp_id`,`ancestors`.`name` AS `name`,`ancestors`.`mgr` AS `mgr`,`ancestors`.`salary` AS `salary` from `ancestors`
44 select row_start into @ts_2 from emp where name="john";
45 explain extended /* All report to 'Bill' */
46 with recursive
47 ancestors
48 as
49 (
50 select e.emp_id, e.name, e.mgr, e.salary
51 from emp for system_time as of timestamp @ts_1 as e
52 where name = 'bill'
53 union
54 select e.emp_id, e.name, e.mgr, e.salary
55 from emp for system_time as of timestamp @ts_1 as e,
56 ancestors as a
57 where e.mgr = a.emp_id
58 )
59 select * from ancestors;
60 id select_type table type possible_keys key key_len ref rows filtered Extra
61 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00
62 2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where
63 3 RECURSIVE UNION e ALL mgr-fk NULL NULL NULL 4 100.00 Using where
64 3 RECURSIVE UNION <derived2> ref key0 key0 5 test.e.mgr 1 100.00
65 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
66 Warnings:
67 Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME AS OF TIMESTAMP @`ts_1` `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`e`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME AS OF TIMESTAMP @`ts_1` `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`e`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu')/* select#1 */ select `ancestors`.`emp_id` AS `emp_id`,`ancestors`.`name` AS `name`,`ancestors`.`mgr` AS `mgr`,`ancestors`.`salary` AS `salary` from `ancestors`
68 /* All report to 'Bill' */
69 with recursive
70 ancestors
71 as
72 (
73 select e.emp_id, e.name, e.mgr, e.salary
74 from emp for system_time as of timestamp @ts_1 as e
75 where name = 'bill'
76 union
77 select e.emp_id, e.name, e.mgr, e.salary
78 from emp for system_time as of timestamp @ts_1 as e,
79 ancestors as a
80 where e.mgr = a.emp_id
81 )
82 select * from ancestors;
83 emp_id name mgr salary
84 1 bill NULL 1000
85 20 john 1 500
86 30 jane 1 750
87 explain extended with recursive
88 ancestors
89 as
90 (
91 select e.emp_id, e.name, e.mgr, e.salary
92 from emp as e
93 where name = 'bill'
94 union
95 select e.emp_id, e.name, e.mgr, e.salary
96 from emp as e,
97 ancestors as a
98 where e.mgr = a.emp_id
99 )
100 select * from ancestors for system_time as of timestamp @ts_1;
101 id select_type table type possible_keys key key_len ref rows filtered Extra
102 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00
103 2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where
104 3 RECURSIVE UNION e ALL mgr-fk NULL NULL NULL 4 100.00 Using where
105 3 RECURSIVE UNION <derived2> ref key0 key0 5 test.e.mgr 1 100.00
106 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
107 Warnings:
108 Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME AS OF TIMESTAMP @`ts_1` `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`e`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME AS OF TIMESTAMP @`ts_1` `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`e`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu')/* select#1 */ select `ancestors`.`emp_id` AS `emp_id`,`ancestors`.`name` AS `name`,`ancestors`.`mgr` AS `mgr`,`ancestors`.`salary` AS `salary` from `ancestors`
109 with recursive
110 ancestors
111 as
112 (
113 select e.emp_id, e.name, e.mgr, e.salary
114 from emp as e
115 where name = 'bill'
116 union
117 select e.emp_id, e.name, e.mgr, e.salary
118 from emp as e,
119 ancestors as a
120 where e.mgr = a.emp_id
121 )
122 select * from ancestors for system_time as of timestamp @ts_1;
123 emp_id name mgr salary
124 1 bill NULL 1000
125 20 john 1 500
126 30 jane 1 750
127 explain extended with recursive
128 ancestors
129 as
130 (
131 select e.emp_id, e.name, e.mgr, e.salary
132 from emp as e
133 where name = 'bill'
134 union
135 select e.emp_id, e.name, e.mgr, e.salary
136 from emp as e,
137 ancestors as a
138 where e.mgr = a.emp_id
139 )
140 select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1);
141 id select_type table type possible_keys key key_len ref rows filtered Extra
142 1 PRIMARY emp ALL PRIMARY NULL NULL NULL 4 100.00 Using where
143 1 PRIMARY <derived2> ref key0 key0 5 test.emp.emp_id 1 100.00 FirstMatch(emp)
144 2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where
145 3 RECURSIVE UNION e ALL mgr-fk NULL NULL NULL 4 100.00 Using where
146 3 RECURSIVE UNION <derived2> ref key0 key0 5 test.e.mgr 1 100.00
147 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
148 Warnings:
149 Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME AS OF TIMESTAMP @`ts_1` `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`e`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME AS OF TIMESTAMP @`ts_1` `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu' and `test`.`e`.`row_start` <= TIMESTAMP/*WITH LOCAL TIME ZONE*/'YYYY-MM-DD hh:ss:mm:.uuuuuu')/* select#1 */ select `test`.`emp`.`name` AS `name` from `test`.`emp` semi join (`ancestors`) where `ancestors`.`emp_id` = `test`.`emp`.`emp_id` and `test`.`emp`.`row_end` = TIMESTAMP'2106-02-07 06:28:15.999999'
150 with recursive
151 ancestors
152 as
153 (
154 select e.emp_id, e.name, e.mgr, e.salary
155 from emp as e
156 where name = 'bill'
157 union
158 select e.emp_id, e.name, e.mgr, e.salary
159 from emp as e,
160 ancestors as a
161 where e.mgr = a.emp_id
162 )
163 select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1);
164 name
165 bill
166 john
167 jane
168 with recursive
169 ancestors
170 as
171 (
172 select e.emp_id, e.name, e.mgr, e.salary
173 from emp as e
174 where name = 'bill'
175 union
176 select e.emp_id, e.name, e.mgr, e.salary
177 from emp as e,
178 ancestors as a
179 where e.mgr = a.emp_id
180 )
181 select * from ancestors for system_time as of @ts_2,
182 ancestors for system_time as of @ts_2 a2;
183 emp_id name mgr salary emp_id name mgr salary
184 1 bill NULL 1000 1 bill NULL 1000
185 30 jane 1 750 1 bill NULL 1000
186 20 john 30 500 1 bill NULL 1000
187 1 bill NULL 1000 30 jane 1 750
188 30 jane 1 750 30 jane 1 750
189 20 john 30 500 30 jane 1 750
190 1 bill NULL 1000 20 john 30 500
191 30 jane 1 750 20 john 30 500
192 20 john 30 500 20 john 30 500
193 with recursive
194 ancestors
195 as
196 (
197 select e.emp_id, e.name, e.mgr, e.salary
198 from emp as e
199 where name = 'bill'
200 union
201 select e.emp_id, e.name, e.mgr, e.salary
202 from emp as e,
203 ancestors as a
204 where e.mgr = a.emp_id
205 )
206 select * from ancestors for system_time as of @ts_2,
207 ancestors for system_time as of now() a2;
208 ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE
209 with recursive
210 ancestors
211 as
212 (
213 select e.emp_id, e.name, e.mgr, e.salary
214 from emp as e
215 where name = 'bill'
216 union
217 select e.emp_id, e.name, e.mgr, e.salary
218 from emp as e,
219 ancestors as a
220 where e.mgr = a.emp_id
221 )
222 select * from ancestors,
223 ancestors for system_time as of @ts_2 a2;
224 ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE
225 with recursive
226 ancestors
227 as
228 (
229 select e.emp_id, e.name, e.mgr, e.salary
230 from emp as e
231 where name = 'bill'
232 union
233 select e.emp_id, e.name, e.mgr, e.salary
234 from emp as e,
235 ancestors as a
236 where e.mgr = a.emp_id
237 )
238 select * from ancestors for system_time as of @ts_2,
239 ancestors a2;
240 ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE
241 with recursive
242 ancestors
243 as
244 (
245 select e.emp_id, e.name, e.mgr, e.salary
246 from emp as e
247 where name = 'bill'
248 union
249 select e.emp_id, e.name, e.mgr, e.salary
250 from emp as e,
251 ancestors as a
252 where e.mgr = a.emp_id
253 )
254 select * from ancestors for system_time as of @ts_2
255 where emp_id in (select * from ancestors);
256 ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE
257 # SYSTEM_TIME to internal recursive instance is prohibited
258 with recursive cte as
259 (
260 select * from emp
261 union all
262 select * from cte for system_time as of @ts_1
263 )
264 select * from cte;
265 ERROR HY000: Table `cte` is not system-versioned
266 create or replace table emp ( emp_id int, name varchar(127), mgr int) with system versioning;
267 create or replace table addr ( emp_id int, address varchar(100)) with system versioning;
268 insert emp values (1, 'bill', 0), (2, 'bill', 1), (3, 'kate', 1);
269 insert addr values (1, 'Moscow'), (2, 'New York'), (3, 'London');
270 set @ts=now(6);
271 delete from emp;
272 delete from addr;
273 with recursive
274 ancestors
275 as
276 (
277 select e.emp_id, e.name, e.mgr
278 from emp for system_time as of timestamp @ts as e
279 where name = 'bill'
280 union
281 select ee.emp_id, ee.name, ee.mgr
282 from emp for system_time as of timestamp @ts as ee, ancestors as a
283 where ee.mgr = a.emp_id
284 )
285 select * from ancestors;
286 emp_id name mgr
287 1 bill 0
288 2 bill 1
289 3 kate 1
290 insert emp values (4, 'john', 1);
291 insert addr values (4, 'Paris');
292 with ancestors as (select * from emp natural join addr) select * from ancestors;
293 emp_id name mgr address
294 4 john 1 Paris
295 with ancestors as (select * from emp natural join addr) select * from ancestors for system_time all;
296 emp_id name mgr address
297 1 bill 0 Moscow
298 2 bill 1 New York
299 3 kate 1 London
300 4 john 1 Paris
301 with ancestors as (select * from (select * from emp natural join addr) for system_time all as t) select * from ancestors;
302 emp_id name mgr address
303 1 bill 0 Moscow
304 2 bill 1 New York
305 3 kate 1 London
306 4 john 1 Paris
307 select * from (select * from emp natural join addr) for system_time all as t;
308 emp_id name mgr address
309 1 bill 0 Moscow
310 2 bill 1 New York
311 3 kate 1 London
312 4 john 1 Paris
313 drop table emp;
314 drop table dept;
315 drop table addr;
316 SET GLOBAL innodb_stats_persistent = @saved_stats_persistent;