bzr push into mysql-5.1-telco-7.0 branch (ole.john.aske:4251 to 4252) Bug#11764737

[Ole John Aske]

 4252 Ole John Aske	2011-03-23
      Cherry picked & backported fix for bug#11764737:
       'Optimizer is overly eager to request ordered access.'
      
        1)  Ensure that JOIN_TAB::sorted and QUICK_SELECT_I::sorted is
            requested only when strict ordering is required from the 
            handler - Either as a result of the query specifying 
            ORDER/GROUP BY, or the handler being a source in a 
            QUICK access method which require the sources to be ordered
            
        2a Call handler::ha_index_init(int idx, bool sorted) 
           with 'sorted==false' in any access methods requesting a 
           single row (HA_READ_KEY_EXACT) (join_read_key(), .....
            
        2b) Else: Always use above 'sorted' attributes as arguments
            to handler::ha_index_init() and other handler methods 
            having a 'bool sorted' argument(::read_range_first())
      
      Furthermore the function 'disable_sorted_access(JOIN_TAB* join_tab)'
      has been introduced which collect all logic for turning off sort requirement. 
     @ mysql-test/r/partition.result
        Accept changed result order for this (unordered) result set.

    modified:
      mysql-test/r/partition.result
      sql/ha_ndbcluster.cc
      sql/opt_range.cc
      sql/opt_range.h
      sql/sql_select.cc
 4251 Magnus Blåudd	2011-03-22
      Bug#11894966 - second mysqld does not have table after non distributed table
      alter to ndb
       - move the ndb_create_table_from_engine call and related code out of
         "if (ndb_binlog_running)", ndb schema distribution should run
         also when mysqld is not configured to binlog 

    modified:
      mysql-test/suite/ndb/r/ndb_multi.result
      mysql-test/suite/ndb/t/ndb_multi.test
      sql/ha_ndbcluster_binlog.cc
