From: Ole John Aske Date: November 16 2010 1:26pm Subject: bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3369 to 3370) List-Archive: http://lists.mysql.com/commits/124036 Message-Id: <20101116132652.2BB05222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3370 Ole John Aske 2010-11-16 SPJ-scan-scan: Added handling and testcase for JT_SYSTEM optimized tables: A JT_SYSTEM table has already been read, or determined to contain no rows, by the optimizer. This table should therefore be excluded from pushability evaluation in ha_ndbcluster. 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/sql_select.cc 3369 Ole John Aske 2010-11-15 spj-svs: Disable 'REF_OR_NULL' as pushable join. - See MTR testcase. modified: mysql-test/suite/ndb/r/ndb_join_pushdown.result mysql-test/suite/ndb/t/ndb_join_pushdown.test sql/abstract_query_plan.cc === modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result' --- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2010-11-15 14:38:45 +0000 +++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2010-11-16 13:26:19 +0000 @@ -4519,6 +4519,25 @@ k uq k uq 9 1 3 NULL 9 1 9 1 drop table t; +create table t (k int primary key, uq int) engine = ndb; +insert into t values (1,3), (3,NULL), (6,9), (9,1); +explain extended +select * from t as a left join t as b +on a.k is null and a.uq=b.uq; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE b system NULL NULL NULL NULL 1 100.00 +1 SIMPLE a ALL NULL NULL NULL NULL 4 50.00 +Warnings: +Note 1644 Table 'b' was const-table optimized, no runtime access required +Note 1003 select `test`.`a`.`k` AS `k`,`test`.`a`.`uq` AS `uq`,NULL AS `k`,NULL AS `uq` from `test`.`t` `a` left join `test`.`t` `b` on((isnull(`test`.`a`.`k`) and (`test`.`a`.`uq` = NULL))) +select * from t as a left join t as b +on a.k is null and a.uq=b.uq; +k uq k uq +1 3 NULL NULL +3 NULL NULL NULL +6 9 NULL NULL +9 1 NULL NULL +drop table t; create table tc( a varchar(10) not null, b varchar(10), @@ -4611,7 +4630,7 @@ LOCAL+REMOTE READS_SENT drop table spj_counts_at_startup; drop table spj_counts_at_end; scan_count -2029 +2035 pruned_scan_count 8 sorted_scan_count === modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test' --- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2010-11-15 14:38:45 +0000 +++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2010-11-16 13:26:19 +0000 @@ -2915,6 +2915,24 @@ select straight_join * from t as a join drop table t; +######## +# JT_SYSTEM testcase, 'a.k is null' is known 'false' -> +# Join condition will always fail, and all 'left joins' can be NULL complemented wo/ +# even requiring to access left table (b) which becomes 'system' -> No pushed joins ! +######## +create table t (k int primary key, uq int) engine = ndb; +insert into t values (1,3), (3,NULL), (6,9), (9,1); + +explain extended +select * from t as a left join t as b + on a.k is null and a.uq=b.uq; + +--sorted_result +select * from t as a left join t as b + on a.k is null and a.uq=b.uq; + +drop table t; + ####### # Test of varchar query parameteres. ####### === modified file 'sql/abstract_query_plan.cc' --- a/sql/abstract_query_plan.cc 2010-11-15 14:38:45 +0000 +++ b/sql/abstract_query_plan.cc 2010-11-16 13:26:19 +0000 @@ -309,6 +309,11 @@ namespace AQP */ switch (join_tab->type) { + case JT_SYSTEM: + DBUG_PRINT("info", ("Operation %d is const-optimized.", m_tab_no)); + m_access_type= AT_FIXED; + break; + case JT_EQ_REF: case JT_CONST: m_index_no= join_tab->ref.key; === modified file 'sql/abstract_query_plan.h' --- a/sql/abstract_query_plan.h 2010-11-11 13:07:41 +0000 +++ b/sql/abstract_query_plan.h 2010-11-16 13:26:19 +0000 @@ -143,6 +143,8 @@ namespace AQP { /** For default initialization.*/ AT_VOID, + /** Value has already been fetched / determined by optimizer.*/ + AT_FIXED, /** Do a lookup of a single primary key.*/ AT_PRIMARY_KEY, /** Do a lookup of a single unique index key.*/ === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2010-11-15 11:08:25 +0000 +++ b/sql/ha_ndbcluster.cc 2010-11-16 13:26:19 +0000 @@ -874,6 +874,12 @@ ndb_pushed_builder_ctx::init_pushability DBUG_ASSERT(false); break; + case AQP::AT_FIXED: + EXPLAIN_NO_PUSH("Table '%s' was const-table optimized, no runtime access required", + table_access->get_table()->alias); + m_tables[i].m_maybe_pushable= 0; + break; + case AQP::AT_OTHER: EXPLAIN_NO_PUSH("Table '%s' is not pushable: %s", table_access->get_table()->alias, @@ -7545,6 +7551,9 @@ int ha_ndbcluster::index_read_last(uchar This is actually (yet) never called for ndbcluster tables, as these table types does not set HA_STATS_RECORDS_IS_EXACT. + UPDATE: Might be called if the predicate contain ' IS NULL', and + is defined as 'NOT NULL' (or is part of primary key) + Implemented regardless of this as the default implememtation would break any pushed joins as it calls ha_rnd_end() / ha_index_end() at end of execution. */ === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-10-21 08:31:51 +0000 +++ b/sql/sql_select.cc 2010-11-16 13:26:19 +0000 @@ -11973,7 +11973,7 @@ join_read_system(JOIN_TAB *tab) table->s->primary_key))) { if (error != HA_ERR_END_OF_FILE) - return report_error(table, error); + DBUG_RETURN(report_error(table, error)); mark_as_null_row(tab->table); empty_record(table); // Make empty record DBUG_RETURN(-1); No bundle (reason: useless for push emails).