MDEV-17096 Pushdown of simple derived tables to storage engines

Resolved the problem of forming a proper query string for FEDERATEDX.
Added test cases.

Cleanup of extra spaces.

10 files changed, 62 insertions, 36 deletions

diff --git a/sql/derived_handler.cc b/sql/derived_handler.cc
index 561c18a5657..1fa5e94a1d4 100644
--- a/sql/derived_handler.cc
+++ b/sql/derived_handler.cc
@@ -16,7 +16,7 @@ void derived_handler::set_derived(TABLE_LIST *tbl)
 
 Pushdown_derived::Pushdown_derived(TABLE_LIST *tbl, derived_handler *h)
  : derived(tbl), handler(h)
-{ 
+{
   is_analyze= handler->thd->lex->analyze_stmt;
 }
 
@@ -42,7 +42,7 @@ int Pushdown_derived::execute()
     handler->end_scan();
     DBUG_RETURN(0);
   }
-  
+
   while (!(err= handler->next_row()))
   {
     if (unlikely(thd->check_killed()))
@@ -50,7 +50,7 @@ int Pushdown_derived::execute()
       handler->end_scan();
       DBUG_RETURN(-1);
     }
-  
+
     if ((err= table->file->ha_write_tmp_row(table->record[0])))
     {
       bool is_duplicate;
@@ -81,4 +81,4 @@ error_2:
   handler->print_error(err, MYF(0));
   DBUG_RETURN(-1);                              // Error not sent to client
 }
