- commits - lists.mariadb.org

[Commits] d5a82208f61: MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' failed in setup_jtbm_semi_joins
by varun 30 Dec '20

30 Dec '20

revision-id: d5a82208f61f2f476c2667c77c9cbb74d137374b (mariadb-10.2.31-642-gd5a82208f61) parent(s): 8d8370e31d48e0bc6139c18770746f9959c21598 author: Varun Gupta committer: Varun Gupta timestamp: 2020-12-24 19:19:43 +0530 message: MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' failed in setup_jtbm_semi_joins Currently window functions do not work with IN/ALL/ANY/SOME subqueries, so throw an error when such a query is encountered --- mysql-test/r/win.result | 28 ++++++++++++++++++++++++---- mysql-test/t/win.test | 24 ++++++++++++++++++++++++ sql/item_subselect.cc | 3 +++ sql/opt_subselect.cc | 8 ++++++++ 4 files changed, 59 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 3023a86eaad..1b4b7435351 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -2391,8 +2391,7 @@ COUNT(*) OVER (PARTITION BY c) 2 2 SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 ); -i -1 +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' DROP TABLE t1, t2; # # MDEV-9976: window function without PARTITION BY and ORDER BY @@ -3153,8 +3152,7 @@ DROP TABLE t1; CREATE TABLE t1 (c CHAR(8)) ENGINE=MyISAM; INSERT IGNORE INTO t1 VALUES ('foo'); SELECT ('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1); -('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1) -0 +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' DROP TABLE t1; # # MDEV-13351: Server crashes in st_select_lex::set_explain_type upon UNION with window function @@ -3866,5 +3864,27 @@ NULL DROP VIEW v1; DROP TABLE t1,t2; # +# MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' +# failed in setup_jtbm_semi_joins +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1 WHERE t1.a IN (SELECT COUNT(t2.a) over () from t1 t2); +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' +SELECT * FROM t1 WHERE t1.a > ALL (SELECT COUNT(t2.a) over () from t1 t2); +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' +SELECT * FROM t1 WHERE t1.a > ANY (SELECT COUNT(t2.a) over () from t1 t2); +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' +SELECT * FROM t1 WHERE EXISTS (SELECT row_number() OVER () FROM t1 A WHERE t1.a= A.a); +a b +1 1 +2 2 +SELECT * FROM t1 WHERE a IN (SELECT 1 FROM t1 t2 ORDER BY sum(t2.b) over ()); +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' +select a from t1 where a in (select row_number() over (order by a) from t1 A +union select row_number() over (order by a) from t1 B) ; +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' +DROP TABLE t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index c7e3dac598b..32eed85224b 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -1440,6 +1440,7 @@ INSERT INTO t2 VALUES ('foo'),('bar'),('foo'); SELECT COUNT(*) OVER (PARTITION BY c) FROM t2; +--error ER_NOT_SUPPORTED_YET SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 ); DROP TABLE t1, t2; @@ -1941,6 +1942,7 @@ DROP TABLE t1; --echo # CREATE TABLE t1 (c CHAR(8)) ENGINE=MyISAM; INSERT IGNORE INTO t1 VALUES ('foo'); +--error ER_NOT_SUPPORTED_YET SELECT ('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1); DROP TABLE t1; @@ -2522,6 +2524,28 @@ SELECT NTH_VALUE(i1, i1) OVER (PARTITION BY i1) FROM v1; DROP VIEW v1; DROP TABLE t1,t2; +--echo # +--echo # MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' +--echo # failed in setup_jtbm_semi_joins +--echo # + +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +--error ER_NOT_SUPPORTED_YET +SELECT * FROM t1 WHERE t1.a IN (SELECT COUNT(t2.a) over () from t1 t2); +--error ER_NOT_SUPPORTED_YET +SELECT * FROM t1 WHERE t1.a > ALL (SELECT COUNT(t2.a) over () from t1 t2); +--error ER_NOT_SUPPORTED_YET +SELECT * FROM t1 WHERE t1.a > ANY (SELECT COUNT(t2.a) over () from t1 t2); +SELECT * FROM t1 WHERE EXISTS (SELECT row_number() OVER () FROM t1 A WHERE t1.a= A.a); +--error ER_NOT_SUPPORTED_YET +SELECT * FROM t1 WHERE a IN (SELECT 1 FROM t1 t2 ORDER BY sum(t2.b) over ()); +--error ER_NOT_SUPPORTED_YET +select a from t1 where a in (select row_number() over (order by a) from t1 A + union select row_number() over (order by a) from t1 B) ; + +DROP TABLE t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 802bfca64b7..5b7b24d9e21 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1951,6 +1951,9 @@ bool Item_allany_subselect::transform_into_max_min(JOIN *join) */ DBUG_ASSERT(!substitution); + if (select_lex->have_window_funcs()) + DBUG_RETURN(false); + /* Check if optimization with aggregate min/max possible 1 There is no aggregate in the subquery diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index fec4e8b2828..3fdba7f4520 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5940,6 +5940,14 @@ bool JOIN::choose_subquery_plan(table_map join_tables) in_subs->set_strategy(SUBS_IN_TO_EXISTS); } + if (select_lex->have_window_funcs()) + { + my_error(ER_NOT_SUPPORTED_YET, MYF(0), + "the combination of this ALL/ANY/SOME/IN subquery with this" + " comparison operator and with contained window functions"); + return true; + } + if (in_subs->test_strategy(SUBS_MATERIALIZATION)) { /* Restore the original query plan used for materialization. */

1 0

[Commits] 9dba597: MDEV-23886 Reusing CTE inside a function fails with table doesn't exist
by IgorBabaev 29 Dec '20

29 Dec '20

