bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3360)

[Ole John Aske]

#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0-spj-scan-scan/ based on revid:ole.john.aske@stripped

 3360 Ole John Aske	2010-11-11
      spj-svs: Followup on commit 'revno: 3322' (Less eager order access request)
      
      This commit was based on the idea that we could disable the ordered index 
      usage by setting 'QUICK_SELECT_I::sorted=false' when we later decided to use a filesort
      to provide the ordered resultset.
      
      However, for QUICK_SELECT_DESC the implementation internals also assumed the underlying 
      table to return its result in sorted order. It will therefore cause incorrect results 
      if 'sorted' is disabled for QUICK_SELECT_DESC.
      
      AQP::is_fixed_ordered_index() has been introduced for the purpose of identifying those
      QUICK_SELECT's which has 'fixed' its access to use an 'ordered_index'.
      
      Furthermore, QUICK_SELECT_DESC::reset() has been extended to make sure that 'sorted= 1'
      before the QUICK_SELECT_DESC resulset is made available.

    modified:
      mysql-test/suite/ndb/r/ndb_join_pushdown.result
      mysql-test/suite/ndb/t/ndb_join_pushdown.test
      sql/abstract_query_plan.cc
      sql/abstract_query_plan.h
      sql/ha_ndbcluster.cc
      sql/opt_range.cc
      sql/opt_range.h
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2010-11-09 10:11:09 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2010-11-11 13:07:41 +0000
@@ -4475,6 +4475,28 @@ select * from t as t1 join t as t2 on t2
 b	a	b	a
 0	0	0	0
 drop table t;
+create table t (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb;
+insert into t values (1,3), (3,6), (6,9), (9,1);
+explain extended
+select * from t as t1 join t as t2
+on t1.pk2 = t2.pk1 
+where t1.pk1 != 6
+order by t1.pk1 DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	20	10.00	Using where with pushed condition: (`test`.`t1`.`pk1` <> 6)
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.pk2	1	100.00	
+Warnings:
+Note	1644	Push of table 't2' as scan-child with ordered indexscan-root 't1' not implemented
+Note	1003	select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`pk2` AS `pk2`,`test`.`t2`.`pk1` AS `pk1`,`test`.`t2`.`pk2` AS `pk2` from `test`.`t` `t1` join `test`.`t` `t2` where ((`test`.`t2`.`pk1` = `test`.`t1`.`pk2`) and (`test`.`t1`.`pk1` <> 6)) order by `test`.`t1`.`pk1` desc
+select * from t as t1 join t as t2
+on t1.pk2 = t2.pk1 
+where t1.pk1 != 6
+order by t1.pk1 DESC;
+pk1	pk2	pk1	pk2
+9	1	1	3
+3	6	6	9
+1	3	3	6
+drop table t;
 create table tc(
 a varchar(10) not null,
 b varchar(10),
@@ -4567,11 +4589,11 @@ LOCAL+REMOTE READS_SENT
 drop table spj_counts_at_startup;
 drop table spj_counts_at_end;
 scan_count
-2014
+2021
 pruned_scan_count
 8
 sorted_scan_count
-7
+9
 pushed_queries_defined
 348
 pushed_queries_dropped

=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2010-11-09 13:01:43 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2010-11-11 13:07:41 +0000
@@ -2875,6 +2875,28 @@ select * from t as t1 join t as t2 on t2
 drop table t;
 
 #######
+# Testcase for bug introduced by initial fix for 
+# bug#57601 'Optimizer is overly eager to request ordered access.'
+# When we turned of 'sorted' for 'descending', we broke QUICK_SELECT_DESC
+# which required result to be read as an ordered index access
+#######
+create table t (pk1 int, pk2 int, primary key(pk1,pk2)) engine = ndb;
+insert into t values (1,3), (3,6), (6,9), (9,1);
+
+explain extended
+select * from t as t1 join t as t2
+  on t1.pk2 = t2.pk1 
+  where t1.pk1 != 6
+  order by t1.pk1 DESC;
+
+select * from t as t1 join t as t2
+  on t1.pk2 = t2.pk1 
+  where t1.pk1 != 6
+  order by t1.pk1 DESC;
+
+drop table t;
+
+#######
 # Test of varchar query parameteres.
 #######
 

=== modified file 'sql/abstract_query_plan.cc'
--- a/sql/abstract_query_plan.cc	2010-11-08 15:13:37 +0000
+++ b/sql/abstract_query_plan.cc	2010-11-11 13:07:41 +0000
@@ -261,9 +261,11 @@ namespace AQP
     DBUG_PRINT("info", ("index:%d", get_join_tab()->index));
     DBUG_PRINT("info", ("quick:%p", get_join_tab()->quick));
     DBUG_PRINT("info", ("select:%p", get_join_tab()->select));
-    if (get_join_tab()->select)
-      DBUG_PRINT("info", ("select->quick:%p",
-                          get_join_tab()->select->quick));
+    if (get_join_tab()->select && get_join_tab()->select->quick)
+    {
+      DBUG_PRINT("info", ("select->quick->get_type():%d",
+                          get_join_tab()->select->quick->get_type()));
+    }
   }
 
 
@@ -400,6 +402,14 @@ namespace AQP
                          (quick->get_type() == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) ||
                          (quick->get_type() == QUICK_SELECT_I::QS_TYPE_ROR_UNION)));
 
