Am 05.01.2018 um 08:40 schrieb IgorBabaev:

revision-id: 81965f03065b84322f2d03d784893179e8b79fd9 (mariadb-10.2.12-6-g81965f0) parent(s): 0f253d3e644d3027face26371eb1bb59e8280728 author: Igor Babaev committer: Igor Babaev timestamp: 2018-01-04 23:40:37 -0800 message:

Fixed mdev-14852 Fails to reopen temp table within standard CTE

If the specification of a CTE contains a reference to a temporary table then THD::open_temporary_table() must be called for this reference for any occurrence of the CTE in the query. By mistake this was done only for the first occurrences of CTEs. The patch fixes this problem in With_element::clone_parsed_spec(). It also moves there the call of check_dependencies_in_with_clauses() to its proper place before the call of check_table_access(). Additionally the patch optimizes the number of calls of the function check_dependencies_in_with_clauses().

OK to push!

--- mysql-test/r/cte_nonrecursive.result | 30 ++++++++++++++++++++++++++++++ mysql-test/t/cte_nonrecursive.test | 29 +++++++++++++++++++++++++++++ sql/sql_cte.cc | 7 +++++++ sql/sql_parse.cc | 22 +++++----------------- sql/sql_prepare.cc | 11 +++-------- 5 files changed, 74 insertions(+), 25 deletions(-)

diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index b1b41b1..c1f4c9f 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -1265,3 +1265,33 @@ a 4 deallocate prepare stmt; drop table t1; +# +# MDEV-14852: CTE using temporary table in query +# with two references to the CTE +# +create temporary table t1 (i int); +insert into t1 values (5),(4),(1),(2),(3); +with +c1 as (select i from t1), +c2 as (select i from c1 where c1.i=2) +select i from c1 where i > 3 union select i from c2; +i +5 +4 +2 +drop table t1; +create table t1 (term char(10)); +create temporary table t2 (term char(10)); +insert into t1 values ('TERM01'),('TERM02'),('TERM03'); +insert into t2 values ('TERM02'),('TERM03'),('TERM04'); +with c1 as (select * from t1), c2 as (select * from t2) +(select * from c1 left outer join c2 on c1.term = c2.term) +union all +(select * from c1 right outer join c2 on c1.term = c2.term +where c1.term is null); +term term +TERM02 TERM02 +TERM03 TERM03 +TERM01 NULL +NULL TERM04 +drop table t1,t2; diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index 1f21dbc..9436665 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -853,3 +853,32 @@ execute stmt; deallocate prepare stmt;

drop table t1; + +--echo # +--echo # MDEV-14852: CTE using temporary table in query +--echo # with two references to the CTE +--echo # + +create temporary table t1 (i int); +insert into t1 values (5),(4),(1),(2),(3); + +with +c1 as (select i from t1), +c2 as (select i from c1 where c1.i=2) +select i from c1 where i > 3 union select i from c2; + +drop table t1; + +create table t1 (term char(10)); +create temporary table t2 (term char(10)); + +insert into t1 values ('TERM01'),('TERM02'),('TERM03'); +insert into t2 values ('TERM02'),('TERM03'),('TERM04'); + +with c1 as (select * from t1), c2 as (select * from t2) +(select * from c1 left outer join c2 on c1.term = c2.term) +union all +(select * from c1 right outer join c2 on c1.term = c2.term + where c1.term is null); + +drop table t1,t2; diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 601c192..d12948f 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -817,12 +817,19 @@ st_select_lex_unit *With_element::clone_parsed_spec(THD *thd, parse_status= parse_sql(thd, &parser_state, 0); if (parse_status) goto err; + + if (check_dependencies_in_with_clauses(lex->with_clauses_list)) + goto err; + spec_tables= lex->query_tables; spec_tables_tail= 0; for (TABLE_LIST *tbl= spec_tables; tbl; tbl= tbl->next_global) { + if (!tbl->derived && !tbl->schema_table && + thd->open_temporary_table(tbl)) + goto err; spec_tables_tail= tbl; } if (check_table_access(thd, SELECT_ACL, spec_tables, FALSE, UINT_MAX, FALSE)) diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index f00c74b..6d068eb 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2990,6 +2990,9 @@ mysql_execute_command(THD *thd) thd->get_stmt_da()->opt_clear_warning_info(thd->query_id); }