revision-id: 9dba597580096b2e8141f2dd201e394b597e5eab (mariadb-10.4.11-507-g9dba597) parent(s): 478b83032b170b2ae030fa77fe4bed60a7910472 author: Igor Babaev committer: Igor Babaev timestamp: 2020-12-29 13:57:01 -0800 message: MDEV-23886 Reusing CTE inside a function fails with table doesn't exist Intermediate commit (not to be pushed). --- mysql-test/main/brackets.result | 2 +- mysql-test/main/cte_nonrecursive.result | 156 +++++++++++++ mysql-test/main/cte_nonrecursive.test | 163 ++++++++++++++ mysql-test/main/cte_recursive.result | 4 +- sql/item_subselect.cc | 1 - sql/sp_head.cc | 3 +- sql/sql_base.cc | 33 +-- sql/sql_class.cc | 56 +++++ sql/sql_class.h | 8 +- sql/sql_cte.cc | 382 +++++++++++++++++++++----------- sql/sql_cte.h | 87 +++++++- sql/sql_derived.cc | 2 + sql/sql_lex.cc | 23 +- sql/sql_lex.h | 25 ++- sql/sql_parse.cc | 15 +- sql/sql_prepare.cc | 3 - sql/sql_view.cc | 9 - sql/sql_yacc.yy | 27 ++- sql/sql_yacc_ora.yy | 26 ++- sql/table.cc | 1 + sql/table.h | 37 ++++ 21 files changed, 842 insertions(+), 221 deletions(-) diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result index 99e84f9..1da5168 100644 --- a/mysql-test/main/brackets.result +++ b/mysql-test/main/brackets.result @@ -4508,7 +4508,7 @@ s as (select * from t where a > 3) select a from t where a=1 union select a from s where a=7 order by a desc; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `t`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) `t` where `t`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `t`.`a` AS `a` from `t` where `t`.`a` > 3)select `t`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 1 diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index ee2320c..b8e885e 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -1846,4 +1846,160 @@ set sql_mode="oracle"; with data as (select 1 as id) select id into @myid from data; set sql_mode= @save_sql_mode; +# +# MDEV-23886: Stored Function returning the result of a query +# that uses CTE over a table twice +# +create table t1 (c1 int); +insert into t1 values (1),(2),(6); +create function f1() returns int return +( with cte1 as (select c1 from t1) +select sum(c1) from +(select * from cte1 union all select * from cte1) dt +); +select f1(); +f1() +18 +create function f2() returns int return +( with cte1 as (select c1 from t1) +select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1 +); +select f2(); +f2() +9 +create function f3() returns int return +( with cte1 as (select c1 from t1) +select +case +when exists(select 1 from cte1 where c1 between 1 and 2) then 1 +when exists(select 1 from cte1 where c1 between 5 and 6) then 2 +else 0 +end +); +select f3(); +f3() +1 +drop function f1; +drop function f2; +drop function f3; +create table t2 (a int, b int); +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5; +select * from t2; +a b +6 6 +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | +call p1(); +select * from t2; +a b +6 6 +2 2 +drop procedure p1; +# checking CTE resolution for queries with hanging CTEs +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from cte3; +a b +1 2 +select * from t2; +a b +6 6 +2 2 +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from t2; +a b +6 6 +2 2 +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where c1 >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from t2; +ERROR 42S22: Unknown column 'c1' in 'where clause' +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.c1) +select * from t2; +ERROR 42S22: Unknown column 'cte2.c1' in 'where clause' +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select * from cte2; +a b +1 1 +2 2 +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select * from t2; +a b +6 6 +2 2 +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=c1) +select * from t2; +ERROR 23000: Column 'c1' in where clause is ambiguous +with cte3 as +( with cte2(a,b) as +( with cte1 as (select * from t1 where c1 <= 2) +select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select r1.a,r2.b from cte2 as r1, cte2 as r2) +select * from cte3; +a b +1 1 +2 1 +1 2 +2 2 +with cte3 as +( with cte2(a,b) as +( with cte1 as (select * from t1 where c1 <= 2) +select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select r1.a,r2.b from cte2 as r1, cte2 as r2) +select * from t2; +a b +6 6 +2 2 +with cte3 as +( with cte2(a,b) as +( with cte1 as (select * from t1 where c1 <= 2) +select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select r1.c1,r2.c1 from cte2 as r1, cte2 as r2) +select * from t2; +ERROR 42S22: Unknown column 'r1.c1' in 'field list' +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | +call p1(); +select * from t2; +a b +6 6 +2 2 +2 2 +drop procedure p1; +create procedure p1() +begin +insert into t2 +with cte1 as (select a from t1) +select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | +call p1(); +ERROR 42S22: Unknown column 'a' in 'field list' +drop procedure p1; +drop table t1,t2; # End of 10.4 tests diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test index 2242dce..2dd4857 100644 --- a/mysql-test/main/cte_nonrecursive.test +++ b/mysql-test/main/cte_nonrecursive.test @@ -1347,4 +1347,167 @@ with data as (select 1 as id) select id into @myid from data; set sql_mode= @save_sql_mode; +--echo # +--echo # MDEV-23886: Stored Function returning the result of a query +--echo # that uses CTE over a table twice +--echo # + +create table t1 (c1 int); +insert into t1 values (1),(2),(6); + +create function f1() returns int return +( with cte1 as (select c1 from t1) + select sum(c1) from + (select * from cte1 union all select * from cte1) dt +); +select f1(); + +create function f2() returns int return +( with cte1 as (select c1 from t1) + select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1 +); +select f2(); + +create function f3() returns int return +( with cte1 as (select c1 from t1) + select + case + when exists(select 1 from cte1 where c1 between 1 and 2) then 1 + when exists(select 1 from cte1 where c1 between 5 and 6) then 2 + else 0 + end +); +select f3(); + +drop function f1; +drop function f2; +drop function f3; + +create table t2 (a int, b int); + +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5; + +select * from t2; + +delimiter |; + +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | + +delimiter ;| + +call p1(); +select * from t2; + +drop procedure p1; + +--echo # checking CTE resolution for queries with hanging CTEs + +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from cte3; + +select * from t2; + +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from t2; + +--error ER_BAD_FIELD_ERROR +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where c1 >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.b) +select * from t2; + +--error ER_BAD_FIELD_ERROR +with +cte1(a) as (select * from t1 where c1 <= 2), +cte2(b) as (select * from cte1 where a >= 2), +cte3 as (select * from cte1,cte2 where cte1.a < cte2.c1) +select * from t2; + +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select * from cte2; + +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) +select * from t2; + +--error ER_NON_UNIQ_ERROR +with +cte1 as (select * from t1 where c1 <= 2), +cte2(a,b) as (select * from cte1 as s1, cte1 as s2 where s1.c1=c1) +select * from t2; + +with cte3 as +( with cte2(a,b) as + ( with cte1 as (select * from t1 where c1 <= 2) + select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) + select r1.a,r2.b from cte2 as r1, cte2 as r2) +select * from cte3; + +with cte3 as +( with cte2(a,b) as + ( with cte1 as (select * from t1 where c1 <= 2) + select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) + select r1.a,r2.b from cte2 as r1, cte2 as r2) +select * from t2; + +--error ER_BAD_FIELD_ERROR +with cte3 as +( with cte2(a,b) as + ( with cte1 as (select * from t1 where c1 <= 2) + select * from cte1 as s1, cte1 as s2 where s1.c1=s2.c1) + select r1.c1,r2.c1 from cte2 as r1, cte2 as r2) +select * from t2; + +delimiter |; + +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | + +delimiter ;| + +call p1(); +select * from t2; + +drop procedure p1; + +delimiter |; + +create procedure p1() +begin +insert into t2 +with cte1 as (select a from t1) +select * from t1 as s, t1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | + +delimiter ;| + +--error ER_BAD_FIELD_ERROR +call p1(); + +drop procedure p1; + +drop table t1,t2; + --echo # End of 10.4 tests + diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 370da62..6f30de3 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -1301,7 +1301,7 @@ select ancestors.name, ancestors.dob from ancestors; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where -6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 +6 UNION <derived3> ALL NULL NULL NULL NULL 12 5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL 3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where @@ -4029,7 +4029,7 @@ id select_type table type possible_keys key key_len ref rows Extra 3 RECURSIVE UNION t1 ALL NULL NULL NULL NULL 4 Using where 3 RECURSIVE UNION <derived2> ref key0 key0 9 test.t1.c 2 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL -4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 +4 UNION <derived2> ALL NULL NULL NULL NULL 4 with recursive r_cte as ( select * from t1 as s union diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index e205847..f6768d0 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1723,7 +1723,6 @@ double Item_in_subselect::val_real() As far as Item_in_subselect called only from Item_in_optimizer this method should not be used */ - DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); if (forced_const) return value; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index aa4f809..3ea4938 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -3411,8 +3411,7 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp, Json_writer_object trace_command(thd); Json_writer_array trace_command_steps(thd, "steps"); if (open_tables) - res= check_dependencies_in_with_clauses(m_lex->with_clauses_list) || - instr->exec_open_and_lock_tables(thd, m_lex->query_tables); + res= instr->exec_open_and_lock_tables(thd, m_lex->query_tables); if (likely(!res)) { diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 8bb6011..c3aeb7d 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -3695,7 +3695,11 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags, if (tables->derived) { if (!tables->view) + { + if (!tables->is_derived()) + tables->set_derived(); goto end; + } /* We restore view's name and database wiped out by derived tables processing and fall back to standard open process in order to @@ -3705,35 +3709,6 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags, tables->db= tables->view_db; tables->table_name= tables->view_name; } - else if (tables->select_lex) - { - /* - Check whether 'tables' refers to a table defined in a with clause. - If so set the reference to the definition in tables->with. - */ - if (!tables->with) - tables->with= tables->select_lex->find_table_def_in_with_clauses(tables); - /* - If 'tables' is defined in a with clause set the pointer to the - specification from its definition in tables->derived. - */ - if (tables->with) - { - if (tables->is_recursive_with_table() && - !tables->is_with_table_recursive_reference()) - { - tables->with->rec_outer_references++; - With_element *with_elem= tables->with; - while ((with_elem= with_elem->get_next_mutually_recursive()) != - tables->with) - with_elem->rec_outer_references++; - } - if (tables->set_as_with_table(thd, tables->with)) - DBUG_RETURN(1); - else - goto end; - } - } if (!tables->derived && is_infoschema_db(&tables->db)) { diff --git a/sql/sql_class.cc b/sql/sql_class.cc index ceb8dc1..9d817aa 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -2804,6 +2804,62 @@ void THD::close_active_vio() #endif +/* + @brief MySQL parser used for recursive invocations + + @param old_lex The LEX structure in the state when this parser + is called recursively + @param lex The LEX structure used to parse a new SQL fragment + @param str The SQL fragment to parse + @param str_len The length of the SQL fragment to parse + @param stmt_prepare_mode true <=> when parsing a prepare statement + + @details + This function is to be used when parsing of an SQL fragment is + needed within one of the grammar rules. + + @notes + Currently the function is used only when the specification of a CTE + is parsed for the not first and not recursive references of the CTE. + + @retval false On a succesful parsing of the fragment + @retval true Otherwise +*/ + +bool THD::sql_parser(LEX *old_lex, LEX *lex, + char *str, uint str_len, bool stmt_prepare_mode) +{ + extern int MYSQLparse(THD * thd); + extern int ORAparse(THD * thd); + + bool parse_status= false; + Parser_state parser_state; + Parser_state *old_parser_state= m_parser_state; + + if (parser_state.init(this, str, str_len)) + return true; + + m_parser_state= &parser_state; + parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode; + parser_state.m_lip.multi_statements= false; + parser_state.m_lip.m_digest= NULL; + + lex->param_list= old_lex->param_list; + lex->sphead= old_lex->sphead; + lex->spname= old_lex->spname; + lex->spcont= old_lex->spcont; + lex->sp_chistics= old_lex->sp_chistics; + + parse_status= (variables.sql_mode & MODE_ORACLE) ? + ORAparse(this) : MYSQLparse(this) != 0; + + m_parser_state= old_parser_state; + + return parse_status; +} + + + struct Item_change_record: public ilink { Item **place; diff --git a/sql/sql_class.h b/sql/sql_class.h index 936437f..ecab13e 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4241,14 +4241,11 @@ class THD: public THD_count, /* this must be first */ to resolve all CTE names as we don't need this message to be thrown for any CTE references. */ - if (!lex->with_clauses_list) + if (!lex->with_cte_resolution) { my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0)); return TRUE; } - /* This will allow to throw an error later for non-CTE references */ - to->str= NULL; - to->length= 0; return FALSE; } @@ -5043,6 +5040,9 @@ class THD: public THD_count, /* this must be first */ Item *sp_prepare_func_item(Item **it_addr, uint cols= 1); bool sp_eval_expr(Field *result_field, Item **expr_item_ptr); + bool sql_parser(LEX *old_lex, LEX *lex, + char *str, uint str_len, bool stmt_prepare_mode); + }; /** A short cut for thd->get_stmt_da()->set_ok_status(). */ diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index a9f5443..c17c5a0 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1,3 +1,4 @@ + /* Copyright (c) 2016, 2017 MariaDB @@ -83,7 +84,7 @@ void st_select_lex_unit::set_with_clause(With_clause *with_cl) true on failure */ -bool check_dependencies_in_with_clauses(With_clause *with_clauses_list) +bool LEX::check_dependencies_in_with_clauses() { for (With_clause *with_clause= with_clauses_list; with_clause; @@ -99,6 +100,165 @@ bool check_dependencies_in_with_clauses(With_clause *with_clauses_list) } +bool +LEX::resolve_references_to_cte_in_hanging_cte() +{ + for (With_clause *with_clause= with_clauses_list; + with_clause; with_clause= with_clause->next_with_clause) + { + for (With_element *with_elem= with_clause->with_list.first; + with_elem; with_elem= with_elem->next) + { + if (!with_elem->is_referenced()) + { + TABLE_LIST *first_tbl= + with_elem->spec->first_select()->table_list.first; + TABLE_LIST **with_elem_end_pos= with_elem->head->tables_pos.end_pos; + if (first_tbl && resolve_references_to_cte(first_tbl, with_elem_end_pos)) + return true; + } + } + } + return false; +} + + +/** + @brief + Resolve table references to CTE from a sunchain of table references + + @param tables Points to the beginning of the subchain + @param tables_last Points to the address with the subchain barrier + + @details + The method resolves tables references to CTE from the chain of + table refereces specified by the parameters 'tables' and 'tables_last'. + It resolves the references against the CTE definition occured in a query + or the specification of a CTE whose parsing tree is represented by + this LEX structure. The method is always called right after the process + of parsing the query or of the specification of a CTE has been finished, + thus the chain of table references used in the parsed fragment has been + already built. It is assumed that parameters of the methos specify a + a subchain of this chain. + If a table reference can be potentially a table reference to a CTE and it + has not been resolved yet then the method tries to find the definition + of the CTE against which the reference can be resolved. If it succeeds + it sets the field TABLE_LIST::with to point to the found definition. + It also sets the field TABLE_LIST::derived to point to the specification + of the found CTE and sets TABLE::db.str to empty_c_string. This will + allow to handle this table reference like a reference to a derived handle. + If another table reference has been already resolved against this CTE + and this CTE is not recursive then a clone of the CTE specification is + constructed using the function With_element::clone_parsed_spec() and + TABLE_LIST::derived is set to point to this clone rather than to the + original specification. + If the method does not find a matched CTE definition in the parsed fragment + then in the case when the flag this->only_cte_resolution is set to true + it just moves to the resolution of the next table reference from the + specified subchain while in the case when this->only_cte_resolution is set + to false the method additionally sets an mdl request for this table + reference. + + @notes + The flag this->only_cte_resolution is set to true in the cases when + the failure to resolve a table reference as a CTE reference within + the fragment assiciated with this LEX structure does not imply that + this table reference cannot be resolved as such at all. + + @retval false On success: no errors reported, no memory allocations failed + @retval true Otherwise + +*/ + +bool LEX::resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last) +{ + With_element *with_elem= 0; + + for (TABLE_LIST *tbl= tables; tbl != *tables_last; tbl= tbl->next_global) + { + if (tbl->derived) + continue; + if (!tbl->db.str && !tbl->with) + tbl->with= tbl->select_lex->find_table_def_in_with_clauses(tbl); + if (!tbl->with) // no CTE matches table reference tbl + { + if (only_cte_resolution) + continue; + if (!tbl->db.str) // no database specified in table reference tbl + { + if (!thd->db.str) // no default database is set + { + my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0)); + return true; + } + if (copy_db_to(&tbl->db)) + return true; + if (!(tbl->table_options & TL_OPTION_ALIAS)) + tbl->mdl_request.init(MDL_key::TABLE, tbl->db.str, + tbl->table_name.str, + tbl->mdl_type, MDL_TRANSACTION); + tbl->mdl_request.set_type((tbl->lock_type >= TL_WRITE_ALLOW_WRITE) ? + MDL_SHARED_WRITE : MDL_SHARED_READ); + } + continue; + } + with_elem= tbl->with; + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + { + tbl->with->rec_outer_references++; + while ((with_elem= with_elem->get_next_mutually_recursive()) != + tbl->with) + with_elem->rec_outer_references++; + } + if (!with_elem->is_used_in_query || with_elem->is_recursive) + { + tbl->derived= with_elem->spec; + if (tbl->derived != tbl->select_lex->master_unit() && + !with_elem->is_recursive && + !tbl->is_with_table_recursive_reference()) + { + tbl->derived->move_as_slave(tbl->select_lex); + } + with_elem->is_used_in_query= true; + } + else + { + if (!(tbl->derived= tbl->with->clone_parsed_spec(thd->lex, tbl))) + return true; + } + tbl->db.str= empty_c_string; + tbl->db.length= 0; + tbl->schema_table= 0; + if (tbl->derived) + { + tbl->derived->first_select()->set_linkage(DERIVED_TABLE_TYPE); + tbl->select_lex->add_statistics(tbl->derived); + } + if (tbl->with->is_recursive && tbl->is_with_table_recursive_reference()) + continue; + with_elem->inc_references(); + } + return false; +} + + +bool +LEX::check_cte_dependencies_and_resolve_references() +{ + if (check_dependencies_in_with_clauses()) + return true; + if (!with_cte_resolution) + return false; + if (resolve_references_to_cte(query_tables, query_tables_last)) + return true; + if (resolve_references_to_cte_in_hanging_cte()) + return true; + return false; +} + + /** @brief Check dependencies between tables defined in this with clause @@ -137,10 +297,11 @@ bool With_clause::check_dependencies() elem != with_elem; elem= elem->next) { - if (lex_string_cmp(system_charset_info, with_elem->query_name, - elem->query_name) == 0) + if (lex_string_cmp(system_charset_info, with_elem->get_name(), + elem->get_name()) == 0) { - my_error(ER_DUP_QUERY_NAME, MYF(0), with_elem->query_name->str); + my_error(ER_DUP_QUERY_NAME, MYF(0), + with_elem->get_name_str()); return true; } } @@ -247,13 +408,12 @@ With_element *With_clause::find_table_def(TABLE_LIST *table, with_elem != barrier; with_elem= with_elem->next) { - if (my_strcasecmp(system_charset_info, with_elem->query_name->str, - table->table_name.str) == 0 && + if (my_strcasecmp(system_charset_info, with_elem->get_name_str(), + table->table_name.str) == 0 && !table->is_fqtn) { table->set_derived(); - table->db.str= empty_c_string; - table->db.length= 0; + with_elem->referenced= true; return with_elem; } } @@ -610,7 +770,7 @@ bool With_clause::check_anchors() if (elem == with_elem) { my_error(ER_RECURSIVE_WITHOUT_ANCHORS, MYF(0), - with_elem->query_name->str); + with_elem->get_name_str()); return true; } } @@ -643,7 +803,7 @@ bool With_clause::check_anchors() if (elem->work_dep_map & elem->get_elem_map()) { my_error(ER_UNACCEPTABLE_MUTUAL_RECURSION, MYF(0), - with_elem->query_name->str); + with_elem->get_name_str()); return true; } } @@ -797,9 +957,10 @@ bool With_element::set_unparsed_spec(THD *thd, @brief Create a clone of the specification for the given with table - @param thd The context of the statement containing this with element + @param old_lex The LEX structure created for the query or CTE specification + where this With_element is defined @param with_table The reference to the table defined in this element for which - the clone is created. + the clone is created. @details The method creates a clone of the specification used in this element. @@ -807,12 +968,13 @@ bool With_element::set_unparsed_spec(THD *thd, this element. The clone is created when the string with the specification saved in unparsed_spec is fed into the parser as an input string. The parsing - this string a unit object representing the specification is build. + this string a unit object representing the specification is built. A chain of all table references occurred in the specification is also formed. The method includes the new unit and its sub-unit into hierarchy of the units of the main query. I also insert the constructed chain of the table references into the chain of all table references of the main query. + The method resolves all references to CTE in the clone. @note Clones is created only for not first references to tables defined in @@ -828,113 +990,127 @@ bool With_element::set_unparsed_spec(THD *thd, NULL - otherwise */ -st_select_lex_unit *With_element::clone_parsed_spec(THD *thd, +st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, TABLE_LIST *with_table) { + THD *thd= old_lex->thd; LEX *lex; - st_select_lex_unit *res= NULL; - Query_arena backup; - Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); + st_select_lex_unit *res= NULL; if (!(lex= (LEX*) new(thd->mem_root) st_lex_local)) - { - if (arena) - thd->restore_active_arena(arena, &backup); return res; - } - LEX *old_lex= thd->lex; thd->lex= lex; bool parse_status= false; - Parser_state parser_state; - TABLE_LIST *spec_tables; - TABLE_LIST *spec_tables_tail; st_select_lex *with_select; char save_end= unparsed_spec.str[unparsed_spec.length]; ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= '\0'; - if (parser_state.init(thd, (char*) unparsed_spec.str, (unsigned int)unparsed_spec.length)) - goto err; - parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode; - parser_state.m_lip.multi_statements= false; - parser_state.m_lip.m_digest= NULL; lex_start(thd); lex->clone_spec_offset= unparsed_spec_offset; - lex->param_list= old_lex->param_list; - lex->sphead= old_lex->sphead; - lex->spname= old_lex->spname; - lex->spcont= old_lex->spcont; - lex->sp_chistics= old_lex->sp_chistics; - - lex->stmt_lex= old_lex; - parse_status= parse_sql(thd, &parser_state, 0); + lex->with_cte_resolution= true; + + /* + The specification of a CTE is to be parsed as a regular query. + At the very end of the parsing query the function + check_cte_dependencies_and_resolve_references() will be called. + It will check the dependencies between CTEs that are defined + within the query and will resolve CTE references in this query. + If a table reference is not resolved as a CTE reference within + this query it still can be resolved as a reference to a CTE defined + in the same clause as the CTE whose specification is to be parsed + or defined in an embedding CTE definition. + + Example: + with + cte1 as ( ... ), + cte2 as ([WITH ...] select ... from cte1 ...) + select ... from cte2 as r, ..., cte2 as s ... + + Here the specification of cte2 has be cloned for table reference + with alias s1. The specification contains a reference to cte1 + that is defined outside this specification. If the reference to + cte1 cannot be resolved within the specification of cte2 it's + not necessarily has to be a reference to a non-CTE table. That's + why the flag lex->only_cte_resolution has to be set to true + before parsing of the specification of cte2 invoked by this + function starts. Otherwise an mdl_lock would be requested for s + and this would not be correct + */ + + lex->only_cte_resolution= true; + + lex->stmt_lex= old_lex->stmt_lex ? old_lex->stmt_lex : old_lex; + + parse_status= thd->sql_parser(old_lex, lex, + (char*) unparsed_spec.str, + (unsigned int)unparsed_spec.length, + stmt_prepare_mode); + ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= save_end; with_select= lex->first_select_lex(); 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 (spec_tables) + /* + The global chain of TABLE_LIST objects created for the specification that + just has been parsed is added to such chain that contains the reference + to the CTE whose specification is parsed right after the TABLE_LIST object + created for the reference. + */ + if (lex->query_tables) { - if (with_table->next_global) + head->tables_pos.set_start_pos(&with_table->next_global); + head->tables_pos.set_end_pos(lex->query_tables_last); + TABLE_LIST *next_tbl= with_table->next_global; + if (next_tbl) { - spec_tables_tail->next_global= with_table->next_global; - with_table->next_global->prev_global= &spec_tables_tail->next_global; + *(lex->query_tables->prev_global= next_tbl->prev_global)= + lex->query_tables; + *(next_tbl->prev_global= lex->query_tables_last)= next_tbl; } else { - old_lex->query_tables_last= &spec_tables_tail->next_global; + *(lex->query_tables->prev_global= old_lex->query_tables_last)= + lex->query_tables; + old_lex->query_tables_last= lex->query_tables_last; } - spec_tables->prev_global= &with_table->next_global; - with_table->next_global= spec_tables; } res= &lex->unit; + /* + The unit of the specification that just has been parsed is included + as a slave of the select that contained in its from list the table + reference for which the unit has been created. + */ lex->unit.include_down(with_table->select_lex); - lex->unit.set_slave(with_select); + lex->unit.set_slave(with_select); + lex->unit.cloned_from= spec; old_lex->all_selects_list= (st_select_lex*) (lex->all_selects_list-> insert_chain_before( (st_select_lex_node **) &(old_lex->all_selects_list), with_select)); - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - res= NULL; + /* - Resolve references to CTE from the spec_tables list that has not - been resolved yet. + Now all references to the CTE defined outside of the cloned specification + has to be resolved. Additionally if old_lex->only_cte_resolution == false + for the table references that has not been resolved requests for mdl_locks + has to be set. */ - for (TABLE_LIST *tbl= spec_tables; - tbl; - tbl= tbl->next_global) + lex->only_cte_resolution= old_lex->only_cte_resolution; + if (lex->resolve_references_to_cte(lex->query_tables, + lex->query_tables_last)) { - if (!tbl->with) - tbl->with= with_select->find_table_def_in_with_clauses(tbl); - if (tbl == spec_tables_tail) - break; - } - if (check_table_access(thd, SELECT_ACL, spec_tables, FALSE, UINT_MAX, FALSE)) + res= NULL; goto err; + } lex->sphead= NULL; // in order not to delete lex->sphead lex_end(lex); err: - if (arena) - thd->restore_active_arena(arena, &backup); thd->lex= old_lex; return res; } @@ -1104,58 +1280,6 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) } -/** - @brief - Set the specifying unit in this reference to a with table - - @details - The method assumes that the given element with_elem defines the table T - this table reference refers to. - If this is the first reference to T the method just sets its specification - in the field 'derived' as the unit that yields T. Otherwise the method - first creates a clone specification and sets rather this clone in this field. - - @retval - false on success - true on failure -*/ - -bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) -{ - if (table) - { - /* - This table was prematurely identified as a temporary table. - We correct it here, but it's not a nice solution in the case - when the temporary table with this name is not used anywhere - else in the query. - */ - thd->mark_tmp_table_as_free_for_reuse(table); - table= 0; - } - with= with_elem; - schema_table= NULL; - if (!with_elem->is_referenced() || with_elem->is_recursive) - { - derived= with_elem->spec; - if (derived != select_lex->master_unit() && - !with_elem->is_recursive && - !is_with_table_recursive_reference()) - { - derived->move_as_slave(select_lex); - } - } - else - { - if(!(derived= with_elem->clone_parsed_spec(thd, this))) - return true; - } - derived->first_select()->set_linkage(DERIVED_TABLE_TYPE); - select_lex->add_statistics(derived); - with_elem->inc_references(); - return false; -} - bool TABLE_LIST::is_recursive_with_table() { @@ -1256,7 +1380,7 @@ bool st_select_lex::check_unrestricted_recursive(bool only_standard_compliant) if (only_standard_compliant && with_elem->is_unrestricted()) { my_error(ER_NOT_STANDARD_COMPLIANT_RECURSIVE, - MYF(0), with_elem->query_name->str); + MYF(0), with_elem->get_name_str()); return true; } @@ -1456,7 +1580,7 @@ void With_clause::print(String *str, enum_query_type query_type) void With_element::print(String *str, enum_query_type query_type) { - str->append(query_name); + str->append(get_name()); if (column_list.elements) { List_iterator_fast<LEX_CSTRING> li(column_list); diff --git a/sql/sql_cte.h b/sql/sql_cte.h index 80d5664..f30c287 100644 --- a/sql/sql_cte.h +++ b/sql/sql_cte.h @@ -23,6 +23,38 @@ class select_unit; struct st_unit_ctxt_elem; +/** + @class With_element_head + @brief Head of the definition of a CTE table + + It contains the name of the CTE and it contains the position of the subchain + of table references used in the definition in the global chain of table + references used in the query where this definition is encountered. +*/ + +class With_element_head : public Sql_alloc +{ + /* The name of the defined CTE */ + LEX_CSTRING *query_name; + +public: + /* + The structure describing the subchain of the table references used in + the specification of the defined CTE in the global chain of table + references used in the query. The structure is fully defined only + after the CTE definition has been parsed. + */ + TABLE_CHAIN tables_pos; + + With_element_head(LEX_CSTRING *name) + : query_name(name) + { + tables_pos.set_start_pos(0); + tables_pos.set_end_pos(0); + } + friend class With_element; +}; + /** @class With_element @@ -85,9 +117,22 @@ class With_element : public Sql_alloc subqueries and specifications of other with elements). */ uint references; + + /* + true <=> this With_element is referred in the query in which the + element is defined + */ + bool referenced; + + /* + true <=> this With_element is needed for the execution of the query + in which the element is defined + */ + bool is_used_in_query; + /* Unparsed specification of the query that specifies this element. - It used to build clones of the specification if they are needed. + It's used to build clones of the specification if they are needed. */ LEX_CSTRING unparsed_spec; /* Offset of the specification in the input string */ @@ -101,10 +146,12 @@ class With_element : public Sql_alloc public: /* - The name of the table introduced by this with elememt. The name - can be used in FROM lists of the queries in the scope of the element. + Contains the name of the defined With element and the position of + the subchain of the tables references used by its definition in the + global chain of TABLE_LIST objects created for the whole query. */ - LEX_CSTRING *query_name; + With_element_head *head; + /* Optional list of column names to name the columns of the table introduced by this with element. It is used in the case when the names are not @@ -162,18 +209,27 @@ class With_element : public Sql_alloc /* List of derived tables containing recursive references to this CTE */ SQL_I_List<TABLE_LIST> derived_with_rec_ref; - With_element(LEX_CSTRING *name, + With_element(With_element_head *h, List <LEX_CSTRING> list, st_select_lex_unit *unit) : next(NULL), base_dep_map(0), derived_dep_map(0), sq_dep_map(0), work_dep_map(0), mutually_recursive(0), top_level_dep_map(0), sq_rec_ref(NULL), next_mutually_recursive(NULL), references(0), - query_name(name), column_list(list), spec(unit), + referenced(false), is_used_in_query(false), + head(h), column_list(list), spec(unit), is_recursive(false), rec_outer_references(0), with_anchor(false), level(0), rec_result(NULL) { unit->with_element= this; } + LEX_CSTRING *get_name() { return head->query_name; } + const char *get_name_str() { return get_name()->str; } + + void set_tables_start_pos(TABLE_LIST **pos) + { head->tables_pos.set_start_pos(pos); } + void set_tables_end_pos(TABLE_LIST **pos) + { head->tables_pos.set_end_pos(pos); } + bool check_dependencies_in_spec(); void check_dependencies_in_select(st_select_lex *sl, st_unit_ctxt_elem *ctxt, @@ -200,9 +256,9 @@ class With_element : public Sql_alloc bool set_unparsed_spec(THD *thd, const char *spec_start, const char *spec_end, my_ptrdiff_t spec_offset); - st_select_lex_unit *clone_parsed_spec(THD *thd, TABLE_LIST *with_table); + st_select_lex_unit *clone_parsed_spec(LEX *old_lex, TABLE_LIST *with_table); - bool is_referenced() { return references != 0; } + bool is_referenced() { return referenced; } void inc_references() { references++; } @@ -260,6 +316,12 @@ class With_element : public Sql_alloc void prepare_for_next_iteration(); friend class With_clause; + + friend + bool LEX::resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last); + friend + bool LEX::resolve_references_to_cte_in_hanging_cte(); }; const uint max_number_of_elements_in_with_clause= sizeof(table_map)*8; @@ -291,6 +353,7 @@ class With_clause : public Sql_alloc in the current statement */ With_clause *next_with_clause; + /* Set to true if dependencies between with elements have been checked */ bool dependencies_are_checked; /* @@ -338,7 +401,7 @@ class With_clause : public Sql_alloc void attach_to(st_select_lex *select_lex); With_clause *pop() { return embedding_with_clause; } - + bool check_dependencies(); bool check_anchors(); @@ -358,8 +421,10 @@ class With_clause : public Sql_alloc friend class With_element; friend - bool - check_dependencies_in_with_clauses(With_clause *with_clauses_list); + bool LEX::check_dependencies_in_with_clauses(); + + friend + bool LEX::resolve_references_to_cte_in_hanging_cte(); }; inline diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index dc079ba..89b1900 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -917,6 +917,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) #ifndef NO_EMBEDDED_ACCESS_CHECKS if (derived->is_view()) table->grant= derived->grant; +#if 1 else { DBUG_ASSERT(derived->is_derived()); @@ -925,6 +926,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) derived->grant.privilege= SELECT_ACL; } #endif +#endif /* Add new temporary table to list of open derived tables */ if (!derived->is_with_table_recursive_reference()) { diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 10cd20e..c674495 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -721,6 +721,8 @@ void LEX::start(THD *thd_arg) explain_json= false; context_analysis_only= 0; derived_tables= 0; + with_cte_resolution= false; + only_cte_resolution= false; safe_to_cache_query= 1; parsing_options.reset(); empty_field_list_on_rset= 0; @@ -2388,6 +2390,7 @@ void st_select_lex_unit::init_query() is_view= false; with_clause= 0; with_element= 0; + cloned_from= 0; columns_are_renamed= false; intersect_mark= NULL; with_wrapped_tvc= false; @@ -4831,14 +4834,14 @@ void st_select_lex::set_explain_type(bool on_the_fly) /* pos_in_table_list=NULL for e.g. post-join aggregation JOIN_TABs. */ - if (!tab->table); - else if (const TABLE_LIST *pos= tab->table->pos_in_table_list) + if (!(tab->table && tab->table->pos_in_table_list)) + continue; + TABLE_LIST *tbl= tab->table->pos_in_table_list; + if (tbl->with && tbl->with->is_recursive && + tbl->is_with_table_recursive_reference()) { - if (pos->with && pos->with->is_recursive) - { - uses_cte= true; - break; - } + uses_cte= true; + break; } } if (uses_cte) @@ -8266,6 +8269,8 @@ bool LEX::check_main_unit_semantics() if (unit.set_nest_level(0) || unit.check_parameters(first_select_lex())) return TRUE; + if (check_cte_dependencies_and_resolve_references()) + return TRUE; return FALSE; } @@ -8948,8 +8953,8 @@ void st_select_lex::add_statistics(SELECT_LEX_UNIT *unit) bool LEX::main_select_push(bool service) { DBUG_ENTER("LEX::main_select_push"); - current_select_number= 1; - builtin_select.select_number= 1; + current_select_number= ++thd->lex->stmt_lex->current_select_number; + builtin_select.select_number= current_select_number; builtin_select.is_service_select= service; if (push_select(&builtin_select)) DBUG_RETURN(TRUE); diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 66c44f2..f2fd67c 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -917,6 +917,8 @@ class st_select_lex_unit: public st_select_lex_node { With_clause *with_clause; /* With element where this unit is used as the specification (if any) */ With_element *with_element; + /* The unit used as a CTE specification from which this unit is cloned */ + st_select_lex_unit *cloned_from; /* thread handler */ THD *thd; /* @@ -1492,7 +1494,9 @@ class st_select_lex: public st_select_lex_node } With_element *get_with_element() { - return master_unit()->with_element; + return master_unit()->cloned_from ? + master_unit()->cloned_from->with_element : + master_unit()->with_element; } With_element *find_table_def_in_with_clauses(TABLE_LIST *table); bool check_unrestricted_recursive(bool only_standard_compliant); @@ -3314,6 +3318,20 @@ struct LEX: public Query_tables_list */ uint8 derived_tables; uint8 context_analysis_only; + /* + true <=> The parsed fragment requires resolution of references to CTE + at the end of parsing. This name resolution process involves searching + for possible dependencies between CTE defined in the parsed fragment and + detecting possible recursive references. + The flag is set to true if the fragment contains CTE definitions. + */ + bool with_cte_resolution; + /* + true <=> only resolution of references to CTE are required in the parsed + fragment, no checking of dependecies between CTE is required. + This flag is used only when parsing clones of CTE specifications. + */ + bool only_cte_resolution; bool local_file; bool check_exists; bool autocommit; @@ -4547,6 +4565,11 @@ struct LEX: public Query_tables_list select_stack[0]->is_service_select); } + bool check_dependencies_in_with_clauses(); + bool resolve_references_to_cte_in_hanging_cte(); + bool check_cte_dependencies_and_resolve_references(); + bool resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last); }; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index e03b981..b60cc86 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3397,9 +3397,6 @@ 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)) { @@ -8139,7 +8136,7 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, ptr->is_fqtn= TRUE; ptr->db= table->db; } - else if (lex->copy_db_to(&ptr->db)) + else if (!lex->with_cte_resolution && lex->copy_db_to(&ptr->db)) DBUG_RETURN(0); else ptr->is_fqtn= FALSE; @@ -8156,7 +8153,9 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, } ptr->table_name= table->table; - ptr->lock_type= lock_type; + ptr->lock_type= lock_type; + ptr->mdl_type= mdl_type; + ptr->table_options= table_options; ptr->updating= MY_TEST(table_options & TL_OPTION_UPDATING); /* TODO: remove TL_OPTION_FORCE_INDEX as it looks like it's not used */ ptr->force_index= MY_TEST(table_options & TL_OPTION_FORCE_INDEX); @@ -8838,8 +8837,10 @@ void st_select_lex::set_lock_for_tables(thr_lock_type lock_type, bool for_update { tables->lock_type= lock_type; tables->updating= for_update; - tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ? - MDL_SHARED_WRITE : MDL_SHARED_READ); + + if (tables->db.str && tables->db.str[0]) + tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ? + MDL_SHARED_WRITE : MDL_SHARED_READ); } DBUG_VOID_RETURN; } diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index ee50402..64e138f 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2376,9 +2376,6 @@ 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); diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 126db90..1e3c4ce 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -431,12 +431,6 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views, lex->link_first_table_back(view, link_to_local); view->open_type= OT_BASE_ONLY; - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - { - res= TRUE; - goto err; - } - WSREP_TO_ISOLATION_BEGIN(WSREP_MYSQL_DB, NULL, NULL); /* @@ -1413,9 +1407,6 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table, TABLE_LIST *tbl; Security_context *security_ctx= 0; - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) - goto err; - /* Check rights to run commands which show underlying tables. In the optimizer trace we would not like to show trace for diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 2548dc2..f3e1cf2 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -770,6 +770,7 @@ Virtual_column_info *add_virtual_expression(THD *thd, Item *expr) class sp_head *sphead; class sp_name *spname; class sp_variable *spvar; + class With_element_head *with_element_head; class With_clause *with_clause; class Virtual_column_info *virtual_column; @@ -2188,7 +2189,7 @@ END_OF_INPUT %type <with_clause> with_clause -%type <lex_str_ptr> query_name +%type <with_element_head> with_element_head %type <lex_str_list> opt_with_column_list @@ -3336,7 +3337,11 @@ call: if (unlikely(Lex->call_statement_start(thd, $2))) MYSQL_YYABORT; } - opt_sp_cparam_list {} + opt_sp_cparam_list + { + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; + } ; /* CALL parameters */ @@ -4185,6 +4190,8 @@ sp_proc_stmt_return: LEX *lex= Lex; sp_head *sp= lex->sphead; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; if (unlikely(sp->m_handler->add_instr_freturn(thd, sp, lex->spcont, $3, lex)) || unlikely(sp->restore_lex(thd))) @@ -13331,6 +13338,8 @@ do: { Lex->insert_list= $3; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; } ; @@ -15497,6 +15506,7 @@ with_clause: if (unlikely(with_clause == NULL)) MYSQL_YYABORT; lex->derived_tables|= DERIVED_WITH; + lex->with_cte_resolution= true; lex->curr_with_clause= with_clause; with_clause->add_to_list(Lex->with_clauses_list_last_next); if (lex->current_select && @@ -15524,7 +15534,7 @@ with_list: with_list_element: - query_name + with_element_head opt_with_column_list { $2= new List<LEX_CSTRING> (Lex->with_column_list); @@ -15544,6 +15554,7 @@ with_list_element: if (elem->set_unparsed_spec(thd, spec_start, $7.pos(), spec_start - query_start)) MYSQL_YYABORT; + elem->set_tables_end_pos(lex->query_tables_last); } ; @@ -15570,16 +15581,18 @@ with_column_list: ; -query_name: +with_element_head: ident { - $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); - if (unlikely($$ == NULL)) + LEX_CSTRING *name= + (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); + $$= new (thd->mem_root) With_element_head(name); + if (unlikely(name == NULL || $$ == NULL)) MYSQL_YYABORT; + $$->tables_pos.set_start_pos(Lex->query_tables_last); } ; - /********************************************************************** ** Creating different items. diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 30727a8..f2e112c 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -248,6 +248,7 @@ void ORAerror(THD *thd, const char *s) class sp_head *sphead; class sp_name *spname; class sp_variable *spvar; + class With_element_head *with_element_head; class With_clause *with_clause; class Virtual_column_info *virtual_column; @@ -1689,7 +1690,7 @@ END_OF_INPUT %type <with_clause> with_clause -%type <lex_str_ptr> query_name +%type <with_element_head> with_element_head %type <lex_str_list> opt_with_column_list @@ -3138,7 +3139,11 @@ call: if (unlikely(Lex->call_statement_start(thd, $2))) MYSQL_YYABORT; } - opt_sp_cparam_list {} + opt_sp_cparam_list + { + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; + } ; /* CALL parameters */ @@ -4092,6 +4097,8 @@ sp_proc_stmt_return: LEX *lex= Lex; sp_head *sp= lex->sphead; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; if (unlikely(sp->m_handler->add_instr_freturn(thd, sp, lex->spcont, $3, lex)) || unlikely(sp->restore_lex(thd))) @@ -13436,6 +13443,8 @@ do: { Lex->insert_list= $3; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; } ; @@ -15620,6 +15629,7 @@ with_clause: if (unlikely(with_clause == NULL)) MYSQL_YYABORT; lex->derived_tables|= DERIVED_WITH; + lex->with_cte_resolution= true; lex->curr_with_clause= with_clause; with_clause->add_to_list(Lex->with_clauses_list_last_next); if (lex->current_select && @@ -15647,7 +15657,7 @@ with_list: with_list_element: - query_name + with_element_head opt_with_column_list { $2= new List<LEX_CSTRING> (Lex->with_column_list); @@ -15667,6 +15677,7 @@ with_list_element: if (elem->set_unparsed_spec(thd, spec_start, $7.pos(), spec_start - query_start)) MYSQL_YYABORT; + elem->set_tables_end_pos(lex->query_tables_last); } ; @@ -15693,12 +15704,15 @@ with_column_list: ; -query_name: +with_element_head: ident { - $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); - if (unlikely($$ == NULL)) + LEX_CSTRING *name= + (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); + $$= new (thd->mem_root) With_element_head(name); + if (unlikely(name == NULL || $$ == NULL)) MYSQL_YYABORT; + $$->tables_pos.set_start_pos(Lex->query_tables_last); } ; diff --git a/sql/table.cc b/sql/table.cc index 084b441..67b2b14 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -9144,6 +9144,7 @@ void TABLE_LIST::set_lock_type(THD *thd, enum thr_lock_type lock) } } + bool TABLE_LIST::is_with_table() { return derived && derived->with_element; diff --git a/sql/table.h b/sql/table.h index 6073e35..248c239 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2139,6 +2139,29 @@ struct vers_select_conds_t struct LEX; class Index_hint; + +/* + @struct TABLE_CHAIN + @brief Subchain of global chain of table references + + The structure contains a pointer to the address of the next_global + pointer to the first TABLE_LIST objectof the subchain and the address + of the next_global pointer to the element right after the last + TABLE_LIST object of the subchain. For an empty subchain both pointers + have the same value. +*/ + +struct TABLE_CHAIN +{ + TABLE_CHAIN() {} + + TABLE_LIST **start_pos; + TABLE_LIST ** end_pos; + + void set_start_pos(TABLE_LIST **pos) { start_pos= pos; } + void set_end_pos(TABLE_LIST **pos) { end_pos= pos; } +}; + struct TABLE_LIST { TABLE_LIST() {} /* Remove gcc warning */ @@ -2473,6 +2496,20 @@ struct TABLE_LIST /* call back function for asking handler about caching in query cache */ qc_engine_callback callback_func; thr_lock_type lock_type; + + /* + Two fields below are set during parsing this table reference in the cases + when the table reference can be potentially a reference to a CTE table. + In this cases the fact that the reference is a reference to a CTE or not + will be ascertained at the very end of parsing of the query when referencies + to CTE are resolved. For references to CTE and to derived tables no mdl + requests are needed while for other table references they are. If a request + is possibly postponed the info that allows to issue this request must be + saved in 'mdl_type' and 'table_options'. + */ + enum_mdl_type mdl_type; + ulong table_options; + uint outer_join; /* Which join type */ uint shared; /* Used in multi-upd */ bool updatable; /* VIEW/TABLE can be updated now */

1 0

[Commits] cdc305c8dd8: MDEV-19620: Changing join_buffer_size causes different results
by varun 29 Dec '20

29 Dec '20

revision-id: cdc305c8dd89a726e09e5fe70ff890d06609cbfb (mariadb-10.3.21-309-gcdc305c8dd8) parent(s): 043bd85a574a88856ab9c6d497e682ed06fe45e9 author: Varun Gupta committer: Varun Gupta timestamp: 2020-12-28 14:12:14 +0530 message: MDEV-19620: Changing join_buffer_size causes different results The scenario here is that query refinement phase decides to use a hash join. When the join buffers are allocated in the JOIN::init_join_caches, for a table the size exceeds the value for join_buffer_space_limit (which is the limit of the space available for all join buffers). When this happens then we disallow join buffering for the table, this is done in the revise_cache_usage and set_join_cache_denial. In this issue the hash key is created on an index for which ref access is possible, so when we disallow hash join then instead of switching to REF access we switch to a table scan. This is a problem because the equijoin conditions for which a lookup can be made are not attached to the table(or are not evaluated for the table). This leads to incorrect results. The fix here would be to switch to using a lookup because it was picked by the join planner to be more efficient than the table scan. --- mysql-test/main/join_cache.result | 138 ++++++++++++++++++++++++++++++++++++++ mysql-test/main/join_cache.test | 105 +++++++++++++++++++++++++++++ sql/sql_select.cc | 69 +++++++++++++++---- sql/sql_select.h | 6 ++ 4 files changed, 304 insertions(+), 14 deletions(-) diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index 3d1d91df997..e58503f422f 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -6128,4 +6128,142 @@ EXPLAIN } } drop table t1,t2,t3; +# +# MDEV-19620: Changing join_buffer_size causes different results +# +SET @save_join_cache_level= @@join_cache_level; +SET @save_join_buffer_size= @@join_buffer_size; +SET @save_join_buffer_space_limit= @@join_buffer_space_limit; +SET join_cache_level = 3; +CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('1','v'),('7','s'); +CREATE TABLE t2 (pk3 INT PRIMARY KEY, i3 VARCHAR(300), c3 VARCHAR(500)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'7','g'),(2,'4','p'),(3,'1','q'); +INSERT INTO t2 VALUES (4,'7','g'),(5,'4','p'),(6,'1','q'); +INSERT INTO t2 VALUES (16,'7','g'),(17,'4','p'),(28,'1','q'); +# +# Hash join + table Scan on t2 +# +set join_buffer_size=1024; +set join_buffer_space_limit=2048; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 9 Using where +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); +i2 c2 pk3 i3 c3 +1 v NULL NULL NULL +7 s NULL NULL NULL +set join_buffer_space_limit=262144; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 hash_ALL NULL #hash#$hj 503 test.t1.c2 9 Using where; Using join buffer (flat, BNLH join) +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); +i2 c2 pk3 i3 c3 +1 v NULL NULL NULL +7 s NULL NULL NULL +# +# HASH join + ref access on t2 +# +ALTER TABLE t2 ADD KEY k1(c3); +set join_buffer_size=1024; +set join_buffer_space_limit=2048; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ref k1 k1 503 test.t1.c2 2 Using where +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); +i2 c2 pk3 i3 c3 +1 v NULL NULL NULL +7 s NULL NULL NULL +set join_buffer_space_limit=262144; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 hash_ALL k1 #hash#k1 503 test.t1.c2 9 Using where; Using join buffer (flat, BNLH join) +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); +i2 c2 pk3 i3 c3 +1 v NULL NULL NULL +7 s NULL NULL NULL +# +# Hash join + index scan on t2 +# +ALTER TABLE t2 DROP KEY k1; +ALTER TABLE t2 ADD KEY k1(i3,c3); +set join_buffer_size=1024; +set join_buffer_space_limit=2048; +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 index NULL k1 806 NULL 9 Using where; Using index +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); +c3 i3 i2 c2 +NULL NULL 1 v +NULL NULL 7 s +set join_buffer_space_limit=262144; +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 hash_index NULL #hash#$hj:k1 503:806 test.t1.c2 9 Using where; Using index; Using join buffer (flat, BNLH join) +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); +c3 i3 i2 c2 +NULL NULL 1 v +NULL NULL 7 s +DROP TABLE t1,t2; +# +# Hash join + range scan on t2 +# +CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)); +INSERT INTO t1 VALUES ('1','v'),('7','s'); +CREATE TABLE t2 (pk3 INT PRIMARY KEY, i3 VARCHAR(300), c3 VARCHAR(500), INDEX(i3,c3)); +INSERT INTO t2 VALUES (1,'7','g'),(2,'4','s'),(3,'1','q'); +set join_buffer_size=1024; +set join_buffer_space_limit=2048; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t2 range i3 i3 303 NULL 2 Using index condition; Using where +SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; +i2 c2 pk3 i3 c3 +7 s 2 4 s +set join_buffer_space_limit=262144; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t2 hash_range i3 #hash#$hj:i3 503:303 test.t1.c2 2 Using where; Using join buffer (flat, BNLH join) +SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; +i2 c2 pk3 i3 c3 +7 s 2 4 s +DROP TABLE t1,t2; +# +# Hash join + eq ref access on t2 +# +CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)); +INSERT INTO t1 VALUES ('1','v'),('7','s'); +CREATE TABLE t2 (pk3 INT, i3 VARCHAR(300), c3 VARCHAR(500) PRIMARY KEY); +INSERT INTO t2 VALUES (1,'7','g'),(2,'4','s'),(3,'1','q'); +set join_buffer_size=1024; +set join_buffer_space_limit=2048; +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 502 test.t1.c2 1 Using where +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); +c3 i3 i2 c2 +NULL NULL 1 v +s 4 7 s +set join_buffer_space_limit=262144; +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 hash_ALL PRIMARY #hash#PRIMARY 502 test.t1.c2 3 Using where; Using join buffer (flat, BNLH join) +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); +c3 i3 i2 c2 +NULL NULL 1 v +s 4 7 s +DROP TABLE t1,t2; +SET @save_join_cache_level= @@join_cache_level; +SET @save_join_buffer_size= @@join_buffer_size; +SET @save_join_buffer_space_limit= @@join_buffer_space_limit; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index 91339c2cb21..6670c62516b 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -4054,5 +4054,110 @@ where drop table t1,t2,t3; +--echo # +--echo # MDEV-19620: Changing join_buffer_size causes different results +--echo # + +SET @save_join_cache_level= @@join_cache_level; +SET @save_join_buffer_size= @@join_buffer_size; +SET @save_join_buffer_space_limit= @@join_buffer_space_limit; +SET join_cache_level = 3; + +CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('1','v'),('7','s'); +CREATE TABLE t2 (pk3 INT PRIMARY KEY, i3 VARCHAR(300), c3 VARCHAR(500)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,'7','g'),(2,'4','p'),(3,'1','q'); +INSERT INTO t2 VALUES (4,'7','g'),(5,'4','p'),(6,'1','q'); +INSERT INTO t2 VALUES (16,'7','g'),(17,'4','p'),(28,'1','q'); + +--echo # +--echo # Hash join + table Scan on t2 +--echo # + +set join_buffer_size=1024; +set join_buffer_space_limit=2048; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); + +set join_buffer_space_limit=262144; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); + +--echo # +--echo # HASH join + ref access on t2 +--echo # + +ALTER TABLE t2 ADD KEY k1(c3); +set join_buffer_size=1024; +set join_buffer_space_limit=2048; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); + +set join_buffer_space_limit=262144; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); +SELECT * FROM t1 LEFT JOIN t2 ON (c3=c2); + +--echo # +--echo # Hash join + index scan on t2 +--echo # +ALTER TABLE t2 DROP KEY k1; +ALTER TABLE t2 ADD KEY k1(i3,c3); + +set join_buffer_size=1024; +set join_buffer_space_limit=2048; + +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); + +set join_buffer_space_limit=262144; +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); +DROP TABLE t1,t2; + +--echo # +--echo # Hash join + range scan on t2 +--echo # + +CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)); +INSERT INTO t1 VALUES ('1','v'),('7','s'); +CREATE TABLE t2 (pk3 INT PRIMARY KEY, i3 VARCHAR(300), c3 VARCHAR(500), INDEX(i3,c3)); +INSERT INTO t2 VALUES (1,'7','g'),(2,'4','s'),(3,'1','q'); + +set join_buffer_size=1024; +set join_buffer_space_limit=2048; + +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; +SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; + +set join_buffer_space_limit=262144; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; +SELECT * FROM t1 LEFT JOIN t2 ON (c3 = c2) WHERE i3 >= '4'; + +DROP TABLE t1,t2; + +--echo # +--echo # Hash join + eq ref access on t2 +--echo # + +CREATE TABLE t1 (i2 VARCHAR(500), c2 VARCHAR(500)); +INSERT INTO t1 VALUES ('1','v'),('7','s'); +CREATE TABLE t2 (pk3 INT, i3 VARCHAR(300), c3 VARCHAR(500) PRIMARY KEY); +INSERT INTO t2 VALUES (1,'7','g'),(2,'4','s'),(3,'1','q'); + +set join_buffer_size=1024; +set join_buffer_space_limit=2048; + +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); + +set join_buffer_space_limit=262144; +EXPLAIN SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); +SELECT c3,i3, i2,c2 FROM t1 LEFT JOIN t2 ON (c3 = c2); +DROP TABLE t1,t2; + +SET @save_join_cache_level= @@join_cache_level; +SET @save_join_buffer_size= @@join_buffer_size; +SET @save_join_buffer_space_limit= @@join_buffer_space_limit; + # The following command must be the last one in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 203422f0b43..8b7deb98dc6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -111,6 +111,7 @@ static bool best_extension_by_limited_search(JOIN *join, uint prune_level, uint use_cond_selectivity); static uint determine_search_depth(JOIN* join); +static void pick_table_access_method(JOIN_TAB *tab); C_MODE_START static int join_tab_cmp(const void *dummy, const void* ptr1, const void* ptr2); static int join_tab_cmp_straight(const void *dummy, const void* ptr1, const void* ptr2); @@ -10081,6 +10082,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, j->ref.disable_cache= FALSE; j->ref.null_ref_part= NO_REF_PART; j->ref.const_ref_part_map= 0; + j->ref.not_null_keyparts= 0; keyuse=org_keyuse; store_key **ref_key= j->ref.key_copy; @@ -10173,23 +10175,12 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, } } /* not ftkey */ *ref_key=0; // end_marker + j->ref.not_null_keyparts= not_null_keyparts; if (j->type == JT_FT) DBUG_RETURN(0); - ulong key_flags= j->table->actual_key_flags(keyinfo); if (j->type == JT_CONST) j->table->const_table= 1; - else if (!((keyparts == keyinfo->user_defined_key_parts && - ( - (key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME || - /* Unique key and all keyparts are NULL rejecting */ - ((key_flags & HA_NOSAME) && keyparts == not_null_keyparts) - )) || - /* true only for extended keys */ - (keyparts > keyinfo->user_defined_key_parts && - MY_TEST(key_flags & HA_EXT_NOSAME) && - keyparts == keyinfo->ext_key_parts) - ) || - null_ref_key) + else if (!j->is_eq_ref_access()|| null_ref_key) { /* Must read with repeat */ j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF; @@ -11582,11 +11573,25 @@ void set_join_cache_denial(JOIN_TAB *join_tab) don't do join buffering for the first table in sjm nest. */ join_tab[-1].next_select= sub_select; - if (join_tab->type == JT_REF && join_tab->is_ref_for_hash_join()) + if ((join_tab->type == JT_REF || join_tab->type == JT_HASH) && + join_tab->is_ref_for_hash_join()) { join_tab->type= JT_ALL; join_tab->ref.key_parts= 0; } + + if (join_tab->type == JT_HASH && !join_tab->is_ref_for_hash_join()) + { + join_tab->type= join_tab->is_eq_ref_access() ? JT_EQ_REF : JT_REF; + pick_table_access_method(join_tab); + } + + if (join_tab->type == JT_HASH_NEXT) + { + join_tab->type = JT_NEXT; + DBUG_ASSERT(join_tab->ref.key_parts == 0); + } + join_tab->join->return_tab= join_tab; } } @@ -27954,6 +27959,42 @@ void JOIN_TAB::partial_cleanup() } +/* + @brief + Check if the access method for the table is EQ_REF access or not + + @retval + TRUE EQ_REF access + FALSE Otherwise +*/ +bool JOIN_TAB::is_eq_ref_access() +{ + + KEY *keyinfo; + if (!is_hash_join_key_no(ref.key)) + keyinfo= table->key_info + ref.key; + else + keyinfo= hj_key; + + uint keyparts= ref.key_parts; + ulong key_flags= table->actual_key_flags(keyinfo); + if ( (keyparts == keyinfo->user_defined_key_parts && + ( + (key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME || + /* Unique key and all keyparts are NULL rejecting */ + ((key_flags & HA_NOSAME) && keyparts == ref.not_null_keyparts) + ) + ) || + /* true only for extended keys */ + (keyparts > keyinfo->user_defined_key_parts && + MY_TEST(key_flags & HA_EXT_NOSAME) && + keyparts == keyinfo->ext_key_parts) + ) + return true; + return false; +} + + /** @} (end of group Query_Optimizer) */ diff --git a/sql/sql_select.h b/sql/sql_select.h index d207363a9ba..d4cdc9ad920 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -178,6 +178,11 @@ typedef struct st_table_ref */ bool disable_cache; + /* + The number of NOT NULL keyparts + */ + uint not_null_keyparts; + bool tmp_table_index_lookup_init(THD *thd, KEY *tmp_key, Item_iterator &it, bool value, uint skip= 0); bool is_access_triggered(); @@ -611,6 +616,7 @@ typedef struct st_join_table { bool hash_join_is_possible(); int make_scan_filter(); bool is_ref_for_hash_join() { return is_hash_join_key_no(ref.key); } + bool is_eq_ref_access(); KEY *get_keyinfo_by_key_no(uint key) { return (is_hash_join_key_no(key) ? hj_key : table->key_info+key);

