revision-id: 9a8baa6829847ee4c4c81a4bf3fd19f7c2e8b103 (mariadb-10.2.15-80-g9a8baa6) parent(s): fe3f9fa9183ea3d10397b6f7f4d422ae9bba00a4 author: Igor Babaev committer: Igor Babaev timestamp: 2018-06-25 19:19:10 -0700 message: MDEV-16473 WITH statement throws 'no database selected' error Before this patch if no default database was set the server threw an error for any table name reference that was not fully qualified by database name. In particular it happened for table names referenced CTE tables. This was incorrect. The error message was thrown at the parser stage when the names referencing different tables were not resolved yet. Now if no default database is set and a with clause is used in the processed statement any table reference is just supplied with a dummy database name "*none*" at the parser stage. Later after a call of check_dependencies_in_with_clauses() when the names for CTE tables can be resolved error messages are thrown only for those names that refer to non-CTE tables. This is done in open_and_process_table(). --- mysql-test/r/cte_nonrecursive.result | 34 ++++++++++++++++++++++++++++++++++ mysql-test/t/cte_nonrecursive.test | 29 ++++++++++++++++++++++++++++- sql/sql_base.cc | 8 ++++++++ sql/sql_class.h | 25 +++++++++++++++++++++---- sql/sql_lex.h | 2 ++ sql/sql_parse.cc | 1 + 6 files changed, 94 insertions(+), 5 deletions(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 1d079c3..f6b8015 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -1478,3 +1478,37 @@ select 2 as f; f 2 drop table t1; +# +# MDEV-16473: query with CTE when no database is set +# +create database db_mdev_16473; +use db_mdev_16473; +drop database db_mdev_16473; +# Now no default database is set +select database(); +database() +NULL +with cte as (select 1 as a) select * from cte; +a +1 +create database db_mdev_16473; +create table db_mdev_16473.t1 (a int); +insert into db_mdev_16473.t1 values (2), (7), (3), (1); +with cte as (select * from db_mdev_16473.t1) select * from cte; +a +2 +7 +3 +1 +with cte as (select * from db_mdev_16473.t1) +select * from cte, t1 as t where cte.a=t.a; +ERROR 3D000: No database selected +with cte as (select * from db_mdev_16473.t1) +select * from cte, db_mdev_16473.t1 as t where cte.a=t.a; +a a +2 2 +7 7 +3 3 +1 1 +drop database db_mdev_16473; +use test; diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index 98a7794..11c864b 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -1029,4 +1029,31 @@ with cte as select 2 as f; drop table t1; - \ No newline at end of file + +--echo # +--echo # MDEV-16473: query with CTE when no database is set +--echo # + +create database db_mdev_16473; +use db_mdev_16473; +drop database db_mdev_16473; + +--echo # Now no default database is set +select database(); + +with cte as (select 1 as a) select * from cte; + +create database db_mdev_16473; +create table db_mdev_16473.t1 (a int); +insert into db_mdev_16473.t1 values (2), (7), (3), (1); +with cte as (select * from db_mdev_16473.t1) select * from cte; + +--error ER_NO_DB_ERROR +with cte as (select * from db_mdev_16473.t1) +select * from cte, t1 as t where cte.a=t.a; +with cte as (select * from db_mdev_16473.t1) +select * from cte, db_mdev_16473.t1 as t where cte.a=t.a; + +drop database db_mdev_16473; + +use test; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 9dfaf82..2e57686 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -3330,6 +3330,14 @@ open_and_process_table(THD *thd, LEX *lex, TABLE_LIST *tables, /* Not a placeholder: must be a base/temporary table or a view. Let us open it. */ + + if (tables->db == no_db) + { + my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0)); + error= TRUE; + goto end; + } + if (tables->table) { /* diff --git a/sql/sql_class.h b/sql/sql_class.h index a12015e..cb0893d 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -3945,11 +3945,28 @@ class THD :public Statement, { if (db == NULL) { - my_message(ER_NO_DB_ERROR, ER(ER_NO_DB_ERROR), MYF(0)); - return TRUE; + /* + No default database is set. In this case if it's guaranteed that + no CTE can be used in the statement then we can throw an error right + now at the parser stage. Otherwise the decision about throwing such + a message must be postponed until a post-parser stage when we are able + to resolve all CTE names as we don't need this message to be thrown + for any CTE references. + */ + if (!lex->with_clauses_list) + { + 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 */ + *p_db= (char *) no_db; + *p_db_length= strlen(no_db); + } + else + { + *p_db= strmake(db, db_length); + *p_db_length= db_length; } - *p_db= strmake(db, db_length); - *p_db_length= db_length; return FALSE; } thd_scheduler event_scheduler; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index a1f6b20..ae010a8 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -156,6 +156,8 @@ struct LEX_TYPE extern const LEX_STRING null_lex_str; extern const LEX_STRING empty_lex_str; +extern const char *no_db; + enum enum_sp_suid_behaviour { SP_IS_DEFAULT_SUID= 0, diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index a5973b9..0f74755 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -139,6 +139,7 @@ static bool execute_show_status(THD *, TABLE_LIST *); static bool check_rename_table(THD *, TABLE_LIST *, TABLE_LIST *); const char *any_db="*any*"; // Special symbol for check_access +const char *no_db="*none*"; // Used when no default db is set const LEX_STRING command_name[257]={ { C_STRING_WITH_LEN("Sleep") }, //0