---
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@xxxxxxxxxxx
https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
