List:Commits« Previous MessageNext Message »
From:Jan Wedvik Date:September 24 2010 7:38am
Subject:bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (jan.wedvik:3291
to 3293)
View as plain text  
 3293 Jan Wedvik	2010-09-24
      Removed compiler warning about wrong initialization order.

    modified:
      sql/ha_ndbcluster.cc
 3292 Jan Wedvik	2010-09-24
      This commit fixes a bug that caused results for pushed queries to be sorted 
      wrongly in some cases. This could happen if the root operation was a sorted
      scan (of an ordered index) and there was at least one scan descendant. Then it
      might happen that the child batch for fragment 0 contained child tuples of 
      parent tuples A and B, while fragment 1 only containing tuples that are children
      of A *with more such tuples coming in the next batch*. The API would then
      emit joined tuples containing parent tuple B before asking for the next batch
      of child tuples. Sorting order would then be wrong, since the next batch would 
      generate more joined tuples containing parent tuple A.
      
      The fix has two element:
      1. The API will return an error code if an application tries to build
      a query with a sorted root scan *and* another scan.
      2. The ndb handler will not try to push such operation trees. This would 
      otherwise happen if e.g. the query had an "ORDER BY" clause or if (parts of)
      a predicate would map to a range scan on the root table. (In the latter case
      sorting is not really needed and should ideally be eliminated.)

    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/ha_ndbcluster.h
      storage/ndb/src/ndbapi/NdbQueryBuilder.cpp
      storage/ndb/src/ndbapi/NdbQueryBuilderImpl.hpp
      storage/ndb/src/ndbapi/NdbQueryOperation.cpp
      storage/ndb/src/ndbapi/ndberror.c
 3291 Ole John Aske	2010-09-23
      spj-svs: 'Sledgehammer fix' which turns of pushability for outer joined scan-scan operations.
      Updated MTR results due to this reduced pushability.

    modified:
      mysql-test/suite/ndb/r/ndb_join_pushdown.result
      sql/ha_ndbcluster.cc
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2010-09-23 11:47:46 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2010-09-24 07:29:43 +0000
@@ -3811,6 +3811,108 @@ pruned
 const_pruned
 6
 drop table t1;
+create table t1 (pk int primary key, a int, b int) engine=ndb;
+create index ix1 on t1(b,a);
+insert into t1 values (0,10,10);
+insert into t1 values (1,10,20);
+insert into t1 values (2,20,20);
+insert into t1 values (3,10,10);
+insert into t1 values (4,10,20);
+insert into t1 values (5,10,20);
+insert into t1 values (6,10,10);
+insert into t1 values (7,10,10);
+insert into t1 values (8,10,20);
+insert into t1 values (9,10,10);
+explain select x1.pk,x1.a,x1.b from t1 as x1 
+join t1 as x2 on x1.a=x2.b 
+join t1 as x3 on x2.a=x3.b 
+order by x1.pk limit 70;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	x1	index	NULL	PRIMARY	4	NULL	10	
+1	SIMPLE	x2	ref	ix1	ix1	5	test.x1.a	1	Parent of 2 pushed join@1; Using where
+1	SIMPLE	x3	ref	ix1	ix1	5	test.x2.a	1	Child of pushed join@1; Using where
+select x1.pk,x1.a,x1.b from t1 as x1 
+join t1 as x2 on x1.a=x2.b 
+join t1 as x3 on x2.a=x3.b 
+order by x1.pk limit 70;
+pk	a	b
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+0	10	10
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+1	10	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+2	20	20
+explain select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	x1	ref	ix1	ix1	5	const	10	Using where with pushed condition
+1	SIMPLE	x2	ref	ix1	ix1	5	test.x1.a	1	Using where
+select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3;
+pk	a	b	pk	a	b
+drop table t1;
 create temporary table spj_counts_at_end
 select counter_name, sum(val) AS val 
 from ndbinfo.counters 
@@ -3828,10 +3930,10 @@ counter_name	spj_counts_at_end.val - spj
 CONST_PRUNED_RANGE_SCANS_RECEIVED	6
 LOCAL_TABLE_SCANS_SENT	190
 PRUNED_RANGE_SCANS_RECEIVED	12
-RANGE_SCANS_RECEIVED	189
+RANGE_SCANS_RECEIVED	195
 READS_NOT_FOUND	398
 READS_RECEIVED	48