3 2

[Commits] 315b283: Correction of the merge 10.2 into 10.3 for MDEV-23619
by IgorBabaev 29 Dec '20

29 Dec '20

revision-id: 315b2837374c3fbe599b60fe29c6795c6a8ec5f4 (mariadb-10.3.26-45-g315b283) parent(s): 043bd85a574a88856ab9c6d497e682ed06fe45e9 author: Igor Babaev committer: Igor Babaev timestamp: 2020-12-28 21:20:13 -0800 message: Correction of the merge 10.2 into 10.3 for MDEV-23619 (correction for commit 6fed6de93f120b5e311b79892e7865639e9613a4) --- mysql-test/main/cte_recursive.result | 4 ++-- sql/sql_lex.cc | 14 +++++++------- 2 files changed, 9 insertions(+), 9 deletions(-) diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 62354a6..21c6b22 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -1301,7 +1301,7 @@ select ancestors.name, ancestors.dob from ancestors; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where -6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 +6 UNION <derived3> ALL NULL NULL NULL NULL 12 5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL 3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where @@ -4029,7 +4029,7 @@ id select_type table type possible_keys key key_len ref rows Extra 3 RECURSIVE UNION t1 ALL NULL NULL NULL NULL 4 Using where 3 RECURSIVE UNION <derived2> ref key0 key0 9 test.t1.c 2 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL -4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 +4 UNION <derived2> ALL NULL NULL NULL NULL 4 with recursive r_cte as ( select * from t1 as s union diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index dc0662e..6116dee 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4712,14 +4712,14 @@ void st_select_lex::set_explain_type(bool on_the_fly) /* pos_in_table_list=NULL for e.g. post-join aggregation JOIN_TABs. */ - if (!tab->table); - else if (const TABLE_LIST *pos= tab->table->pos_in_table_list) + if (!(tab->table && tab->table->pos_in_table_list)) + continue; + TABLE_LIST *tbl= tab->table->pos_in_table_list; + if (tbl->with && tbl->with->is_recursive && + tbl->is_with_table_recursive_reference()) { - if (pos->with && pos->with->is_recursive) - { - uses_cte= true; - break; - } + uses_cte= true; + break; } } if (uses_cte)

1 0

[Commits] 7e129fb: Correction of the merge 10.2 into 10.3 for MDEV-23619
by IgorBabaev 29 Dec '20

29 Dec '20

