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.

5 files changed, 52 insertions, 14 deletions

diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index fc654588072..8ad3818b453 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 920c27a70f9..c0c5c22bed0 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 c282db42fdd..e0a907abfb3 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 bcba4d03492..d922a7a7551 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 a80bf004916..28cf549bfa1 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;
   }