Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3(EOL)
-
None
Description
FOR SYSTEM_TIME AS OF is (and must be) pushed down to individual tables.
Prepare the test case:
create or replace table emp ( |
emp_id int(10) primary key, |
dept_id int(10) not null, |
name varchar(100) not null, |
mgr int(10), |
salary int(10) not null |
)
|
with system versioning; |
|
|
insert into emp (emp_id, name, salary, dept_id, mgr) values |
(1, "bill", 1000, 10, null), |
(20, "john", 500, 10, 1), |
(30, "jane", 750, 10,1 ); |
|
|
set @ts_1=now(6); |
|
|
update emp set mgr=30 where name ="john"; |
1.
It cannot work with recursive CTE, when it's used many times in a top level select with different system_time values, like in
WITH RECURSIVE cte AS (...) |
SELECT ... FROM cte FOR SYSTEM_TIME AS OF xxx JOIN as ctex |
cte FOR SYSTEM_TIME AS OF yyy JOIN as ctey |
This cannot work at all, because we cannot push two different WHERE clauses down into the same SELECT_LEX. Condition pushdown generally doesn't work for recursive CTEs
2. It can work for non-recursive CTE, because generally condition pushdown works for non-recursive CTEs — they're cloned as many times as they're used. But it doesn't:
explain extended
|
with
|
ancestors
|
as
|
(
|
select e.emp_id, e.name, e.mgr, e.salary |
from emp as e |
where name = 'bill' |
union |
select e.emp_id, e.name, e.mgr, e.salary |
from emp as e |
)
|
select * from ancestors |
for system_time as of @ts_1; |
→ with ancestors as (select e.emp_id,e.name,e.mgr,e.salary,e.sys_trx_start,e.sys_trx_end from emp FOR SYSTEM_TIME ALL e where e.name = 'bill' and e.sys_trx_end > @ts_1 and e.sys_trx_start <= @ts_1 union select e.emp_id,e.name,e.mgr,e.salary,e.sys_trx_start,e.sys_trx_end from emp FOR SYSTEM_TIME ALL e) select ancestors.emp_id,ancestors.name,ancestors.mgr,ancestors.salary,ancestors.sys_trx_start,ancestors.sys_trx_end from ancestors FOR SYSTEM_TIME ALL |
As you can see, @ts_1 condition was only attached to the first instance of emp, not to both.
3. Similarly it can work with recursive CTE that is only used once in the top level select, but it doesn't:
explain extended
|
with recursive |
ancestors
|
as
|
(
|
select e.emp_id, e.name, e.mgr, e.salary |
from emp as e |
where name = 'bill' |
union |
select e.emp_id, e.name, e.mgr, e.salary |
from emp as e, |
ancestors as a |
where e.mgr = a.emp_id |
)
|
select * from ancestors |
for system_time as of @ts_1; |
→ with recursive ancestors as (select e.emp_id,e.name,e.mgr,e.salary,e.sys_trx_start,e.sys_trx_end from emp FOR SYSTEM_TIME ALL e where e.name = 'bill' and e.sys_trx_end = TIMESTAMP'2038-01-19 04:14:07.999999' union select e.emp_id,e.name,e.mgr,e.salary,e.sys_trx_start,e.sys_trx_end from emp FOR SYSTEM_TIME ALL e join ancestors FOR SYSTEM_TIME ALL a where a.emp_id = e.mgr and e.sys_trx_end = TIMESTAMP'2038-01-19 04:14:07.999999' and a.sys_trx_end = TIMESTAMP'2038-01-19 04:14:07.999999') select ancestors.emp_id,ancestors.name,ancestors.mgr,ancestors.salary from ancestors FOR SYSTEM_TIME ALL where ancestors.sys_trx_end > @ts_1 and ancestors.sys_trx_start <= @ts_1 |
The condition is not attached to emp at all, but only to the top level select.
Attachments
Issue Links
- relates to
-
-
- Closed
-