3856 Ole John Aske 2012-03-22
SPJ: Implement native support of pushed queries being sorted-scan-scan query.
Until now we didn't allow a scan-scan query which required 'sorted' result
set to be pushed to the SPJ block. Instead we supressed the 'sorted' request
and instead dumped the resultset to a temporary file and 'filesorted' it.
(Explained as 'Using tempfile; Using filesort')
This has been commented as 'questionable as best' by Evgeny which is
doing the review if WL5940: 'Integrating pushed join with optimizer & handler interface'
So we want to change it.....
This fix implement native support of sorted scan-scan by setting the
parent-scan batchsize to '1' when a sorted-scan-scan request is
about to be sent to the SPJ block.
(Dependant child scan/lookups are retreived with 'normal' batchsize)
SPJ API has to NEXTREQ more result until all related child-scan result for
the single parent row has been retrieved - This will guarantee sorted results
as all child rows will be retrieved together with its related parent.
(at the cost of some overhead though).
modified:
mysql-test/suite/ndb/r/ndb_join_pushdown_default.result
sql/abstract_query_plan.cc
sql/abstract_query_plan.h
sql/ha_ndbcluster.cc
sql/ha_ndbcluster.h
sql/ha_ndbcluster_push.cc
sql/ha_ndbcluster_push.h
sql/handler.h
sql/sql_select.cc
storage/ndb/src/ndbapi/NdbQueryBuilder.cpp
storage/ndb/src/ndbapi/NdbQueryOperation.cpp
storage/ndb/test/ndbapi/testSpj.cpp
storage/ndb/test/tools/spj_sanity_test.cpp
3855 Ole John Aske 2012-03-22
Set correct Bazaar 'tree_name' for this branch
modified:
.bzr-mysql/default.conf
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown_default.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown_default.result 2012-03-20 09:42:12 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown_default.result 2012-03-22 14:18:01 +0000
@@ -5142,7 +5142,7 @@ 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 filtered Extra
-1 SIMPLE x1 index NULL PRIMARY 4 NULL 10 100.00 Parent of 3 pushed join@1; Using temporary; Using filesort
+1 SIMPLE x1 index NULL PRIMARY 4 NULL 10 100.00 Parent of 3 pushed join@1
1 SIMPLE x2 ref ix1 ix1 5 test.x1.a 2 100.00 Child of 'x1' in pushed join@1; Using where
1 SIMPLE x3 ref ix1 ix1 5 test.x2.a 2 100.00 Child of 'x2' in pushed join@1; Using where
Warnings:
@@ -5287,10 +5287,9 @@ 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 6 66.67 Using where with pushed condition: (`test`.`t1`.`pk1` <> 6)
-1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.pk2 1 100.00
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 6 66.67 Parent of 2 pushed join@1; Using where with pushed condition: (`test`.`t1`.`pk1` <> 6)
+1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.pk2 1 100.00 Child of 't1' in pushed join@1
Warnings:
-Note 9999 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
@@ -5619,7 +5618,7 @@ counter_name spj_counts_at_end.val - spj
CONST_PRUNED_RANGE_SCANS_RECEIVED 8
LOCAL_TABLE_SCANS_SENT 250
PRUNED_RANGE_SCANS_RECEIVED 27
-RANGE_SCANS_RECEIVED 732
+RANGE_SCANS_RECEIVED 736
READS_RECEIVED 58
TABLE_SCANS_RECEIVED 250
drop table spj_counts_at_startup;
@@ -5631,9 +5630,9 @@ where new.variable_name = old.variable_n
order by new.variable_name;
variable_name new.variable_value - old.variable_value
NDB_PRUNED_SCAN_COUNT 8
-NDB_PUSHED_QUERIES_DEFINED 406
+NDB_PUSHED_QUERIES_DEFINED 408
NDB_PUSHED_QUERIES_DROPPED 8
-NDB_PUSHED_QUERIES_EXECUTED 550
-NDB_SORTED_SCAN_COUNT 10
+NDB_PUSHED_QUERIES_EXECUTED 552
+NDB_SORTED_SCAN_COUNT 11
drop table server_counts_at_startup;
set ndb_join_pushdown = @save_ndb_join_pushdown;
=== modified file 'sql/abstract_query_plan.cc'
--- a/sql/abstract_query_plan.cc 2011-10-20 19:52:11 +0000
+++ b/sql/abstract_query_plan.cc 2012-03-22 14:18:01 +0000
@@ -63,6 +63,10 @@ namespace AQP
return m_join_tabs + join_tab_no;
}
+ /**
+ * Check if either a GROUP BY or ORDER BY could be
+ * executed without sorting by reading an ordered index.
+ */
void
Join_plan::find_skippabable_group_or_order() const
{
@@ -546,28 +550,6 @@ 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;
- }
-
- /**
Check if the results from this operation will joined with results
from the next operation using a join buffer (instead of plain nested loop).
@return True if using a join buffer.
=== modified file 'sql/abstract_query_plan.h'
--- a/sql/abstract_query_plan.h 2011-10-20 19:52:11 +0000
+++ b/sql/abstract_query_plan.h 2012-03-22 14:18:01 +0000
@@ -210,8 +210,6 @@ namespace AQP
void dbug_print() const;
- bool is_fixed_ordered_index() const;
-
bool uses_join_cache() const;
private:
=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc 2012-03-19 12:09:39 +0000
+++ b/sql/ha_ndbcluster.cc 2012-03-22 14:18:01 +0000
@@ -4023,8 +4023,7 @@ int ha_ndbcluster::ordered_index_scan(co
}
#ifndef NDB_WITHOUT_JOIN_PUSHDOWN
- if (check_if_pushable(NdbQueryOperationDef::OrderedIndexScan, active_index,
- sorted))
+ if (check_if_pushable(NdbQueryOperationDef::OrderedIndexScan, active_index))
{
const int error= create_pushed_join();
if (unlikely(error))
@@ -13919,8 +13918,7 @@ ha_ndbcluster::read_multi_range_first(KE
#ifndef NDB_WITHOUT_JOIN_PUSHDOWN
/* Create the scan operation for the first scan range. */
if (check_if_pushable(NdbQueryOperationDef::OrderedIndexScan,
- active_index,
- !m_active_query && sorted))
+ active_index))
{
if (!m_active_query)
{
@@ -14578,8 +14576,7 @@ ha_ndbcluster::maybe_pushable_join(const
#ifndef NDB_WITHOUT_JOIN_PUSHDOWN
bool
ha_ndbcluster::check_if_pushable(int type, //NdbQueryOperationDef::Type,
- uint idx,
- bool needSorted) const
+ uint idx) const
{
if (m_disable_pushed_join)
{
@@ -14590,8 +14587,7 @@ ha_ndbcluster::check_if_pushable(int typ
&& m_pushed_join_member != NULL
&& m_pushed_join_member->match_definition(
type,
- (idx<MAX_KEY) ? &m_index[idx] : NULL,
- needSorted);
+ (idx<MAX_KEY) ? &m_index[idx] : NULL);
}
int
@@ -14728,46 +14724,6 @@ ha_ndbcluster::test_push_flag(enum ha_pu
}
DBUG_RETURN(false);
- case HA_PUSH_NO_ORDERED_INDEX:
- {
- if (m_pushed_join_operation != PUSHED_ROOT)
- {
- DBUG_RETURN(true);
- }
- const NdbQueryDef& query_def = m_pushed_join_member->get_query_def();
- const NdbQueryOperationDef::Type root_type=
- query_def.getQueryOperation((uint)PUSHED_ROOT)->getType();
-
- /**
- * Primary key/ unique key lookup is always 'ordered' wrt. itself.
- */
- if (root_type == NdbQueryOperationDef::PrimaryKeyAccess ||
- root_type == NdbQueryOperationDef::UniqueIndexAccess)
- {
- DBUG_RETURN(false);
- }
-
- /**
- * Ordered index scan can be provided as an ordered resultset iff
- * it has no child scans.
- */
- if (root_type == NdbQueryOperationDef::OrderedIndexScan)
- {
- for (uint i= 1; i < query_def.getNoOfOperations(); i++)
- {
- const NdbQueryOperationDef::Type child_type=
- query_def.getQueryOperation(i)->getType();
- if (child_type == NdbQueryOperationDef::TableScan ||
- child_type == NdbQueryOperationDef::OrderedIndexScan)
- {
- DBUG_RETURN(true);
- }
- }
- DBUG_RETURN(false);
- }
- DBUG_RETURN(true);
- }
-
default:
DBUG_ASSERT(0);
DBUG_RETURN(false);
=== modified file 'sql/ha_ndbcluster.h'
--- a/sql/ha_ndbcluster.h 2012-02-23 15:41:31 +0000
+++ b/sql/ha_ndbcluster.h 2012-03-22 14:18:01 +0000
@@ -445,8 +445,7 @@ private:
bool check_index_fields_not_null(KEY *key_info) const;
bool check_if_pushable(int type, //NdbQueryOperationDef::Type,
- uint idx= MAX_KEY,
- bool rootSorted= false) const;
+ uint idx= MAX_KEY) const;
bool check_is_pushed() const;
int create_pushed_join(const NdbQueryParamValue* keyFieldParams=NULL,
uint paramCnt= 0);
=== modified file 'sql/ha_ndbcluster_push.cc'
--- a/sql/ha_ndbcluster_push.cc 2011-09-30 11:05:03 +0000
+++ b/sql/ha_ndbcluster_push.cc 2012-03-22 14:18:01 +0000
@@ -153,8 +153,7 @@ ndb_pushed_join::~ndb_pushed_join()
bool ndb_pushed_join::match_definition(
int type, //NdbQueryOperationDef::Type,
- const NDB_INDEX_DATA* idx,
- bool needSorted) const
+ const NDB_INDEX_DATA* idx) const
{
const NdbQueryOperationDef* const root_operation=
m_query_def->getQueryOperation((uint)0);
@@ -195,13 +194,6 @@ bool ndb_pushed_join::match_definition(
case NdbQueryOperationDef::TableScan:
DBUG_ASSERT (idx==NULL && expected_index==NULL);
- if (needSorted)
- {
- DBUG_PRINT("info",
- ("TableScan access can not be provied as sorted result. "
- "Therefore, join cannot be pushed."));
- return FALSE;
- }
break;
case NdbQueryOperationDef::OrderedIndexScan:
@@ -215,13 +207,6 @@ bool ndb_pushed_join::match_definition(
expected_index->getName()));
return FALSE;
}
- if (needSorted && m_query_def->getQueryType() == NdbQueryDef::MultiScanQuery)
- {
- DBUG_PRINT("info",
- ("OrderedIndexScan with scan siblings "
- "can not execute as pushed join."));
- return FALSE;
- }
break;
default:
@@ -622,16 +607,6 @@ ndb_pushed_builder_ctx::is_pushable_as_c
DBUG_RETURN(false);
}
- if (access_type==AQP::AT_ORDERED_INDEX_SCAN && m_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,
- m_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, "
=== modified file 'sql/ha_ndbcluster_push.h'
--- a/sql/ha_ndbcluster_push.h 2011-09-30 11:05:03 +0000
+++ b/sql/ha_ndbcluster_push.h 2012-03-22 14:18:01 +0000
@@ -98,8 +98,7 @@ public:
* of operation specified by the arguments.
*/
bool match_definition(int type, //NdbQueryOperationDef::Type,
- const NDB_INDEX_DATA* idx,
- bool needSorted) const;
+ const NDB_INDEX_DATA* idx) const;
/** Create an executable instance of this defined query. */
NdbQuery* make_query_instance(
=== modified file 'sql/handler.h'
--- a/sql/handler.h 2012-02-23 15:41:31 +0000
+++ b/sql/handler.h 2012-03-22 14:18:01 +0000
@@ -499,11 +499,6 @@ enum ha_push_flag {
within this pushed join
*/
,HA_PUSH_MULTIPLE_DEPENDENCY
-
- /* Handler is unable to return the result in sorted order using an
- ordered index on the parent operation.
- */
- ,HA_PUSH_NO_ORDERED_INDEX
};
#endif
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2012-02-23 15:41:31 +0000
+++ b/sql/sql_select.cc 2012-03-22 14:18:01 +0000
@@ -1780,16 +1780,12 @@ make_pushed_join(THD *thd, JOIN *join)
if (join->const_tables < join->tables &&
join->join_tab[join->const_tables].table->file->number_of_pushed_joins() > 0)
{
- const handler *ha=join->join_tab[join->const_tables].table->file;
-
- if (join->group_list && join->simple_group &&
- (!plan.group_by_filesort_is_skippable() || ha->test_push_flag(HA_PUSH_NO_ORDERED_INDEX)))
+ if (join->group_list && join->simple_group && !plan.group_by_filesort_is_skippable())
{
join->need_tmp= 1;
join->simple_order= join->simple_group= 0;
}
- else if (join->order && join->simple_order &&
- (!plan.order_by_filesort_is_skippable() || ha->test_push_flag(HA_PUSH_NO_ORDERED_INDEX)))
+ else if (join->order && join->simple_order && !plan.order_by_filesort_is_skippable())
{
join->need_tmp= 1;
join->simple_order= join->simple_group= 0;
=== modified file 'storage/ndb/src/ndbapi/NdbQueryBuilder.cpp'
--- a/storage/ndb/src/ndbapi/NdbQueryBuilder.cpp 2012-02-23 15:41:31 +0000
+++ b/storage/ndb/src/ndbapi/NdbQueryBuilder.cpp 2012-03-22 14:18:01 +0000
@@ -1033,13 +1033,6 @@ NdbQueryBuilder::scanIndex(const NdbDict
returnErrIf(!m_impl.m_operations[0]->isScanOperation(),
QRY_WRONG_OPERATION_TYPE);
- // If the root is a sorted scan, we should not add another scan.
- const NdbQueryOptions::ScanOrdering rootOrder =
- m_impl.m_operations[0]->getOrdering();
- returnErrIf(rootOrder == NdbQueryOptions::ScanOrdering_ascending ||
- rootOrder == NdbQueryOptions::ScanOrdering_descending,
- QRY_MULTIPLE_SCAN_SORTED);
-
if (options != NULL)
{
// A child scan should not be sorted.
=== modified file 'storage/ndb/src/ndbapi/NdbQueryOperation.cpp'
--- a/storage/ndb/src/ndbapi/NdbQueryOperation.cpp 2012-02-23 15:41:31 +0000
+++ b/storage/ndb/src/ndbapi/NdbQueryOperation.cpp 2012-03-22 14:18:01 +0000
@@ -122,6 +122,7 @@ public:
explicit TupleCorrelation(Uint32 val)
: m_correlation(val)
{}
+
Uint32 toUint32() const
{ return m_correlation; }
@@ -3078,6 +3079,18 @@ NdbQueryImpl::doSend(int nodeId, bool la
batchByteSize);
assert(batchRows==root.getMaxBatchRows());
assert(batchRows<=batchByteSize);
+
+ /**
+ * Check if query is a sorted scan-scan.
+ * Ordering can then only be guarented by restricting
+ * parent batch to contain single rows.
+ * (Child scans will have 'normal' batch size).
+ */
+ if (root.getOrdering() != NdbQueryOptions::ScanOrdering_unordered &&
+ getQueryDef().getQueryType() == NdbQueryDef::MultiScanQuery)
+ {
+ batchRows = 1;
+ }
ScanTabReq::setScanBatch(reqInfo, batchRows);
scanTabReq->batch_byte_size = batchByteSize;
scanTabReq->first_batch_size = batchRows;
@@ -5106,16 +5119,6 @@ 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 &&
- getQueryDef().getQueryType() == NdbQueryDef::MultiScanQuery)
- {
- getQuery().setErrorCode(QRY_MULTIPLE_SCAN_SORTED);
- return -1;
- }
-
m_ordering = ordering;
return 0;
} // NdbQueryOperationImpl::setOrdering()
=== modified file 'storage/ndb/test/ndbapi/testSpj.cpp'
--- a/storage/ndb/test/ndbapi/testSpj.cpp 2012-03-15 14:33:38 +0000
+++ b/storage/ndb/test/ndbapi/testSpj.cpp 2012-03-22 14:18:01 +0000
@@ -929,32 +929,6 @@ NegativeTest::runGraphTest() const
builder->destroy();
}
- // Try adding a child scan to a sorted query.
- {
- NdbQueryBuilder* const builder = NdbQueryBuilder::create();
-
- NdbQueryOptions parentOptions;
- parentOptions.setOrdering(NdbQueryOptions::ScanOrdering_ascending);
-
- const NdbQueryIndexScanOperationDef* parentOperation
- = builder->scanIndex(m_nt1OrdIdx, m_nt1Tab, NULL, &parentOptions);
- ASSERT_ALWAYS(parentOperation != NULL);
-
- const NdbQueryOperand* const childOperands[] =
- {builder->linkedValue(parentOperation, "ui1"),
- NULL};
- const NdbQueryIndexBound bound(childOperands);
-
- if (builder->scanIndex(m_nt1OrdIdx, m_nt1Tab, &bound) != NULL ||
- builder->getNdbError().code != QRY_MULTIPLE_SCAN_SORTED)
- {
- g_err << "Sorted query with scan child scan gave unexpected result.";
- builder->destroy();
- return NDBT_FAILED;
- }
- builder->destroy();
- }
-
/**
* Try adding a child operation with two parents that are not descendants of each
* other (i.e. a diamond-shaped query graph).
=== modified file 'storage/ndb/test/tools/spj_sanity_test.cpp'
--- a/storage/ndb/test/tools/spj_sanity_test.cpp 2011-04-14 08:59:45 +0000
+++ b/storage/ndb/test/tools/spj_sanity_test.cpp 2012-03-22 14:18:01 +0000
@@ -63,8 +63,8 @@
#define QRY_SCAN_ORDER_ALREADY_SET 4821
#define QRY_PARAMETER_HAS_WRONG_TYPE 4822
#define QRY_CHAR_PARAMETER_TRUNCATED 4823
-#define QRY_MULTIPLE_SCAN_BRANCHES 4824
-#define QRY_MULTIPLE_SCAN_SORTED 4825
+#define QRY_MULTIPLE_SCAN_SORTED 4824
+#define QRY_BATCH_SIZE_TOO_SMALL 4825
namespace SPJSanityTest{
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-5.5-cluster-7.2-spj branch (ole.john.aske:3855 to 3856) | Ole John Aske | 22 Mar |