-  
+
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 4cd63042558..821f51ffaf5 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1215,8 +1215,13 @@ bool Item_in_optimizer::eval_not_null_tables(void *opt_arg)
 
 void Item_in_optimizer::print(String *str, enum_query_type query_type)
 {
-   restore_first_argument();
-   Item_func::print(str, query_type);
+  if (query_type & QT_PARSABLE)
+    args[1]->print(str, query_type);
+  else
+  {
+     restore_first_argument();
+     Item_func::print(str, query_type);
+  }
 }
 
 
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 0ace59fd2fc..7aa2ed489a3 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -3272,7 +3272,8 @@ out:
 
 void Item_in_subselect::print(String *str, enum_query_type query_type)
 {
-  if (test_strategy(SUBS_IN_TO_EXISTS))
+  if (test_strategy(SUBS_IN_TO_EXISTS) &&
+      !(query_type & QT_PARSABLE))
     str->append(STRING_WITH_LEN("<exists>"));
   else
   {
@@ -3499,7 +3500,8 @@ Item_allany_subselect::select_transformer(JOIN *join)
 
 void Item_allany_subselect::print(String *str, enum_query_type query_type)
 {
-  if (test_strategy(SUBS_IN_TO_EXISTS))
+  if (test_strategy(SUBS_IN_TO_EXISTS) &&
+      !(query_type & QT_PARSABLE))
     str->append(STRING_WITH_LEN("<exists>"));
   else
   {
diff --git a/sql/mysqld.h b/sql/mysqld.h
index 3d056fb95c7..320dedc4671 100644
--- a/sql/mysqld.h
+++ b/sql/mysqld.h
@@ -745,6 +745,8 @@ enum enum_query_type
   /// SHOW CREATE {VIEW|PROCEDURE|FUNCTION} and other cases where the
   /// original representation is required, should set this flag.
   QT_ITEM_ORIGINAL_FUNC_NULLIF= (1 << 7),
+  /// good for parsing
+  QT_PARSABLE= (1 << 8),
 
   /// This value means focus on readability, not on ability to parse back, etc.
   QT_EXPLAIN=           QT_TO_SYSTEM_CHARSET |
diff --git a/sql/select_handler.cc b/sql/select_handler.cc
index 17475f4907d..9a8d3914b21 100644
--- a/sql/select_handler.cc
+++ b/sql/select_handler.cc
@@ -6,7 +6,7 @@
 
 Pushdown_select::Pushdown_select(SELECT_LEX *sel, select_handler *h)
   : select(sel), handler(h)
-{ 
+{
   is_analyze= handler->thd->lex->analyze_stmt;
 }
 
@@ -35,7 +35,7 @@ bool Pushdown_select::init()
     DBUG_RETURN(true);
   if (handler->table->fill_item_list(&result_columns))
     DBUG_RETURN(true);
-  DBUG_RETURN(false);  
+  DBUG_RETURN(false);
 }
 
 bool Pushdown_select::send_result_set_metadata()
@@ -74,7 +74,7 @@ bool Pushdown_select::send_data()
     protocol->remove_last_row();
     DBUG_RETURN(true);
   }
- 
+
   thd->inc_sent_row_count(1);
 
   if (thd->vio_ok())
@@ -88,13 +88,13 @@ bool Pushdown_select::send_eof()
   THD *thd= handler->thd;
   DBUG_ENTER("Pushdown_select::send_eof");
 
-  /* 
+  /*
     Don't send EOF if we're in error condition (which implies we've already
     sent or are sending an error)
   */
   if (thd->is_error())
     DBUG_RETURN(true);
-  ::my_eof(thd);  
+  ::my_eof(thd);
   DBUG_RETURN(false);
 }
 
@@ -113,10 +113,10 @@ int Pushdown_select::execute()
     handler->end_scan();
     DBUG_RETURN(0);
   }
-  
+
   if (send_result_set_metadata())
     DBUG_RETURN(-1);
-  
+
   while (!(err= handler->next_row()))
   {
     if (thd->check_killed() || send_data())
diff --git a/sql/select_handler.h b/sql/select_handler.h
index 3b1347efed6..19a1883f6c7 100644
--- a/sql/select_handler.h
+++ b/sql/select_handler.h
@@ -20,9 +20,9 @@ class select_handler
 
   select_handler(THD *thd_arg, handlerton *ht_arg)
     : thd(thd_arg), ht(ht_arg), table(0) {}
-  
-  virtual ~select_handler() {} 
- 
+
+  virtual ~select_handler() {}
+
   /*
     Functions to scan the select result set.
     All these returns 0 if ok, error code in case of error.
@@ -32,7 +32,7 @@ class select_handler
   virtual int init_scan() = 0;
 
   /*
-    Put the next produced row of the result set in table->record[0] 
+    Put the next produced row of the result set in table->record[0]
     and return 0. Return HA_ERR_END_OF_FILE if there are no more rows,
     return other error number in case of fatal error.
   */
@@ -40,7 +40,7 @@ class select_handler
 
   /* Finish scanning */
   virtual int end_scan() = 0;
-  
+
   /* Report errors */
   virtual void print_error(int error, myf errflag) = 0;
 };
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 9ad22dde9c6..564049e59e1 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -385,7 +385,7 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived)
     DBUG_RETURN(FALSE);
   }
 
-  if ((derived->dt_handler= derived->find_derived_handler(thd)))
+  if (derived->dt_handler)
   {
     derived->change_refs_to_fields();
     derived->set_materialized_derived();
@@ -820,6 +820,24 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
   if (derived->is_derived() && derived->is_merged_derived())
     first_select->mark_as_belong_to_derived(derived);
 
+  derived->dt_handler= derived->find_derived_handler(thd);
+  if (derived->dt_handler)
+  {
+    char query_buff[4096];
+    String derived_query(query_buff, sizeof(query_buff), thd->charset());
+    derived_query.length(0);
+    derived->derived->print(&derived_query,
+                            enum_query_type(QT_VIEW_INTERNAL | 
+                                            QT_ITEM_ORIGINAL_FUNC_NULLIF |
+                                            QT_PARSABLE));
+    if (!thd->make_lex_string(&derived->derived_spec,
+                              derived_query.ptr(), derived_query.length()))
+    {
+      delete derived->dt_handler;
+      derived->dt_handler= NULL;
+    }
+  }
+
 exit:
   /* Hide "Unknown column" or "Unknown function" error */
   if (derived->view)
@@ -912,19 +930,17 @@ bool mysql_derived_optimize(THD *thd, LEX *lex, TABLE_LIST *derived)
     DBUG_RETURN(FALSE);
   }
 
-  if (derived->is_materialized_derived() && !derived->dt_handler)
-    derived->dt_handler= derived->find_derived_handler(thd);
-  if (derived->dt_handler)
+  if (derived->is_materialized_derived() && derived->dt_handler)
   {
     if (!(derived->pushdown_derived=
             new (thd->mem_root) Pushdown_derived(derived, derived->dt_handler)))
     {
       delete derived->dt_handler;
-      derived->dt_handler= NULL; 
-      DBUG_RETURN(1);
+      derived->dt_handler= NULL;
+      DBUG_RETURN(TRUE);
     }
-  }    
-  
+  }
+
   lex->current_select= first_select;
 
   if (unit->is_unit_op())
@@ -1136,7 +1152,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived)
     if (unit->executed)
       DBUG_RETURN(FALSE);
     res= derived->pushdown_derived->execute();
-    unit->executed= true; 
+    unit->executed= true;
     delete derived->pushdown_derived;
       DBUG_RETURN(res);
   }
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index b8d80c7c59c..7bb04fe6be4 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -339,7 +339,7 @@ int print_explain_row(select_result_sink *result,
 
   if (!select_type[0])
     return 0;
-  
+
   item_list.push_back(new (mem_root) Item_int(thd, (int32) select_number),
                       mem_root);
   item_list.push_back(new (mem_root) Item_string_sys(thd, select_type),
@@ -756,7 +756,7 @@ int Explain_select::print_explain(Explain_query *query,
   {
      print_explain_message_line(output, explain_flags, is_analyze,
                                 select_id /*select number*/,
-                                select_type, 
+                                select_type,
                                 NULL, /* rows */
                                 NULL);
   }
@@ -1225,7 +1225,7 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai
 {
   THD *thd= output->thd;
   MEM_ROOT *mem_root= thd->mem_root;
-  
+
   List<Item> item_list;
   Item *item_null= new (mem_root) Item_null(thd);
   
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 7cbbdfeee4f..e9040e197e0 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4044,7 +4044,7 @@ void JOIN::exec_inner()
     not the case.
   */
   if (exec_const_order_group_cond.elements &&
-      !(select_options & SELECT_DESCRIBE) && 
+      !(select_options & SELECT_DESCRIBE) &&
       !select_lex->pushdown_select)
   {
     List_iterator_fast<Item> const_item_it(exec_const_order_group_cond);
@@ -4300,7 +4300,7 @@ mysql_select(THD *thd,
       DBUG_RETURN(TRUE);
     }
   }
-      
+
   if ((err= join->optimize()))
   {
     goto err;					// 1
@@ -4322,7 +4322,7 @@ mysql_select(THD *thd,
     select_lex->where= join->conds_history;
     select_lex->having= join->having_history;
   }
-    
+
 err:
 
   if (select_lex->pushdown_select)
@@ -25885,7 +25885,7 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
         res= unit->exec();
     }
   }
-  else 
+  else
   {
     thd->lex->current_select= first;
     unit->set_limit(unit->global_parameters());
diff --git a/sql/table.h b/sql/table.h
index 7b88344830b..f7bcdaa58b2 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -2137,6 +2137,7 @@ struct TABLE_LIST
   bool is_derived_with_recursive_reference;
   bool block_handle_derived;
   derived_handler *dt_handler;
+  LEX_CSTRING derived_spec;
   Pushdown_derived *pushdown_derived;
   ST_SCHEMA_TABLE *schema_table;        /* Information_schema table */
   st_select_lex	*schema_select_lex;