[MDEV-14820] System versioning is applied incorrectly to CTEs Created: 2017-12-30  Updated: 2018-05-29  Resolved: 2018-05-12

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.3
Fix Version/s: 10.3.7

Type: Bug Priority: Critical
Reporter: Sergei Golubchik Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-14832 CTE does not work with system version... Closed

 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.

 Comments   
Comment by Aleksey Midenkov [ 2018-03-14 ]

For Case 1 the query is prohibited when any of multiple CTE instances have SYSTEM_TIME clause:

with cte as (select * from emp)
 
select * from cte for system_time as of @ts_1 as ctex, 
cte for system_time as of @ts_2 as ctey;
 
 
 
→ ERROR 4142 (HY000): Multiple `cte` prohibited in historical CTE query

even if SYSTEM_TIME is the same in all instances or absent in some instances:

with cte as (select * from emp)
 
select * from cte for system_time as of @ts_1 as ctex, 
cte for system_time as of @ts_1 as ctey;
 
 
 
→ ERROR 4142 (HY000): Multiple `cte` prohibited in historical CTE query
 
 
 
with cte as (select * from emp)
 
select * from cte for system_time as of @ts_1 as ctex, 
cte as ctey;
 
 
 
→ ERROR 4142 (HY000): Multiple `cte` prohibited in historical CTE query

Comment by Igor Babaev [ 2018-04-24 ]

Ok to push

Generated at Thu Feb 08 08:16:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.