January 2018 - developers - lists.mariadb.org

[Maria-developers] subscribe
by Martijn Tonies (Upscene Productions) 09 Jan '18

09 Jan '18

subscribe With regards, Martijn Tonies Upscene Productions http://www.upscene.com Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL, SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

1 0

Re: [Maria-developers] [Commits] fbe0fc2: Fixed mdev-14879 Lost rows for query using recursive CTE
by Oleksandr Byelkin 09 Jan '18

09 Jan '18

Hi, Igor! Am 06.01.2018 um 18:32 schrieb IgorBabaev: > revision-id: fbe0fc2d87cb1bdb9f7040d44ad3c58a94857e20 (mariadb-10.2.12-14-gfbe0fc2) > parent(s): 15b1840f43be8c660382458826e9d5a47d148a67 > author: Igor Babaev > committer: Igor Babaev > timestamp: 2018-01-06 09:32:47 -0800 > message: > > Fixed mdev-14879 Lost rows for query using recursive CTE > with recursive reference in subquery > > If a recursive CTE uses a subquery with recursive reference then > the virtual function reset() must be called after each iteration > performed at the execution of the CTE. OK to push. [skip]

1 0

Re: [Maria-developers] [Commits] 81965f0: Fixed mdev-14852 Fails to reopen temp table within standard CTE
by Oleksandr Byelkin 05 Jan '18

05 Jan '18

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(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0