From: Ole John Aske Date: March 22 2012 2:18pm Subject: bzr push into mysql-5.5-cluster-7.2-spj branch (ole.john.aske:3855 to 3856) List-Archive: http://lists.mysql.com/commits/143283 Message-Id: <20120322141834.BA876244@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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, - (idxget_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).