revision-id: 7e129fbcccf5e5d68ab0eff915fe5b460492bc4d (mariadb-10.3.26-45-g7e129fb) parent(s): 043bd85a574a88856ab9c6d497e682ed06fe45e9 author: Igor Babaev committer: Igor Babaev timestamp: 2020-12-28 21:18:24 -0800 message: Correction of the merge 10.2 into 10.3 for MDEV-23619 (correction for commit 6fed6de93f120b5e311b79892e7865639e9613a4) --- mysql-test/main/cte_recursive.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 62354a6..21c6b22 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -1301,7 +1301,7 @@ select ancestors.name, ancestors.dob from ancestors; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where -6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 +6 UNION <derived3> ALL NULL NULL NULL NULL 12 5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL 3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where @@ -4029,7 +4029,7 @@ id select_type table type possible_keys key key_len ref rows Extra 3 RECURSIVE UNION t1 ALL NULL NULL NULL NULL 4 Using where 3 RECURSIVE UNION <derived2> ref key0 key0 9 test.t1.c 2 NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL -4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 +4 UNION <derived2> ALL NULL NULL NULL NULL 4 with recursive r_cte as ( select * from t1 as s union

1 0

[Commits] 540e36c: MDEV-23886 Reusing CTE inside a function fails with table doesn't exist
by IgorBabaev 26 Dec '20

26 Dec '20

revision-id: 540e36ca0dc7471374502acaae67b2182ac82060 (mariadb-10.4.11-424-g540e36c) parent(s): a6f956488c712bef3b13660584d1b905e0c676cc author: Igor Babaev committer: Igor Babaev timestamp: 2020-12-26 14:13:12 -0800 message: MDEV-23886 Reusing CTE inside a function fails with table doesn't exist Intermediate commit (not to be pushed). --- mysql-test/main/brackets.result | 14 +- mysql-test/main/cte_nonrecursive.result | 64 +++++- mysql-test/main/cte_nonrecursive.test | 63 ++++++ mysql-test/main/cte_recursive.result | 279 +++++++++++++++++++++++- mysql-test/main/cte_recursive.test | 50 +++++ sql/item_subselect.cc | 1 - sql/sp_head.cc | 3 +- sql/sql_base.cc | 47 +--- sql/sql_class.cc | 56 +++++ sql/sql_class.h | 8 +- sql/sql_cte.cc | 374 ++++++++++++++++++++++---------- sql/sql_cte.h | 87 +++++++- sql/sql_derived.cc | 2 + sql/sql_lex.cc | 17 +- sql/sql_lex.h | 25 ++- sql/sql_parse.cc | 15 +- sql/sql_prepare.cc | 3 - sql/sql_select.cc | 5 +- sql/sql_union.cc | 42 ++-- sql/sql_view.cc | 9 - sql/sql_yacc.yy | 27 ++- sql/sql_yacc_ora.yy | 16 +- sql/table.cc | 1 + sql/table.h | 37 ++++ 24 files changed, 1002 insertions(+), 243 deletions(-) diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result index dedd9a2..1da5168 100644 --- a/mysql-test/main/brackets.result +++ b/mysql-test/main/brackets.result @@ -4397,7 +4397,7 @@ with t as (select * from t1 where a <=3) select * from t; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` <= 3)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci select * from v1; a 3 @@ -4413,7 +4413,7 @@ order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3 order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3 order by `a` desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 latin1 latin1_swedish_ci select * from v1; a 4 @@ -4429,7 +4429,7 @@ order by a desc limit 3 ) (select a from t1 where a=4 union select a from t where a=7 order by a desc); show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as ((select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3) union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3) order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as ((select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) union (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3) order by `a` desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4469,7 +4469,7 @@ order by 1 desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 order by a desc; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(a) as (values (2),(1) union (values (4),(7)) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(a) as (values (2),(1) union (values (4),(7)) order by 1 desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4484,7 +4484,7 @@ order by 1 desc limit 3 ) select a from t1 where a=1 union select a from t where a=7 order by a desc; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(a) as ((values (2),(1)) union (values (4),(7) order by 1 desc) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(a) as ((values (2),(1)) union (values (4),(7) order by 1 desc) order by 1 desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4496,7 +4496,7 @@ s as (select * from t1 where a > 3) select a from t where a=1 union select a from s where a=7 order by a desc; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3), s as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4508,7 +4508,7 @@ s as (select * from t where a > 3) select a from t where a=1 union select a from s where a=7 order by a desc; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `t`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) `t` where `t`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `t`.`a` AS `a` from `t` where `t`.`a` > 3)select `t`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 1 diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index d236183..efc02db 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -571,7 +571,7 @@ with t as (select a from t1 where b >= 'c') select * from t2,t where t2.c=t.a; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`b` >= 'c')select `t2`.`c` AS `c`,`t`.`a` AS `a` from (`t2` join `t`) where `t2`.`c` = `t`.`a` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `test`.`t2`.`c` AS `c`,`t`.`a` AS `a` from (`test`.`t2` join `t`) where `test`.`t2`.`c` = `t`.`a` latin1 latin1_swedish_ci select * from v1; c a 4 4 @@ -588,7 +588,7 @@ with t as (select a, count(*) from t1 where b >= 'c' group by a) select * from t2,t where t2.c=t.a; show create view v2; View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `t1`.`a` AS `a`,count(0) AS `count(*)` from `t1` where `t1`.`b` >= 'c' group by `t1`.`a`)select `t2`.`c` AS `c`,`t`.`a` AS `a`,`t`.`count(*)` AS `count(*)` from (`t2` join `t`) where `t2`.`c` = `t`.`a` latin1 latin1_swedish_ci +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `test`.`t1`.`a` AS `a`,count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` >= 'c' group by `test`.`t1`.`a`)select `test`.`t2`.`c` AS `c`,`t`.`a` AS `a`,`t`.`count(*)` AS `count(*)` from (`test`.`t2` join `t`) where `test`.`t2`.`c` = `t`.`a` latin1 latin1_swedish_ci select * from v2; c a count(*) 4 4 2 @@ -606,7 +606,7 @@ with t(c) as (select a from t1 where b >= 'c') select * from t r1 where r1.c=4; show create view v3; View Create View character_set_client collation_connection -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t(c) as (select `t1`.`a` AS `c` from `t1` where `t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4 latin1 latin1_swedish_ci +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t(c) as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4 latin1 latin1_swedish_ci select * from v3; c 4 @@ -1349,7 +1349,7 @@ r.r_regionkey in select r_regionkey from t where r_name <> "ASIA"); show create view v; View Create View character_set_client collation_connection -v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `n`.`n_nationkey` AS `n_nationkey`,`n`.`n_name` AS `n_name`,`n`.`n_regionkey` AS `n_regionkey`,`r`.`r_regionkey` AS `r_regionkey`,`r`.`r_name` AS `r_name` from (`nation` `n` join `region` `r`) where `n`.`n_regionkey` = `r`.`r_regionkey` and `r`.`r_regionkey` in (with t as (select `region`.`r_regionkey` AS `r_regionkey`,`region`.`r_name` AS `r_name` from `region` where `region`.`r_regionkey` <= 3)select `t`.`r_regionkey` from `t` where `t`.`r_name` <> 'ASIA') latin1 latin1_swedish_ci +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `n`.`n_nationkey` AS `n_nationkey`,`n`.`n_name` AS `n_name`,`n`.`n_regionkey` AS `n_regionkey`,`r`.`r_regionkey` AS `r_regionkey`,`r`.`r_name` AS `r_name` from (`test`.`nation` `n` join `test`.`region` `r`) where `n`.`n_regionkey` = `r`.`r_regionkey` and `r`.`r_regionkey` in (with t as (select `test`.`region`.`r_regionkey` AS `r_regionkey`,`test`.`region`.`r_name` AS `r_name` from `test`.`region` where `test`.`region`.`r_regionkey` <= 3)select `t`.`r_regionkey` from `t` where `t`.`r_name` <> 'ASIA') latin1 latin1_swedish_ci select * from v; n_nationkey n_name n_regionkey r_regionkey r_name 0 ALGERIA 0 0 AFRICA @@ -1811,4 +1811,60 @@ set sql_mode="oracle"; with data as (select 1 as id) select id into @myid from data; set sql_mode= @save_sql_mode; +# +# MDEV-23886: Stored Function returning the result of a query +# that uses CTE over a table twice +# +create table t1 (c1 int); +insert into t1 values (1),(2),(6); +create function f1() returns int return +( with cte1 as (select c1 from t1) +select sum(c1) from +(select * from cte1 union all select * from cte1) dt +); +select f1(); +f1() +18 +create function f2() returns int return +( with cte1 as (select c1 from t1) +select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1 +); +select f2(); +f2() +9 +create function f3() returns int return +( with cte1 as (select c1 from t1) +select +case +when exists(select 1 from cte1 where c1 between 1 and 2) then 1 +when exists(select 1 from cte1 where c1 between 5 and 6) then 2 +else 0 +end +); +select f3(); +f3() +1 +drop function f1; +drop function f2; +drop function f3; +create table t2 (a int, b int); +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5; +select * from t2; +a b +6 6 +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | +call p1(); +select * from t2; +a b +6 6 +2 2 +drop procedure p1; +drop table t1,t2; # End of 10.4 tests diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test index 1f5b2a6..52e6d95 100644 --- a/mysql-test/main/cte_nonrecursive.test +++ b/mysql-test/main/cte_nonrecursive.test @@ -1318,4 +1318,67 @@ with data as (select 1 as id) select id into @myid from data; set sql_mode= @save_sql_mode; +--echo # +--echo # MDEV-23886: Stored Function returning the result of a query +--echo # that uses CTE over a table twice +--echo # + +create table t1 (c1 int); +insert into t1 values (1),(2),(6); + +create function f1() returns int return +( with cte1 as (select c1 from t1) + select sum(c1) from + (select * from cte1 union all select * from cte1) dt +); +select f1(); + +create function f2() returns int return +( with cte1 as (select c1 from t1) + select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1 +); +select f2(); + +create function f3() returns int return +( with cte1 as (select c1 from t1) + select + case + when exists(select 1 from cte1 where c1 between 1 and 2) then 1 + when exists(select 1 from cte1 where c1 between 5 and 6) then 2 + else 0 + end +); +select f3(); + +drop function f1; +drop function f2; +drop function f3; + +create table t2 (a int, b int); + +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5; + +select * from t2; + +delimiter |; + +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | + +delimiter ;| + +call p1(); +select * from t2; + +drop procedure p1; + +drop table t1,t2; + --echo # End of 10.4 tests + diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 7ed8a301..78e1e44 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -818,7 +818,7 @@ where p.id = a.father or p.id = a.mother select * from ancestors; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with recursive ancestors as (select `folks`.`id` AS `id`,`folks`.`name` AS `name`,`folks`.`dob` AS `dob`,`folks`.`father` AS `father`,`folks`.`mother` AS `mother` from `folks` where `folks`.`name` = 'Me' and `folks`.`dob` = '2000-01-01' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `a`) where `p`.`id` = `a`.`father` or `p`.`id` = `a`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with recursive ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' and `test`.`folks`.`dob` = '2000-01-01' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `a`) where `p`.`id` = `a`.`father` or `p`.`id` = `a`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci select * from v1; id name dob father mother 100 Me 2000-01-01 20 30 @@ -849,7 +849,7 @@ where p.id = ma.mother select * from ancestors; show create view v2; View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with recursive ancestors as (select `folks`.`id` AS `id`,`folks`.`name` AS `name`,`folks`.`dob` AS `dob`,`folks`.`father` AS `father`,`folks`.`mother` AS `mother` from `folks` where `folks`.`name` = 'Me' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `fa`) where `p`.`id` = `fa`.`father` union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `ma`) where `p`.`id` = `ma`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with recursive ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `fa`) where `p`.`id` = `fa`.`father` union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `ma`) where `p`.`id` = `ma`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci select * from v2; id name dob father mother 100 Me 2000-01-01 20 30 @@ -1301,7 +1301,7 @@ select ancestors.name, ancestors.dob from ancestors; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where -6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 +6 UNION <derived3> ALL NULL NULL NULL NULL 12 5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL 3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where @@ -4008,6 +4008,279 @@ YEAR d1 d2 DROP PROCEDURE p; DROP TABLE t1,t2,t3,t4; # +# MDEV-23619: recursive CTE used only in the second operand of UNION +# +create table t1 ( +a bigint(10) not null auto_increment, +b int(5) not null, +c bigint(10) default null, +primary key (a) +) engine myisam; +insert into t1 values +(1,3,12), (2,7,15), (3,1,3), (4,3,1); +explain with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DERIVED s ALL NULL NULL NULL NULL 4 +3 RECURSIVE UNION t1 ALL NULL NULL NULL NULL 4 Using where +3 RECURSIVE UNION <derived2> ref key0 key0 9 test.t1.c 2 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +4 UNION <derived2> ALL NULL NULL NULL NULL 4 +with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +b +0 +3 +7 +1 +3 +analyze format=json with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<union1,4>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 4, + "operation": "UNION", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union2,3>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "s", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "t1.c is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "9", + "used_key_parts": ["a"], + "ref": ["test.t1.c"], + "r_loops": 4, + "rows": 2, + "r_rows": 0.5, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + ] + } + } + } + } + } + } + ] + } + } +} +prepare stmt from "with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t"; +execute stmt; +b +0 +3 +7 +1 +3 +execute stmt; +b +0 +3 +7 +1 +3 +deallocate prepare stmt; +#checking hanging cte that uses a recursive cte +explain with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tt ALL NULL NULL NULL NULL 4 +with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +analyze format=json with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "tt", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } +} +prepare stmt from "with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt"; +execute stmt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +execute stmt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +deallocate prepare stmt; +drop table t1; +# +# MDEV-24019: query with recursive CTE when no default database is set +# +drop database test; +with recursive a as +(select 1 from dual union select * from a as r) +select * from a; +1 +1 +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3), (7), (1); +with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte; +a +3 +7 +1 +explain with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 +2 DERIVED t1 ALL NULL NULL NULL NULL 3 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 3 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +prepare stmt from "with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte"; +execute stmt; +a +3 +7 +1 +execute stmt; +a +3 +7 +1 +deallocate prepare stmt; +drop database db1; +create database test; +use test; +# # End of 10.2 tests # # diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index 94da45b..58264ba 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -2676,6 +2676,56 @@ DROP PROCEDURE p; DROP TABLE t1,t2,t3,t4; --echo # +--echo # MDEV-23619: recursive CTE used only in the second operand of UNION +--echo # + +create table t1 ( + a bigint(10) not null auto_increment, + b int(5) not null, + c bigint(10) default null, + primary key (a) +) engine myisam; +insert into t1 values + (1,3,12), (2,7,15), (3,1,3), (4,3,1); + +let $q= +with recursive r_cte as +( select * from t1 as s + union + select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; + +eval explain $q; +eval $q; +--source include/analyze-format.inc +eval analyze format=json $q; +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +--echo #checking hanging cte that uses a recursive cte +let $q1= +with h_cte as +( with recursive r_cte as + ( select * from t1 as s + union + select t1.* from t1, r_cte as r where t1.c = r.a ) + select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; + +eval explain $q1; +eval $q1; +--source include/analyze-format.inc +eval analyze format=json $q1; +eval prepare stmt from "$q1"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 513aad7..da53bf6 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1722,7 +1722,6 @@ double Item_in_subselect::val_real() As far as Item_in_subselect called only from Item_in_optimizer this method should not be used */ - DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); if (forced_const) return value; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index e91364b..032b045 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -3411,8 +3411,7 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp, Json_writer_object trace_command(thd); Json_writer_array trace_command_steps(thd, "steps"); if (open_tables) - res= check_dependencies_in_with_clauses(m_lex->with_clauses_list) || - instr->exec_open_and_lock_tables(thd, m_lex->query_tables); + res= instr->exec_open_and_lock_tables(thd, m_lex->query_tables); if (likely(!res)) { diff --git a/sql/sql_base.cc b/sql/sql_base.cc index ede62b7..a340173 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -3690,7 +3690,11 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags, if (tables->derived) { if (!tables->view) + { + if (!tables->is_derived()) + tables->set_derived(); goto end; + } /* We restore view's name and database wiped out by derived tables processing and fall back to standard open process in order to @@ -3700,35 +3704,6 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags, tables->db= tables->view_db; tables->table_name= tables->view_name; } - else if (tables->select_lex) - { - /* - Check whether 'tables' refers to a table defined in a with clause. - If so set the reference to the definition in tables->with. - */ - if (!tables->with) - tables->with= tables->select_lex->find_table_def_in_with_clauses(tables); - /* - If 'tables' is defined in a with clause set the pointer to the - specification from its definition in tables->derived. - */ - if (tables->with) - { - if (tables->is_recursive_with_table() && - !tables->is_with_table_recursive_reference()) - { - tables->with->rec_outer_references++; - With_element *with_elem= tables->with; - while ((with_elem= with_elem->get_next_mutually_recursive()) != - tables->with) - with_elem->rec_outer_references++; - } - if (tables->set_as_with_table(thd, tables->with)) - DBUG_RETURN(1); - else - goto end; - } - } if (!tables->derived && is_infoschema_db(&tables->db)) { @@ -8089,21 +8064,21 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, /* Ensure that we have access rights to all fields to be inserted. Under some circumstances, this check may be skipped. - + - If any_privileges is true, skip the check. - + - If the SELECT privilege has been found as fulfilled already for both the TABLE and TABLE_LIST objects (and both of these exist, of course), the check is skipped. - + - If the SELECT privilege has been found fulfilled for the TABLE object and the TABLE_LIST represents a derived table other than a view (see below), the check is skipped. - + - If the TABLE_LIST object represents a view, we may skip checking if the SELECT privilege has been found fulfilled for it, regardless of the TABLE object. - + - If there is no TABLE object, the test is skipped if either * the TABLE_LIST does not represent a view, or * the SELECT privilege has been found fulfilled. @@ -8114,8 +8089,8 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, */ if (!((table && tables->is_non_derived() && (table->grant.privilege & SELECT_ACL)) || - ((!tables->is_non_derived() && - (tables->grant.privilege & SELECT_ACL)))) && + ((!tables->is_non_derived() && + (tables->grant.privilege & SELECT_ACL)))) && !any_privileges) { field_iterator.set(tables); diff --git a/sql/sql_class.cc b/sql/sql_class.cc index e57196e..46c90b3 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -2804,6 +2804,62 @@ void THD::close_active_vio() #endif +/* + @brief MySQL parser used for recursive invocations + + @param old_lex The LEX structure in the state when this parser + is called recursively + @param lex The LEX structure used to parse a new SQL fragment + @param str The SQL fragment to parse + @param str_len The length of the SQL fragment to parse + @param stmt_prepare_mode true <=> when parsing a prepare statement + + @details + This function is to be used when parsing of an SQL fragment is + needed within one of the grammar rules. + + @notes + Currently the function is used only when the specification of a CTE + is parsed for the not first and not recursive references of the CTE. + + @retval false On a succesful parsing of the fragment + @retval true Otherwise +*/ + +bool THD::sql_parser(LEX *old_lex, LEX *lex, + char *str, uint str_len, bool stmt_prepare_mode) +{ + extern int MYSQLparse(THD * thd); + extern int ORAparse(THD * thd); + + bool parse_status= false; + Parser_state parser_state; + Parser_state *old_parser_state= m_parser_state; + + if (parser_state.init(this, str, str_len)) + return true; + + m_parser_state= &parser_state; + parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode; + parser_state.m_lip.multi_statements= false; + parser_state.m_lip.m_digest= NULL; + + lex->param_list= old_lex->param_list; + lex->sphead= old_lex->sphead; + lex->spname= old_lex->spname; + lex->spcont= old_lex->spcont; + lex->sp_chistics= old_lex->sp_chistics; + + parse_status= (variables.sql_mode & MODE_ORACLE) ? + ORAparse(this) : MYSQLparse(this) != 0; + + m_parser_state= old_parser_state; + + return parse_status; +} + + + struct Item_change_record: public ilink { Item **place; diff --git a/sql/sql_class.h b/sql/sql_class.h index 64cd1ed..cb78e3c 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4232,14 +4232,11 @@ class THD: public THD_count, /* this must be first */ to resolve all CTE names as we don't need this message to be thrown for any CTE references. */ - if (!lex->with_clauses_list) + if (!lex->with_cte_resolution) { my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0)); return TRUE; } - /* This will allow to throw an error later for non-CTE references */ - to->str= NULL; - to->length= 0; return FALSE; } @@ -5027,6 +5024,9 @@ class THD: public THD_count, /* this must be first */ Item *sp_prepare_func_item(Item **it_addr, uint cols= 1); bool sp_eval_expr(Field *result_field, Item **expr_item_ptr); + bool sql_parser(LEX *old_lex, LEX *lex, + char *str, uint str_len, bool stmt_prepare_mode); + }; /** A short cut for thd->get_stmt_da()->set_ok_status(). */ diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index d4b6d81..4d2293b 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1,3 +1,4 @@ + /* Copyright (c) 2016, 2017 MariaDB @@ -83,7 +84,7 @@ void st_select_lex_unit::set_with_clause(With_clause *with_cl) true on failure */ -bool check_dependencies_in_with_clauses(With_clause *with_clauses_list) +bool LEX::check_dependencies_in_with_clauses() { for (With_clause *with_clause= with_clauses_list; with_clause; @@ -99,6 +100,165 @@ bool check_dependencies_in_with_clauses(With_clause *with_clauses_list) } +bool +LEX::resolve_references_to_cte_in_hanging_cte() +{ + for (With_clause *with_clause= with_clauses_list; + with_clause; with_clause= with_clause->next_with_clause) + { + for (With_element *with_elem= with_clause->with_list.first; + with_elem; with_elem= with_elem->next) + { + if (!with_elem->is_referenced()) + { + TABLE_LIST *first_tbl= + with_elem->spec->first_select()->table_list.first; + TABLE_LIST **with_elem_end_pos= with_elem->head->tables_pos.end_pos; + if (first_tbl && resolve_references_to_cte(first_tbl, with_elem_end_pos)) + return true; + } + } + } + return false; +} + + +/** + @brief + Resolve table references to CTE from a sunchain of table references + + @param tables Points to the beginning of the subchain + @param tables_last Points to the address with the subchain barrier + + @details + The method resolves tables references to CTE from the chain of + table refereces specified by the parameters 'tables' and 'tables_last'. + It resolves the references against the CTE definition occured in a query + or the specification of a CTE whose parsing tree is represented by + this LEX structure. The method is always called right after the process + of parsing the query or of the specification of a CTE has been finished, + thus the chain of table references used in the parsed fragment has been + already built. It is assumed that parameters of the methos specify a + a subchain of this chain. + If a table reference can be potentially a table reference to a CTE and it + has not been resolved yet then the method tries to find the definition + of the CTE against which the reference can be resolved. If it succeeds + it sets the field TABLE_LIST::with to point to the found definition. + It also sets the field TABLE_LIST::derived to point to the specification + of the found CTE and sets TABLE::db.str to empty_c_string. This will + allow to handle this table reference like a reference to a derived handle. + If another table reference has been already resolved against this CTE + and this CTE is not recursive then a clone of the CTE specification is + constructed using the function With_element::clone_parsed_spec() and + TABLE_LIST::derived is set to point to this clone rather than to the + original specification. + If the method does not find a matched CTE definition in the parsed fragment + then in the case when the flag this->only_cte_resolution is set to true + it just moves to the resolution of the next table reference from the + specified subchain while in the case when this->only_cte_resolution is set + to false the method additionally sets an mdl request for this table + reference. + + @notes + The flag this->only_cte_resolution is set to true in the cases when + the failure to resolve a table reference as a CTE reference within + the fragment assiciated with this LEX structure does not imply that + this table reference cannot be resolved as such at all. + + @retval false On success: no errors reported, no memory allocations failed + @retval true Otherwise + +*/ + +bool LEX::resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last) +{ + With_element *with_elem= 0; + + for (TABLE_LIST *tbl= tables; tbl != *tables_last; tbl= tbl->next_global) + { + if (tbl->derived) + continue; + if (!tbl->db.str && !tbl->with) + tbl->with= tbl->select_lex->find_table_def_in_with_clauses(tbl); + if (!tbl->with) // no CTE matches table reference tbl + { + if (only_cte_resolution) + continue; + if (!tbl->db.str) // no database specified in table reference tbl + { + if (!thd->db.str) // no default database is set + { + my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0)); + return true; + } + if (copy_db_to(&tbl->db)) + return true; + if (!(tbl->table_options & TL_OPTION_ALIAS)) + tbl->mdl_request.init(MDL_key::TABLE, tbl->db.str, + tbl->table_name.str, + tbl->mdl_type, MDL_TRANSACTION); + tbl->mdl_request.set_type((tbl->lock_type >= TL_WRITE_ALLOW_WRITE) ? + MDL_SHARED_WRITE : MDL_SHARED_READ); + } + continue; + } + with_elem= tbl->with; + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + { + tbl->with->rec_outer_references++; + while ((with_elem= with_elem->get_next_mutually_recursive()) != + tbl->with) + with_elem->rec_outer_references++; + } + if (!with_elem->is_used_in_query || with_elem->is_recursive) + { + tbl->derived= with_elem->spec; + if (tbl->derived != tbl->select_lex->master_unit() && + !with_elem->is_recursive && + !tbl->is_with_table_recursive_reference()) + { + tbl->derived->move_as_slave(tbl->select_lex); + } + with_elem->is_used_in_query= true; + } + else + { + if (!(tbl->derived= tbl->with->clone_parsed_spec(thd->lex, tbl))) + return true; + } + tbl->db.str= empty_c_string; + tbl->db.length= 0; + tbl->schema_table= 0; + if (tbl->derived) + { + tbl->derived->first_select()->set_linkage(DERIVED_TABLE_TYPE); + tbl->select_lex->add_statistics(tbl->derived); + } + if (tbl->with->is_recursive && tbl->is_with_table_recursive_reference()) + continue; + with_elem->inc_references(); + } + return false; +} + + +bool +LEX::check_cte_dependencies_and_resolve_references() +{ + if (check_dependencies_in_with_clauses()) + return true; + if (!with_cte_resolution) + return false; + if (resolve_references_to_cte(query_tables, query_tables_last)) + return true; + if (resolve_references_to_cte_in_hanging_cte()) + return true; + return false; +} + + /** @brief Check dependencies between tables defined in this with clause @@ -137,10 +297,11 @@ bool With_clause::check_dependencies() elem != with_elem; elem= elem->next) { - if (lex_string_cmp(system_charset_info, with_elem->query_name, - elem->query_name) == 0) + if (lex_string_cmp(system_charset_info, with_elem->get_name(), + elem->get_name()) == 0) { - my_error(ER_DUP_QUERY_NAME, MYF(0), with_elem->query_name->str); + my_error(ER_DUP_QUERY_NAME, MYF(0), + with_elem->get_name_str()); return true; } } @@ -247,11 +408,12 @@ With_element *With_clause::find_table_def(TABLE_LIST *table, with_elem != barrier; with_elem= with_elem->next) { - if (my_strcasecmp(system_charset_info, with_elem->query_name->str, - table->table_name.str) == 0 && + if (my_strcasecmp(system_charset_info, with_elem->get_name_str(), + table->table_name.str) == 0 && !table->is_fqtn) { table->set_derived(); + with_elem->referenced= true; return with_elem; } } @@ -608,7 +770,7 @@ bool With_clause::check_anchors() if (elem == with_elem) { my_error(ER_RECURSIVE_WITHOUT_ANCHORS, MYF(0), - with_elem->query_name->str); + with_elem->get_name_str()); return true; } } @@ -641,7 +803,7 @@ bool With_clause::check_anchors() if (elem->work_dep_map & elem->get_elem_map()) { my_error(ER_UNACCEPTABLE_MUTUAL_RECURSION, MYF(0), - with_elem->query_name->str); + with_elem->get_name_str()); return true; } } @@ -795,9 +957,10 @@ bool With_element::set_unparsed_spec(THD *thd, @brief Create a clone of the specification for the given with table - @param thd The context of the statement containing this with element + @param old_lex The LEX structure created for the query or CTE specification + where this With_element is defined @param with_table The reference to the table defined in this element for which - the clone is created. + the clone is created. @details The method creates a clone of the specification used in this element. @@ -805,12 +968,13 @@ bool With_element::set_unparsed_spec(THD *thd, this element. The clone is created when the string with the specification saved in unparsed_spec is fed into the parser as an input string. The parsing - this string a unit object representing the specification is build. + this string a unit object representing the specification is built. A chain of all table references occurred in the specification is also formed. The method includes the new unit and its sub-unit into hierarchy of the units of the main query. I also insert the constructed chain of the table references into the chain of all table references of the main query. + The method resolves all references to CTE in the clone. @note Clones is created only for not first references to tables defined in @@ -826,99 +990,127 @@ bool With_element::set_unparsed_spec(THD *thd, NULL - otherwise */ -st_select_lex_unit *With_element::clone_parsed_spec(THD *thd, +st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, TABLE_LIST *with_table) { + THD *thd= old_lex->thd; LEX *lex; - st_select_lex_unit *res= NULL; - Query_arena backup; - Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); + st_select_lex_unit *res= NULL; if (!(lex= (LEX*) new(thd->mem_root) st_lex_local)) - { - if (arena) - thd->restore_active_arena(arena, &backup); return res; - } - LEX *old_lex= thd->lex; thd->lex= lex; bool parse_status= false; - Parser_state parser_state; - TABLE_LIST *spec_tables; - TABLE_LIST *spec_tables_tail; st_select_lex *with_select; char save_end= unparsed_spec.str[unparsed_spec.length]; ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= '\0'; - if (parser_state.init(thd, (char*) unparsed_spec.str, (unsigned int)unparsed_spec.length)) - goto err; - parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode; - parser_state.m_lip.multi_statements= false; - parser_state.m_lip.m_digest= NULL; lex_start(thd); lex->clone_spec_offset= unparsed_spec_offset; - lex->param_list= old_lex->param_list; - lex->sphead= old_lex->sphead; - lex->spname= old_lex->spname; - lex->spcont= old_lex->spcont; - lex->sp_chistics= old_lex->sp_chistics; - - lex->stmt_lex= old_lex; - parse_status= parse_sql(thd, &parser_state, 0); + lex->with_cte_resolution= true; + + /* + The specification of a CTE is to be parsed as a regular query. + At the very end of the parsing query the function + check_cte_dependencies_and_resolve_references() will be called. + It will check the dependencies between CTEs that are defined + within the query and will resolve CTE references in this query. + If a table reference is not resolved as a CTE reference within + this query it still can be resolved as a reference to a CTE defined + in the same clause as the CTE whose specification is to be parsed + or defined in an embedding CTE definition. + + Example: + with + cte1 as ( ... ), + cte2 as ([WITH ...] select ... from cte1 ...) + select ... from cte2 as r, ..., cte2 as s ... + + Here the specification of cte2 has be cloned for table reference + with alias s1. The specification contains a reference to cte1 + that is defined outside this specification. If the reference to + cte1 cannot be resolved within the specification of cte2 it's + not necessarily has to be a reference to a non-CTE table. That's + why the flag lex->only_cte_resolution has to be set to true + before parsing of the specification of cte2 invoked by this + function starts. Otherwise an mdl_lock would be requested for s + and this would not be correct + */ + + lex->only_cte_resolution= true; + + lex->stmt_lex= old_lex->stmt_lex ? old_lex->stmt_lex : old_lex; + + parse_status= thd->sql_parser(old_lex, lex, + (char*) unparsed_spec.str, + (unsigned int)unparsed_spec.length, + stmt_prepare_mode); + ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= save_end; with_select= lex->first_select_lex(); 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)) - goto err; - if (spec_tables) + /* + The global chain of TABLE_LIST objects created for the specification that + just has been parsed is added to such chain that contains the reference + to the CTE whose specification is parsed right after the TABLE_LIST object + created for the reference. + */ + if (lex->query_tables) { - if (with_table->next_global) + head->tables_pos.set_start_pos(&with_table->next_global); + head->tables_pos.set_end_pos(lex->query_tables_last); + TABLE_LIST *next_tbl= with_table->next_global; + if (next_tbl) { - spec_tables_tail->next_global= with_table->next_global; - with_table->next_global->prev_global= &spec_tables_tail->next_global; + *(lex->query_tables->prev_global= next_tbl->prev_global)= + lex->query_tables; + *(next_tbl->prev_global= lex->query_tables_last)= next_tbl; } else { - old_lex->query_tables_last= &spec_tables_tail->next_global; + *(lex->query_tables->prev_global= old_lex->query_tables_last)= + lex->query_tables; + old_lex->query_tables_last= lex->query_tables_last; } - spec_tables->prev_global= &with_table->next_global; - with_table->next_global= spec_tables; } res= &lex->unit; + /* + The unit of the specification that just has been parsed is included + as a slave of the select that contained in its from list the table + reference for which the unit has been created. + */ lex->unit.include_down(with_table->select_lex); - lex->unit.set_slave(with_select); + lex->unit.set_slave(with_select); + lex->unit.cloned_from= spec; old_lex->all_selects_list= (st_select_lex*) (lex->all_selects_list-> insert_chain_before( (st_select_lex_node **) &(old_lex->all_selects_list), with_select)); - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) + + /* + Now all references to the CTE defined outside of the cloned specification + has to be resolved. Additionally if old_lex->only_cte_resolution == false + for the table references that has not been resolved requests for mdl_locks + has to be set. + */ + lex->only_cte_resolution= old_lex->only_cte_resolution; + if (lex->resolve_references_to_cte(lex->query_tables, + lex->query_tables_last)) + { res= NULL; + goto err; + } + lex->sphead= NULL; // in order not to delete lex->sphead lex_end(lex); err: - if (arena) - thd->restore_active_arena(arena, &backup); thd->lex= old_lex; return res; } @@ -1088,58 +1280,6 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) } -/** - @brief - Set the specifying unit in this reference to a with table - - @details - The method assumes that the given element with_elem defines the table T - this table reference refers to. - If this is the first reference to T the method just sets its specification - in the field 'derived' as the unit that yields T. Otherwise the method - first creates a clone specification and sets rather this clone in this field. - - @retval - false on success - true on failure -*/ - -bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) -{ - if (table) - { - /* - This table was prematurely identified as a temporary table. - We correct it here, but it's not a nice solution in the case - when the temporary table with this name is not used anywhere - else in the query. - */ - thd->mark_tmp_table_as_free_for_reuse(table); - table= 0; - } - with= with_elem; - schema_table= NULL; - if (!with_elem->is_referenced() || with_elem->is_recursive) - { - derived= with_elem->spec; - if (derived != select_lex->master_unit() && - !with_elem->is_recursive && - !is_with_table_recursive_reference()) - { - derived->move_as_slave(select_lex); - } - } - else - { - if(!(derived= with_elem->clone_parsed_spec(thd, this))) - return true; - } - derived->first_select()->set_linkage(DERIVED_TABLE_TYPE); - select_lex->add_statistics(derived); - with_elem->inc_references(); - return false; -} - bool TABLE_LIST::is_recursive_with_table() { @@ -1240,7 +1380,7 @@ bool st_select_lex::check_unrestricted_recursive(bool only_standard_compliant) if (only_standard_compliant && with_elem->is_unrestricted()) { my_error(ER_NOT_STANDARD_COMPLIANT_RECURSIVE, - MYF(0), with_elem->query_name->str); + MYF(0), with_elem->get_name_str()); return true; } @@ -1440,7 +1580,7 @@ void With_clause::print(String *str, enum_query_type query_type) void With_element::print(String *str, enum_query_type query_type) { - str->append(query_name); + str->append(get_name()); if (column_list.elements) { List_iterator_fast<LEX_CSTRING> li(column_list); diff --git a/sql/sql_cte.h b/sql/sql_cte.h index 80d5664..abd8852 100644 --- a/sql/sql_cte.h +++ b/sql/sql_cte.h @@ -23,6 +23,38 @@ class select_unit; struct st_unit_ctxt_elem; +/** + @class With_element_head + @brief Head of the definition of a CTE table + + It contains the name of the CTE and it contains the position of the subchain + of table references used in the definition in the global chain of table + references used in the query where this definition is encountered. +*/ + +class With_element_head : public Sql_alloc +{ + /* The name of the defined CTE */ + LEX_CSTRING *query_name; + +public: + /* + The structure describing the subchain of the table references used in + the specification of the defined CTE in the global chain of table + references used in the query. The structure is fully defined only + after the CTE definition has been parsed. + */ + TABLE_CHAIN tables_pos; + + With_element_head(LEX_CSTRING *name) + : query_name(name) + { + tables_pos.set_start_pos(0); + tables_pos.set_end_pos(0); + } + friend class With_element; +}; + /** @class With_element @@ -85,9 +117,22 @@ class With_element : public Sql_alloc subqueries and specifications of other with elements). */ uint references; + + /* + true <=> this With_element is referred in the query in which the + element is defined + */ + bool referenced; + + /* + true <=> this With_element is needed for the execution of the query + in which the element is defined + */ + bool is_used_in_query; + /* Unparsed specification of the query that specifies this element. - It used to build clones of the specification if they are needed. + It's used to build clones of the specification if they are needed. */ LEX_CSTRING unparsed_spec; /* Offset of the specification in the input string */ @@ -101,10 +146,12 @@ class With_element : public Sql_alloc public: /* - The name of the table introduced by this with elememt. The name - can be used in FROM lists of the queries in the scope of the element. + Contains the name of the defined With element and the position of + the subchain of the tables references used by its definition in the + global chain of TABLE_LIST objects created for the whole query. */ - LEX_CSTRING *query_name; + With_element_head *head; + /* Optional list of column names to name the columns of the table introduced by this with element. It is used in the case when the names are not @@ -162,18 +209,27 @@ class With_element : public Sql_alloc /* List of derived tables containing recursive references to this CTE */ SQL_I_List<TABLE_LIST> derived_with_rec_ref; - With_element(LEX_CSTRING *name, + With_element(With_element_head *h, List <LEX_CSTRING> list, st_select_lex_unit *unit) : next(NULL), base_dep_map(0), derived_dep_map(0), sq_dep_map(0), work_dep_map(0), mutually_recursive(0), top_level_dep_map(0), sq_rec_ref(NULL), next_mutually_recursive(NULL), references(0), - query_name(name), column_list(list), spec(unit), + referenced(false), is_used_in_query(false), + head(h), column_list(list), spec(unit), is_recursive(false), rec_outer_references(0), with_anchor(false), level(0), rec_result(NULL) { unit->with_element= this; } + LEX_CSTRING *get_name() { return head->query_name; } + const char *get_name_str() { return get_name()->str; } + + void set_tables_start_pos(TABLE_LIST **pos) + { head->tables_pos.set_start_pos(pos); } + void set_tables_end_pos(TABLE_LIST **pos) + { head->tables_pos.set_end_pos(pos); } + bool check_dependencies_in_spec(); void check_dependencies_in_select(st_select_lex *sl, st_unit_ctxt_elem *ctxt, @@ -200,9 +256,9 @@ class With_element : public Sql_alloc bool set_unparsed_spec(THD *thd, const char *spec_start, const char *spec_end, my_ptrdiff_t spec_offset); - st_select_lex_unit *clone_parsed_spec(THD *thd, TABLE_LIST *with_table); + st_select_lex_unit *clone_parsed_spec(LEX *old_lex, TABLE_LIST *with_table); - bool is_referenced() { return references != 0; } + bool is_referenced() { return referenced; } void inc_references() { references++; } @@ -260,6 +316,12 @@ class With_element : public Sql_alloc void prepare_for_next_iteration(); friend class With_clause; + + friend + bool LEX::resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last); + friend + bool LEX::resolve_references_to_cte_in_hanging_cte(); }; const uint max_number_of_elements_in_with_clause= sizeof(table_map)*8; @@ -291,6 +353,7 @@ class With_clause : public Sql_alloc in the current statement */ With_clause *next_with_clause; + /* Set to true if dependencies between with elements have been checked */ bool dependencies_are_checked; /* @@ -338,7 +401,7 @@ class With_clause : public Sql_alloc void attach_to(st_select_lex *select_lex); With_clause *pop() { return embedding_with_clause; } - + bool check_dependencies(); bool check_anchors(); @@ -357,9 +420,11 @@ class With_clause : public Sql_alloc friend class With_element; + friend + bool LEX::check_dependencies_in_with_clauses(); + friend - bool - check_dependencies_in_with_clauses(With_clause *with_clauses_list); + bool LEX::resolve_references_to_cte_in_hanging_cte(); }; inline diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 3b31222..b37c29e 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -916,6 +916,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) #ifndef NO_EMBEDDED_ACCESS_CHECKS if (derived->is_view()) table->grant= derived->grant; +#if 1 else { DBUG_ASSERT(derived->is_derived()); @@ -924,6 +925,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) derived->grant.privilege= SELECT_ACL; } #endif +#endif /* Add new temporary table to list of open derived tables */ if (!derived->is_with_table_recursive_reference()) { diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index cbb1138..54c3d87 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -721,6 +721,8 @@ void LEX::start(THD *thd_arg) explain_json= false; context_analysis_only= 0; derived_tables= 0; + with_cte_resolution= false; + only_cte_resolution= false; safe_to_cache_query= 1; parsing_options.reset(); empty_field_list_on_rset= 0; @@ -2388,6 +2390,7 @@ void st_select_lex_unit::init_query() is_view= false; with_clause= 0; with_element= 0; + cloned_from= 0; columns_are_renamed= false; intersect_mark= NULL; with_wrapped_tvc= false; @@ -4829,9 +4832,11 @@ void st_select_lex::set_explain_type(bool on_the_fly) /* pos_in_table_list=NULL for e.g. post-join aggregation JOIN_TABs. */ - if (tab->table && tab->table->pos_in_table_list && - tab->table->pos_in_table_list->with && - tab->table->pos_in_table_list->with->is_recursive) + if (!(tab->table && tab->table->pos_in_table_list)) + continue; + TABLE_LIST *tbl= tab->table->pos_in_table_list; + if (tbl->with && tbl->with->is_recursive && + tbl->is_with_table_recursive_reference()) { uses_cte= true; break; @@ -8262,6 +8267,8 @@ bool LEX::check_main_unit_semantics() if (unit.set_nest_level(0) || unit.check_parameters(first_select_lex())) return TRUE; + if (check_cte_dependencies_and_resolve_references()) + return TRUE; return FALSE; } @@ -8944,8 +8951,8 @@ void st_select_lex::add_statistics(SELECT_LEX_UNIT *unit) bool LEX::main_select_push(bool service) { DBUG_ENTER("LEX::main_select_push"); - current_select_number= 1; - builtin_select.select_number= 1; + current_select_number= ++thd->lex->stmt_lex->current_select_number; + builtin_select.select_number= current_select_number; builtin_select.is_service_select= service; if (push_select(&builtin_select)) DBUG_RETURN(TRUE); diff --git a/sql/sql_lex.h b/sql/sql_lex.h index c8fca74..61199c0 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -917,6 +917,8 @@ class st_select_lex_unit: public st_select_lex_node { With_clause *with_clause; /* With element where this unit is used as the specification (if any) */ With_element *with_element; + /* The unit used as a CTE specification from which this unit is cloned */ + st_select_lex_unit *cloned_from; /* thread handler */ THD *thd; /* @@ -1492,7 +1494,9 @@ class st_select_lex: public st_select_lex_node } With_element *get_with_element() { - return master_unit()->with_element; + return master_unit()->cloned_from ? + master_unit()->cloned_from->with_element : + master_unit()->with_element; } With_element *find_table_def_in_with_clauses(TABLE_LIST *table); bool check_unrestricted_recursive(bool only_standard_compliant); @@ -3314,6 +3318,20 @@ struct LEX: public Query_tables_list */ uint8 derived_tables; uint8 context_analysis_only; + /* + true <=> The parsed fragment requires resolution of references to CTE + at the end of parsing. This name resolution process involves searching + for possible dependencies between CTE defined in the parsed fragment and + detecting possible recursive references. + The flag is set to true if the fragment contains CTE definitions. + */ + bool with_cte_resolution; + /* + true <=> only resolution of references to CTE are required in the parsed + fragment, no checking of dependecies between CTE is required. + This flag is used only when parsing clones of CTE specifications. + */ + bool only_cte_resolution; bool local_file; bool check_exists; bool autocommit; @@ -4541,6 +4559,11 @@ struct LEX: public Query_tables_list select_stack[0]->is_service_select); } + bool check_dependencies_in_with_clauses(); + bool resolve_references_to_cte_in_hanging_cte(); + bool check_cte_dependencies_and_resolve_references(); + bool resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last); }; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 909cb24..90e998f 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3362,9 +3362,6 @@ 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)) { @@ -8088,7 +8085,7 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, ptr->is_fqtn= TRUE; ptr->db= table->db; } - else if (lex->copy_db_to(&ptr->db)) + else if (!lex->with_cte_resolution && lex->copy_db_to(&ptr->db)) DBUG_RETURN(0); else ptr->is_fqtn= FALSE; @@ -8105,7 +8102,9 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, } ptr->table_name= table->table; - ptr->lock_type= lock_type; + ptr->lock_type= lock_type; + ptr->mdl_type= mdl_type; + ptr->table_options= table_options; ptr->updating= MY_TEST(table_options & TL_OPTION_UPDATING); /* TODO: remove TL_OPTION_FORCE_INDEX as it looks like it's not used */ ptr->force_index= MY_TEST(table_options & TL_OPTION_FORCE_INDEX); @@ -8787,8 +8786,10 @@ void st_select_lex::set_lock_for_tables(thr_lock_type lock_type, bool for_update { tables->lock_type= lock_type; tables->updating= for_update; - tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ? - MDL_SHARED_WRITE : MDL_SHARED_READ); + + if (tables->db.str && tables->db.str[0]) + tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ? + MDL_SHARED_WRITE : MDL_SHARED_READ); } DBUG_VOID_RETURN; } diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index e5b178b..25ceed0 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2303,9 +2303,6 @@ 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); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ee166c3..9b1e3f6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -13717,6 +13717,9 @@ void JOIN::join_free() for (tmp_unit= select_lex->first_inner_unit(); tmp_unit; tmp_unit= tmp_unit->next_unit()) + { + if (tmp_unit->with_element && tmp_unit->with_element->is_recursive) + continue; for (sl= tmp_unit->first_select(); sl; sl= sl->next_select()) { Item_subselect *subselect= sl->master_unit()->item; @@ -13734,7 +13737,7 @@ void JOIN::join_free() /* Can't unlock if at least one JOIN is still needed */ can_unlock= can_unlock && full_local; } - + } /* We are not using tables anymore Unlock all tables. We may be in an INSERT .... SELECT statement. diff --git a/sql/sql_union.cc b/sql/sql_union.cc index a487f4d..994e366 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1900,13 +1900,7 @@ bool st_select_lex_unit::cleanup() { DBUG_RETURN(FALSE); } - /* - When processing a PS/SP or an EXPLAIN command cleanup of a unit can - be performed immediately when the unit is reached in the cleanup - traversal initiated by the cleanup of the main unit. - */ - if (!thd->stmt_arena->is_stmt_prepare() && !thd->lex->describe && - with_element && with_element->is_recursive && union_result) + if (with_element && with_element->is_recursive && union_result) { select_union_recursive *result= with_element->rec_result; if (++result->cleanup_count == with_element->rec_outer_references) @@ -2088,27 +2082,31 @@ bool st_select_lex::cleanup() if (join) { + List_iterator<TABLE_LIST> ti(leaf_tables); + TABLE_LIST *tbl; + while ((tbl= ti++)) + { + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + { + /* + If query is killed before open_and_process_table() for tbl + is called then 'with' is already set, but 'derived' is not. + */ + st_select_lex_unit *unit= tbl->with->spec; + error|= (bool) error | (uint) unit->cleanup(); + } + } DBUG_ASSERT((st_select_lex*)join->select_lex == this); error= join->destroy(); delete join; join= 0; } - for (TABLE_LIST *tbl= get_table_list(); tbl; tbl= tbl->next_local) - { - if (tbl->is_recursive_with_table() && - !tbl->is_with_table_recursive_reference()) - { - /* - If query is killed before open_and_process_table() for tbl - is called then 'with' is already set, but 'derived' is not. - */ - st_select_lex_unit *unit= tbl->with->spec; - error|= (bool) error | (uint) unit->cleanup(); - } - } for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ; lex_unit= lex_unit->next_unit()) { + if (lex_unit->with_element && lex_unit->with_element->is_recursive) + continue; error= (bool) ((uint) error | (uint) lex_unit->cleanup()); } inner_refs_list.empty(); @@ -2128,8 +2126,12 @@ void st_select_lex::cleanup_all_joins(bool full) join->cleanup(full); for (unit= first_inner_unit(); unit; unit= unit->next_unit()) + { + if (unit->with_element && unit->with_element->is_recursive) + continue; for (sl= unit->first_select(); sl; sl= sl->next_select()) sl->cleanup_all_joins(full); + } DBUG_VOID_RETURN; } diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 0a18a85..f64872a 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -429,12 +429,6 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views, lex->link_first_table_back(view, link_to_local); view->open_type= OT_BASE_ONLY; - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - { - res= TRUE; - goto err; - } - WSREP_TO_ISOLATION_BEGIN(WSREP_MYSQL_DB, NULL, NULL); /* @@ -1411,9 +1405,6 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table, TABLE_LIST *tbl; Security_context *security_ctx= 0; - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) - goto err; - /* Check rights to run commands which show underlying tables. In the optimizer trace we would not like to show trace for diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 83323e3..f70d72d 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -762,6 +762,7 @@ Virtual_column_info *add_virtual_expression(THD *thd, Item *expr) class sp_head *sphead; class sp_name *spname; class sp_variable *spvar; + class With_element_head *with_element_head; class With_clause *with_clause; class Virtual_column_info *virtual_column; @@ -2190,7 +2191,7 @@ END_OF_INPUT %type <with_clause> with_clause -%type <lex_str_ptr> query_name +%type <with_element_head> with_element_head %type <lex_str_list> opt_with_column_list @@ -3338,7 +3339,11 @@ call: if (unlikely(Lex->call_statement_start(thd, $2))) MYSQL_YYABORT; } - opt_sp_cparam_list {} + opt_sp_cparam_list + { + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; + } ; /* CALL parameters */ @@ -4187,6 +4192,8 @@ sp_proc_stmt_return: LEX *lex= Lex; sp_head *sp= lex->sphead; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; if (unlikely(sp->m_handler->add_instr_freturn(thd, sp, lex->spcont, $3, lex)) || unlikely(sp->restore_lex(thd))) @@ -13332,6 +13339,8 @@ do: { Lex->insert_list= $3; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; } ; @@ -15494,6 +15503,7 @@ with_clause: if (unlikely(with_clause == NULL)) MYSQL_YYABORT; lex->derived_tables|= DERIVED_WITH; + lex->with_cte_resolution= true; lex->curr_with_clause= with_clause; with_clause->add_to_list(Lex->with_clauses_list_last_next); if (lex->current_select && @@ -15521,7 +15531,7 @@ with_list: with_list_element: - query_name + with_element_head opt_with_column_list { $2= new List<LEX_CSTRING> (Lex->with_column_list); @@ -15541,6 +15551,7 @@ with_list_element: if (elem->set_unparsed_spec(thd, spec_start, $7.pos(), spec_start - query_start)) MYSQL_YYABORT; + elem->set_tables_end_pos(lex->query_tables_last); } ; @@ -15567,16 +15578,18 @@ with_column_list: ; -query_name: +with_element_head: ident { - $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); - if (unlikely($$ == NULL)) + LEX_CSTRING *name= + (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); + $$= new (thd->mem_root) With_element_head(name); + if (unlikely(name == NULL || $$ == NULL)) MYSQL_YYABORT; + $$->tables_pos.set_start_pos(Lex->query_tables_last); } ; - /********************************************************************** ** Creating different items. diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index c8084f8..bd80c2e 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -240,6 +240,7 @@ void ORAerror(THD *thd, const char *s) class sp_head *sphead; class sp_name *spname; class sp_variable *spvar; + class With_element_head *with_element_head; class With_clause *with_clause; class Virtual_column_info *virtual_column; @@ -1691,7 +1692,7 @@ END_OF_INPUT %type <with_clause> with_clause -%type <lex_str_ptr> query_name +%type <with_element_head> with_element_head %type <lex_str_list> opt_with_column_list @@ -15621,6 +15622,7 @@ with_clause: if (unlikely(with_clause == NULL)) MYSQL_YYABORT; lex->derived_tables|= DERIVED_WITH; + lex->with_cte_resolution= true; lex->curr_with_clause= with_clause; with_clause->add_to_list(Lex->with_clauses_list_last_next); if (lex->current_select && @@ -15648,7 +15650,7 @@ with_list: with_list_element: - query_name + with_element_head opt_with_column_list { $2= new List<LEX_CSTRING> (Lex->with_column_list); @@ -15668,6 +15670,7 @@ with_list_element: if (elem->set_unparsed_spec(thd, spec_start, $7.pos(), spec_start - query_start)) MYSQL_YYABORT; + elem->set_tables_end_pos(lex->query_tables_last); } ; @@ -15694,12 +15697,15 @@ with_column_list: ; -query_name: +with_element_head: ident { - $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); - if (unlikely($$ == NULL)) + LEX_CSTRING *name= + (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); + $$= new (thd->mem_root) With_element_head(name); + if (unlikely(name == NULL || $$ == NULL)) MYSQL_YYABORT; + $$->tables_pos.set_start_pos(Lex->query_tables_last); } ; diff --git a/sql/table.cc b/sql/table.cc index 38777de..80bed60 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -9144,6 +9144,7 @@ void TABLE_LIST::set_lock_type(THD *thd, enum thr_lock_type lock) } } + bool TABLE_LIST::is_with_table() { return derived && derived->with_element; diff --git a/sql/table.h b/sql/table.h index 15ab359..e90aa0a 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2139,6 +2139,29 @@ struct vers_select_conds_t struct LEX; class Index_hint; + +/* + @struct TABLE_CHAIN + @brief Subchain of global chain of table references + + The structure contains a pointer to the address of the next_global + pointer to the first TABLE_LIST objectof the subchain and the address + of the next_global pointer to the element right after the last + TABLE_LIST object of the subchain. For an empty subchain both pointers + have the same value. +*/ + +struct TABLE_CHAIN +{ + TABLE_CHAIN() {} + + TABLE_LIST **start_pos; + TABLE_LIST ** end_pos; + + void set_start_pos(TABLE_LIST **pos) { start_pos= pos; } + void set_end_pos(TABLE_LIST **pos) { end_pos= pos; } +}; + struct TABLE_LIST { TABLE_LIST() {} /* Remove gcc warning */ @@ -2474,6 +2497,20 @@ struct TABLE_LIST /* call back function for asking handler about caching in query cache */ qc_engine_callback callback_func; thr_lock_type lock_type; + + /* + Two fields below are set during parsing this table reference in the cases + when the table reference can be potentially a reference to a CTE table. + In this cases the fact that the reference is a reference to a CTE or not + will be ascertained at the very end of parsing of the query when referencies + to CTE are resolved. For references to CTE and to derived tables no mdl + requests are needed while for other table references they are. If a request + is possibly postponed the info that allows to issue this request must be + saved in 'mdl_type' and 'table_options'. + */ + enum_mdl_type mdl_type; + ulong table_options; + uint outer_join; /* Which join type */ uint shared; /* Used in multi-upd */ bool updatable; /* VIEW/TABLE can be updated now */