-SCAN_ROWS_RETURNED	42489
+SCAN_ROWS_RETURNED	42579
 TABLE_SCANS_RECEIVED	190
 select sum(spj_counts_at_end.val - spj_counts_at_startup.val) as 'LOCAL+REMOTE READS_SENT'
        from spj_counts_at_end, spj_counts_at_startup 
@@ -3843,15 +3945,15 @@ LOCAL+REMOTE READS_SENT
 drop table spj_counts_at_startup;
 drop table spj_counts_at_end;
 scan_count
-1944
+1955
 pruned_scan_count
 7
 sorted_scan_count
-42
+44
 pushed_queries_defined
-325
+327
 pushed_queries_dropped
 11
 pushed_queries_executed
-241
+244
 set ndb_join_pushdown = @save_ndb_join_pushdown;

=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2010-09-23 09:22:03 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2010-09-24 07:29:43 +0000
@@ -2723,6 +2723,40 @@ select straight_join * from t1 x, t1 y w
 
 drop table t1;
 
+###
+# Test that sorted scan with sub scan is *not* pushed.
+###
+create table t1 (pk int primary key, a int, b int) engine=ndb;
+create index ix1 on t1(b,a);
+
+insert into t1 values (0,10,10);
+insert into t1 values (1,10,20);
+insert into t1 values (2,20,20);
+insert into t1 values (3,10,10);
+insert into t1 values (4,10,20);
+insert into t1 values (5,10,20);
+insert into t1 values (6,10,10);
+insert into t1 values (7,10,10);
+insert into t1 values (8,10,20);
+insert into t1 values (9,10,10);
+
+
+# Results would be sorted wrongly if pushed.
+explain select x1.pk,x1.a,x1.b from t1 as x1 
+   join t1 as x2 on x1.a=x2.b 
+   join t1 as x3 on x2.a=x3.b 
+   order by x1.pk limit 70;
+select x1.pk,x1.a,x1.b from t1 as x1 
+   join t1 as x2 on x1.a=x2.b 
+   join t1 as x3 on x2.a=x3.b 
+   order by x1.pk limit 70;
+
+# This query should not be pushed, since mysqld requires sorted
+# results for the root scan.
+explain select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3;
+select * from t1 as x1, t1 as x2 where x1.a=x2.b and x1.b = 3;
+
+drop table t1;
 
 ########################################
 # Verify DBSPJ counters for entire test:

=== modified file 'sql/abstract_query_plan.cc'
--- a/sql/abstract_query_plan.cc	2010-09-22 07:44:06 +0000
+++ b/sql/abstract_query_plan.cc	2010-09-24 07:29:43 +0000
@@ -449,6 +449,16 @@ namespace AQP
   {}
 
   /**
+    @return True if sorted results are expetced from this operation. 
+   */
+  bool Table_access::is_sorted() const
+  {
+    return get_join_tab()->type == JT_NEXT || 
+      (get_join_tab()->type == JT_REF && get_join_tab()->sorted) ||
+      (get_join_tab()->type == JT_REF_OR_NULL && get_join_tab()->sorted); 
+  }
+
+  /**
     @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-09-22 07:44:06 +0000
+++ b/sql/abstract_query_plan.h	2010-09-24 07:29:43 +0000
@@ -205,6 +205,8 @@ namespace AQP
 
     void dbug_print() const;
 
+    bool is_sorted() 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-09-23 11:47:46 +0000
+++ b/sql/ha_ndbcluster.cc	2010-09-24 07:38:03 +0000
@@ -519,7 +519,12 @@ private:
 
   struct pushed_tables
   {
-    pushed_tables() : m_parent(MAX_TABLES), m_last_scan_descendant(MAX_TABLES), m_ancestors(), op(NULL) {}
+    pushed_tables() : 
+      m_parent(MAX_TABLES), 
+      m_ancestors(), 
+      m_last_scan_descendant(MAX_TABLES), 
+      op(NULL) 
+    {}
 
     uint m_parent;
     ndb_table_access_map m_ancestors;
@@ -1274,6 +1279,13 @@ ha_ndbcluster::make_pushed_join(AQP::Joi
       DBUG_PRINT("info", ("Table %d not a pushable access type", join_cnt));
       continue;
     }
+    if (join_root->is_sorted() && !is_lookup_operation(child_type))  
+    {
+      // Sorted scan cannot have scan descendant.
+      DBUG_PRINT("info", ("Table %d is scanned. Therefore it cannot be pushed"
+                          " as a descendant of a sorted scan", join_cnt));
+      continue;
+    }
     if (!context.field_ref_is_join_pushable(join_tab, join_items, join_parent))
     {
       DBUG_PRINT("info", ("Table %d not REF-joined, not pushable", join_cnt));
@@ -1596,82 +1608,119 @@ private:
  *
  * @param type This is the operation type that the server want to execute.
  * @param idx  Index used whenever relevant for operation type
+ * @param rootSorted True if the root operation is an ordered index scan 
+ * with sorted results.
  * @return True if the operation may be pushed.
  */
 bool 
