From: Ole John Aske Date: March 17 2011 8:02am Subject: bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3445 to 3446) List-Archive: http://lists.mysql.com/commits/133171 Message-Id: <20110317080241.01C0B223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3446 Ole John Aske 2011-03-17 SPJ-scan-scan Fixed a problem where parameters for a pushed lookup query might be specified in incorrect order. modified: mysql-test/suite/ndb/r/ndb_join_pushdown.result mysql-test/suite/ndb/t/ndb_join_pushdown.test sql/ha_ndbcluster.cc 3445 Ole John Aske 2011-03-01 [merge] Merge from 'telco' mainline to SPJ branch (Due to fix of Windows build break) modified: 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 2011-02-28 12:25:52 +0000 +++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result 2011-03-17 08:02:01 +0000 @@ -2188,6 +2188,42 @@ pk a3 b3 c3 d3 pk a3 b3 c3 d3 1001 31 47 1 31 1001 31 47 1 31 1002 47 63 2 47 1002 47 63 2 47 1003 63 31 3 63 1003 63 31 3 63 +explain extended +select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3 +and x.a3=0x2f and x.b3=0x3f; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE x const PRIMARY PRIMARY 8 const,const 1 100.00 Parent of 2 pushed join@1 +1 SIMPLE y eq_ref PRIMARY PRIMARY 8 const,test.x.d3 1 100.00 Child of pushed join@1; Using where +Warnings: +Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3` `x` join `test`.`t3` `y` where ((`test`.`y`.`b3` = `test`.`x`.`b3`) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f) and (`test`.`x`.`b3` = 0x3f)) +select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3 +and x.a3=0x2f and x.b3=0x3f; +a3 b3 c3 d3 a3 b3 c3 d3 +47 63 2 47 47 63 2 47 +explain extended +select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3 +and x.a3=0x2f and x.b3=0x3f; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE x const PRIMARY PRIMARY 8 const,const 1 100.00 Parent of 2 pushed join@1 +1 SIMPLE y eq_ref PRIMARY PRIMARY 8 const,test.x.d3 1 100.00 Child of pushed join@1; Using where +Warnings: +Note 1003 select `test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3_hash` `x` join `test`.`t3_hash` `y` where ((`test`.`y`.`b3` = `test`.`x`.`b3`) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f) and (`test`.`x`.`b3` = 0x3f)) +select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3 +and x.a3=0x2f and x.b3=0x3f; +a3 b3 c3 d3 a3 b3 c3 d3 +47 63 2 47 47 63 2 47 +explain extended +select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3 +and x.a3=0x2f and x.b3=0x3f; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE x const b3 b3 8 const,const 1 100.00 Parent of 2 pushed join@1 +1 SIMPLE y eq_ref b3 b3 8 const,test.x.d3 1 100.00 Child of pushed join@1; Using where +Warnings: +Note 1003 select `test`.`x`.`pk` AS `pk`,`test`.`x`.`a3` AS `a3`,`test`.`x`.`b3` AS `b3`,`test`.`x`.`c3` AS `c3`,`test`.`x`.`d3` AS `d3`,`test`.`y`.`pk` AS `pk`,`test`.`y`.`a3` AS `a3`,`test`.`y`.`b3` AS `b3`,`test`.`y`.`c3` AS `c3`,`test`.`y`.`d3` AS `d3` from `test`.`t3_unq` `x` join `test`.`t3_unq` `y` where ((`test`.`y`.`b3` = `test`.`x`.`b3`) and (`test`.`y`.`a3` = `test`.`x`.`d3`) and (`test`.`x`.`a3` = 0x2f) and (`test`.`x`.`b3` = 0x3f)) +select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3 +and x.a3=0x2f and x.b3=0x3f; +pk a3 b3 c3 d3 pk a3 b3 c3 d3 +1002 47 63 2 47 1002 47 63 2 47 drop table t3, t3_hash, t3_unq; create table t3 (a3 int, b3 int, c3 int, d3 int, primary key(a3), unique key(d3)) engine = ndb; @@ -4856,7 +4892,7 @@ LOCAL_TABLE_SCANS_SENT 228 PRUNED_RANGE_SCANS_RECEIVED 17 RANGE_SCANS_RECEIVED 718 READS_NOT_FOUND 404 -READS_RECEIVED 49 +READS_RECEIVED 52 SCAN_ROWS_RETURNED 78728 TABLE_SCANS_RECEIVED 228 select sum(spj_counts_at_end.val - spj_counts_at_startup.val) as 'LOCAL+REMOTE READS_SENT' @@ -4865,7 +4901,7 @@ where spj_counts_at_end.counter_name = s and (spj_counts_at_end.counter_name = 'LOCAL_READS_SENT' or spj_counts_at_end.counter_name = 'REMOTE_READS_SENT'); LOCAL+REMOTE READS_SENT -28863 +28871 drop table spj_counts_at_startup; drop table spj_counts_at_end; scan_count @@ -4875,9 +4911,9 @@ pruned_scan_count sorted_scan_count 9 pushed_queries_defined -354 +360 pushed_queries_dropped 11 pushed_queries_executed -526 +529 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 2011-02-28 11:46:27 +0000 +++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test 2011-03-17 08:02:01 +0000 @@ -1114,6 +1114,7 @@ insert into t3_unq values (1001, 0x1f, 0 insert into t3_unq values (1002, 0x2f, 0x3f, 2, 0x2f); insert into t3_unq values (1003, 0x3f, 0x1f, 3, 0x3f); +## Table scans (ALL) as pushed root explain extended select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3; --sorted_result @@ -1129,6 +1130,25 @@ select * from t3_unq x, t3_unq y where y --sorted_result select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3; +## Lookup (eq_ref/const) as pushed root +explain extended +select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3 + and x.a3=0x2f and x.b3=0x3f; +select * from t3 x, t3 y where y.a3=x.d3 and y.b3=x.b3 + and x.a3=0x2f and x.b3=0x3f; + +explain extended +select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3 + and x.a3=0x2f and x.b3=0x3f; +select * from t3_hash x, t3_hash y where y.a3=x.d3 and y.b3=x.b3 + and x.a3=0x2f and x.b3=0x3f; + +explain extended +select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3 + and x.a3=0x2f and x.b3=0x3f; +select * from t3_unq x, t3_unq y where y.a3=x.d3 and y.b3=x.b3 + and x.a3=0x2f and x.b3=0x3f; + drop table t3, t3_hash, t3_unq; ########### === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2011-03-01 08:01:47 +0000 +++ b/sql/ha_ndbcluster.cc 2011-03-17 08:02:01 +0000 @@ -910,6 +910,12 @@ ha_ndbcluster::check_if_pushable(const N const NdbQueryOperationTypeWrapper& query_def_type= root_operation->getType(); + if (m_disable_pushed_join) + { + DBUG_PRINT("info", ("Push disabled (HA_EXTRA_KEYREAD)")); + return FALSE; + } + if (query_def_type != type) { DBUG_PRINT("info", @@ -920,12 +926,6 @@ ha_ndbcluster::check_if_pushable(const N return FALSE; } - 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. @@ -4521,6 +4521,9 @@ ha_ndbcluster::pk_unique_index_read_key_ Uint32 offset= 0; NdbQueryParamValue paramValues[ndb_pushed_join::MAX_KEY_PART + ndb_pushed_join::MAX_REFERRED_FIELDS]; + uint map[ndb_pushed_join::MAX_KEY_PART]; + ndbcluster_build_key_map(m_table, m_index[idx], &table->key_info[idx], map); + // Bind key values defining root of pushed join for (i = 0, key_part= key_def->key_part; i < key_def->key_parts; i++, key_part++) { @@ -4531,11 +4534,11 @@ ha_ndbcluster::pk_unique_index_read_key_ DBUG_ASSERT(idx != table_share->primary_key); // PK can't be nullable DBUG_ASSERT(*(key+offset)==0); // Null values not allowed in key // Value is imm. after NULL indicator - paramValues[i]= NdbQueryParamValue(key+offset+1,shrinkVarChar); + paramValues[map[i]]= NdbQueryParamValue(key+offset+1,shrinkVarChar); } else // Non-nullable column { - paramValues[i]= NdbQueryParamValue(key+offset,shrinkVarChar); + paramValues[map[i]]= NdbQueryParamValue(key+offset,shrinkVarChar); } offset+= key_part->store_length; } No bundle (reason: useless for push emails).