1 0

[Commits] 3b5491c: MDEV-23886 Reusing CTE inside a function fails with table doesn't exist
by IgorBabaev 26 Dec '20

26 Dec '20

revision-id: 3b5491c8fff0eaad1644b232d48f40c9d6f80b27 (mariadb-10.4.11-424-g3b5491c) parent(s): a6f956488c712bef3b13660584d1b905e0c676cc author: Igor Babaev committer: Igor Babaev timestamp: 2020-12-26 14:10:57 -0800 message: MDEV-23886 Reusing CTE inside a function fails with table doesn't exist Intermediate commit (not to be pushed). --- mysql-test/main/brackets.result | 14 +- mysql-test/main/cte_nonrecursive.result | 64 +++++- mysql-test/main/cte_recursive.result | 279 +++++++++++++++++++++++- mysql-test/main/cte_recursive.test | 50 +++++ sql/item_subselect.cc | 1 - sql/sp_head.cc | 3 +- sql/sql_base.cc | 47 +--- sql/sql_class.cc | 56 +++++ sql/sql_class.h | 8 +- sql/sql_cte.cc | 374 ++++++++++++++++++++++---------- sql/sql_cte.h | 87 +++++++- sql/sql_derived.cc | 2 + sql/sql_lex.cc | 17 +- sql/sql_lex.h | 25 ++- sql/sql_parse.cc | 15 +- sql/sql_prepare.cc | 3 - sql/sql_select.cc | 5 +- sql/sql_union.cc | 42 ++-- sql/sql_view.cc | 9 - sql/sql_yacc.yy | 27 ++- sql/sql_yacc_ora.yy | 16 +- sql/table.cc | 1 + sql/table.h | 37 ++++ 23 files changed, 939 insertions(+), 243 deletions(-) diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result index dedd9a2..1da5168 100644 --- a/mysql-test/main/brackets.result +++ b/mysql-test/main/brackets.result @@ -4397,7 +4397,7 @@ with t as (select * from t1 where a <=3) select * from t; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` <= 3)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` <= 3)select `t`.`a` AS `a` from `t` latin1 latin1_swedish_ci select * from v1; a 3 @@ -4413,7 +4413,7 @@ order by a desc limit 3 ) select a from t1 where a=4 union select a from t where a=7; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3 union select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3 order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3 order by `a` desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 latin1 latin1_swedish_ci select * from v1; a 4 @@ -4429,7 +4429,7 @@ order by a desc limit 3 ) (select a from t1 where a=4 union select a from t where a=7 order by a desc); show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as ((select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3) union (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3) order by `a` desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as ((select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) union (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3) order by `a` desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4469,7 +4469,7 @@ order by 1 desc limit 3 ) select a from t1 where a=4 union select a from t where a=7 order by a desc; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(a) as (values (2),(1) union (values (4),(7)) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(a) as (values (2),(1) union (values (4),(7)) order by 1 desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 4 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4484,7 +4484,7 @@ order by 1 desc limit 3 ) select a from t1 where a=1 union select a from t where a=7 order by a desc; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(a) as ((values (2),(1)) union (values (4),(7) order by 1 desc) order by 1 desc limit 3)select `t1`.`a` AS `a` from `t1` where `t1`.`a` = 1 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t(a) as ((values (2),(1)) union (values (4),(7) order by 1 desc) order by 1 desc limit 3)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union select `t`.`a` AS `a` from `t` where `t`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4496,7 +4496,7 @@ s as (select * from t1 where a > 3) select a from t where a=1 union select a from s where a=7 order by a desc; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` < 3), s as (select `t1`.`a` AS `a` from `t1` where `t1`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 7 @@ -4508,7 +4508,7 @@ s as (select * from t where a > 3) select a from t where a=1 union select a from s where a=7 order by a desc; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `t`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) `t` where `t`.`a` > 3)select `t`.`a` AS `a` from `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3), s as (select `t`.`a` AS `a` from `t` where `t`.`a` > 3)select `t`.`a` AS `a` from (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3) `t` where `t`.`a` = 1 union select `s`.`a` AS `a` from `s` where `s`.`a` = 7 order by `a` desc latin1 latin1_swedish_ci select * from v1; a 1 diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index d236183..efc02db 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -571,7 +571,7 @@ with t as (select a from t1 where b >= 'c') select * from t2,t where t2.c=t.a; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `t1`.`a` AS `a` from `t1` where `t1`.`b` >= 'c')select `t2`.`c` AS `c`,`t`.`a` AS `a` from (`t2` join `t`) where `t2`.`c` = `t`.`a` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with t as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `test`.`t2`.`c` AS `c`,`t`.`a` AS `a` from (`test`.`t2` join `t`) where `test`.`t2`.`c` = `t`.`a` latin1 latin1_swedish_ci select * from v1; c a 4 4 @@ -588,7 +588,7 @@ with t as (select a, count(*) from t1 where b >= 'c' group by a) select * from t2,t where t2.c=t.a; show create view v2; View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `t1`.`a` AS `a`,count(0) AS `count(*)` from `t1` where `t1`.`b` >= 'c' group by `t1`.`a`)select `t2`.`c` AS `c`,`t`.`a` AS `a`,`t`.`count(*)` AS `count(*)` from (`t2` join `t`) where `t2`.`c` = `t`.`a` latin1 latin1_swedish_ci +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with t as (select `test`.`t1`.`a` AS `a`,count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` >= 'c' group by `test`.`t1`.`a`)select `test`.`t2`.`c` AS `c`,`t`.`a` AS `a`,`t`.`count(*)` AS `count(*)` from (`test`.`t2` join `t`) where `test`.`t2`.`c` = `t`.`a` latin1 latin1_swedish_ci select * from v2; c a count(*) 4 4 2 @@ -606,7 +606,7 @@ with t(c) as (select a from t1 where b >= 'c') select * from t r1 where r1.c=4; show create view v3; View Create View character_set_client collation_connection -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t(c) as (select `t1`.`a` AS `c` from `t1` where `t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4 latin1 latin1_swedish_ci +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS with t(c) as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c` from `t` `r1` where `r1`.`c` = 4 latin1 latin1_swedish_ci select * from v3; c 4 @@ -1349,7 +1349,7 @@ r.r_regionkey in select r_regionkey from t where r_name <> "ASIA"); show create view v; View Create View character_set_client collation_connection -v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `n`.`n_nationkey` AS `n_nationkey`,`n`.`n_name` AS `n_name`,`n`.`n_regionkey` AS `n_regionkey`,`r`.`r_regionkey` AS `r_regionkey`,`r`.`r_name` AS `r_name` from (`nation` `n` join `region` `r`) where `n`.`n_regionkey` = `r`.`r_regionkey` and `r`.`r_regionkey` in (with t as (select `region`.`r_regionkey` AS `r_regionkey`,`region`.`r_name` AS `r_name` from `region` where `region`.`r_regionkey` <= 3)select `t`.`r_regionkey` from `t` where `t`.`r_name` <> 'ASIA') latin1 latin1_swedish_ci +v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `n`.`n_nationkey` AS `n_nationkey`,`n`.`n_name` AS `n_name`,`n`.`n_regionkey` AS `n_regionkey`,`r`.`r_regionkey` AS `r_regionkey`,`r`.`r_name` AS `r_name` from (`test`.`nation` `n` join `test`.`region` `r`) where `n`.`n_regionkey` = `r`.`r_regionkey` and `r`.`r_regionkey` in (with t as (select `test`.`region`.`r_regionkey` AS `r_regionkey`,`test`.`region`.`r_name` AS `r_name` from `test`.`region` where `test`.`region`.`r_regionkey` <= 3)select `t`.`r_regionkey` from `t` where `t`.`r_name` <> 'ASIA') latin1 latin1_swedish_ci select * from v; n_nationkey n_name n_regionkey r_regionkey r_name 0 ALGERIA 0 0 AFRICA @@ -1811,4 +1811,60 @@ set sql_mode="oracle"; with data as (select 1 as id) select id into @myid from data; set sql_mode= @save_sql_mode; +# +# MDEV-23886: Stored Function returning the result of a query +# that uses CTE over a table twice +# +create table t1 (c1 int); +insert into t1 values (1),(2),(6); +create function f1() returns int return +( with cte1 as (select c1 from t1) +select sum(c1) from +(select * from cte1 union all select * from cte1) dt +); +select f1(); +f1() +18 +create function f2() returns int return +( with cte1 as (select c1 from t1) +select sum(s.c1) from cte1 as s, cte1 as t where s.c1=t.c1 +); +select f2(); +f2() +9 +create function f3() returns int return +( with cte1 as (select c1 from t1) +select +case +when exists(select 1 from cte1 where c1 between 1 and 2) then 1 +when exists(select 1 from cte1 where c1 between 5 and 6) then 2 +else 0 +end +); +select f3(); +f3() +1 +drop function f1; +drop function f2; +drop function f3; +create table t2 (a int, b int); +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 > 5; +select * from t2; +a b +6 6 +create procedure p1() +begin +insert into t2 +with cte1 as (select c1 from t1) +select * from cte1 as s, cte1 as t where s.c1=t.c1 and s.c1 <= 2 and t.c1 >= 2; +end | +call p1(); +select * from t2; +a b +6 6 +2 2 +drop procedure p1; +drop table t1,t2; # End of 10.4 tests diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 7ed8a301..78e1e44 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -818,7 +818,7 @@ where p.id = a.father or p.id = a.mother select * from ancestors; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with recursive ancestors as (select `folks`.`id` AS `id`,`folks`.`name` AS `name`,`folks`.`dob` AS `dob`,`folks`.`father` AS `father`,`folks`.`mother` AS `mother` from `folks` where `folks`.`name` = 'Me' and `folks`.`dob` = '2000-01-01' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `a`) where `p`.`id` = `a`.`father` or `p`.`id` = `a`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with recursive ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' and `test`.`folks`.`dob` = '2000-01-01' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `a`) where `p`.`id` = `a`.`father` or `p`.`id` = `a`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci select * from v1; id name dob father mother 100 Me 2000-01-01 20 30 @@ -849,7 +849,7 @@ where p.id = ma.mother select * from ancestors; show create view v2; View Create View character_set_client collation_connection -v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with recursive ancestors as (select `folks`.`id` AS `id`,`folks`.`name` AS `name`,`folks`.`dob` AS `dob`,`folks`.`father` AS `father`,`folks`.`mother` AS `mother` from `folks` where `folks`.`name` = 'Me' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `fa`) where `p`.`id` = `fa`.`father` union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`folks` `p` join `ancestors` `ma`) where `p`.`id` = `ma`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS with recursive ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `fa`) where `p`.`id` = `fa`.`father` union select `p`.`id` AS `id`,`p`.`name` AS `name`,`p`.`dob` AS `dob`,`p`.`father` AS `father`,`p`.`mother` AS `mother` from (`test`.`folks` `p` join `ancestors` `ma`) where `p`.`id` = `ma`.`mother`)select `ancestors`.`id` AS `id`,`ancestors`.`name` AS `name`,`ancestors`.`dob` AS `dob`,`ancestors`.`father` AS `father`,`ancestors`.`mother` AS `mother` from `ancestors` latin1 latin1_swedish_ci select * from v2; id name dob father mother 100 Me 2000-01-01 20 30 @@ -1301,7 +1301,7 @@ select ancestors.name, ancestors.dob from ancestors; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24 4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where -6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 +6 UNION <derived3> ALL NULL NULL NULL NULL 12 5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24 NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL 3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where @@ -4008,6 +4008,279 @@ YEAR d1 d2 DROP PROCEDURE p; DROP TABLE t1,t2,t3,t4; # +# MDEV-23619: recursive CTE used only in the second operand of UNION +# +create table t1 ( +a bigint(10) not null auto_increment, +b int(5) not null, +c bigint(10) default null, +primary key (a) +) engine myisam; +insert into t1 values +(1,3,12), (2,7,15), (3,1,3), (4,3,1); +explain with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DERIVED s ALL NULL NULL NULL NULL 4 +3 RECURSIVE UNION t1 ALL NULL NULL NULL NULL 4 Using where +3 RECURSIVE UNION <derived2> ref key0 key0 9 test.t1.c 2 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +4 UNION <derived2> ALL NULL NULL NULL NULL 4 +with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +b +0 +3 +7 +1 +3 +analyze format=json with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<union1,4>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 4, + "operation": "UNION", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union2,3>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "s", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "t1.c is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "9", + "used_key_parts": ["a"], + "ref": ["test.t1.c"], + "r_loops": 4, + "rows": 2, + "r_rows": 0.5, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + ] + } + } + } + } + } + } + ] + } + } +} +prepare stmt from "with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t"; +execute stmt; +b +0 +3 +7 +1 +3 +execute stmt; +b +0 +3 +7 +1 +3 +deallocate prepare stmt; +#checking hanging cte that uses a recursive cte +explain with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tt ALL NULL NULL NULL NULL 4 +with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +analyze format=json with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "tt", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } +} +prepare stmt from "with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt"; +execute stmt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +execute stmt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +deallocate prepare stmt; +drop table t1; +# +# MDEV-24019: query with recursive CTE when no default database is set +# +drop database test; +with recursive a as +(select 1 from dual union select * from a as r) +select * from a; +1 +1 +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3), (7), (1); +with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte; +a +3 +7 +1 +explain with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 +2 DERIVED t1 ALL NULL NULL NULL NULL 3 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 3 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +prepare stmt from "with recursive cte as +(select * from db1.t1 union select * from (select * from cte) as t) +select * from cte"; +execute stmt; +a +3 +7 +1 +execute stmt; +a +3 +7 +1 +deallocate prepare stmt; +drop database db1; +create database test; +use test; +# # End of 10.2 tests # # diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index 94da45b..58264ba 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -2676,6 +2676,56 @@ DROP PROCEDURE p; DROP TABLE t1,t2,t3,t4; --echo # +--echo # MDEV-23619: recursive CTE used only in the second operand of UNION +--echo # + +create table t1 ( + a bigint(10) not null auto_increment, + b int(5) not null, + c bigint(10) default null, + primary key (a) +) engine myisam; +insert into t1 values + (1,3,12), (2,7,15), (3,1,3), (4,3,1); + +let $q= +with recursive r_cte as +( select * from t1 as s + union + select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; + +eval explain $q; +eval $q; +--source include/analyze-format.inc +eval analyze format=json $q; +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +--echo #checking hanging cte that uses a recursive cte +let $q1= +with h_cte as +( with recursive r_cte as + ( select * from t1 as s + union + select t1.* from t1, r_cte as r where t1.c = r.a ) + select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; + +eval explain $q1; +eval $q1; +--source include/analyze-format.inc +eval analyze format=json $q1; +eval prepare stmt from "$q1"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 513aad7..da53bf6 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1722,7 +1722,6 @@ double Item_in_subselect::val_real() As far as Item_in_subselect called only from Item_in_optimizer this method should not be used */ - DBUG_ASSERT(0); DBUG_ASSERT(fixed == 1); if (forced_const) return value; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index e91364b..032b045 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -3411,8 +3411,7 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp, Json_writer_object trace_command(thd); Json_writer_array trace_command_steps(thd, "steps"); if (open_tables) - res= check_dependencies_in_with_clauses(m_lex->with_clauses_list) || - instr->exec_open_and_lock_tables(thd, m_lex->query_tables); + res= instr->exec_open_and_lock_tables(thd, m_lex->query_tables); if (likely(!res)) { diff --git a/sql/sql_base.cc b/sql/sql_base.cc index ede62b7..a340173 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -3690,7 +3690,11 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags, if (tables->derived) { if (!tables->view) + { + if (!tables->is_derived()) + tables->set_derived(); goto end; + } /* We restore view's name and database wiped out by derived tables processing and fall back to standard open process in order to @@ -3700,35 +3704,6 @@ open_and_process_table(THD *thd, TABLE_LIST *tables, uint *counter, uint flags, tables->db= tables->view_db; tables->table_name= tables->view_name; } - else if (tables->select_lex) - { - /* - Check whether 'tables' refers to a table defined in a with clause. - If so set the reference to the definition in tables->with. - */ - if (!tables->with) - tables->with= tables->select_lex->find_table_def_in_with_clauses(tables); - /* - If 'tables' is defined in a with clause set the pointer to the - specification from its definition in tables->derived. - */ - if (tables->with) - { - if (tables->is_recursive_with_table() && - !tables->is_with_table_recursive_reference()) - { - tables->with->rec_outer_references++; - With_element *with_elem= tables->with; - while ((with_elem= with_elem->get_next_mutually_recursive()) != - tables->with) - with_elem->rec_outer_references++; - } - if (tables->set_as_with_table(thd, tables->with)) - DBUG_RETURN(1); - else - goto end; - } - } if (!tables->derived && is_infoschema_db(&tables->db)) { @@ -8089,21 +8064,21 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, /* Ensure that we have access rights to all fields to be inserted. Under some circumstances, this check may be skipped. - + - If any_privileges is true, skip the check. - + - If the SELECT privilege has been found as fulfilled already for both the TABLE and TABLE_LIST objects (and both of these exist, of course), the check is skipped. - + - If the SELECT privilege has been found fulfilled for the TABLE object and the TABLE_LIST represents a derived table other than a view (see below), the check is skipped. - + - If the TABLE_LIST object represents a view, we may skip checking if the SELECT privilege has been found fulfilled for it, regardless of the TABLE object. - + - If there is no TABLE object, the test is skipped if either * the TABLE_LIST does not represent a view, or * the SELECT privilege has been found fulfilled. @@ -8114,8 +8089,8 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, */ if (!((table && tables->is_non_derived() && (table->grant.privilege & SELECT_ACL)) || - ((!tables->is_non_derived() && - (tables->grant.privilege & SELECT_ACL)))) && + ((!tables->is_non_derived() && + (tables->grant.privilege & SELECT_ACL)))) && !any_privileges) { field_iterator.set(tables); diff --git a/sql/sql_class.cc b/sql/sql_class.cc index e57196e..46c90b3 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -2804,6 +2804,62 @@ void THD::close_active_vio() #endif +/* + @brief MySQL parser used for recursive invocations + + @param old_lex The LEX structure in the state when this parser + is called recursively + @param lex The LEX structure used to parse a new SQL fragment + @param str The SQL fragment to parse + @param str_len The length of the SQL fragment to parse + @param stmt_prepare_mode true <=> when parsing a prepare statement + + @details + This function is to be used when parsing of an SQL fragment is + needed within one of the grammar rules. + + @notes + Currently the function is used only when the specification of a CTE + is parsed for the not first and not recursive references of the CTE. + + @retval false On a succesful parsing of the fragment + @retval true Otherwise +*/ + +bool THD::sql_parser(LEX *old_lex, LEX *lex, + char *str, uint str_len, bool stmt_prepare_mode) +{ + extern int MYSQLparse(THD * thd); + extern int ORAparse(THD * thd); + + bool parse_status= false; + Parser_state parser_state; + Parser_state *old_parser_state= m_parser_state; + + if (parser_state.init(this, str, str_len)) + return true; + + m_parser_state= &parser_state; + parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode; + parser_state.m_lip.multi_statements= false; + parser_state.m_lip.m_digest= NULL; + + lex->param_list= old_lex->param_list; + lex->sphead= old_lex->sphead; + lex->spname= old_lex->spname; + lex->spcont= old_lex->spcont; + lex->sp_chistics= old_lex->sp_chistics; + + parse_status= (variables.sql_mode & MODE_ORACLE) ? + ORAparse(this) : MYSQLparse(this) != 0; + + m_parser_state= old_parser_state; + + return parse_status; +} + + + struct Item_change_record: public ilink { Item **place; diff --git a/sql/sql_class.h b/sql/sql_class.h index 64cd1ed..cb78e3c 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4232,14 +4232,11 @@ class THD: public THD_count, /* this must be first */ to resolve all CTE names as we don't need this message to be thrown for any CTE references. */ - if (!lex->with_clauses_list) + if (!lex->with_cte_resolution) { my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0)); return TRUE; } - /* This will allow to throw an error later for non-CTE references */ - to->str= NULL; - to->length= 0; return FALSE; } @@ -5027,6 +5024,9 @@ class THD: public THD_count, /* this must be first */ Item *sp_prepare_func_item(Item **it_addr, uint cols= 1); bool sp_eval_expr(Field *result_field, Item **expr_item_ptr); + bool sql_parser(LEX *old_lex, LEX *lex, + char *str, uint str_len, bool stmt_prepare_mode); + }; /** A short cut for thd->get_stmt_da()->set_ok_status(). */ diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index d4b6d81..4d2293b 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1,3 +1,4 @@ + /* Copyright (c) 2016, 2017 MariaDB @@ -83,7 +84,7 @@ void st_select_lex_unit::set_with_clause(With_clause *with_cl) true on failure */ -bool check_dependencies_in_with_clauses(With_clause *with_clauses_list) +bool LEX::check_dependencies_in_with_clauses() { for (With_clause *with_clause= with_clauses_list; with_clause; @@ -99,6 +100,165 @@ bool check_dependencies_in_with_clauses(With_clause *with_clauses_list) } +bool +LEX::resolve_references_to_cte_in_hanging_cte() +{ + for (With_clause *with_clause= with_clauses_list; + with_clause; with_clause= with_clause->next_with_clause) + { + for (With_element *with_elem= with_clause->with_list.first; + with_elem; with_elem= with_elem->next) + { + if (!with_elem->is_referenced()) + { + TABLE_LIST *first_tbl= + with_elem->spec->first_select()->table_list.first; + TABLE_LIST **with_elem_end_pos= with_elem->head->tables_pos.end_pos; + if (first_tbl && resolve_references_to_cte(first_tbl, with_elem_end_pos)) + return true; + } + } + } + return false; +} + + +/** + @brief + Resolve table references to CTE from a sunchain of table references + + @param tables Points to the beginning of the subchain + @param tables_last Points to the address with the subchain barrier + + @details + The method resolves tables references to CTE from the chain of + table refereces specified by the parameters 'tables' and 'tables_last'. + It resolves the references against the CTE definition occured in a query + or the specification of a CTE whose parsing tree is represented by + this LEX structure. The method is always called right after the process + of parsing the query or of the specification of a CTE has been finished, + thus the chain of table references used in the parsed fragment has been + already built. It is assumed that parameters of the methos specify a + a subchain of this chain. + If a table reference can be potentially a table reference to a CTE and it + has not been resolved yet then the method tries to find the definition + of the CTE against which the reference can be resolved. If it succeeds + it sets the field TABLE_LIST::with to point to the found definition. + It also sets the field TABLE_LIST::derived to point to the specification + of the found CTE and sets TABLE::db.str to empty_c_string. This will + allow to handle this table reference like a reference to a derived handle. + If another table reference has been already resolved against this CTE + and this CTE is not recursive then a clone of the CTE specification is + constructed using the function With_element::clone_parsed_spec() and + TABLE_LIST::derived is set to point to this clone rather than to the + original specification. + If the method does not find a matched CTE definition in the parsed fragment + then in the case when the flag this->only_cte_resolution is set to true + it just moves to the resolution of the next table reference from the + specified subchain while in the case when this->only_cte_resolution is set + to false the method additionally sets an mdl request for this table + reference. + + @notes + The flag this->only_cte_resolution is set to true in the cases when + the failure to resolve a table reference as a CTE reference within + the fragment assiciated with this LEX structure does not imply that + this table reference cannot be resolved as such at all. + + @retval false On success: no errors reported, no memory allocations failed + @retval true Otherwise + +*/ + +bool LEX::resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last) +{ + With_element *with_elem= 0; + + for (TABLE_LIST *tbl= tables; tbl != *tables_last; tbl= tbl->next_global) + { + if (tbl->derived) + continue; + if (!tbl->db.str && !tbl->with) + tbl->with= tbl->select_lex->find_table_def_in_with_clauses(tbl); + if (!tbl->with) // no CTE matches table reference tbl + { + if (only_cte_resolution) + continue; + if (!tbl->db.str) // no database specified in table reference tbl + { + if (!thd->db.str) // no default database is set + { + my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0)); + return true; + } + if (copy_db_to(&tbl->db)) + return true; + if (!(tbl->table_options & TL_OPTION_ALIAS)) + tbl->mdl_request.init(MDL_key::TABLE, tbl->db.str, + tbl->table_name.str, + tbl->mdl_type, MDL_TRANSACTION); + tbl->mdl_request.set_type((tbl->lock_type >= TL_WRITE_ALLOW_WRITE) ? + MDL_SHARED_WRITE : MDL_SHARED_READ); + } + continue; + } + with_elem= tbl->with; + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + { + tbl->with->rec_outer_references++; + while ((with_elem= with_elem->get_next_mutually_recursive()) != + tbl->with) + with_elem->rec_outer_references++; + } + if (!with_elem->is_used_in_query || with_elem->is_recursive) + { + tbl->derived= with_elem->spec; + if (tbl->derived != tbl->select_lex->master_unit() && + !with_elem->is_recursive && + !tbl->is_with_table_recursive_reference()) + { + tbl->derived->move_as_slave(tbl->select_lex); + } + with_elem->is_used_in_query= true; + } + else + { + if (!(tbl->derived= tbl->with->clone_parsed_spec(thd->lex, tbl))) + return true; + } + tbl->db.str= empty_c_string; + tbl->db.length= 0; + tbl->schema_table= 0; + if (tbl->derived) + { + tbl->derived->first_select()->set_linkage(DERIVED_TABLE_TYPE); + tbl->select_lex->add_statistics(tbl->derived); + } + if (tbl->with->is_recursive && tbl->is_with_table_recursive_reference()) + continue; + with_elem->inc_references(); + } + return false; +} + + +bool +LEX::check_cte_dependencies_and_resolve_references() +{ + if (check_dependencies_in_with_clauses()) + return true; + if (!with_cte_resolution) + return false; + if (resolve_references_to_cte(query_tables, query_tables_last)) + return true; + if (resolve_references_to_cte_in_hanging_cte()) + return true; + return false; +} + + /** @brief Check dependencies between tables defined in this with clause @@ -137,10 +297,11 @@ bool With_clause::check_dependencies() elem != with_elem; elem= elem->next) { - if (lex_string_cmp(system_charset_info, with_elem->query_name, - elem->query_name) == 0) + if (lex_string_cmp(system_charset_info, with_elem->get_name(), + elem->get_name()) == 0) { - my_error(ER_DUP_QUERY_NAME, MYF(0), with_elem->query_name->str); + my_error(ER_DUP_QUERY_NAME, MYF(0), + with_elem->get_name_str()); return true; } } @@ -247,11 +408,12 @@ With_element *With_clause::find_table_def(TABLE_LIST *table, with_elem != barrier; with_elem= with_elem->next) { - if (my_strcasecmp(system_charset_info, with_elem->query_name->str, - table->table_name.str) == 0 && + if (my_strcasecmp(system_charset_info, with_elem->get_name_str(), + table->table_name.str) == 0 && !table->is_fqtn) { table->set_derived(); + with_elem->referenced= true; return with_elem; } } @@ -608,7 +770,7 @@ bool With_clause::check_anchors() if (elem == with_elem) { my_error(ER_RECURSIVE_WITHOUT_ANCHORS, MYF(0), - with_elem->query_name->str); + with_elem->get_name_str()); return true; } } @@ -641,7 +803,7 @@ bool With_clause::check_anchors() if (elem->work_dep_map & elem->get_elem_map()) { my_error(ER_UNACCEPTABLE_MUTUAL_RECURSION, MYF(0), - with_elem->query_name->str); + with_elem->get_name_str()); return true; } } @@ -795,9 +957,10 @@ bool With_element::set_unparsed_spec(THD *thd, @brief Create a clone of the specification for the given with table - @param thd The context of the statement containing this with element + @param old_lex The LEX structure created for the query or CTE specification + where this With_element is defined @param with_table The reference to the table defined in this element for which - the clone is created. + the clone is created. @details The method creates a clone of the specification used in this element. @@ -805,12 +968,13 @@ bool With_element::set_unparsed_spec(THD *thd, this element. The clone is created when the string with the specification saved in unparsed_spec is fed into the parser as an input string. The parsing - this string a unit object representing the specification is build. + this string a unit object representing the specification is built. A chain of all table references occurred in the specification is also formed. The method includes the new unit and its sub-unit into hierarchy of the units of the main query. I also insert the constructed chain of the table references into the chain of all table references of the main query. + The method resolves all references to CTE in the clone. @note Clones is created only for not first references to tables defined in @@ -826,99 +990,127 @@ bool With_element::set_unparsed_spec(THD *thd, NULL - otherwise */ -st_select_lex_unit *With_element::clone_parsed_spec(THD *thd, +st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, TABLE_LIST *with_table) { + THD *thd= old_lex->thd; LEX *lex; - st_select_lex_unit *res= NULL; - Query_arena backup; - Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); + st_select_lex_unit *res= NULL; if (!(lex= (LEX*) new(thd->mem_root) st_lex_local)) - { - if (arena) - thd->restore_active_arena(arena, &backup); return res; - } - LEX *old_lex= thd->lex; thd->lex= lex; bool parse_status= false; - Parser_state parser_state; - TABLE_LIST *spec_tables; - TABLE_LIST *spec_tables_tail; st_select_lex *with_select; char save_end= unparsed_spec.str[unparsed_spec.length]; ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= '\0'; - if (parser_state.init(thd, (char*) unparsed_spec.str, (unsigned int)unparsed_spec.length)) - goto err; - parser_state.m_lip.stmt_prepare_mode= stmt_prepare_mode; - parser_state.m_lip.multi_statements= false; - parser_state.m_lip.m_digest= NULL; lex_start(thd); lex->clone_spec_offset= unparsed_spec_offset; - lex->param_list= old_lex->param_list; - lex->sphead= old_lex->sphead; - lex->spname= old_lex->spname; - lex->spcont= old_lex->spcont; - lex->sp_chistics= old_lex->sp_chistics; - - lex->stmt_lex= old_lex; - parse_status= parse_sql(thd, &parser_state, 0); + lex->with_cte_resolution= true; + + /* + The specification of a CTE is to be parsed as a regular query. + At the very end of the parsing query the function + check_cte_dependencies_and_resolve_references() will be called. + It will check the dependencies between CTEs that are defined + within the query and will resolve CTE references in this query. + If a table reference is not resolved as a CTE reference within + this query it still can be resolved as a reference to a CTE defined + in the same clause as the CTE whose specification is to be parsed + or defined in an embedding CTE definition. + + Example: + with + cte1 as ( ... ), + cte2 as ([WITH ...] select ... from cte1 ...) + select ... from cte2 as r, ..., cte2 as s ... + + Here the specification of cte2 has be cloned for table reference + with alias s1. The specification contains a reference to cte1 + that is defined outside this specification. If the reference to + cte1 cannot be resolved within the specification of cte2 it's + not necessarily has to be a reference to a non-CTE table. That's + why the flag lex->only_cte_resolution has to be set to true + before parsing of the specification of cte2 invoked by this + function starts. Otherwise an mdl_lock would be requested for s + and this would not be correct + */ + + lex->only_cte_resolution= true; + + lex->stmt_lex= old_lex->stmt_lex ? old_lex->stmt_lex : old_lex; + + parse_status= thd->sql_parser(old_lex, lex, + (char*) unparsed_spec.str, + (unsigned int)unparsed_spec.length, + stmt_prepare_mode); + ((char*) &unparsed_spec.str[unparsed_spec.length])[0]= save_end; with_select= lex->first_select_lex(); 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)) - goto err; - if (spec_tables) + /* + The global chain of TABLE_LIST objects created for the specification that + just has been parsed is added to such chain that contains the reference + to the CTE whose specification is parsed right after the TABLE_LIST object + created for the reference. + */ + if (lex->query_tables) { - if (with_table->next_global) + head->tables_pos.set_start_pos(&with_table->next_global); + head->tables_pos.set_end_pos(lex->query_tables_last); + TABLE_LIST *next_tbl= with_table->next_global; + if (next_tbl) { - spec_tables_tail->next_global= with_table->next_global; - with_table->next_global->prev_global= &spec_tables_tail->next_global; + *(lex->query_tables->prev_global= next_tbl->prev_global)= + lex->query_tables; + *(next_tbl->prev_global= lex->query_tables_last)= next_tbl; } else { - old_lex->query_tables_last= &spec_tables_tail->next_global; + *(lex->query_tables->prev_global= old_lex->query_tables_last)= + lex->query_tables; + old_lex->query_tables_last= lex->query_tables_last; } - spec_tables->prev_global= &with_table->next_global; - with_table->next_global= spec_tables; } res= &lex->unit; + /* + The unit of the specification that just has been parsed is included + as a slave of the select that contained in its from list the table + reference for which the unit has been created. + */ lex->unit.include_down(with_table->select_lex); - lex->unit.set_slave(with_select); + lex->unit.set_slave(with_select); + lex->unit.cloned_from= spec; old_lex->all_selects_list= (st_select_lex*) (lex->all_selects_list-> insert_chain_before( (st_select_lex_node **) &(old_lex->all_selects_list), with_select)); - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) + + /* + Now all references to the CTE defined outside of the cloned specification + has to be resolved. Additionally if old_lex->only_cte_resolution == false + for the table references that has not been resolved requests for mdl_locks + has to be set. + */ + lex->only_cte_resolution= old_lex->only_cte_resolution; + if (lex->resolve_references_to_cte(lex->query_tables, + lex->query_tables_last)) + { res= NULL; + goto err; + } + lex->sphead= NULL; // in order not to delete lex->sphead lex_end(lex); err: - if (arena) - thd->restore_active_arena(arena, &backup); thd->lex= old_lex; return res; } @@ -1088,58 +1280,6 @@ With_element *st_select_lex::find_table_def_in_with_clauses(TABLE_LIST *table) } -/** - @brief - Set the specifying unit in this reference to a with table - - @details - The method assumes that the given element with_elem defines the table T - this table reference refers to. - If this is the first reference to T the method just sets its specification - in the field 'derived' as the unit that yields T. Otherwise the method - first creates a clone specification and sets rather this clone in this field. - - @retval - false on success - true on failure -*/ - -bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) -{ - if (table) - { - /* - This table was prematurely identified as a temporary table. - We correct it here, but it's not a nice solution in the case - when the temporary table with this name is not used anywhere - else in the query. - */ - thd->mark_tmp_table_as_free_for_reuse(table); - table= 0; - } - with= with_elem; - schema_table= NULL; - if (!with_elem->is_referenced() || with_elem->is_recursive) - { - derived= with_elem->spec; - if (derived != select_lex->master_unit() && - !with_elem->is_recursive && - !is_with_table_recursive_reference()) - { - derived->move_as_slave(select_lex); - } - } - else - { - if(!(derived= with_elem->clone_parsed_spec(thd, this))) - return true; - } - derived->first_select()->set_linkage(DERIVED_TABLE_TYPE); - select_lex->add_statistics(derived); - with_elem->inc_references(); - return false; -} - bool TABLE_LIST::is_recursive_with_table() { @@ -1240,7 +1380,7 @@ bool st_select_lex::check_unrestricted_recursive(bool only_standard_compliant) if (only_standard_compliant && with_elem->is_unrestricted()) { my_error(ER_NOT_STANDARD_COMPLIANT_RECURSIVE, - MYF(0), with_elem->query_name->str); + MYF(0), with_elem->get_name_str()); return true; } @@ -1440,7 +1580,7 @@ void With_clause::print(String *str, enum_query_type query_type) void With_element::print(String *str, enum_query_type query_type) { - str->append(query_name); + str->append(get_name()); if (column_list.elements) { List_iterator_fast<LEX_CSTRING> li(column_list); diff --git a/sql/sql_cte.h b/sql/sql_cte.h index 80d5664..abd8852 100644 --- a/sql/sql_cte.h +++ b/sql/sql_cte.h @@ -23,6 +23,38 @@ class select_unit; struct st_unit_ctxt_elem; +/** + @class With_element_head + @brief Head of the definition of a CTE table + + It contains the name of the CTE and it contains the position of the subchain + of table references used in the definition in the global chain of table + references used in the query where this definition is encountered. +*/ + +class With_element_head : public Sql_alloc +{ + /* The name of the defined CTE */ + LEX_CSTRING *query_name; + +public: + /* + The structure describing the subchain of the table references used in + the specification of the defined CTE in the global chain of table + references used in the query. The structure is fully defined only + after the CTE definition has been parsed. + */ + TABLE_CHAIN tables_pos; + + With_element_head(LEX_CSTRING *name) + : query_name(name) + { + tables_pos.set_start_pos(0); + tables_pos.set_end_pos(0); + } + friend class With_element; +}; + /** @class With_element @@ -85,9 +117,22 @@ class With_element : public Sql_alloc subqueries and specifications of other with elements). */ uint references; + + /* + true <=> this With_element is referred in the query in which the + element is defined + */ + bool referenced; + + /* + true <=> this With_element is needed for the execution of the query + in which the element is defined + */ + bool is_used_in_query; + /* Unparsed specification of the query that specifies this element. - It used to build clones of the specification if they are needed. + It's used to build clones of the specification if they are needed. */ LEX_CSTRING unparsed_spec; /* Offset of the specification in the input string */ @@ -101,10 +146,12 @@ class With_element : public Sql_alloc public: /* - The name of the table introduced by this with elememt. The name - can be used in FROM lists of the queries in the scope of the element. + Contains the name of the defined With element and the position of + the subchain of the tables references used by its definition in the + global chain of TABLE_LIST objects created for the whole query. */ - LEX_CSTRING *query_name; + With_element_head *head; + /* Optional list of column names to name the columns of the table introduced by this with element. It is used in the case when the names are not @@ -162,18 +209,27 @@ class With_element : public Sql_alloc /* List of derived tables containing recursive references to this CTE */ SQL_I_List<TABLE_LIST> derived_with_rec_ref; - With_element(LEX_CSTRING *name, + With_element(With_element_head *h, List <LEX_CSTRING> list, st_select_lex_unit *unit) : next(NULL), base_dep_map(0), derived_dep_map(0), sq_dep_map(0), work_dep_map(0), mutually_recursive(0), top_level_dep_map(0), sq_rec_ref(NULL), next_mutually_recursive(NULL), references(0), - query_name(name), column_list(list), spec(unit), + referenced(false), is_used_in_query(false), + head(h), column_list(list), spec(unit), is_recursive(false), rec_outer_references(0), with_anchor(false), level(0), rec_result(NULL) { unit->with_element= this; } + LEX_CSTRING *get_name() { return head->query_name; } + const char *get_name_str() { return get_name()->str; } + + void set_tables_start_pos(TABLE_LIST **pos) + { head->tables_pos.set_start_pos(pos); } + void set_tables_end_pos(TABLE_LIST **pos) + { head->tables_pos.set_end_pos(pos); } + bool check_dependencies_in_spec(); void check_dependencies_in_select(st_select_lex *sl, st_unit_ctxt_elem *ctxt, @@ -200,9 +256,9 @@ class With_element : public Sql_alloc bool set_unparsed_spec(THD *thd, const char *spec_start, const char *spec_end, my_ptrdiff_t spec_offset); - st_select_lex_unit *clone_parsed_spec(THD *thd, TABLE_LIST *with_table); + st_select_lex_unit *clone_parsed_spec(LEX *old_lex, TABLE_LIST *with_table); - bool is_referenced() { return references != 0; } + bool is_referenced() { return referenced; } void inc_references() { references++; } @@ -260,6 +316,12 @@ class With_element : public Sql_alloc void prepare_for_next_iteration(); friend class With_clause; + + friend + bool LEX::resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last); + friend + bool LEX::resolve_references_to_cte_in_hanging_cte(); }; const uint max_number_of_elements_in_with_clause= sizeof(table_map)*8; @@ -291,6 +353,7 @@ class With_clause : public Sql_alloc in the current statement */ With_clause *next_with_clause; + /* Set to true if dependencies between with elements have been checked */ bool dependencies_are_checked; /* @@ -338,7 +401,7 @@ class With_clause : public Sql_alloc void attach_to(st_select_lex *select_lex); With_clause *pop() { return embedding_with_clause; } - + bool check_dependencies(); bool check_anchors(); @@ -357,9 +420,11 @@ class With_clause : public Sql_alloc friend class With_element; + friend + bool LEX::check_dependencies_in_with_clauses(); + friend - bool - check_dependencies_in_with_clauses(With_clause *with_clauses_list); + bool LEX::resolve_references_to_cte_in_hanging_cte(); }; inline diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 3b31222..b37c29e 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -916,6 +916,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) #ifndef NO_EMBEDDED_ACCESS_CHECKS if (derived->is_view()) table->grant= derived->grant; +#if 1 else { DBUG_ASSERT(derived->is_derived()); @@ -924,6 +925,7 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) derived->grant.privilege= SELECT_ACL; } #endif +#endif /* Add new temporary table to list of open derived tables */ if (!derived->is_with_table_recursive_reference()) { diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index cbb1138..54c3d87 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -721,6 +721,8 @@ void LEX::start(THD *thd_arg) explain_json= false; context_analysis_only= 0; derived_tables= 0; + with_cte_resolution= false; + only_cte_resolution= false; safe_to_cache_query= 1; parsing_options.reset(); empty_field_list_on_rset= 0; @@ -2388,6 +2390,7 @@ void st_select_lex_unit::init_query() is_view= false; with_clause= 0; with_element= 0; + cloned_from= 0; columns_are_renamed= false; intersect_mark= NULL; with_wrapped_tvc= false; @@ -4829,9 +4832,11 @@ void st_select_lex::set_explain_type(bool on_the_fly) /* pos_in_table_list=NULL for e.g. post-join aggregation JOIN_TABs. */ - if (tab->table && tab->table->pos_in_table_list && - tab->table->pos_in_table_list->with && - tab->table->pos_in_table_list->with->is_recursive) + if (!(tab->table && tab->table->pos_in_table_list)) + continue; + TABLE_LIST *tbl= tab->table->pos_in_table_list; + if (tbl->with && tbl->with->is_recursive && + tbl->is_with_table_recursive_reference()) { uses_cte= true; break; @@ -8262,6 +8267,8 @@ bool LEX::check_main_unit_semantics() if (unit.set_nest_level(0) || unit.check_parameters(first_select_lex())) return TRUE; + if (check_cte_dependencies_and_resolve_references()) + return TRUE; return FALSE; } @@ -8944,8 +8951,8 @@ void st_select_lex::add_statistics(SELECT_LEX_UNIT *unit) bool LEX::main_select_push(bool service) { DBUG_ENTER("LEX::main_select_push"); - current_select_number= 1; - builtin_select.select_number= 1; + current_select_number= ++thd->lex->stmt_lex->current_select_number; + builtin_select.select_number= current_select_number; builtin_select.is_service_select= service; if (push_select(&builtin_select)) DBUG_RETURN(TRUE); diff --git a/sql/sql_lex.h b/sql/sql_lex.h index c8fca74..61199c0 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -917,6 +917,8 @@ class st_select_lex_unit: public st_select_lex_node { With_clause *with_clause; /* With element where this unit is used as the specification (if any) */ With_element *with_element; + /* The unit used as a CTE specification from which this unit is cloned */ + st_select_lex_unit *cloned_from; /* thread handler */ THD *thd; /* @@ -1492,7 +1494,9 @@ class st_select_lex: public st_select_lex_node } With_element *get_with_element() { - return master_unit()->with_element; + return master_unit()->cloned_from ? + master_unit()->cloned_from->with_element : + master_unit()->with_element; } With_element *find_table_def_in_with_clauses(TABLE_LIST *table); bool check_unrestricted_recursive(bool only_standard_compliant); @@ -3314,6 +3318,20 @@ struct LEX: public Query_tables_list */ uint8 derived_tables; uint8 context_analysis_only; + /* + true <=> The parsed fragment requires resolution of references to CTE + at the end of parsing. This name resolution process involves searching + for possible dependencies between CTE defined in the parsed fragment and + detecting possible recursive references. + The flag is set to true if the fragment contains CTE definitions. + */ + bool with_cte_resolution; + /* + true <=> only resolution of references to CTE are required in the parsed + fragment, no checking of dependecies between CTE is required. + This flag is used only when parsing clones of CTE specifications. + */ + bool only_cte_resolution; bool local_file; bool check_exists; bool autocommit; @@ -4541,6 +4559,11 @@ struct LEX: public Query_tables_list select_stack[0]->is_service_select); } + bool check_dependencies_in_with_clauses(); + bool resolve_references_to_cte_in_hanging_cte(); + bool check_cte_dependencies_and_resolve_references(); + bool resolve_references_to_cte(TABLE_LIST *tables, + TABLE_LIST **tables_last); }; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 909cb24..90e998f 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3362,9 +3362,6 @@ 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)) { @@ -8088,7 +8085,7 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, ptr->is_fqtn= TRUE; ptr->db= table->db; } - else if (lex->copy_db_to(&ptr->db)) + else if (!lex->with_cte_resolution && lex->copy_db_to(&ptr->db)) DBUG_RETURN(0); else ptr->is_fqtn= FALSE; @@ -8105,7 +8102,9 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, } ptr->table_name= table->table; - ptr->lock_type= lock_type; + ptr->lock_type= lock_type; + ptr->mdl_type= mdl_type; + ptr->table_options= table_options; ptr->updating= MY_TEST(table_options & TL_OPTION_UPDATING); /* TODO: remove TL_OPTION_FORCE_INDEX as it looks like it's not used */ ptr->force_index= MY_TEST(table_options & TL_OPTION_FORCE_INDEX); @@ -8787,8 +8786,10 @@ void st_select_lex::set_lock_for_tables(thr_lock_type lock_type, bool for_update { tables->lock_type= lock_type; tables->updating= for_update; - tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ? - MDL_SHARED_WRITE : MDL_SHARED_READ); + + if (tables->db.str && tables->db.str[0]) + tables->mdl_request.set_type((lock_type >= TL_WRITE_ALLOW_WRITE) ? + MDL_SHARED_WRITE : MDL_SHARED_READ); } DBUG_VOID_RETURN; } diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index e5b178b..25ceed0 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2303,9 +2303,6 @@ 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); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index ee166c3..9b1e3f6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -13717,6 +13717,9 @@ void JOIN::join_free() for (tmp_unit= select_lex->first_inner_unit(); tmp_unit; tmp_unit= tmp_unit->next_unit()) + { + if (tmp_unit->with_element && tmp_unit->with_element->is_recursive) + continue; for (sl= tmp_unit->first_select(); sl; sl= sl->next_select()) { Item_subselect *subselect= sl->master_unit()->item; @@ -13734,7 +13737,7 @@ void JOIN::join_free() /* Can't unlock if at least one JOIN is still needed */ can_unlock= can_unlock && full_local; } - + } /* We are not using tables anymore Unlock all tables. We may be in an INSERT .... SELECT statement. diff --git a/sql/sql_union.cc b/sql/sql_union.cc index a487f4d..994e366 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1900,13 +1900,7 @@ bool st_select_lex_unit::cleanup() { DBUG_RETURN(FALSE); } - /* - When processing a PS/SP or an EXPLAIN command cleanup of a unit can - be performed immediately when the unit is reached in the cleanup - traversal initiated by the cleanup of the main unit. - */ - if (!thd->stmt_arena->is_stmt_prepare() && !thd->lex->describe && - with_element && with_element->is_recursive && union_result) + if (with_element && with_element->is_recursive && union_result) { select_union_recursive *result= with_element->rec_result; if (++result->cleanup_count == with_element->rec_outer_references) @@ -2088,27 +2082,31 @@ bool st_select_lex::cleanup() if (join) { + List_iterator<TABLE_LIST> ti(leaf_tables); + TABLE_LIST *tbl; + while ((tbl= ti++)) + { + if (tbl->is_recursive_with_table() && + !tbl->is_with_table_recursive_reference()) + { + /* + If query is killed before open_and_process_table() for tbl + is called then 'with' is already set, but 'derived' is not. + */ + st_select_lex_unit *unit= tbl->with->spec; + error|= (bool) error | (uint) unit->cleanup(); + } + } DBUG_ASSERT((st_select_lex*)join->select_lex == this); error= join->destroy(); delete join; join= 0; } - for (TABLE_LIST *tbl= get_table_list(); tbl; tbl= tbl->next_local) - { - if (tbl->is_recursive_with_table() && - !tbl->is_with_table_recursive_reference()) - { - /* - If query is killed before open_and_process_table() for tbl - is called then 'with' is already set, but 'derived' is not. - */ - st_select_lex_unit *unit= tbl->with->spec; - error|= (bool) error | (uint) unit->cleanup(); - } - } for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ; lex_unit= lex_unit->next_unit()) { + if (lex_unit->with_element && lex_unit->with_element->is_recursive) + continue; error= (bool) ((uint) error | (uint) lex_unit->cleanup()); } inner_refs_list.empty(); @@ -2128,8 +2126,12 @@ void st_select_lex::cleanup_all_joins(bool full) join->cleanup(full); for (unit= first_inner_unit(); unit; unit= unit->next_unit()) + { + if (unit->with_element && unit->with_element->is_recursive) + continue; for (sl= unit->first_select(); sl; sl= sl->next_select()) sl->cleanup_all_joins(full); + } DBUG_VOID_RETURN; } diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 0a18a85..f64872a 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -429,12 +429,6 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views, lex->link_first_table_back(view, link_to_local); view->open_type= OT_BASE_ONLY; - if (check_dependencies_in_with_clauses(lex->with_clauses_list)) - { - res= TRUE; - goto err; - } - WSREP_TO_ISOLATION_BEGIN(WSREP_MYSQL_DB, NULL, NULL); /* @@ -1411,9 +1405,6 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table, TABLE_LIST *tbl; Security_context *security_ctx= 0; - if (check_dependencies_in_with_clauses(thd->lex->with_clauses_list)) - goto err; - /* Check rights to run commands which show underlying tables. In the optimizer trace we would not like to show trace for diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 83323e3..f70d72d 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -762,6 +762,7 @@ Virtual_column_info *add_virtual_expression(THD *thd, Item *expr) class sp_head *sphead; class sp_name *spname; class sp_variable *spvar; + class With_element_head *with_element_head; class With_clause *with_clause; class Virtual_column_info *virtual_column; @@ -2190,7 +2191,7 @@ END_OF_INPUT %type <with_clause> with_clause -%type <lex_str_ptr> query_name +%type <with_element_head> with_element_head %type <lex_str_list> opt_with_column_list @@ -3338,7 +3339,11 @@ call: if (unlikely(Lex->call_statement_start(thd, $2))) MYSQL_YYABORT; } - opt_sp_cparam_list {} + opt_sp_cparam_list + { + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; + } ; /* CALL parameters */ @@ -4187,6 +4192,8 @@ sp_proc_stmt_return: LEX *lex= Lex; sp_head *sp= lex->sphead; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; if (unlikely(sp->m_handler->add_instr_freturn(thd, sp, lex->spcont, $3, lex)) || unlikely(sp->restore_lex(thd))) @@ -13332,6 +13339,8 @@ do: { Lex->insert_list= $3; Lex->pop_select(); //main select + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; } ; @@ -15494,6 +15503,7 @@ with_clause: if (unlikely(with_clause == NULL)) MYSQL_YYABORT; lex->derived_tables|= DERIVED_WITH; + lex->with_cte_resolution= true; lex->curr_with_clause= with_clause; with_clause->add_to_list(Lex->with_clauses_list_last_next); if (lex->current_select && @@ -15521,7 +15531,7 @@ with_list: with_list_element: - query_name + with_element_head opt_with_column_list { $2= new List<LEX_CSTRING> (Lex->with_column_list); @@ -15541,6 +15551,7 @@ with_list_element: if (elem->set_unparsed_spec(thd, spec_start, $7.pos(), spec_start - query_start)) MYSQL_YYABORT; + elem->set_tables_end_pos(lex->query_tables_last); } ; @@ -15567,16 +15578,18 @@ with_column_list: ; -query_name: +with_element_head: ident { - $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); - if (unlikely($$ == NULL)) + LEX_CSTRING *name= + (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); + $$= new (thd->mem_root) With_element_head(name); + if (unlikely(name == NULL || $$ == NULL)) MYSQL_YYABORT; + $$->tables_pos.set_start_pos(Lex->query_tables_last); } ; - /********************************************************************** ** Creating different items. diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index c8084f8..bd80c2e 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -240,6 +240,7 @@ void ORAerror(THD *thd, const char *s) class sp_head *sphead; class sp_name *spname; class sp_variable *spvar; + class With_element_head *with_element_head; class With_clause *with_clause; class Virtual_column_info *virtual_column; @@ -1691,7 +1692,7 @@ END_OF_INPUT %type <with_clause> with_clause -%type <lex_str_ptr> query_name +%type <with_element_head> with_element_head %type <lex_str_list> opt_with_column_list @@ -15621,6 +15622,7 @@ with_clause: if (unlikely(with_clause == NULL)) MYSQL_YYABORT; lex->derived_tables|= DERIVED_WITH; + lex->with_cte_resolution= true; lex->curr_with_clause= with_clause; with_clause->add_to_list(Lex->with_clauses_list_last_next); if (lex->current_select && @@ -15648,7 +15650,7 @@ with_list: with_list_element: - query_name + with_element_head opt_with_column_list { $2= new List<LEX_CSTRING> (Lex->with_column_list); @@ -15668,6 +15670,7 @@ with_list_element: if (elem->set_unparsed_spec(thd, spec_start, $7.pos(), spec_start - query_start)) MYSQL_YYABORT; + elem->set_tables_end_pos(lex->query_tables_last); } ; @@ -15694,12 +15697,15 @@ with_column_list: ; -query_name: +with_element_head: ident { - $$= (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); - if (unlikely($$ == NULL)) + LEX_CSTRING *name= + (LEX_CSTRING *) thd->memdup(&$1, sizeof(LEX_CSTRING)); + $$= new (thd->mem_root) With_element_head(name); + if (unlikely(name == NULL || $$ == NULL)) MYSQL_YYABORT; + $$->tables_pos.set_start_pos(Lex->query_tables_last); } ; diff --git a/sql/table.cc b/sql/table.cc index 38777de..80bed60 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -9144,6 +9144,7 @@ void TABLE_LIST::set_lock_type(THD *thd, enum thr_lock_type lock) } } + bool TABLE_LIST::is_with_table() { return derived && derived->with_element; diff --git a/sql/table.h b/sql/table.h index 15ab359..e90aa0a 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2139,6 +2139,29 @@ struct vers_select_conds_t struct LEX; class Index_hint; + +/* + @struct TABLE_CHAIN + @brief Subchain of global chain of table references + + The structure contains a pointer to the address of the next_global + pointer to the first TABLE_LIST objectof the subchain and the address + of the next_global pointer to the element right after the last + TABLE_LIST object of the subchain. For an empty subchain both pointers + have the same value. +*/ + +struct TABLE_CHAIN +{ + TABLE_CHAIN() {} + + TABLE_LIST **start_pos; + TABLE_LIST ** end_pos; + + void set_start_pos(TABLE_LIST **pos) { start_pos= pos; } + void set_end_pos(TABLE_LIST **pos) { end_pos= pos; } +}; + struct TABLE_LIST { TABLE_LIST() {} /* Remove gcc warning */ @@ -2474,6 +2497,20 @@ struct TABLE_LIST /* call back function for asking handler about caching in query cache */ qc_engine_callback callback_func; thr_lock_type lock_type; + + /* + Two fields below are set during parsing this table reference in the cases + when the table reference can be potentially a reference to a CTE table. + In this cases the fact that the reference is a reference to a CTE or not + will be ascertained at the very end of parsing of the query when referencies + to CTE are resolved. For references to CTE and to derived tables no mdl + requests are needed while for other table references they are. If a request + is possibly postponed the info that allows to issue this request must be + saved in 'mdl_type' and 'table_options'. + */ + enum_mdl_type mdl_type; + ulong table_options; + uint outer_join; /* Which join type */ uint shared; /* Used in multi-upd */ bool updatable; /* VIEW/TABLE can be updated now */

