Debian Codegraph @

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

raw
1 if (`SELECT $PS_PROTOCOL + $CURSOR_PROTOCOL > 0`)
2 {
3 --skip Test temporarily disabled for ps-protocol and cursor-protocol
4 }
5 --source include/have_innodb.inc
6 --source include/default_optimizer_switch.inc
7 --source include/word_size.inc
8 --source suite/versioning/sys_time.inc
9
10 --let $replace_regex_tsltz6= /TIMESTAMP..WITH LOCAL TIME ZONE..'....-..-.. ..:..:..[.]......'/TIMESTAMP\/*WITH LOCAL TIME ZONE*\/'YYYY-MM-DD hh:ss:mm:.uuuuuu'/
11
12 SET @saved_stats_persistent = @@GLOBAL.innodb_stats_persistent;
13 SET GLOBAL innodb_stats_persistent = OFF;
14
15 set time_zone="+00:00";
16 set default_storage_engine=innodb;
17 create or replace table dept (
18 dept_id int(10) primary key,
19 name varchar(100)
20 ) with system versioning;
21
22 create or replace table emp (
23 emp_id int(10) primary key,
24 dept_id int(10) not null,
25 name varchar(100) not null,
26 mgr int(10),
27 salary int(10) not null,
28 constraint `dept-emp-fk`
29 foreign key (dept_id) references dept (dept_id)
30 on delete cascade
31 on update restrict,
32 constraint `mgr-fk`
33 foreign key (mgr) references emp (emp_id)
34 on delete restrict
35 on update restrict
36 ) with system versioning;
37
38 insert into dept (dept_id, name) values (10, "accounting");
39
40 insert into emp (emp_id, name, salary, dept_id, mgr) values
41 (1, "bill", 1000, 10, null),
42 (20, "john", 500, 10, 1),
43 (30, "jane", 750, 10,1 );
44
45 --disable_cursor_protocol
46 select row_start into @ts_1 from emp where name="jane";
47 --enable_cursor_protocol
48
49 update emp set mgr=30 where name ="john";
50
51 --replace_regex $replace_regex_tsltz6
52 explain extended
53 with ancestors as (
54 select e.emp_id, e.name, e.mgr, e.salary from emp as e where name = 'bill'
55 union
56 select e.emp_id, e.name, e.mgr, e.salary from emp as e
57 ) select * from ancestors for system_time as of @ts_1;
58
59 --disable_cursor_protocol
60 select row_start into @ts_2 from emp where name="john";
61 --enable_cursor_protocol
62
63 let $q=
64 /* All report to 'Bill' */
65 with recursive
66 ancestors
67 as
68 (
69 select e.emp_id, e.name, e.mgr, e.salary
70 from emp for system_time as of timestamp @ts_1 as e
71 where name = 'bill'
72 union
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 ancestors as a
76 where e.mgr = a.emp_id
77 )
78 select * from ancestors;
79
80 --replace_regex $replace_regex_tsltz6
81 eval explain extended $q;
82 eval $q;
83
84 let $q=with recursive
85 ancestors
86 as
87 (
88 select e.emp_id, e.name, e.mgr, e.salary
89 from emp as e
90 where name = 'bill'
91 union
92 select e.emp_id, e.name, e.mgr, e.salary
93 from emp as e,
94 ancestors as a
95 where e.mgr = a.emp_id
96 )
97 select * from ancestors for system_time as of timestamp @ts_1;
98
99 --replace_regex $replace_regex_tsltz6
100 eval explain extended $q;
101 eval $q;
102
103 let $q=with recursive
104 ancestors
105 as
106 (
107 select e.emp_id, e.name, e.mgr, e.salary
108 from emp as e
109 where name = 'bill'
110 union
111 select e.emp_id, e.name, e.mgr, e.salary
112 from emp as e,
113 ancestors as a
114 where e.mgr = a.emp_id
115 )
116 select name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1);
117
118 --replace_regex $replace_regex_tsltz6 /$sys_time_max/SYS_TIME_MAX/
119 eval explain extended $q;
120 eval $q;
121
122 with recursive
123 ancestors
124 as
125 (
126 select e.emp_id, e.name, e.mgr, e.salary
127 from emp as e
128 where name = 'bill'
129 union
130 select e.emp_id, e.name, e.mgr, e.salary
131 from emp as e,
132 ancestors as a
133 where e.mgr = a.emp_id
134 )
135 select * from ancestors for system_time as of @ts_2,
136 ancestors for system_time as of @ts_2 a2;
137
138 --error ER_CONFLICTING_FOR_SYSTEM_TIME
139 with recursive
140 ancestors
141 as
142 (
143 select e.emp_id, e.name, e.mgr, e.salary
144 from emp as e
145 where name = 'bill'
146 union
147 select e.emp_id, e.name, e.mgr, e.salary
148 from emp as e,
149 ancestors as a
150 where e.mgr = a.emp_id
151 )
152 select * from ancestors for system_time as of @ts_2,
153 ancestors for system_time as of now() a2;
154
155 --error ER_CONFLICTING_FOR_SYSTEM_TIME
156 with recursive
157 ancestors
158 as
159 (
160 select e.emp_id, e.name, e.mgr, e.salary
161 from emp as e
162 where name = 'bill'
163 union
164 select e.emp_id, e.name, e.mgr, e.salary
165 from emp as e,
166 ancestors as a
167 where e.mgr = a.emp_id
168 )
169 select * from ancestors,
170 ancestors for system_time as of @ts_2 a2;
171
172 --error ER_CONFLICTING_FOR_SYSTEM_TIME
173 with recursive
174 ancestors
175 as
176 (
177 select e.emp_id, e.name, e.mgr, e.salary
178 from emp as e
179 where name = 'bill'
180 union
181 select e.emp_id, e.name, e.mgr, e.salary
182 from emp as e,
183 ancestors as a
184 where e.mgr = a.emp_id
185 )
186 select * from ancestors for system_time as of @ts_2,
187 ancestors a2;
188
189 --error ER_CONFLICTING_FOR_SYSTEM_TIME
190 with recursive
191 ancestors
192 as
193 (
194 select e.emp_id, e.name, e.mgr, e.salary
195 from emp as e
196 where name = 'bill'
197 union
198 select e.emp_id, e.name, e.mgr, e.salary
199 from emp as e,
200 ancestors as a
201 where e.mgr = a.emp_id
202 )
203 select * from ancestors for system_time as of @ts_2
204 where emp_id in (select * from ancestors);
205
206 --echo # SYSTEM_TIME to internal recursive instance is prohibited
207 --error ER_VERS_NOT_VERSIONED
208 with recursive cte as
209 (
210 select * from emp
211 union all
212 select * from cte for system_time as of @ts_1
213 )
214 select * from cte;
215
216 create or replace table emp ( emp_id int, name varchar(127), mgr int) with system versioning;
217 create or replace table addr ( emp_id int, address varchar(100)) with system versioning;
218 insert emp values (1, 'bill', 0), (2, 'bill', 1), (3, 'kate', 1);
219 insert addr values (1, 'Moscow'), (2, 'New York'), (3, 'London');
220 set @ts=now(6);
221 delete from emp;
222 delete from addr;
223
224 with recursive
225 ancestors
226 as
227 (
228 select e.emp_id, e.name, e.mgr
229 from emp for system_time as of timestamp @ts as e
230 where name = 'bill'
231 union
232 select ee.emp_id, ee.name, ee.mgr
233 from emp for system_time as of timestamp @ts as ee, ancestors as a
234 where ee.mgr = a.emp_id
235 )
236 select * from ancestors;
237
238 insert emp values (4, 'john', 1);
239 insert addr values (4, 'Paris');
240 with ancestors as (select * from emp natural join addr) select * from ancestors;
241 with ancestors as (select * from emp natural join addr) select * from ancestors for system_time all;
242 with ancestors as (select * from (select * from emp natural join addr) for system_time all as t) select * from ancestors;
243 select * from (select * from emp natural join addr) for system_time all as t;
244
245 drop table emp;
246 drop table dept;
247 drop table addr;
248
249 SET GLOBAL innodb_stats_persistent = @saved_stats_persistent;