revision-id: 962782f767879eb64d088aa483007dc25b4fe74b (mariadb-10.2.23-42-g962782f) parent(s): b718ec055d41e45cbbca0bb0c9fdf86310ce9e02 author: Igor Babaev committer: Igor Babaev timestamp: 2019-04-03 22:52:29 -0700 message: MDEV-19112 WITH clause does not work with information_schema as default database With INFORMATION_SCHEMA set as the default database the check that a table referred in the processed query is defined in INORMATION_SCHEMA must be postponed until all CTE names can be identified. --- mysql-test/r/cte_nonrecursive.result | 14 ++++++++++++++ mysql-test/t/cte_nonrecursive.test | 12 ++++++++++++ sql/sql_base.cc | 24 ++++++++++++++++++++++++ sql/sql_cte.cc | 1 + sql/sql_parse.cc | 15 +-------------- 5 files changed, 52 insertions(+), 14 deletions(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index fc65458..8ad3818 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -1659,3 +1659,17 @@ a 2 drop view v1; drop table t1,t2; +# +# MDEV-19112: CTE usage when information_schema is set as default db +# +with t as (select 1 as t ) select * from t; +t +1 +use information_schema; +with t as (select 1 as t) select * from t; +t +1 +with columns as (select 1 as t) select * from columns; +t +1 +use test; diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index 920c27a..c0c5c22 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -1168,3 +1168,15 @@ select * from v1; drop view v1; drop table t1,t2; + +--echo # +--echo # MDEV-19112: CTE usage when information_schema is set as default db +--echo # + +with t as (select 1 as t ) select * from t; + +use information_schema; +with t as (select 1 as t) select * from t; +with columns as (select 1 as t) select * from columns; + +use test; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index c282db4..e0a907a 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -3344,6 +3344,30 @@ open_and_process_table(THD *thd, LEX *lex, TABLE_LIST *tables, goto end; } } + + if (!tables->derived && + is_infoschema_db(tables->db, tables->db_length)) + { + /* + Check whether the information schema contains a table + whose name is tables->schema_table_name + */ + ST_SCHEMA_TABLE *schema_table; + schema_table= find_schema_table(thd, tables->schema_table_name); + if (!schema_table || + (schema_table->hidden && + ((sql_command_flags[lex->sql_command] & CF_STATUS_COMMAND) == 0 || + /* + this check is used for show columns|keys from I_S hidden table + */ + lex->sql_command == SQLCOM_SHOW_FIELDS || + lex->sql_command == SQLCOM_SHOW_KEYS))) + { + my_error(ER_UNKNOWN_TABLE, MYF(0), + tables->schema_table_name, INFORMATION_SCHEMA_NAME.str); + DBUG_RETURN(1); + } + } /* If this TABLE_LIST object is a placeholder for an information_schema table, create a temporary table to represent the information_schema diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index bcba4d0..d922a7a 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1094,6 +1094,7 @@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem) table= 0; } with= with_elem; + schema_table= NULL; if (!with_elem->is_referenced() || with_elem->is_recursive) { derived= with_elem->spec; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index a80bf00..28cf549 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -8222,7 +8222,6 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, ptr->derived= table->sel; if (!ptr->derived && is_infoschema_db(ptr->db, ptr->db_length)) { - ST_SCHEMA_TABLE *schema_table; if (ptr->updating && /* Special cases which are processed by commands itself */ lex->sql_command != SQLCOM_CHECK && @@ -8234,20 +8233,8 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, INFORMATION_SCHEMA_NAME.str); DBUG_RETURN(0); } + ST_SCHEMA_TABLE *schema_table; schema_table= find_schema_table(thd, ptr->table_name); - if (!schema_table || - (schema_table->hidden && - ((sql_command_flags[lex->sql_command] & CF_STATUS_COMMAND) == 0 || - /* - this check is used for show columns|keys from I_S hidden table - */ - lex->sql_command == SQLCOM_SHOW_FIELDS || - lex->sql_command == SQLCOM_SHOW_KEYS))) - { - my_error(ER_UNKNOWN_TABLE, MYF(0), - ptr->table_name, INFORMATION_SCHEMA_NAME.str); - DBUG_RETURN(0); - } ptr->schema_table_name= ptr->table_name; ptr->schema_table= schema_table; }