1 0

[Commits] d5a82208f61: MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' failed in setup_jtbm_semi_joins
by varun 24 Dec '20

24 Dec '20

revision-id: d5a82208f61f2f476c2667c77c9cbb74d137374b (mariadb-10.2.31-642-gd5a82208f61) parent(s): 8d8370e31d48e0bc6139c18770746f9959c21598 author: Varun Gupta committer: Varun Gupta timestamp: 2020-12-24 19:19:43 +0530 message: MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' failed in setup_jtbm_semi_joins Currently window functions do not work with IN/ALL/ANY/SOME subqueries, so throw an error when such a query is encountered --- mysql-test/r/win.result | 28 ++++++++++++++++++++++++---- mysql-test/t/win.test | 24 ++++++++++++++++++++++++ sql/item_subselect.cc | 3 +++ sql/opt_subselect.cc | 8 ++++++++ 4 files changed, 59 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 3023a86eaad..1b4b7435351 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -2391,8 +2391,7 @@ COUNT(*) OVER (PARTITION BY c) 2 2 SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 ); -i -1 +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' DROP TABLE t1, t2; # # MDEV-9976: window function without PARTITION BY and ORDER BY @@ -3153,8 +3152,7 @@ DROP TABLE t1; CREATE TABLE t1 (c CHAR(8)) ENGINE=MyISAM; INSERT IGNORE INTO t1 VALUES ('foo'); SELECT ('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1); -('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1) -0 +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' DROP TABLE t1; # # MDEV-13351: Server crashes in st_select_lex::set_explain_type upon UNION with window function @@ -3866,5 +3864,27 @@ NULL DROP VIEW v1; DROP TABLE t1,t2; # +# MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' +# failed in setup_jtbm_semi_joins +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +SELECT * FROM t1 WHERE t1.a IN (SELECT COUNT(t2.a) over () from t1 t2); +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' +SELECT * FROM t1 WHERE t1.a > ALL (SELECT COUNT(t2.a) over () from t1 t2); +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' +SELECT * FROM t1 WHERE t1.a > ANY (SELECT COUNT(t2.a) over () from t1 t2); +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' +SELECT * FROM t1 WHERE EXISTS (SELECT row_number() OVER () FROM t1 A WHERE t1.a= A.a); +a b +1 1 +2 2 +SELECT * FROM t1 WHERE a IN (SELECT 1 FROM t1 t2 ORDER BY sum(t2.b) over ()); +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' +select a from t1 where a in (select row_number() over (order by a) from t1 A +union select row_number() over (order by a) from t1 B) ; +ERROR 42000: This version of MariaDB doesn't yet support 'the combination of this ALL/ANY/SOME/IN subquery with this comparison operator and with contained window functions' +DROP TABLE t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index c7e3dac598b..32eed85224b 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -1440,6 +1440,7 @@ INSERT INTO t2 VALUES ('foo'),('bar'),('foo'); SELECT COUNT(*) OVER (PARTITION BY c) FROM t2; +--error ER_NOT_SUPPORTED_YET SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 ); DROP TABLE t1, t2; @@ -1941,6 +1942,7 @@ DROP TABLE t1; --echo # CREATE TABLE t1 (c CHAR(8)) ENGINE=MyISAM; INSERT IGNORE INTO t1 VALUES ('foo'); +--error ER_NOT_SUPPORTED_YET SELECT ('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1); DROP TABLE t1; @@ -2522,6 +2524,28 @@ SELECT NTH_VALUE(i1, i1) OVER (PARTITION BY i1) FROM v1; DROP VIEW v1; DROP TABLE t1,t2; +--echo # +--echo # MDEV-22700: Assertion `subq_pred->engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE' +--echo # failed in setup_jtbm_semi_joins +--echo # + +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +--error ER_NOT_SUPPORTED_YET +SELECT * FROM t1 WHERE t1.a IN (SELECT COUNT(t2.a) over () from t1 t2); +--error ER_NOT_SUPPORTED_YET +SELECT * FROM t1 WHERE t1.a > ALL (SELECT COUNT(t2.a) over () from t1 t2); +--error ER_NOT_SUPPORTED_YET +SELECT * FROM t1 WHERE t1.a > ANY (SELECT COUNT(t2.a) over () from t1 t2); +SELECT * FROM t1 WHERE EXISTS (SELECT row_number() OVER () FROM t1 A WHERE t1.a= A.a); +--error ER_NOT_SUPPORTED_YET +SELECT * FROM t1 WHERE a IN (SELECT 1 FROM t1 t2 ORDER BY sum(t2.b) over ()); +--error ER_NOT_SUPPORTED_YET +select a from t1 where a in (select row_number() over (order by a) from t1 A + union select row_number() over (order by a) from t1 B) ; + +DROP TABLE t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 802bfca64b7..5b7b24d9e21 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1951,6 +1951,9 @@ bool Item_allany_subselect::transform_into_max_min(JOIN *join) */ DBUG_ASSERT(!substitution); + if (select_lex->have_window_funcs()) + DBUG_RETURN(false); + /* Check if optimization with aggregate min/max possible 1 There is no aggregate in the subquery diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index fec4e8b2828..3fdba7f4520 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5940,6 +5940,14 @@ bool JOIN::choose_subquery_plan(table_map join_tables) in_subs->set_strategy(SUBS_IN_TO_EXISTS); } + if (select_lex->have_window_funcs()) + { + my_error(ER_NOT_SUPPORTED_YET, MYF(0), + "the combination of this ALL/ANY/SOME/IN subquery with this" + " comparison operator and with contained window functions"); + return true; + } + if (in_subs->test_strategy(SUBS_MATERIALIZATION)) { /* Restore the original query plan used for materialization. */

