Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14820

System versioning is applied incorrectly to CTEs

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              serg Sergei Golubchik
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: