revision-id: 058554027f6696775ca1b289688956606f59ce7d (mariadb-10.2.16-21-g0585540) parent(s): 400cf017152c732387c89deaa082b43c8fb42d71 author: Igor Babaev committer: Igor Babaev timestamp: 2018-07-05 00:06:39 -0700 message: MDEV-16629 "Table Does Not Exist" Error from Recursive CTE Query Inside Function When processing a query containing with clauses a call of the function check_dependencies_in_with_clauses() before opening tables used in the query is necessary if with clauses include specifications of recursive CTEs. This call was missing if such a query belonged to a stored function. This caused misbehavior of the server: it could report a fake error as in the test case for MDEV-16629 or the executed query could hang as in the test cases for MDEV-16661 and MDEV-15151. --- mysql-test/r/cte_recursive.result | 58 +++++++++++++++++++++++++++++++ mysql-test/t/cte_recursive.test | 72 +++++++++++++++++++++++++++++++++++++++ sql/sp_head.cc | 4 ++- 3 files changed, 133 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 2e93e9f..856f00a 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -3263,3 +3263,61 @@ select 3, 0*(@d:=@d+1) from qn where @d<1 select * from qn; ERROR 42000: This version of MariaDB doesn't yet support 'mix of ALL and DISTINCT UNION operations in recursive CTE spec' drop table t1; +# +# MDEV-16629: function with recursive CTE using a base table +# +CREATE TABLE t1 (id int); +INSERT INTO t1 VALUES (0), (1),(2); +WITH recursive cte AS +(SELECT id FROM t1 UNION SELECT 3 FROM cte) +SELECT count(id) FROM cte; +count(id) +4 +CREATE OR REPLACE FUNCTION func() RETURNS int +RETURN +( +WITH recursive cte AS +(SELECT id FROM t1 UNION SELECT 3 FROM cte) +SELECT count(id) FROM cte +); +SELECT func(); +func() +4 +DROP FUNCTION func; +DROP TABLE t1; +# +# MDEV-16661: function with recursive CTE using no base tables +# (fixed by the patch for MDEV-16629) +# +CREATE OR REPLACE FUNCTION func() RETURNS int +RETURN +( +WITH RECURSIVE cte AS +(SELECT 1 as id UNION SELECT * FROM cte) +SELECT count(id) FROM cte +); +SELECT func(); +func() +1 +DROP FUNCTION func; +# +# MDEV-15151: function with recursive CTE using no base tables +# (duplicate of MDEV-16661) +# +connection default; +CREATE TABLE t1 (id int KEY); +INSERT INTO t1 VALUES (0), (1),(2); +CREATE OR REPLACE FUNCTION func() RETURNS int +RETURN +( +WITH recursive cte AS +(SELECT 1 a UNION SELECT cte.* FROM cte natural join t1) +SELECT * FROM cte limit 1 +); +SELECT func();; +connect con1,localhost,root,,; +KILL QUERY 4; +DROP FUNCTION func; +DROP TABLE t1; +disconnect con1; +connection default; diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 32a82c4..504b2c6 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2282,3 +2282,75 @@ select 3, 0*(@d:=@d+1) from qn where @d<1 select * from qn; drop table t1; + +--echo # +--echo # MDEV-16629: function with recursive CTE using a base table +--echo # + +CREATE TABLE t1 (id int); +INSERT INTO t1 VALUES (0), (1),(2); + +WITH recursive cte AS +(SELECT id FROM t1 UNION SELECT 3 FROM cte) +SELECT count(id) FROM cte; + +CREATE OR REPLACE FUNCTION func() RETURNS int +RETURN +( + WITH recursive cte AS + (SELECT id FROM t1 UNION SELECT 3 FROM cte) + SELECT count(id) FROM cte +); + +SELECT func(); + +DROP FUNCTION func; +DROP TABLE t1; + +--echo # +--echo # MDEV-16661: function with recursive CTE using no base tables +--echo # (fixed by the patch for MDEV-16629) +--echo # + +CREATE OR REPLACE FUNCTION func() RETURNS int +RETURN +( + WITH RECURSIVE cte AS + (SELECT 1 as id UNION SELECT * FROM cte) + SELECT count(id) FROM cte +); + +SELECT func(); + +DROP FUNCTION func; + +--echo # +--echo # MDEV-15151: function with recursive CTE using no base tables +--echo # (duplicate of MDEV-16661) +--echo # + +--connection default + +CREATE TABLE t1 (id int KEY); +INSERT INTO t1 VALUES (0), (1),(2); + +CREATE OR REPLACE FUNCTION func() RETURNS int +RETURN +( + WITH recursive cte AS + (SELECT 1 a UNION SELECT cte.* FROM cte natural join t1) + SELECT * FROM cte limit 1 +); + +--let $conid= `SELECT CONNECTION_ID()` +--send SELECT func(); + +--connect (con1,localhost,root,,) +--eval KILL QUERY $conid +--source include/restart_mysqld.inc + +DROP FUNCTION func; +DROP TABLE t1; +--disconnect con1 + +--connection default diff --git a/sql/sp_head.cc b/sql/sp_head.cc index a832aa9..effe9d1 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -29,6 +29,7 @@ #include "sql_array.h" // Dynamic_array #include "log_event.h" // Query_log_event #include "sql_derived.h" // mysql_handle_derived +#include "sql_cte.h" #ifdef USE_PRAGMA_IMPLEMENTATION #pragma implementation @@ -3000,7 +3001,8 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp, #endif if (open_tables) - res= instr->exec_open_and_lock_tables(thd, m_lex->query_tables); + res= check_dependencies_in_with_clauses(m_lex->with_clauses_list) || + instr->exec_open_and_lock_tables(thd, m_lex->query_tables); if (!res) {