=== modified file 'mysql-test/r/partition.result'
--- a/mysql-test/r/partition.result	2010-03-22 12:30:27 +0000
+++ b/mysql-test/r/partition.result	2011-03-23 13:15:16 +0000
@@ -1563,8 +1563,8 @@ insert into t1 values (18446744073709551
 select * from t1;
 a
 18446744073709551612
-18446744073709551613
 18446744073709551614
+18446744073709551613
 18446744073709551615
 select * from t1 where a = 18446744073709551615;
 a
@@ -1573,8 +1573,8 @@ delete from t1 where a = 184467440737095
 select * from t1;
 a
 18446744073709551612
-18446744073709551613
 18446744073709551614
+18446744073709551613
 drop table t1;
 CREATE TABLE t1 (
 num int(11) NOT NULL, cs int(11) NOT NULL)

=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc	2011-03-16 06:53:41 +0000
+++ b/sql/ha_ndbcluster.cc	2011-03-23 13:15:16 +0000
@@ -5575,7 +5575,11 @@ int ha_ndbcluster::index_first(uchar *bu
   // Start the ordered index scan and fetch the first row
 
   // Only HA_READ_ORDER indexes get called by index_first
+#ifdef MCP_BUG11764737
   const int error= ordered_index_scan(0, 0, TRUE, FALSE, buf, NULL);
+#else
+  const int error= ordered_index_scan(0, 0, m_sorted, FALSE, buf, NULL);
+#endif
   table->status=error ? STATUS_NOT_FOUND: 0;
   DBUG_RETURN(error);
 }
@@ -5585,7 +5589,11 @@ int ha_ndbcluster::index_last(uchar *buf
 {
   DBUG_ENTER("ha_ndbcluster::index_last");
   ha_statistic_increment(&SSV::ha_read_last_count);
+#ifdef MCP_BUG11764737
   const int error= ordered_index_scan(0, 0, TRUE, TRUE, buf, NULL);
+#else
+  const int error= ordered_index_scan(0, 0, m_sorted, TRUE, buf, NULL);
+#endif
   table->status=error ? STATUS_NOT_FOUND: 0;
   DBUG_RETURN(error);
 }

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2010-10-06 10:06:47 +0000
+++ b/sql/opt_range.cc	2011-03-23 13:15:16 +0000
@@ -1097,7 +1097,12 @@ SQL_SELECT::~SQL_SELECT()
 #undef index					// Fix for Unixware 7
 
 QUICK_SELECT_I::QUICK_SELECT_I()
+#ifdef MCP_BUG11764737
   :max_used_key_length(0),
+#else
+  :sorted(false),
+   max_used_key_length(0),
+#endif
    used_key_parts(0)
 {}
 
@@ -1109,7 +1114,9 @@ QUICK_RANGE_SELECT::QUICK_RANGE_SELECT(T
   DBUG_ENTER("QUICK_RANGE_SELECT::QUICK_RANGE_SELECT");
 
   in_ror_merged_scan= 0;
+#ifdef MCP_BUG11764737
   sorted= 0;
+#endif
   index= key_nr;
   head=  table;
   key_part_info= head->key_info[index].key_part;
@@ -4983,7 +4990,11 @@ QUICK_SELECT_I *TRP_ROR_INTERSECT::make_
     {
       if (!(quick= get_quick_select(param, (*first_scan)->idx,
                                     (*first_scan)->sel_arg, alloc)) ||
+#ifdef MCP_BUG11764737
           quick_intrsect->push_quick_back(quick))
+#else
+          (quick->sorted= 1, quick_intrsect->push_quick_back(quick)))
+#endif
       {
         delete quick_intrsect;
         DBUG_RETURN(NULL);
@@ -4998,6 +5009,9 @@ QUICK_SELECT_I *TRP_ROR_INTERSECT::make_
         DBUG_RETURN(NULL);
       }
       quick->file= NULL; 
+#ifndef MCP_BUG11764737
+      quick->sorted= 1;
+#endif
       quick_intrsect->cpk_quick= quick;
     }
     quick_intrsect->records= records;
@@ -5024,7 +5038,11 @@ QUICK_SELECT_I *TRP_ROR_UNION::make_quic
     for (scan= first_ror; scan != last_ror; scan++)
     {
       if (!(quick= (*scan)->make_quick(param, FALSE, &quick_roru->alloc)) ||
+#ifdef MCP_BUG11764737
           quick_roru->push_quick_back(quick))
+#else
+          (quick->sorted= 1, quick_roru->push_quick_back(quick)))
+#endif
         DBUG_RETURN(NULL);
     }
     quick_roru->records= records;
@@ -8454,7 +8472,11 @@ int QUICK_RANGE_SELECT::reset()
   in_range= FALSE;
   cur_range= (QUICK_RANGE**) ranges.buffer;
 
+#ifdef MCP_BUG11764737
   if (file->inited == handler::NONE && (error= file->ha_index_init(index,1)))
+#else
+  if (file->inited == handler::NONE && (error= file->ha_index_init(index,sorted)))
+#endif
     DBUG_RETURN(error);
  
   /* Do not allocate the buffers twice. */
@@ -8671,7 +8693,11 @@ int QUICK_RANGE_SELECT::get_next_prefix(
     result= file->read_range_first(last_range->min_keypart_map ? &start_key : 0,
 				   last_range->max_keypart_map ? &end_key : 0,
                                    test(last_range->flag & EQ_RANGE),
+#ifdef MCP_BUG11764737
 				   TRUE);
+#else
+				   sorted);
+#endif
     if (last_range->flag == (UNIQUE_RANGE | EQ_RANGE))
       last_range= 0;			// Stop searching
 
@@ -8784,6 +8810,10 @@ QUICK_SELECT_DESC::QUICK_SELECT_DESC(QUI
   multi_range= NULL;
   multi_range_buff= NULL;
 
+#ifndef MCP_BUG11764737
+  sorted= 1;      // 'sorted' as internals use index_last/_prev
+#endif
+
   QUICK_RANGE **pr= (QUICK_RANGE**)ranges.buffer;
   QUICK_RANGE **end_range= pr + ranges.elements;
   for (; pr!=end_range; pr++)
@@ -8802,6 +8832,16 @@ QUICK_SELECT_DESC::QUICK_SELECT_DESC(QUI
 }
 
 
+#ifndef MCP_BUG11764737
+int QUICK_SELECT_DESC::reset(void)
+{
+  sorted= 1; // 'sorted' index access is required by internals
+  rev_it.rewind();
+  return QUICK_RANGE_SELECT::reset();
+}
+#endif
+
+
 int QUICK_SELECT_DESC::get_next()
 {
   DBUG_ENTER("QUICK_SELECT_DESC::get_next");
@@ -10208,11 +10248,21 @@ TRP_GROUP_MIN_MAX::make_quick(PARAM *par
     if (quick_prefix_records == HA_POS_ERROR)
       quick->quick_prefix_select= NULL; /* Can't construct a quick select. */
     else
+    {
       /* Make a QUICK_RANGE_SELECT to be used for group prefix retrieval. */
       quick->quick_prefix_select= get_quick_select(param, param_idx,
                                                    index_tree,
                                                    &quick->alloc);
 
+#ifndef MCP_BUG11764737
+      if (!quick->quick_prefix_select)
+      {
+        delete quick;
+        DBUG_RETURN(NULL);
+      }
+      quick->quick_prefix_select->sorted= 1;
+#endif
+    }
     /*
       Extract the SEL_ARG subtree that contains only ranges for the MIN/MAX
       attribute, and create an array of QUICK_RANGES to be used by the
@@ -10603,6 +10653,10 @@ int QUICK_GROUP_MIN_MAX_SELECT::reset(vo
   DBUG_ENTER("QUICK_GROUP_MIN_MAX_SELECT::reset");
 
   head->set_keyread(TRUE); /* We need only the key attributes */
+  /*
+    Request ordered index access as usage of ::index_last(), 
+    ::index_first() within QUICK_GROUP_MIN_MAX_SELECT depends on it.
+  */
   if ((result= file->ha_index_init(index,1)))
     DBUG_RETURN(result);
   if (quick_prefix_select && quick_prefix_select->reset())

=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h	2010-10-06 10:06:47 +0000
+++ b/sql/opt_range.h	2011-03-23 13:15:16 +0000
@@ -762,7 +762,11 @@ public:
   int get_type() { return QS_TYPE_RANGE_DESC; }
 private:
   bool range_reads_after_key(QUICK_RANGE *range);
+#ifdef MCP_BUG11764737
   int reset(void) { rev_it.rewind(); return QUICK_RANGE_SELECT::reset(); }
+#else
+  int reset(void);
+#endif
   List<QUICK_RANGE> rev_ranges;
   List_iterator<QUICK_RANGE> rev_it;
   uint used_key_parts;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-02-23 10:42:16 +0000
+++ b/sql/sql_select.cc	2011-03-23 13:15:16 +0000
@@ -1743,6 +1743,19 @@ JOIN::save_join_tab()
 }
 
 
+#ifndef MCP_BUG11764737
+static void
+disable_sorted_access(JOIN_TAB* join_tab)
+{
+  join_tab->sorted= 0;
+  if (join_tab->select && join_tab->select->quick)
+  {
+    join_tab->select->quick->sorted= 0;
+  }
+}
+#endif
+
+
 /**
   Exec select.
 
@@ -1912,7 +1925,11 @@ JOIN::exec()
     DBUG_PRINT("info", ("%s", thd->proc_info));
     if (!curr_join->sort_and_group &&
         curr_join->const_tables != curr_join->tables)
+#ifdef MCP_BUG11764737
       curr_join->join_tab[curr_join->const_tables].sorted= 0;
+#else
+      disable_sorted_access(&curr_join->join_tab[curr_join->const_tables]);
+#endif
     if ((tmp_error= do_select(curr_join, (List<Item> *) 0, curr_tmp_table, 0)))
     {
       error= tmp_error;
@@ -2076,7 +2093,11 @@ JOIN::exec()
       curr_join->group_list= 0;
       if (!curr_join->sort_and_group &&
           curr_join->const_tables != curr_join->tables)
+#ifdef MCP_BUG11764737
         curr_join->join_tab[curr_join->const_tables].sorted= 0;
+#else
+        disable_sorted_access(&curr_join->join_tab[curr_join->const_tables]);
+#endif
       if (setup_sum_funcs(curr_join->thd, curr_join->sum_funcs) ||
 	  (tmp_error= do_select(curr_join, (List<Item> *) 0, curr_tmp_table,
 				0)))
@@ -6846,9 +6867,15 @@ make_join_readinfo(JOIN *join, ulonglong
   uint i;
   bool statistics= test(!(join->select_options & SELECT_DESCRIBE));
   bool ordered_set= 0;
-  bool sorted= 1;
   DBUG_ENTER("make_join_readinfo");
 
+#ifdef MCP_BUG11764737
+  bool sorted= 1;
+#else
+  /* First table sorted if ORDER or GROUP BY was specified */
+  bool sorted= (join->order || join->group_list);
+#endif
+
   for (i=join->const_tables ; i < join->tables ; i++)
   {
     JOIN_TAB *tab=join->join_tab+i;
@@ -12156,7 +12183,12 @@ join_read_key(JOIN_TAB *tab)
 
   if (!table->file->inited)
   {
+#ifdef MCP_BUG11764737
+    table->file->ha_index_init(tab->ref.key, 0);
+#else
+    DBUG_ASSERT(!tab->sorted);  // Don't expect sort req. for single row.
     table->file->ha_index_init(tab->ref.key, tab->sorted);
+#endif
   }
   if (cmp_buffer_with_ref(tab) ||
       (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT | STATUS_NULL_ROW)))
@@ -12428,7 +12460,11 @@ join_read_last(JOIN_TAB *tab)
   tab->read_record.index=tab->index;
   tab->read_record.record=table->record[0];
   if (!table->file->inited)
+#ifdef MCP_BUG11764737
     table->file->ha_index_init(tab->index, 1);
+#else
+    table->file->ha_index_init(tab->index, tab->sorted);
+#endif
   if ((error= tab->table->file->index_last(tab->table->record[0])))
     return report_error(table, error);
   return 0;
@@ -12452,7 +12488,11 @@ join_ft_read_first(JOIN_TAB *tab)
   TABLE *table= tab->table;
 
   if (!table->file->inited)
+#ifdef MCP_BUG11764737
     table->file->ha_index_init(tab->ref.key, 1);
+#else
+    table->file->ha_index_init(tab->ref.key, tab->sorted);
+#endif
 #if NOT_USED_YET
   /* as ft-key doesn't use store_key's, see also FT_SELECT::init() */
   if (cp_buffer_from_ref(tab->join->thd, table, &tab->ref))

No bundle (reason: useless for push emails).