-ha_ndbcluster::check_if_pushable(const NdbQueryOperationTypeWrapper& type, uint idx) const
+ha_ndbcluster::check_if_pushable(const NdbQueryOperationTypeWrapper& type, 
+                                 uint idx,
+                                 bool rootSorted) const
 {
-  bool pushable= FALSE;
+  if (m_pushed_join == NULL)
+  {
+    return FALSE;
+  }
 
-  if (m_pushed_join != NULL)
+  const NdbQueryOperationDef* const root_operation= 
+    m_pushed_join->get_query_def().getQueryOperation(0U);
+
+  const NdbQueryOperationTypeWrapper& query_def_type=  
+    root_operation->getType();
+
+  if (query_def_type != type)
   {
-    const NdbQueryOperationDef* const root_operation= 
-      m_pushed_join->get_query_def().getQueryOperation(0U);
+    DBUG_PRINT("info", 
+               ("Cannot execute push join. Root operation prepared as %s "
+                "not executable as %s w/ index %d",
+                NdbQueryOperationDef::getTypeName(query_def_type),
+                NdbQueryOperationDef::getTypeName(type), idx));
+    return FALSE;
+  }
 
-    const NdbQueryOperationTypeWrapper& query_def_type=  root_operation->getType();
+  if (m_disable_pushed_join)
+  {
+    DBUG_PRINT("info", ("Push disabled (HA_EXTRA_KEYREAD)"));
+    return FALSE;
+  }
+
+  const NdbDictionary::Index* const expected_index= root_operation->getIndex();
+
+  // Check that we still use the same index as when the query was prepared.
+  switch (type)
+  {
+  case NdbQueryOperationDef::PrimaryKeyAccess:
+    DBUG_ASSERT(idx==table->s->primary_key);
+    break;
 
-    if (query_def_type == type)
+  case NdbQueryOperationDef::UniqueIndexAccess:
+    DBUG_ASSERT(idx<MAX_KEY);
+    //          DBUG_ASSERT(m_index[idx].unique_index == expected_index);
+    if (m_index[idx].unique_index != expected_index)
     {
-      if (m_disable_pushed_join)
-      {
-        DBUG_PRINT("info", ("Push disabled (HA_EXTRA_KEYREAD)"));
-      }
-      else
-      {
-        const NdbDictionary::Index* const expected_index= root_operation->getIndex();
-        pushable= TRUE;
+      DBUG_PRINT("info", ("Actual index %s differs from expected index %s."
+                          "Therefore, join cannot be pushed.", 
+                          m_index[idx].unique_index->getName(),
+                          expected_index->getName()));
+      return FALSE;
+    }
+    break;
 
-        switch (type)
+  case NdbQueryOperationDef::TableScan:
+    DBUG_ASSERT (idx==MAX_KEY);
+    break;
+
+  case NdbQueryOperationDef::OrderedIndexScan:
+    DBUG_ASSERT(idx<MAX_KEY);
+    //          DBUG_ASSERT(m_index[idx].index == expected_index);
+    if (m_index[idx].index != expected_index)
+    {
+      DBUG_PRINT("info", ("Actual index %s differs from expected index %s."
+                          "Therefore, join cannot be pushed.", 
+                          m_index[idx].index->getName(),
+                          expected_index->getName()));
+      return FALSE;
+    }
+    // Check that we do not have a sorted scan with sub scans.
+    if (rootSorted)
+    {
+      const NdbQueryDef& query_def = m_pushed_join->get_query_def();
+      for (uint i= 1; i < query_def.getNoOfOperations(); i++)
+      {
+        const NdbQueryOperationTypeWrapper& child_type= 
+          query_def.getQueryOperation(i)->getType();
+        if (child_type == NdbQueryOperationDef::TableScan ||
+            child_type == NdbQueryOperationDef::OrderedIndexScan)
         {
-          case NdbQueryOperationDef::PrimaryKeyAccess:
-            DBUG_ASSERT(idx==table->s->primary_key);
-            break;
-          case NdbQueryOperationDef::UniqueIndexAccess:
-            DBUG_ASSERT(idx<MAX_KEY);
-//          DBUG_ASSERT(m_index[idx].unique_index == expected_index);
-            pushable= (m_index[idx].unique_index == expected_index);
-            break;
-          case NdbQueryOperationDef::TableScan:
-            DBUG_ASSERT (idx==MAX_KEY);
-            break;
-          case NdbQueryOperationDef::OrderedIndexScan:
-            DBUG_ASSERT(idx<MAX_KEY);
-//          DBUG_ASSERT(m_index[idx].index == expected_index);
-            pushable= (m_index[idx].index == expected_index);
-            break;
-          default:
-            DBUG_ASSERT(false);
-            break;
-        }
-
-        if (likely(pushable))
-        {
-          // There may be referrences to Field values from tables outside the scope of
-          // our pushed join which are supplied as paramValues().
-          // If any of these are NULL values, join can't be pushed
-          for (uint i= 0; i < m_pushed_join->get_field_referrences_count(); i++)
-          {
-            Field* field= m_pushed_join->get_field_ref(i);
-            if (field->is_real_null())
-            {
-              DBUG_PRINT("info", ("paramValue is NULL, can not execute as pushed join"));
-              pushable= false;
-              break;
-            }
-          }
+          DBUG_PRINT("info", ("If the root operation is a sorted scan, then "
+                              "there may not be scan children. This join "
+                              "cannot be pushed.")); 
+          return FALSE;
         }
       }
     }
-    else
+    break;
+
+  default:
+    DBUG_ASSERT(false);
+    break;
+  }
+
+  // There may be referrences to Field values from tables outside the scope of
+  // our pushed join which are supplied as paramValues().
+  // If any of these are NULL values, join can't be pushed
+  for (uint i= 0; i < m_pushed_join->get_field_referrences_count(); i++)
+  {
+    Field* field= m_pushed_join->get_field_ref(i);
+    if (field->is_real_null())
     {
       DBUG_PRINT("info", 
-                 ("Cannot execute push join. Root operation prepared as %s "
-                  "not executable as %s w/ index %d",
-                  NdbQueryOperationDef::getTypeName(query_def_type),
-                  NdbQueryOperationDef::getTypeName(type), idx));
+                 ("paramValue is NULL, can not execute as pushed join"));
+      return FALSE;
     }
   }
-  return pushable;
+
+  return TRUE;
 }
 
 