1 0

[Commits] 4947bf154: Apply the changes from:
by psergey 22 Dec '20

22 Dec '20

revision-id: 4947bf1549371a1eb28b5c907b22b856d3506dad (v5.8-3195-g4947bf154) parent(s): 4d897e51dfe1abfd9b5e9f11db1efd26265b4ba3 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-12-22 21:47:27 +0300 message: Apply the changes from: commit d5178f513c0b4144a5ac9358ec0f6a3b54a28e76 Author: George O. Lorch III <george.lorch(a)percona.com> Date: Tue Mar 19 12:18:40 2019 -0700 PS-5501 : Re-license PerconaFT 'locktree' to Apache V2 - Fixed some incomplete relicensed files from previous round. - Added missing license text to some. - Relicensed more files to Apache V2 that locktree depends on. --- .../lock/range/range_tree/lib/ft/comparator.h | 14 +++++++ .../lock/range/range_tree/lib/ft/ft-status.h | 14 +++++++ .../lock/range/range_tree/lib/locktree/keyrange.cc | 1 + .../lock/range/range_tree/lib/locktree/keyrange.h | 1 + .../range/range_tree/lib/locktree/lock_request.cc | 1 + .../range/range_tree/lib/locktree/lock_request.h | 1 + .../lock/range/range_tree/lib/locktree/locktree.cc | 1 + .../lock/range/range_tree/lib/locktree/locktree.h | 1 + .../lock/range/range_tree/lib/locktree/manager.cc | 1 + .../range/range_tree/lib/locktree/range_buffer.cc | 1 + .../range/range_tree/lib/locktree/range_buffer.h | 1 + .../lock/range/range_tree/lib/locktree/treenode.cc | 1 + .../lock/range/range_tree/lib/locktree/treenode.h | 1 + .../range/range_tree/lib/locktree/txnid_set.cc | 1 + .../lock/range/range_tree/lib/locktree/txnid_set.h | 1 + .../lock/range/range_tree/lib/locktree/wfg.cc | 1 + .../lock/range/range_tree/lib/locktree/wfg.h | 1 + .../lock/range/range_tree/lib/portability/memory.h | 14 +++++++ .../range/range_tree/lib/portability/toku_atomic.h | 14 +++++++ .../lib/portability/toku_instrumentation.h | 46 ++++++++++++++++++++++ .../range_tree/lib/portability/toku_portability.h | 14 +++++++ .../range_tree/lib/portability/toku_pthread.h | 14 +++++++ .../range_tree/lib/portability/toku_race_tools.h | 14 +++++++ .../range/range_tree/lib/portability/toku_time.h | 14 +++++++ .../lock/range/range_tree/lib/util/dbt.h | 14 +++++++ .../range/range_tree/lib/util/growable_array.h | 1 + .../lock/range/range_tree/lib/util/memarena.cc | 14 +++++++ .../lock/range/range_tree/lib/util/memarena.h | 14 +++++++ .../lock/range/range_tree/lib/util/omt.h | 1 + .../lock/range/range_tree/lib/util/omt_impl.h | 1 + .../range_tree/lib/util/partitioned_counter.h | 14 +++++++ .../lock/range/range_tree/lib/util/status.h | 14 +++++++ 32 files changed, 246 insertions(+) diff --git a/utilities/transactions/lock/range/range_tree/lib/ft/comparator.h b/utilities/transactions/lock/range/range_tree/lib/ft/comparator.h index 6f4a823db..718efc623 100644 --- a/utilities/transactions/lock/range/range_tree/lib/ft/comparator.h +++ b/utilities/transactions/lock/range/range_tree/lib/ft/comparator.h @@ -31,6 +31,20 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. You should have received a copy of the GNU Affero General Public License along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/ft/ft-status.h b/utilities/transactions/lock/range/range_tree/lib/ft/ft-status.h index 095c044a0..1b4511172 100644 --- a/utilities/transactions/lock/range/range_tree/lib/ft/ft-status.h +++ b/utilities/transactions/lock/range/range_tree/lib/ft/ft-status.h @@ -32,6 +32,20 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. You should have received a copy of the GNU Affero General Public License along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/keyrange.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/keyrange.cc index c4b2a1258..e50ace5a9 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/keyrange.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/keyrange.cc @@ -47,6 +47,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/keyrange.h b/utilities/transactions/lock/range/range_tree/lib/locktree/keyrange.h index 6b6c6cb9a..f9aeea0c4 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/keyrange.h +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/keyrange.h @@ -45,6 +45,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.cc index 9df8f3cb3..fb14f98bd 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.cc @@ -47,6 +47,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.h b/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.h index 1dce4973f..ac47c3428 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.h +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.h @@ -45,6 +45,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.cc index bcd3a0044..b8482a227 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.cc @@ -47,6 +47,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.h b/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.h index fd8223da4..3e438f502 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.h +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.h @@ -45,6 +45,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/manager.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/manager.cc index 775988d06..4186182be 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/manager.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/manager.cc @@ -47,6 +47,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.cc index 3759d750d..1e1d23ef8 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.cc @@ -47,6 +47,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.h b/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.h index 280ee9dd1..76e28d747 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.h +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.h @@ -45,6 +45,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.cc index 5a6af141c..8997f634b 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.cc @@ -47,6 +47,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.h b/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.h index f87646b79..ec25a8c58 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.h +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.h @@ -45,6 +45,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/txnid_set.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/txnid_set.cc index 1e22f716b..4caf1e26f 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/txnid_set.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/txnid_set.cc @@ -47,6 +47,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/txnid_set.h b/utilities/transactions/lock/range/range_tree/lib/locktree/txnid_set.h index 66e799e71..d79c24fb0 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/txnid_set.h +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/txnid_set.h @@ -45,6 +45,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/wfg.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/wfg.cc index 5dfc94ad8..24536c88e 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/wfg.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/wfg.cc @@ -47,6 +47,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/wfg.h b/utilities/transactions/lock/range/range_tree/lib/locktree/wfg.h index 8354e34a0..804202170 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/wfg.h +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/wfg.h @@ -45,6 +45,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/portability/memory.h b/utilities/transactions/lock/range/range_tree/lib/portability/memory.h index 4b340f6a8..0a621f8e0 100644 --- a/utilities/transactions/lock/range/range_tree/lib/portability/memory.h +++ b/utilities/transactions/lock/range/range_tree/lib/portability/memory.h @@ -32,6 +32,20 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. You should have received a copy of the GNU Affero General Public License along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/portability/toku_atomic.h b/utilities/transactions/lock/range/range_tree/lib/portability/toku_atomic.h index c8f08eab7..aaa2298fa 100644 --- a/utilities/transactions/lock/range/range_tree/lib/portability/toku_atomic.h +++ b/utilities/transactions/lock/range/range_tree/lib/portability/toku_atomic.h @@ -32,6 +32,20 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. You should have received a copy of the GNU Affero General Public License along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/portability/toku_instrumentation.h b/utilities/transactions/lock/range/range_tree/lib/portability/toku_instrumentation.h index 1b85e2de1..c967e7177 100644 --- a/utilities/transactions/lock/range/range_tree/lib/portability/toku_instrumentation.h +++ b/utilities/transactions/lock/range/range_tree/lib/portability/toku_instrumentation.h @@ -1,3 +1,49 @@ +/*====== +This file is part of PerconaFT. + +Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. + + PerconaFT is free software: you can redistribute it and/or modify + it under the terms of the GNU General Public License, version 2, + as published by the Free Software Foundation. + + PerconaFT is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + PerconaFT is free software: you can redistribute it and/or modify + it under the terms of the GNU Affero General Public License, version 3, + as published by the Free Software Foundation. + + PerconaFT is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU Affero General Public License for more details. + + You should have received a copy of the GNU Affero General Public License + along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +======= */ + #pragma once #include <stdio.h> // FILE diff --git a/utilities/transactions/lock/range/range_tree/lib/portability/toku_portability.h b/utilities/transactions/lock/range/range_tree/lib/portability/toku_portability.h index cb07e6516..9a95b38bd 100644 --- a/utilities/transactions/lock/range/range_tree/lib/portability/toku_portability.h +++ b/utilities/transactions/lock/range/range_tree/lib/portability/toku_portability.h @@ -32,6 +32,20 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. You should have received a copy of the GNU Affero General Public License along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/portability/toku_pthread.h b/utilities/transactions/lock/range/range_tree/lib/portability/toku_pthread.h index 0bd0a084a..bd1cc8e6c 100644 --- a/utilities/transactions/lock/range/range_tree/lib/portability/toku_pthread.h +++ b/utilities/transactions/lock/range/range_tree/lib/portability/toku_pthread.h @@ -32,6 +32,20 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. You should have received a copy of the GNU Affero General Public License along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/portability/toku_race_tools.h b/utilities/transactions/lock/range/range_tree/lib/portability/toku_race_tools.h index 7ac9c00f2..3cb5b5790 100644 --- a/utilities/transactions/lock/range/range_tree/lib/portability/toku_race_tools.h +++ b/utilities/transactions/lock/range/range_tree/lib/portability/toku_race_tools.h @@ -32,6 +32,20 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. You should have received a copy of the GNU Affero General Public License along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/portability/toku_time.h b/utilities/transactions/lock/range/range_tree/lib/portability/toku_time.h index 9e1dce27a..4425a4a2e 100644 --- a/utilities/transactions/lock/range/range_tree/lib/portability/toku_time.h +++ b/utilities/transactions/lock/range/range_tree/lib/portability/toku_time.h @@ -32,6 +32,20 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. You should have received a copy of the GNU Affero General Public License along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/util/dbt.h b/utilities/transactions/lock/range/range_tree/lib/util/dbt.h index 963ad7ccf..d86c440f8 100644 --- a/utilities/transactions/lock/range/range_tree/lib/util/dbt.h +++ b/utilities/transactions/lock/range/range_tree/lib/util/dbt.h @@ -32,6 +32,20 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. You should have received a copy of the GNU Affero General Public License along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/util/growable_array.h b/utilities/transactions/lock/range/range_tree/lib/util/growable_array.h index ddd6a19a3..158750fdb 100644 --- a/utilities/transactions/lock/range/range_tree/lib/util/growable_array.h +++ b/utilities/transactions/lock/range/range_tree/lib/util/growable_array.h @@ -45,6 +45,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/util/memarena.cc b/utilities/transactions/lock/range/range_tree/lib/util/memarena.cc index 9a68f76c0..0e7a9880b 100644 --- a/utilities/transactions/lock/range/range_tree/lib/util/memarena.cc +++ b/utilities/transactions/lock/range/range_tree/lib/util/memarena.cc @@ -34,6 +34,20 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. You should have received a copy of the GNU Affero General Public License along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/util/memarena.h b/utilities/transactions/lock/range/range_tree/lib/util/memarena.h index 186c51995..ddcc1144f 100644 --- a/utilities/transactions/lock/range/range_tree/lib/util/memarena.h +++ b/utilities/transactions/lock/range/range_tree/lib/util/memarena.h @@ -32,6 +32,20 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. You should have received a copy of the GNU Affero General Public License along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/util/omt.h b/utilities/transactions/lock/range/range_tree/lib/util/omt.h index 63a3dd605..f208002d3 100644 --- a/utilities/transactions/lock/range/range_tree/lib/util/omt.h +++ b/utilities/transactions/lock/range/range_tree/lib/util/omt.h @@ -45,6 +45,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/util/omt_impl.h b/utilities/transactions/lock/range/range_tree/lib/util/omt_impl.h index 86b681d6b..e77986716 100644 --- a/utilities/transactions/lock/range/range_tree/lib/util/omt_impl.h +++ b/utilities/transactions/lock/range/range_tree/lib/util/omt_impl.h @@ -45,6 +45,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/util/partitioned_counter.h b/utilities/transactions/lock/range/range_tree/lib/util/partitioned_counter.h index 08fdda80d..f20eeedf2 100644 --- a/utilities/transactions/lock/range/range_tree/lib/util/partitioned_counter.h +++ b/utilities/transactions/lock/range/range_tree/lib/util/partitioned_counter.h @@ -32,6 +32,20 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. You should have received a copy of the GNU Affero General Public License along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \ diff --git a/utilities/transactions/lock/range/range_tree/lib/util/status.h b/utilities/transactions/lock/range/range_tree/lib/util/status.h index 996cefb50..3fd0095d0 100644 --- a/utilities/transactions/lock/range/range_tree/lib/util/status.h +++ b/utilities/transactions/lock/range/range_tree/lib/util/status.h @@ -32,6 +32,20 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. You should have received a copy of the GNU Affero General Public License along with PerconaFT. If not, see <http://www.gnu.org/licenses/>. + +---------------------------------------- + + Licensed under the Apache License, Version 2.0 (the "License"); + you may not use this file except in compliance with the License. + You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. ======= */ #ident \

