MDEV-4978 - Server cursor is broken with blobs in the select list,

            ORDER BY does not work

Use "dynamic" row format (instead of "block") for MARIA internal
temporary tables created for cursors.

With "block" row format MARIA may shuffle rows, with "dynamic" row
format records are inserted sequentially (there are no gaps in data
file while we fill temporary tables).

This is needed to preserve row order when scanning materialized cursors.

7 files changed, 24 insertions, 10 deletions

diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 8b0006431a2..be1e24c61c1 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -3496,7 +3496,8 @@ select_materialize_with_stats::
 create_result_table(THD *thd_arg, List<Item> *column_types,
                     bool is_union_distinct, ulonglong options,
                     const char *table_alias, bool bit_fields_as_long,
-                    bool create_table)
+                    bool create_table,
+                    bool keep_row_order)
 {
   DBUG_ASSERT(table == 0);
   tmp_table_param.field_count= column_types->elements;
@@ -3504,7 +3505,8 @@ create_result_table(THD *thd_arg, List<Item> *column_types,
 
   if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types,
                                  (ORDER*) 0, is_union_distinct, 1,
-                                 options, HA_POS_ERROR, (char*) table_alias)))
+                                 options, HA_POS_ERROR, (char*) table_alias,
+                                 keep_row_order)))
     return TRUE;
 
   col_stat= (Column_statistics*) table->in_use->alloc(table->s->fields *
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 9b6cbcc2e8a..dab57a680f3 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -3580,7 +3580,8 @@ public:
                                    bool is_distinct, ulonglong options,
                                    const char *alias, 
                                    bool bit_fields_as_long,
-                                   bool create_table);
+                                   bool create_table,
+                                   bool keep_row_order= FALSE);
   TMP_TABLE_PARAM *get_tmp_table_param() { return &tmp_table_param; }
 };
 
@@ -3650,7 +3651,8 @@ public:
                            bool is_distinct, ulonglong options,
                            const char *alias, 
                            bool bit_fields_as_long,
-                           bool create_table);
+                           bool create_table,
+                           bool keep_row_order= FALSE);
   bool init_result_table(ulonglong select_options);
   int send_data(List<Item> &items);
   void cleanup();
diff --git a/sql/sql_cursor.cc b/sql/sql_cursor.cc
index 3758bf27819..230a8b2c802 100644
--- a/sql/sql_cursor.cc
+++ b/sql/sql_cursor.cc
@@ -389,7 +389,7 @@ bool Select_materialize::send_result_set_metadata(List<Item> &list, uint flags)
   if (create_result_table(unit->thd, unit->get_unit_column_types(),
                           FALSE,
                           thd->variables.option_bits | TMP_TABLE_ALL_COLUMNS,
-                          "", FALSE, TRUE))
+                          "", FALSE, TRUE, TRUE))
     return TRUE;
 
   materialized_cursor= new (&table->mem_root)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 1ce88b3c48c..8e3976ba3ca 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -14572,7 +14572,8 @@ TABLE *
 create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
 		 ORDER *group, bool distinct, bool save_sum_fields,
 		 ulonglong select_options, ha_rows rows_limit,
-                 const char *table_alias, bool do_not_open)
+                 const char *table_alias, bool do_not_open,
+                 bool keep_row_order)
 {
   MEM_ROOT *mem_root_save, own_root;
   TABLE *table;
@@ -15375,6 +15376,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
   share->db_record_offset= 1;
   table->used_for_duplicate_elimination= (param->sum_func_count == 0 &&
                                           (table->group || table->distinct));
+  table->keep_row_order= keep_row_order;
 
   if (!do_not_open)
   {
@@ -15712,7 +15714,8 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo,
                            table->no_rows ? NO_RECORD :
                            (share->reclength < 64 &&
                             !share->blob_fields ? STATIC_RECORD :
-                            table->used_for_duplicate_elimination ?
+                            table->used_for_duplicate_elimination ||
+                            table->keep_row_order ?
                             DYNAMIC_RECORD : BLOCK_RECORD),
                            share->keys, &keydef,
                            (uint) (*recinfo-start_recinfo),
diff --git a/sql/sql_select.h b/sql/sql_select.h
index eb50f65a0cc..179d1ccf8c6 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1821,7 +1821,8 @@ void push_index_cond(JOIN_TAB *tab, uint keyno);
 TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
 			ORDER *group, bool distinct, bool save_sum_fields,
 			ulonglong select_options, ha_rows rows_limit,
-			const char* alias, bool do_not_open=FALSE);
+			const char* alias, bool do_not_open=FALSE,
+                        bool keep_row_order= FALSE);
 void free_tmp_table(THD *thd, TABLE *entry);
 bool create_internal_tmp_table_from_heap(THD *thd, TABLE *table,
                                          ENGINE_COLUMNDEF *start_recinfo,
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 1551a8ff6d6..8a5ec26ba80 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -129,6 +129,7 @@ bool select_union::flush()
       table_alias        name of the temporary table
       bit_fields_as_long convert bit fields to ulonglong
       create_table       whether to physically create result table
+      keep_row_order     keep rows in order as they were inserted
 
   DESCRIPTION
     Create a temporary table that is used to store the result of a UNION,
@@ -143,7 +144,8 @@ bool
 select_union::create_result_table(THD *thd_arg, List<Item> *column_types,
                                   bool is_union_distinct, ulonglong options,
                                   const char *alias,
-                                  bool bit_fields_as_long, bool create_table)
+                                  bool bit_fields_as_long, bool create_table,
+                                  bool keep_row_order)
 {
   DBUG_ASSERT(table == 0);
   tmp_table_param.init();
@@ -153,7 +155,7 @@ select_union::create_result_table(THD *thd_arg, List<Item> *column_types,
   if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types,
                                  (ORDER*) 0, is_union_distinct, 1,
                                  options, HA_POS_ERROR, alias,
-                                 !create_table)))
+                                 !create_table, keep_row_order)))
     return TRUE;
 
   table->keys_in_use_for_query.clear_all();
diff --git a/sql/table.h b/sql/table.h
index 7a946d63bcc..0e41c10447a 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1149,6 +1149,10 @@ public:
   */
   bool force_index_group;
   bool distinct,const_table,no_rows, used_for_duplicate_elimination;
+  /**
+    Forces DYNAMIC Aria row format for internal temporary tables.
+  */
+  bool keep_row_order;
 
   /**
      If set, the optimizer has found that row retrieval should access index