+#if 0
+          if (quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE_DESC) 
+          {
+            m_access_type= AT_OTHER;    // Multiple PKs are produced by merge
+            m_other_access_reason = "DESCending ORDER BY can not be pushed while using index";
+          }
+#endif
+
           // JT_INDEX_MERGE: We have a set of qualifying PKs as root of pushed joins
           if (quick->index == MAX_KEY) 
           {
@@ -460,6 +470,28 @@ namespace AQP
   {}
 
   /**
+    @return True iff ordered index access is *required* from this operation. 
+  */
+  bool Table_access::is_fixed_ordered_index() const
+  {
+    const JOIN_TAB* const join_tab= get_join_tab();
+
+    /* For the QUICK_SELECT_I classes we can disable ordered index usage by
+     * setting 'QUICK_SELECT_I::sorted = false'.
+     * However, QUICK_SELECT_I::QS_TYPE_RANGE_DESC is special as its 
+     * internal implementation requires its 'multi-ranges' to be retrieved
+     * in (descending) sorted order from the underlying table.
+     */
+    if (join_tab->select != NULL &&
+        join_tab->select->quick != NULL)
+    {
+      QUICK_SELECT_I *quick= join_tab->select->quick;
+      return (quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE_DESC);
+    }
+    return false;
+  }
+
+  /**
     @param plan Iterate over fields within this plan.
     @param field_item Iterate over Item_fields equal to this.
   */

=== modified file 'sql/abstract_query_plan.h'
--- a/sql/abstract_query_plan.h	2010-11-08 15:13:37 +0000
+++ b/sql/abstract_query_plan.h	2010-11-11 13:07:41 +0000
@@ -209,6 +209,8 @@ namespace AQP
 
     void dbug_print() const;
 
+    bool is_fixed_ordered_index() const;
+
   private:
 
     /** Backref. to the Join_plan which this Table_access is part of */

=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc	2010-11-10 08:01:00 +0000
+++ b/sql/ha_ndbcluster.cc	2010-11-11 13:07:41 +0000
@@ -905,7 +905,7 @@ ndb_pushed_builder_ctx::init_pushability
 bool
 ndb_pushed_builder_ctx::is_pushable_as_parent(const AQP::Table_access* table)
 {
-  DBUG_ENTER("::is_pushable_as_parent");
+  DBUG_ENTER("is_pushable_as_parent");
   uint table_no = table->get_access_no();
   if ((m_tables[table_no].m_maybe_pushable & PUSHABLE_AS_PARENT) != PUSHABLE_AS_PARENT)
   {
@@ -978,7 +978,7 @@ ndb_pushed_builder_ctx::is_pushable_as_c
   }
      
   // Currently there is a limitation in not allowing LOOKUP - (index)SCAN operations
-  if (is_lookup_operation(root_type) && access_type==AQP::AT_ORDERED_INDEX_SCAN)
+  if (access_type==AQP::AT_ORDERED_INDEX_SCAN && is_lookup_operation(root_type))
   {
     EXPLAIN_NO_PUSH("Push of table '%s' as scan-child "
                     "with lookup-root '%s' not implemented",
@@ -987,6 +987,15 @@ ndb_pushed_builder_ctx::is_pushable_as_c
     DBUG_RETURN(false);
   }
 
+  if (access_type==AQP::AT_ORDERED_INDEX_SCAN && join_root()->is_fixed_ordered_index())  
+  {
+    // root must be an ordered index scan - Thus it cannot have other scan descendant.
+    EXPLAIN_NO_PUSH("Push of table '%s' as scan-child "
+                    "with ordered indexscan-root '%s' not implemented",
+                     table->get_table()->alias, join_root()->get_table()->alias);
+    DBUG_RETURN(false);
+  }
+
   if (table->get_no_of_key_fields() > ndb_pushed_join::MAX_LINKED_KEYS)
   {
     EXPLAIN_NO_PUSH("Can't push table '%s' as child, "
@@ -1477,7 +1486,7 @@ ha_ndbcluster::make_pushed_join(ndb_push
 
     if (!context.is_pushable_as_child(join_tab, join_items, join_parent))
     {
-      DBUG_PRINT("info", ("Table %d not REF-joined, not pushable", join_cnt));
+      DBUG_PRINT("info", ("Table %d not pushable as child", join_cnt));
       continue;
     }
     /**

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2010-10-20 13:53:02 +0000
+++ b/sql/opt_range.cc	2010-11-11 13:07:41 +0000
@@ -1097,7 +1097,8 @@ SQL_SELECT::~SQL_SELECT()
 #undef index					// Fix for Unixware 7
 
 QUICK_SELECT_I::QUICK_SELECT_I()
-  :max_used_key_length(0),
+  :sorted(false),
+   max_used_key_length(0),
    used_key_parts(0)
 {}
 
@@ -1109,7 +1110,6 @@ QUICK_RANGE_SELECT::QUICK_RANGE_SELECT(T
   DBUG_ENTER("QUICK_RANGE_SELECT::QUICK_RANGE_SELECT");
 
   in_ror_merged_scan= 0;
-  sorted= 0;
   index= key_nr;
   head=  table;
   key_part_info= head->key_info[index].key_part;
@@ -8804,6 +8804,14 @@ QUICK_SELECT_DESC::QUICK_SELECT_DESC(QUI
 }
 
 
+int QUICK_SELECT_DESC::reset(void)
+{
+  sorted= 1; // 'sorted' index access is required by internals
+  rev_it.rewind();
+  return QUICK_RANGE_SELECT::reset();
+}
+
+
 int QUICK_SELECT_DESC::get_next()
 {
   DBUG_ENTER("QUICK_SELECT_DESC::get_next");

=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h	2010-10-20 13:53:02 +0000
+++ b/sql/opt_range.h	2010-11-11 13:07:41 +0000
@@ -762,7 +762,7 @@ public:
   int get_type() { return QS_TYPE_RANGE_DESC; }
 private:
   bool range_reads_after_key(QUICK_RANGE *range);
-  int reset(void) { rev_it.rewind(); return QUICK_RANGE_SELECT::reset(); }
+  int reset(void);
   List<QUICK_RANGE> rev_ranges;
   List_iterator<QUICK_RANGE> rev_it;
   uint used_key_parts;


Attachment: [text/bzr-bundle] bzr/[e-mail]