1 0

[Commits] 8d8370e31d4: Forgot to add this change to previous cset
by psergey 22 Dec '20

22 Dec '20

revision-id: 8d8370e31d48e0bc6139c18770746f9959c21598 (mariadb-10.2.31-641-g8d8370e31d4) parent(s): df4f4bd84acce75150caff95b08c9b82d61e45db author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-12-22 19:22:41 +0300 message: Forgot to add this change to previous cset --- mysql-test/r/range_mrr_icp.result | 10 ++++++++++ 1 file changed, 10 insertions(+) diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index 3561283044a..f3203fea73d 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -3186,6 +3186,16 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 1000 Using where drop table t1,t2; # +# MDEV-24444: ASAN use-after-poison in Item_func_in::get_func_mm_tree with NOT IN +# +CREATE TABLE t1 (id INT, a CHAR(3), b INT, PRIMARY KEY(id), KEY(b), KEY(a)); +INSERT INTO t1 VALUES (1,'foo',10),(2,'bar',20); +CREATE TABLE t2 (code CHAR(8), num INT, PRIMARY KEY (code)); +INSERT INTO t2 VALUES ('100',1),('111',2); +SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR t2.num != 3; +id a b code num +DROP TABLE t1, t2; +# # End of 10.2 tests # set optimizer_switch=@mrr_icp_extra_tmp;

1 0