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).
| Thread |
|---|
| • bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(ole.john.aske:3445 to 3446) | Ole John Aske | 17 Mar |
