diff options
| -rw-r--r-- | mysql-test/suite/versioning/r/cte.result (renamed from mysql-test/suite/versioning/r/cte_recursive.result) | 31 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/r/derived.result | 16 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/t/cte.opt (renamed from mysql-test/suite/versioning/t/cte_recursive.opt) | 1 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/t/cte.test (renamed from mysql-test/suite/versioning/t/cte_recursive.test) | 15 | ||||
| -rw-r--r-- | mysql-test/suite/versioning/t/derived.test | 8 | ||||
| -rw-r--r-- | sql/sql_select.cc | 31 |
6 files changed, 88 insertions, 14 deletions
diff --git a/mysql-test/suite/versioning/r/cte_recursive.result b/mysql-test/suite/versioning/r/cte.result index 4ad56b8f3a7..d03e55d2f80 100644 --- a/mysql-test/suite/versioning/r/cte_recursive.result +++ b/mysql-test/suite/versioning/r/cte.result @@ -64,5 +64,36 @@ emp_id name mgr salary 1 bill NULL 1000 30 jane 1 750 20 john 30 500 +create or replace table emp ( emp_id int, name varchar(127), mgr int) with system versioning; +create or replace table addr ( emp_id int, address varchar(100)) with system versioning; +insert emp values (1, 'bill', 0), (2, 'bill', 1), (3, 'kate', 1); +insert addr values (1, 'Moscow'), (2, 'New York'), (3, 'London'); +set @ts=now(6); +delete from emp; +delete from addr; +insert emp values (4, 'john', 1); +insert addr values (4, 'Paris'); +with ancestors as (select * from emp natural join addr) select * from ancestors; +emp_id name mgr address +4 john 1 Paris +with ancestors as (select * from emp natural join addr) select * from ancestors for system_time all; +emp_id name mgr address +1 bill 0 Moscow +2 bill 1 New York +3 kate 1 London +4 john 1 Paris +with ancestors as (select * from emp natural join addr query for system_time all) select * from ancestors; +emp_id name mgr address +1 bill 0 Moscow +2 bill 1 New York +3 kate 1 London +4 john 1 Paris +select * from emp natural join addr query for system_time all; +emp_id name mgr address +1 bill 0 Moscow +2 bill 1 New York +3 kate 1 London +4 john 1 Paris drop table emp; drop table dept; +drop table addr; diff --git a/mysql-test/suite/versioning/r/derived.result b/mysql-test/suite/versioning/r/derived.result index 6b26b18adc0..06bdf9799dd 100644 --- a/mysql-test/suite/versioning/r/derived.result +++ b/mysql-test/suite/versioning/r/derived.result @@ -139,10 +139,22 @@ ERROR HY000: Derived table is prohibited: system fields from multiple tables `t1 select * from (select * from t1 for system_time as of timestamp @t0, t2) as s0; x y 1 10 -select * from (select *, t1.sys_trx_end from t2, t1 for system_time as of timestamp @t0) as s0; +with s1 as (select * from t1 for system_time as of timestamp @t0, t2) select * from s1; +x y +1 10 +select * from (select *, t1.sys_trx_end from t2, t1 for system_time as of timestamp @t0) as s2; +y x +10 1 +with s3 as (select *, t1.sys_trx_end from t2, t1 for system_time as of timestamp @t0) select * from s3; +y x +10 1 +select * from (select *, t1.sys_trx_start from t2 for system_time as of now, t1) as s4 query for system_time as of timestamp @t0; +y x +10 1 +with s5 as (select *, t1.sys_trx_start from t2 for system_time as of now, t1) select * from s5 for system_time as of timestamp @t0; y x 10 1 -select * from (select *, t1.sys_trx_start from t2 for system_time as of now, t1) as s0 query for system_time as of timestamp @t0; +with s6 as (select *, t1.sys_trx_start from t2 for system_time as of now, t1) select * from s6 query for system_time as of timestamp @t0; y x 10 1 set @q= concat("create view vt1 as select * from t1 for system_time as of timestamp '", @t0, "'"); diff --git a/mysql-test/suite/versioning/t/cte_recursive.opt b/mysql-test/suite/versioning/t/cte.opt index 993df05011e..36f683f4f5b 100644 --- a/mysql-test/suite/versioning/t/cte_recursive.opt +++ b/mysql-test/suite/versioning/t/cte.opt @@ -1,2 +1,3 @@ --innodb --default-storage-engine=innodb --plugin-load=versioning +--versioning-hide=implicit diff --git a/mysql-test/suite/versioning/t/cte_recursive.test b/mysql-test/suite/versioning/t/cte.test index d1de218bf62..2650590b552 100644 --- a/mysql-test/suite/versioning/t/cte_recursive.test +++ b/mysql-test/suite/versioning/t/cte.test @@ -65,5 +65,20 @@ as ) select * from ancestors; +create or replace table emp ( emp_id int, name varchar(127), mgr int) with system versioning; +create or replace table addr ( emp_id int, address varchar(100)) with system versioning; +insert emp values (1, 'bill', 0), (2, 'bill', 1), (3, 'kate', 1); +insert addr values (1, 'Moscow'), (2, 'New York'), (3, 'London'); +set @ts=now(6); +delete from emp; +delete from addr; +insert emp values (4, 'john', 1); +insert addr values (4, 'Paris'); +with ancestors as (select * from emp natural join addr) select * from ancestors; +with ancestors as (select * from emp natural join addr) select * from ancestors for system_time all; +with ancestors as (select * from emp natural join addr query for system_time all) select * from ancestors; +select * from emp natural join addr query for system_time all; + drop table emp; drop table dept; +drop table addr; diff --git a/mysql-test/suite/versioning/t/derived.test b/mysql-test/suite/versioning/t/derived.test index 867973aee7e..f4de94f2f27 100644 --- a/mysql-test/suite/versioning/t/derived.test +++ b/mysql-test/suite/versioning/t/derived.test @@ -103,10 +103,14 @@ select * from (select *, t1.sys_trx_end, t2.sys_trx_start from t1, t2) as s0; # system_time propagation from inner to outer select * from (select * from t1 for system_time as of timestamp @t0, t2) as s0; +with s1 as (select * from t1 for system_time as of timestamp @t0, t2) select * from s1; # leading table selection -select * from (select *, t1.sys_trx_end from t2, t1 for system_time as of timestamp @t0) as s0; +select * from (select *, t1.sys_trx_end from t2, t1 for system_time as of timestamp @t0) as s2; +with s3 as (select *, t1.sys_trx_end from t2, t1 for system_time as of timestamp @t0) select * from s3; # system_time propagation from outer to inner -select * from (select *, t1.sys_trx_start from t2 for system_time as of now, t1) as s0 query for system_time as of timestamp @t0; +select * from (select *, t1.sys_trx_start from t2 for system_time as of now, t1) as s4 query for system_time as of timestamp @t0; +with s5 as (select *, t1.sys_trx_start from t2 for system_time as of now, t1) select * from s5 for system_time as of timestamp @t0; +with s6 as (select *, t1.sys_trx_start from t2 for system_time as of now, t1) select * from s6 query for system_time as of timestamp @t0; # VIEW instead of t1 set @q= concat("create view vt1 as select * from t1 for system_time as of timestamp '", @t0, "'"); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 208946c1191..ff3ce427ef5 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -771,7 +771,7 @@ int vers_setup_select(THD *thd, TABLE_LIST *tables, COND **where_expr, } SELECT_LEX *outer_slex= slex->next_select_in_list(); - bool use_slex_conds= false; + bool force_slex_conds= false; if (outer_slex) { if (slex->vers_derived_conds) @@ -786,15 +786,26 @@ int vers_setup_select(THD *thd, TABLE_LIST *tables, COND **where_expr, } if (slex->vers_conditions.import_outer) { - // Propagate query conditions from nearest outer SELECT_LEX: - while (outer_slex && (!outer_slex->vers_conditions || outer_slex->vers_conditions.from_inner)) - outer_slex= outer_slex->next_select_in_list(); - if (outer_slex) + DBUG_ASSERT(slex->master_unit()); + TABLE_LIST* derived= slex->master_unit()->derived; + DBUG_ASSERT(derived); + if (derived->vers_conditions) { - slex->vers_conditions= outer_slex->vers_conditions; - outer_slex->vers_conditions.used= true; - DBUG_ASSERT(slex->master_unit()->derived); - use_slex_conds= slex->master_unit()->derived->is_view(); + slex->vers_conditions= derived->vers_conditions; + derived->vers_conditions.used= true; + force_slex_conds= derived->is_view(); + } + else + { + // Propagate query conditions from nearest outer SELECT_LEX: + while (outer_slex && (!outer_slex->vers_conditions || outer_slex->vers_conditions.from_inner)) + outer_slex= outer_slex->next_select_in_list(); + if (outer_slex) + { + slex->vers_conditions= outer_slex->vers_conditions; + outer_slex->vers_conditions.used= true; + force_slex_conds= derived->is_view(); + } } } } @@ -803,7 +814,7 @@ int vers_setup_select(THD *thd, TABLE_LIST *tables, COND **where_expr, { if (table->table && table->table->versioned()) { - vers_select_conds_t &vers_conditions= use_slex_conds || !table->vers_conditions? + vers_select_conds_t &vers_conditions= force_slex_conds || !table->vers_conditions? (slex->vers_conditions.used= true, slex->vers_conditions) : table->vers_conditions; |