From: Ole John Aske Date: November 11 2010 1:08pm Subject: bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3359 to 3360) List-Archive: http://lists.mysql.com/commits/123605 Message-Id: <20101111130856.6AA4F222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 3359 Ole John Aske 2010-11-10 spj-svs- Changed RQG test to run with 'engine_condition_pushdown=on' after fix of bug 57735. modified: storage/ndb/test/rqg/runall.sh === 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 rev_ranges; List_iterator rev_it; uint used_key_parts; No bundle (reason: useless for push emails).