@@ -5112,7 +5161,8 @@ int ha_ndbcluster::ordered_index_scan(co
     pbound = &bound;
   }
 
-  if (check_if_pushable(NdbQueryOperationDef::OrderedIndexScan, active_index))
+  if (check_if_pushable(NdbQueryOperationDef::OrderedIndexScan, active_index,
+                        sorted))
   {
     NdbQueryParamValue paramValues[ndb_pushed_join::MAX_REFERRED_FIELDS];
     const int error= create_pushed_join(paramValues);
@@ -13686,7 +13736,9 @@ ha_ndbcluster::read_multi_range_first(KE
       }
 
       /* Create the scan operation for the first scan range. */
-      if (check_if_pushable(NdbQueryOperationDef::OrderedIndexScan, active_index))
+      if (check_if_pushable(NdbQueryOperationDef::OrderedIndexScan, 
+                            active_index,
+                            !m_active_query && sorted))
       {
         if (!m_active_query)
         {

=== modified file 'sql/ha_ndbcluster.h'
--- a/sql/ha_ndbcluster.h	2010-09-21 06:33:27 +0000
+++ b/sql/ha_ndbcluster.h	2010-09-24 07:29:43 +0000
@@ -690,7 +690,8 @@ private:
   bool check_index_fields_not_null(KEY *key_info);
 
   bool check_if_pushable(const NdbQueryOperationTypeWrapper& type,
-                         uint idx= MAX_KEY) const;
+                         uint idx= MAX_KEY,
+			 bool rootSorted= false) const;
   bool check_is_pushed() const;
   int create_pushed_join(NdbQueryParamValue* paramValues, uint paramOffs= 0);
 

=== modified file 'storage/ndb/src/ndbapi/NdbQueryBuilder.cpp'
--- a/storage/ndb/src/ndbapi/NdbQueryBuilder.cpp	2010-09-03 13:04:45 +0000
+++ b/storage/ndb/src/ndbapi/NdbQueryBuilder.cpp	2010-09-24 07:29:43 +0000
@@ -980,6 +980,25 @@ NdbQueryBuilderImpl::contains(const NdbQ
 const NdbQueryDefImpl*
 NdbQueryBuilderImpl::prepare()
 {
+  /* Check if query is sorted and has multiple scan operations. This 
+   * combination is not implemented.
+   */
+  if (m_operations.size() > 0 && 
+      m_operations[0]->isScanOperation() &&
+      m_operations[0]->getOrdering() 
+        != NdbQueryOptions::ScanOrdering_unordered &&
+      m_operations[0]->getOrdering() != NdbQueryOptions::ScanOrdering_void)
+  {
+    for (Uint32 i = 1; i<m_operations.size(); i++)
+    {
+      if (m_operations[i]->isScanOperation())
+      {
+        setErrorCode(QRY_MULTIPLE_SCAN_SORTED);
+        return NULL;
+      } 
+    }
+  }
+
   int error;
   NdbQueryDefImpl* def = new NdbQueryDefImpl(m_operations, m_operands, error);
   m_operations.clear();
@@ -2186,9 +2205,6 @@ NdbQueryIndexScanOperationDefImpl::appen
       appendedPattern |= DABits::NI_KEY_CONSTS;
       const NdbConstOperandImpl& constOp = *static_cast<const NdbConstOperandImpl*>(value);
      
-      // No of words needed for storing the constant data.
-      const Uint32 wordCount =  AttributeHeader::getDataSize(constOp.getSizeInBytes());
-
       // Build the AttributeHeader for const value
       // (AttributeId is later filled in by SPJ in Dbspj::scanIndex_fixupBound())
       AttributeHeader ah(0, constOp.getSizeInBytes());

=== modified file 'storage/ndb/src/ndbapi/NdbQueryBuilderImpl.hpp'
--- a/storage/ndb/src/ndbapi/NdbQueryBuilderImpl.hpp	2010-09-03 13:04:45 +0000
+++ b/storage/ndb/src/ndbapi/NdbQueryBuilderImpl.hpp	2010-09-24 07:29:43 +0000
@@ -45,6 +45,7 @@
 #define QRY_PARAMETER_HAS_WRONG_TYPE 4821
 #define QRY_CHAR_PARAMETER_TRUNCATED 4822
 #define QRY_MULTIPLE_SCAN_BRANCHES 4823
+#define QRY_MULTIPLE_SCAN_SORTED 4824
 
 #ifdef __cplusplus
 #include <Vector.hpp>

=== modified file 'storage/ndb/src/ndbapi/NdbQueryOperation.cpp'
--- a/storage/ndb/src/ndbapi/NdbQueryOperation.cpp	2010-09-23 09:17:21 +0000
+++ b/storage/ndb/src/ndbapi/NdbQueryOperation.cpp	2010-09-24 07:29:43 +0000
@@ -4505,6 +4505,22 @@ NdbQueryOperationImpl::setOrdering(NdbQu
     return -1;
   }
   
+  /* Check if query is sorted and has multiple scan operations. This 
+   * combination is not implemented.
+   */
+  if (ordering != NdbQueryOptions::ScanOrdering_unordered)
+  {
+    for (Uint32 i = 1; i < getQuery().getNoOfOperations(); i++)
+    {
+      if (getQuery().getQueryOperation(i).getQueryOperationDef()
+          .isScanOperation())
+      {
+        getQuery().setErrorCode(QRY_MULTIPLE_SCAN_SORTED);
+        return -1;
+      }
+    }
+  }
+  
   m_ordering = ordering;
   return 0;
 } // NdbQueryOperationImpl::setOrdering()

=== modified file 'storage/ndb/src/ndbapi/ndberror.c'
--- a/storage/ndb/src/ndbapi/ndberror.c	2010-08-17 07:32:36 +0000
+++ b/storage/ndb/src/ndbapi/ndberror.c	2010-09-24 07:29:43 +0000
@@ -799,6 +799,8 @@ ErrorBundle ErrorCodes[] = {
     "Character Parameter was right truncated" },
   { QRY_MULTIPLE_SCAN_BRANCHES, DMEC, AE, 
     "Query has scans that are not descendants/ancestors of each other." },
+  { QRY_MULTIPLE_SCAN_SORTED, DMEC, AE, 
+    "Query with multiple scans may not be sorted." },
 
   { NO_CONTACT_WITH_PROCESS, DMEC, AE,
     "No contact with the process (dead ?)."},


Attachment: [text/bzr-bundle] bzr/jan.wedvik@sun.com-20100924073803-yxspb3c67uj3v1xi.bundle
Thread
bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (jan.wedvik:3291to 3293) Jan Wedvik24 Sep