revision-id: 65f79b36f9859333678938850d5ff78a59490dfc (10.4-12-g65f79b36f98) parent(s): ceadd5485a56e272365e201e6c89429202abae3a author: Sergei Golubchik committer: Sergei Golubchik timestamp: 2018-04-23 18:46:47 +0200 message: MDEV-14820 System versioning is applied incorrectly to CTEs Make sure that SELECT_LEX_UNIT::derived, behaves as documented (points to the "TABLE_LIST representing this union in the embedding select"). For recursive CTE this was not necessarily the case, it could've pointed to the TABLE_LIST inside the CTE, not in the embedding select. To fix: * don't update unit->derived in mysql_derived_prepare(), pass derived as an argument to st_select_lex_unit::prepare() * prefer to set unit->derived in TABLE_LIST::init_derived() to the TABLE_LIST in the embedding select, not to the recursive reference. Fail if there are many TABLE_LISTs in the embedding select with conflicting FOR SYSTEM_TIME clauses. cleanup: * remove redundant THD* argument from st_select_lex_unit::prepare() --- mysql-test/suite/versioning/disabled.def | 1 - mysql-test/suite/versioning/r/cte.result | 225 ++++++++++++++++++++++++++++--- mysql-test/suite/versioning/t/cte.test | 152 +++++++++++++++++++-- sql/item_subselect.cc | 2 +- sql/share/errmsg-utf8.txt | 4 +- sql/sql_cte.cc | 2 +- sql/sql_derived.cc | 4 +- sql/sql_lex.h | 3 +- sql/sql_prepare.cc | 4 +- sql/sql_select.cc | 3 +- sql/sql_union.cc | 58 ++++---- sql/sql_view.cc | 2 +- sql/table.cc | 42 +++++- sql/table.h | 2 + 14 files changed, 432 insertions(+), 72 deletions(-) diff --git a/mysql-test/suite/versioning/disabled.def b/mysql-test/suite/versioning/disabled.def index 11e45360f19..888298bbb09 100644 --- a/mysql-test/suite/versioning/disabled.def +++ b/mysql-test/suite/versioning/disabled.def @@ -9,4 +9,3 @@ # Do not use any TAB characters for whitespace. # ############################################################################## -cte: MDEV-14820 diff --git a/mysql-test/suite/versioning/r/cte.result b/mysql-test/suite/versioning/r/cte.result index fda5e086be2..6ad09ede5a6 100644 --- a/mysql-test/suite/versioning/r/cte.result +++ b/mysql-test/suite/versioning/r/cte.result @@ -1,9 +1,8 @@ set default_storage_engine=innodb; create or replace table dept ( -dept_id int(10) primary key, +dept_id int(10) primary key, name varchar(100) -) -with system versioning; +) with system versioning; create or replace table emp ( emp_id int(10) primary key, dept_id int(10) not null, @@ -18,16 +17,51 @@ constraint `mgr-fk` foreign key (mgr) references emp (emp_id) on delete restrict on update restrict -) -with system versioning; +) with system versioning; insert into dept (dept_id, name) values (10, "accounting"); -insert into emp (emp_id, name, salary, dept_id, mgr) values +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 ); -select max(sys_trx_start) into @ts_1 from emp; +select row_start into @ts_1 from emp where name="jane"; update emp set mgr=30 where name ="john"; -select sys_trx_start into @ts_2 from emp where name="john"; +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8 100.00 +2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where +3 UNION e ALL NULL NULL NULL NULL 4 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 with ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `ancestors`.`emp_id` AS `emp_id`,`ancestors`.`name` AS `name`,`ancestors`.`mgr` AS `mgr`,`ancestors`.`salary` AS `salary` from `ancestors` +select row_start into @ts_2 from emp where name="john"; +explain extended /* All report to 'Bill' */ +with recursive +ancestors +as +( +select e.emp_id, e.name, e.mgr, e.salary +from emp for system_time as of timestamp @ts_1 as e +where name = 'bill' + union +select e.emp_id, e.name, e.mgr, e.salary +from emp for system_time as of timestamp @ts_1 as e, +ancestors as a +where e.mgr = a.emp_id +) +select * from ancestors; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 +2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where +3 RECURSIVE UNION e ALL mgr-fk NULL NULL NULL 4 100.00 Using where +3 RECURSIVE UNION <derived2> ref key0 key0 4 test.e.mgr 2 100.00 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `ancestors`.`emp_id` AS `emp_id`,`ancestors`.`name` AS `name`,`ancestors`.`mgr` AS `mgr`,`ancestors`.`salary` AS `salary` from `ancestors` /* All report to 'Bill' */ with recursive ancestors @@ -36,7 +70,7 @@ as select e.emp_id, e.name, e.mgr, e.salary from emp for system_time as of timestamp @ts_1 as e where name = 'bill' - union + union select e.emp_id, e.name, e.mgr, e.salary from emp for system_time as of timestamp @ts_1 as e, ancestors as a @@ -47,25 +81,186 @@ emp_id name mgr salary 1 bill NULL 1000 20 john 1 500 30 jane 1 750 -/* Expected 3 rows */ +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 timestamp @ts_1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 +2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where +3 RECURSIVE UNION e ALL mgr-fk NULL NULL NULL 4 100.00 Using where +3 RECURSIVE UNION <derived2> ref key0 key0 4 test.e.mgr 2 100.00 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `ancestors`.`emp_id` AS `emp_id`,`ancestors`.`name` AS `name`,`ancestors`.`mgr` AS `mgr`,`ancestors`.`salary` AS `salary` from `ancestors` with recursive ancestors as ( select e.emp_id, e.name, e.mgr, e.salary -from emp for system_time as of timestamp @ts_2 as e +from emp as e where name = 'bill' - union + union select e.emp_id, e.name, e.mgr, e.salary -from emp for system_time as of timestamp @ts_2 as e, +from emp as e, ancestors as a where e.mgr = a.emp_id ) -select * from ancestors; +select * from ancestors for system_time as of timestamp @ts_1; emp_id name mgr salary 1 bill NULL 1000 +20 john 1 500 30 jane 1 750 -20 john 30 500 +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 name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 4 100.00 +1 PRIMARY emp ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer (flat, BNL join) +4 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 4 100.00 +2 DERIVED e ALL NULL NULL NULL NULL 4 100.00 Using where +3 RECURSIVE UNION e ALL mgr-fk NULL NULL NULL 4 100.00 Using where +3 RECURSIVE UNION <derived2> ref key0 key0 4 test.e.mgr 2 100.00 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 with recursive ancestors as (/* select#2 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` where `test`.`e`.`name` = 'bill' and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1` union /* select#3 */ select `test`.`e`.`emp_id` AS `emp_id`,`test`.`e`.`name` AS `name`,`test`.`e`.`mgr` AS `mgr`,`test`.`e`.`salary` AS `salary` from `test`.`emp` FOR SYSTEM_TIME ALL `e` join `ancestors` `a` where `a`.`emp_id` = `test`.`e`.`mgr` and `test`.`e`.`row_end` > @`ts_1` and `test`.`e`.`row_start` <= @`ts_1`)/* select#1 */ select `test`.`emp`.`name` AS `name` from `test`.`emp` FOR SYSTEM_TIME ALL semi join (`ancestors`) where `test`.`emp`.`emp_id` = `ancestors`.`emp_id` and `test`.`emp`.`row_end` = TIMESTAMP'2038-01-19 04:14:07.999999' +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 name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1); +name +bill +john +jane +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_2, +ancestors for system_time as of @ts_2 a2; +emp_id name mgr salary emp_id name mgr salary +1 bill NULL 1000 1 bill NULL 1000 +30 jane 1 750 1 bill NULL 1000 +20 john 30 500 1 bill NULL 1000 +1 bill NULL 1000 30 jane 1 750 +30 jane 1 750 30 jane 1 750 +20 john 30 500 30 jane 1 750 +1 bill NULL 1000 20 john 30 500 +30 jane 1 750 20 john 30 500 +20 john 30 500 20 john 30 500 +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_2, +ancestors for system_time as of now() a2; +ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE +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, +ancestors for system_time as of @ts_2 a2; +ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE +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_2, +ancestors a2; +ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE +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_2 +where emp_id in (select * from ancestors); +ERROR HY000: Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE +# SYSTEM_TIME to internal recursive instance is prohibited +with recursive cte as +( +select * from emp +union all +select * from cte for system_time as of @ts_1 +) +select * from cte; +ERROR HY000: Table `cte` is not system-versioned 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); diff --git a/mysql-test/suite/versioning/t/cte.test b/mysql-test/suite/versioning/t/cte.test index 9df0bb3dfba..68ca53dc045 100644 --- a/mysql-test/suite/versioning/t/cte.test +++ b/mysql-test/suite/versioning/t/cte.test @@ -1,10 +1,9 @@ -- source include/have_innodb.inc set default_storage_engine=innodb; create or replace table dept ( - dept_id int(10) primary key, + dept_id int(10) primary key, name varchar(100) -) -with system versioning; +) with system versioning; create or replace table emp ( emp_id int(10) primary key, @@ -20,21 +19,29 @@ create or replace table emp ( foreign key (mgr) references emp (emp_id) on delete restrict on update restrict -) -with system versioning; +) with system versioning; insert into dept (dept_id, name) values (10, "accounting"); -insert into emp (emp_id, name, salary, dept_id, mgr) values +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 ); -select max(sys_trx_start) into @ts_1 from emp; +select row_start into @ts_1 from emp where name="jane"; update emp set mgr=30 where name ="john"; -select sys_trx_start into @ts_2 from emp where name="john"; +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; + +select row_start into @ts_2 from emp where name="john"; + +let $q= /* All report to 'Bill' */ with recursive ancestors @@ -43,7 +50,7 @@ as select e.emp_id, e.name, e.mgr, e.salary from emp for system_time as of timestamp @ts_1 as e where name = 'bill' - union + union select e.emp_id, e.name, e.mgr, e.salary from emp for system_time as of timestamp @ts_1 as e, ancestors as a @@ -51,21 +58,138 @@ as ) select * from ancestors; -/* Expected 3 rows */ +eval explain extended $q; +eval $q; + +let $q=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 timestamp @ts_1; + +eval explain extended $q; +eval $q; + +let $q=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 name from emp where emp_id in (select emp_id from ancestors for system_time as of timestamp @ts_1); + +eval explain extended $q; +eval $q; + with recursive ancestors as ( select e.emp_id, e.name, e.mgr, e.salary - from emp for system_time as of timestamp @ts_2 as e + from emp as e where name = 'bill' - union + union select e.emp_id, e.name, e.mgr, e.salary - from emp for system_time as of timestamp @ts_2 as e, + from emp as e, ancestors as a where e.mgr = a.emp_id ) -select * from ancestors; +select * from ancestors for system_time as of @ts_2, + ancestors for system_time as of @ts_2 a2; + +--error ER_AMBIGUOUS_FOR_SYSTEM_TIME +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_2, + ancestors for system_time as of now() a2; + +--error ER_AMBIGUOUS_FOR_SYSTEM_TIME +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, + ancestors for system_time as of @ts_2 a2; + +--error ER_AMBIGUOUS_FOR_SYSTEM_TIME +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_2, + ancestors a2; + +--error ER_AMBIGUOUS_FOR_SYSTEM_TIME +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_2 + where emp_id in (select * from ancestors); + +--echo # SYSTEM_TIME to internal recursive instance is prohibited +--error ER_VERS_NOT_VERSIONED +with recursive cte as +( + select * from emp + union all + select * from cte for system_time as of @ts_1 +) +select * from cte; 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; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index a5209ba5115..4b31e4b528a 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -3674,7 +3674,7 @@ int subselect_single_select_engine::prepare(THD *thd) int subselect_union_engine::prepare(THD *thd_arg) { set_thd(thd_arg); - return unit->prepare(thd, result, SELECT_NO_UNLOCK); + return unit->prepare(unit->derived, result, SELECT_NO_UNLOCK); } int subselect_uniquesubquery_engine::prepare(THD *) diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 17192663381..e9a07bc8c59 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7859,8 +7859,8 @@ ER_VERS_ALTER_ENGINE_PROHIBITED ER_VERS_RANGE_PROHIBITED eng "SYSTEM_TIME range selector is not allowed" -ER_UNUSED_26 - eng "You should never see it" +ER_CONFLICTING_FOR_SYSTEM_TIME + eng "Conflicting FOR SYSTEM_TIME clauses in WITH RECURSIVE" ER_VERS_TABLE_MUST_HAVE_COLUMNS eng "Table %`s must have at least one versioned column" diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index a58a9254a82..0cdd664d05c 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -978,7 +978,7 @@ bool With_element::prepare_unreferenced(THD *thd) thd->lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED; if (!spec->prepared && - (spec->prepare(thd, 0, 0) || + (spec->prepare(spec->derived, 0, 0) || rename_columns_of_derived_unit(thd, spec) || check_duplicate_names(thd, first_sl->item_list, 1))) rc= true; diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index ab66384c6cb..6c2242b6ced 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -747,8 +747,6 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) } } - unit->derived= derived; - /* Above cascade call of prepare is important for PS protocol, but after it is called we can check if we really need prepare for this derived @@ -766,7 +764,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_DERIVED; // st_select_lex_unit::prepare correctly work for single select - if ((res= unit->prepare(thd, derived->derived_result, 0))) + if ((res= unit->prepare(derived, derived->derived_result, 0))) goto exit; if (derived->with && (res= derived->with->rename_columns_of_derived_unit(thd, unit))) diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 02c2ffb6a12..6b66670617c 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -770,7 +770,8 @@ class st_select_lex_unit: public st_select_lex_node { bool is_excluded() { return prev == NULL; } /* UNION methods */ - bool prepare(THD *thd, select_result *result, ulong additional_options); + bool prepare(TABLE_LIST *derived_arg, select_result *sel_result, + ulong additional_options); bool optimize(); bool exec(); bool exec_recursive(); diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 24f3cc66c6b..5e46a7192d7 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -1528,7 +1528,7 @@ static int mysql_test_select(Prepared_statement *stmt, It is not SELECT COMMAND for sure, so setup_tables will be called as usual, and we pass 0 as setup_tables_done_option */ - if (unit->prepare(thd, 0, 0)) + if (unit->prepare(unit->derived, 0, 0)) goto error; if (!lex->describe && !thd->lex->analyze_stmt && !stmt->is_sql_prepare()) { @@ -1699,7 +1699,7 @@ static bool select_like_stmt_test(Prepared_statement *stmt, thd->lex->used_tables= 0; // Updated by setup_fields /* Calls JOIN::prepare */ - DBUG_RETURN(lex->unit.prepare(thd, 0, setup_tables_done_option)); + DBUG_RETURN(lex->unit.prepare(lex->unit.derived, 0, setup_tables_done_option)); } /** diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b8fd6bd3da9..45850328f14 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -25635,7 +25635,8 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result) unit->fake_select_lex->type= unit_operation_text[unit->common_op()]; unit->fake_select_lex->options|= SELECT_DESCRIBE; } - if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE))) + if (!(res= unit->prepare(unit->derived, result, + SELECT_NO_UNLOCK | SELECT_DESCRIBE))) res= unit->exec(); } else diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 446e33366f3..d0922347b77 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -36,7 +36,7 @@ bool mysql_union(THD *thd, LEX *lex, select_result *result, { DBUG_ENTER("mysql_union"); bool res; - if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK | + if (!(res= unit->prepare(unit->derived, result, SELECT_NO_UNLOCK | setup_tables_done_option))) res= unit->exec(); res|= unit->cleanup(); @@ -810,10 +810,11 @@ bool st_select_lex_unit::join_union_item_types(THD *thd_arg, } -bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, +bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, + select_result *sel_result, ulong additional_options) { - SELECT_LEX *lex_select_save= thd_arg->lex->current_select; + SELECT_LEX *lex_select_save= thd->lex->current_select; SELECT_LEX *sl, *first_sl= first_select(); bool is_recursive= with_element && with_element->is_recursive; bool is_rec_result_table_created= false; @@ -824,7 +825,6 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, bool instantiate_tmp_table= false; bool single_tvc= !first_sl->next_select() && first_sl->tvc; DBUG_ENTER("st_select_lex_unit::prepare"); - DBUG_ASSERT(thd == thd_arg); DBUG_ASSERT(thd == current_thd); describe= additional_options & SELECT_DESCRIBE; @@ -876,7 +876,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, prepared= 1; saved_error= FALSE; - thd_arg->lex->current_select= sl= first_sl; + thd->lex->current_select= sl= first_sl; found_rows_for_union= first_sl->options & OPTION_FOUND_ROWS; is_union_select= is_unit_op() || fake_select_lex || single_tvc; @@ -905,7 +905,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, while (last->next_select()) last= last->next_select(); if (!(tmp_result= union_result= - new (thd_arg->mem_root) select_union_direct(thd_arg, sel_result, + new (thd->mem_root) select_union_direct(thd, sel_result, last))) goto err; /* purecov: inspected */ fake_select_lex= NULL; @@ -914,11 +914,11 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, else { if (!is_recursive) - union_result= new (thd_arg->mem_root) select_unit(thd_arg); + union_result= new (thd->mem_root) select_unit(thd); else { with_element->rec_result= - new (thd_arg->mem_root) select_union_recursive(thd_arg); + new (thd->mem_root) select_union_recursive(thd); union_result= with_element->rec_result; fake_select_lex= NULL; } @@ -936,10 +936,10 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, { if (sl->tvc) { - if (sl->tvc->prepare(thd_arg, sl, tmp_result, this)) + if (sl->tvc->prepare(thd, sl, tmp_result, this)) goto err; } - else if (prepare_join(thd_arg, first_sl, tmp_result, additional_options, + else if (prepare_join(thd, first_sl, tmp_result, additional_options, is_union_select)) goto err; types= first_sl->item_list; @@ -950,10 +950,10 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, { if (sl->tvc) { - if (sl->tvc->prepare(thd_arg, sl, tmp_result, this)) + if (sl->tvc->prepare(thd, sl, tmp_result, this)) goto err; } - else if (prepare_join(thd_arg, sl, tmp_result, additional_options, + else if (prepare_join(thd, sl, tmp_result, additional_options, is_union_select)) goto err; @@ -973,7 +973,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, { if (with_element) { - if (derived->with->rename_columns_of_derived_unit(thd, this)) + if (derived_arg->with->rename_columns_of_derived_unit(thd, this)) goto err; if (check_duplicate_names(thd, sl->item_list, 0)) goto err; @@ -984,7 +984,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, if (first_sl->item_list.elements != sl->item_list.elements) { my_message(ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT, - ER_THD(thd_arg, ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT), + ER_THD(thd, ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT), MYF(0)); goto err; } @@ -993,25 +993,25 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, { if (!with_element->is_anchor(sl)) sl->uncacheable|= UNCACHEABLE_UNITED; - if(!is_rec_result_table_created && - (!sl->next_select() || - sl->next_select() == with_element->first_recursive)) + if (!is_rec_result_table_created && + (!sl->next_select() || + sl->next_select() == with_element->first_recursive)) { ulonglong create_options; - create_options= (first_sl->options | thd_arg->variables.option_bits | + create_options= (first_sl->options | thd->variables.option_bits | TMP_TABLE_ALL_COLUMNS); // Join data types for all non-recursive parts of a recursive UNION if (join_union_item_types(thd, types, union_part_count + 1)) goto err; if (union_result->create_result_table(thd, &types, MY_TEST(union_distinct), - create_options, &derived->alias, - false, + create_options, + &derived_arg->alias, false, instantiate_tmp_table, false, 0)) goto err; - if (!derived->table) - derived->table= derived->derived_result->table= + if (!derived_arg->table) + derived_arg->table= derived_arg->derived_result->table= with_element->rec_result->rec_tables.head(); with_element->mark_as_with_prepared_anchor(); is_rec_result_table_created= true; @@ -1082,7 +1082,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, } - create_options= (first_sl->options | thd_arg->variables.option_bits | + create_options= (first_sl->options | thd->variables.option_bits | TMP_TABLE_ALL_COLUMNS); /* Force the temporary table to be a MyISAM table if we're going to use @@ -1110,7 +1110,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, Query_arena *arena, backup_arena; arena= thd->activate_stmt_arena_if_needed(&backup_arena); - intersect_mark= new (thd_arg->mem_root) Item_int(thd, 0); + intersect_mark= new (thd->mem_root) Item_int(thd, 0); if (arena) thd->restore_active_arena(arena, &backup_arena); @@ -1154,7 +1154,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, result_table_list.maybe_null_exec= save_maybe_null; } - thd_arg->lex->current_select= lex_select_save; + thd->lex->current_select= lex_select_save; if (!item_list.elements) { Query_arena *arena, backup_arena; @@ -1194,7 +1194,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, */ fake_select_lex->item_list= item_list; - thd_arg->lex->current_select= fake_select_lex; + thd->lex->current_select= fake_select_lex; /* We need to add up n_sum_items in order to make the correct @@ -1222,12 +1222,12 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result, } } - thd_arg->lex->current_select= lex_select_save; + thd->lex->current_select= lex_select_save; - DBUG_RETURN(saved_error || thd_arg->is_fatal_error); + DBUG_RETURN(saved_error || thd->is_fatal_error); err: - thd_arg->lex->current_select= lex_select_save; + thd->lex->current_select= lex_select_save; (void) cleanup(); DBUG_RETURN(TRUE); } diff --git a/sql/sql_view.cc b/sql/sql_view.cc index e910d48c75c..b5700fa5ee1 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -530,7 +530,7 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views, /* prepare select to resolve all fields */ lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_VIEW; - if (unit->prepare(thd, 0, 0)) + if (unit->prepare(unit->derived, 0, 0)) { /* some errors from prepare are reported to user, if is not then diff --git a/sql/table.cc b/sql/table.cc index 577ed20a87e..75c7d69cfa7 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -8114,7 +8114,21 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view) (first_table && first_table->is_multitable())) set_multitable(); - unit->derived= this; + if (!unit->derived) + unit->derived= this; + else if (!is_with_table_recursive_reference()) + { + if (unit->derived->is_with_table_recursive_reference()) + unit->derived= this; + else if (vers_conditions.eq(unit->derived->vers_conditions)) + vers_conditions.empty(); + else + { + my_error(ER_CONFLICTING_FOR_SYSTEM_TIME, MYF(0)); + return TRUE; + } + } + if (init_view && !view) { /* This is all what we can do for a derived table for now. */ @@ -8864,6 +8878,26 @@ void vers_select_conds_t::resolve_units(bool timestamps_only) end.resolve_unit(timestamps_only); } +bool vers_select_conds_t::eq(const vers_select_conds_t &conds) +{ + if (type != conds.type) + return false; + switch (type) { + case SYSTEM_TIME_UNSPECIFIED: + case SYSTEM_TIME_ALL: + return true; + case SYSTEM_TIME_BEFORE: + DBUG_ASSERT(0); + case SYSTEM_TIME_AS_OF: + return start.eq(conds.start); + case SYSTEM_TIME_FROM_TO: + case SYSTEM_TIME_BETWEEN: + return start.eq(conds.start) && end.eq(conds.end); + } + DBUG_ASSERT(0); + return false; +} + void Vers_history_point::resolve_unit(bool timestamps_only) { if (item && unit == VERS_UNDEFINED) @@ -8885,6 +8919,12 @@ void Vers_history_point::fix_item() item->decimals= 6; } + +bool Vers_history_point::eq(const vers_history_point_t &point) +{ + return unit == point.unit && item->eq(point.item, false); +} + void Vers_history_point::print(String *str, enum_query_type query_type, const char *prefix, size_t plen) { diff --git a/sql/table.h b/sql/table.h index ac382e5aa94..8f8929de066 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1827,6 +1827,7 @@ class Vers_history_point : public vers_history_point_t void empty() { unit= VERS_UNDEFINED; item= NULL; } void print(String *str, enum_query_type, const char *prefix, size_t plen); void resolve_unit(bool timestamps_only); + bool eq(const vers_history_point_t &point); }; struct vers_select_conds_t @@ -1876,6 +1877,7 @@ struct vers_select_conds_t { return !from_query && type != SYSTEM_TIME_UNSPECIFIED; } + bool eq(const vers_select_conds_t &conds); }; /*