revision-id: a3f7f2334a267ec4e120f70e84a8551fb502860f (mariadb-10.2.31-608-ga3f7f23) parent(s): 2db6eb142956cd1744cf5c452bca66b20067d73e author: Igor Babaev committer: Igor Babaev timestamp: 2020-12-08 11:13:36 -0800 message: MDEV-24019 Assertion is hit for query using recursive CTE with no default DB When the query using a recursive CTE whose definition contained wildcard symbols in the recursive part was processed at the prepare stage an assertion was hit if the query was executed without any default database set. The failure happened when the function insert_fields() tried to check column privileges for the temporary table created for a recursive reference to the CTE. No acl checks are needed for any CTE. That's why this check should be blocked as well. The patch formulates a stricter condition at which this check is to be blocked that covers the case when a query using recursive CTEs is executed with no default database set. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/r/cte_recursive.result | 44 +++++++++++++++++++++++++++++++++++++++ mysql-test/t/cte_recursive.test | 35 +++++++++++++++++++++++++++++++ sql/sql_base.cc | 35 ++++++++++--------------------- 3 files changed, 90 insertions(+), 24 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 405bfbb..5090a0f 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -4191,5 +4191,49 @@ a b c 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/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 082e9be..f902ac2 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2691,5 +2691,40 @@ deallocate prepare stmt; drop table t1; --echo # +--echo # MDEV-24019: query with recursive CTE when no default database is set +--echo # + +drop database test; + +let $q= +with recursive a as + (select 1 from dual union select * from a as r) +select * from a; + +eval $q; + +create database db1; +create table db1.t1 (a int); +insert into db1.t1 values (3), (7), (1); + +let $q= +with recursive cte as + (select * from db1.t1 union select * from (select * from cte) as t) +select * from cte; + +eval $q; +eval explain $q; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop database db1; + +create database test; +use test; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 6222736..8e57ea4 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -7648,36 +7648,23 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, #ifndef NO_EMBEDDED_ACCESS_CHECKS /* - Ensure that we have access rights to all fields to be inserted. Under - some circumstances, this check may be skipped. + Ensure that we have access rights to all fields to be inserted + the table 'tables'. Under some circumstances, this check may be skipped. - - If any_privileges is true, skip the check. + The check is skipped in the following cases: - - 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. + - any_privileges is true - - 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. + - the table is a derived table - - 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. + - the table is a view with SELECT privilege - - 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. - - A TABLE_LIST that is not a view may be a subquery, an - information_schema table, or a nested table reference. See the comment - for TABLE_LIST. + - the table is a base table with SELECT privilege */ - if (!((table && tables->is_non_derived() && - (table->grant.privilege & SELECT_ACL)) || - ((!tables->is_non_derived() && - (tables->grant.privilege & SELECT_ACL)))) && - !any_privileges) + if (!any_privileges && + !tables->is_derived() && + !(tables->is_view() && (tables->grant.privilege & SELECT_ACL)) && + !(table && (table->grant.privilege & SELECT_ACL))) { field_iterator.set(tables); if (check_grant_all_columns(thd, SELECT_ACL, &field_iterator))