+ if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) + DBUG_RETURN(1); + #ifdef HAVE_REPLICATION if (unlikely(thd->slave_thread)) { @@ -3446,14 +3449,6 @@ mysql_execute_command(THD *thd) ulong privileges_requested= lex->exchange ? SELECT_ACL | FILE_ACL : SELECT_ACL;

- /* - The same function must be called for DML commands - when CTEs are supported in DML statements - */ - res= check_dependencies_in_with_clauses(thd->lex->with_clauses_list); - if (res) - break; - if (all_tables) res= check_table_access(thd, privileges_requested, @@ -3879,8 +3874,7 @@ mysql_execute_command(THD *thd) /* Copy temporarily the statement flags to thd for lock_table_names() */ uint save_thd_create_info_options= thd->lex->create_info.options; thd->lex->create_info.options|= create_info.options; - if (!(res= check_dependencies_in_with_clauses(lex->with_clauses_list))) - res= open_and_lock_tables(thd, create_info, lex->query_tables, TRUE, 0); + res= open_and_lock_tables(thd, create_info, lex->query_tables, TRUE, 0); thd->lex->create_info.options= save_thd_create_info_options; if (res) { @@ -4493,8 +4487,7 @@ mysql_execute_command(THD *thd)

unit->set_limit(select_lex);

- if (!(res= check_dependencies_in_with_clauses(lex->with_clauses_list)) && - !(res=open_and_lock_tables(thd, all_tables, TRUE, 0))) + if (!(res=open_and_lock_tables(thd, all_tables, TRUE, 0))) { MYSQL_INSERT_SELECT_START(thd->query()); /* @@ -4821,9 +4814,6 @@ mysql_execute_command(THD *thd) { List<set_var_base> *lex_var_list= &lex->var_list;

- if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) - goto error; - if ((check_table_access(thd, SELECT_ACL, all_tables, FALSE, UINT_MAX, FALSE) || open_and_lock_tables(thd, all_tables, TRUE, 0))) goto error; @@ -6376,8 +6366,6 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables) new (thd->mem_root) Item_int(thd, (ulonglong) thd->variables.select_limit); } - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - return 1;

if (!(res= open_and_lock_tables(thd, all_tables, TRUE, 0))) { diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index be78a76..390b708 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -1573,8 +1573,6 @@ static int mysql_test_select(Prepared_statement *stmt, lex->select_lex.context.resolve_in_select_list= TRUE;

ulong privilege= lex->exchange ? SELECT_ACL | FILE_ACL : SELECT_ACL; - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - goto error; if (tables) { if (check_table_access(thd, privilege, tables, FALSE, UINT_MAX, FALSE)) @@ -1841,9 +1839,6 @@ static bool mysql_test_create_table(Prepared_statement *stmt) if (create_table_precheck(thd, tables, create_table)) DBUG_RETURN(TRUE);

- if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - DBUG_RETURN(TRUE); - if (select_lex->item_list.elements) { /* Base table and temporary table are not in the same name space. */ @@ -2234,9 +2229,6 @@ static bool mysql_test_insert_select(Prepared_statement *stmt, if (insert_precheck(stmt->thd, tables)) return 1;

- if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - return 1; - /* store it, because mysql_insert_select_prepare_tester change it */ first_local_table= lex->select_lex.table_list.first; DBUG_ASSERT(first_local_table != 0); @@ -2339,6 +2331,9 @@ static bool check_prepared_statement(Prepared_statement *stmt) if (tables) thd->get_stmt_da()->opt_clear_warning_info(thd->query_id);

+ if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) + goto error; + if (sql_command_flags[sql_command] & CF_HA_CLOSE) mysql_ha_rm_tables(thd, tables);

_______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits