MDEV-13694: Wrong result upon GROUP BY with orderby_uses_equalities=on

For the case when  the SJM scan table is the first table in the join order,
then if we want to do the sorting on the SJM scan table, then we need to
make sure that we unpack the values to base table fields in two cases:
    1) Reading the SJM table and writing the sort-keys inside the sort-buffer
    2) Reading the sorted data from the sort file

1 files changed, 48 insertions, 58 deletions

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index fc9fea42a99..cef61ece5d8 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3858,6 +3858,16 @@ JOIN::add_sorting_to_table(JOIN_TAB *tab, ORDER *order)
                                  tab->select);
   if (!tab->filesort)
     return true;
+
+  TABLE *table= tab->table;
+  if ((tab == join_tab + const_tables) &&
+       table->pos_in_table_list &&
+       table->pos_in_table_list->is_sjm_scan_table())
+  {
+    tab->filesort->set_all_read_bits= TRUE;
+    tab->filesort->unpack= unpack_to_base_table_fields;
+  }
+
   /*
     Select was moved to filesort->select to force join_init_read_record to use
     sorted result instead of reading table through select.
@@ -14244,37 +14254,8 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond,
             can be used without tmp. table.
           */
           bool can_subst_to_first_table= false;
-          bool first_is_in_sjm_nest= false;
-          if (first_is_base_table)
-          {
-            TABLE_LIST *tbl_for_first=
-              join->join_tab[join->const_tables].table->pos_in_table_list;
-            first_is_in_sjm_nest= tbl_for_first->sj_mat_info &&
-                                  tbl_for_first->sj_mat_info->is_used;
-          }
-          /*
-            Currently we do not employ the optimization that uses multiple
-            equalities for ORDER BY to remove tmp table in the case when
-            the first table happens to be the result of materialization of
-            a semi-join nest ( <=> first_is_in_sjm_nest == true).
-
-            When a semi-join nest is materialized and scanned to look for
-            possible matches in the remaining tables for every its row
-            the fields from the result of materialization are copied
-            into the record buffers of tables from the semi-join nest.
-            So these copies are used to access the remaining tables rather
-            than the fields from the result of materialization.
-
-            Unfortunately now this so-called 'copy back' technique is
-            supported only if the rows  are scanned with the rr_sequential
-            function, but not with other rr_* functions that are employed
-            when the result of materialization is required to be sorted.
-
-            TODO: either to support 'copy back' technique for the above case,
-                  or to get rid of this technique altogether.
-          */
           if (optimizer_flag(join->thd, OPTIMIZER_SWITCH_ORDERBY_EQ_PROP) &&
-              first_is_base_table && !first_is_in_sjm_nest &&
+              first_is_base_table &&
               order->item[0]->real_item()->type() == Item::FIELD_ITEM &&
               join->cond_equal)
           {
@@ -20243,19 +20224,6 @@ do_select(JOIN *join, Procedure *procedure)
 }
 
 
-int rr_sequential_and_unpack(READ_RECORD *info)
-{
-  int error;
-  if (unlikely((error= rr_sequential(info))))
-    return error;
-  
-  for (Copy_field *cp= info->copy_field; cp != info->copy_field_end; cp++)
-    (*cp->do_copy)(cp);
-
-  return error;
-}
-
-
 /**
   @brief
   Instantiates temporary table
@@ -21551,6 +21519,8 @@ bool test_if_use_dynamic_range_scan(JOIN_TAB *join_tab)
 
 int join_init_read_record(JOIN_TAB *tab)
 {
+  bool need_unpacking= FALSE;
+  JOIN *join= tab->join;
   /* 
     Note: the query plan tree for the below operations is constructed in
     save_agg_explain_data.
@@ -21558,6 +21528,12 @@ int join_init_read_record(JOIN_TAB *tab)
   if (tab->distinct && tab->remove_duplicates())  // Remove duplicates.
     return 1;
 
+  if (join->top_join_tab_count != join->const_tables)
+  {
+    TABLE_LIST *tbl= tab->table->pos_in_table_list;
+    need_unpacking= tbl ? tbl->is_sjm_scan_table() : FALSE;
+  }
+
   tab->build_range_rowid_filter_if_needed();
 
   if (tab->filesort && tab->sort_table())     // Sort table.
@@ -21565,6 +21541,11 @@ int join_init_read_record(JOIN_TAB *tab)
 
   DBUG_EXECUTE_IF("kill_join_init_read_record",
                   tab->join->thd->set_killed(KILL_QUERY););
+
+
+  if (!tab->preread_init_done  && tab->preread_init())
+    return 1;
+
   if (tab->select && tab->select->quick && tab->select->quick->reset())
   {
     /* Ensures error status is propagated back to client */
@@ -21575,19 +21556,7 @@ int join_init_read_record(JOIN_TAB *tab)
   /* make sure we won't get ER_QUERY_INTERRUPTED from any code below */
   DBUG_EXECUTE_IF("kill_join_init_read_record",
                   tab->join->thd->reset_killed(););
-  if (!tab->preread_init_done  && tab->preread_init())
-    return 1;
 
-
-  if (init_read_record(&tab->read_record, tab->join->thd, tab->table,
-                       tab->select, tab->filesort_result, 1,1, FALSE))
-    return 1;
-  return tab->read_record.read_record();
-}
-
-int
-join_read_record_no_init(JOIN_TAB *tab)
-{
   Copy_field *save_copy, *save_copy_end;
   
   /*
@@ -21597,12 +21566,19 @@ join_read_record_no_init(JOIN_TAB *tab)
   save_copy=     tab->read_record.copy_field;
   save_copy_end= tab->read_record.copy_field_end;
   
-  init_read_record(&tab->read_record, tab->join->thd, tab->table,
-		   tab->select, tab->filesort_result, 1, 1, FALSE);
+  if (init_read_record(&tab->read_record, tab->join->thd, tab->table,
+                       tab->select, tab->filesort_result, 1, 1, FALSE))
+    return 1;
 
   tab->read_record.copy_field=     save_copy;
   tab->read_record.copy_field_end= save_copy_end;
-  tab->read_record.read_record_func= rr_sequential_and_unpack;
+
+  if (need_unpacking)
+  {
+    tab->read_record.read_record_func_and_unpack_calls=
+                                             tab->read_record.read_record_func;
+    tab->read_record.read_record_func = read_record_func_for_rr_and_unpack;
+  }
 
   return tab->read_record.read_record();
 }
@@ -29426,6 +29402,20 @@ void JOIN::init_join_cache_and_keyread()
 }
 
 
+/*
+  @brief
+    Unpack temp table fields to base table fields.
+*/
+
+void unpack_to_base_table_fields(TABLE *table)
+{
+  JOIN_TAB *tab= table->reginfo.join_tab;
+  for (Copy_field *cp= tab->read_record.copy_field;
+       cp != tab->read_record.copy_field_end; cp++)
+    (*cp->do_copy)(cp);
+}
+
+
 /**
   @} (end of group Query_Optimizer)
 */