List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:March 17 2011 8:02am
Subject:bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch
(ole.john.aske:3446)
View as plain text  
#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0-spj-scan-scan/ based on revid:ole.john.aske@stripped

 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
=== 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;
   }


Attachment: [text/bzr-bundle] bzr/ole.john.aske@oracle.com-20110317080201-8s8enpv2oll1i1og.bundle
Thread
bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch(ole.john.aske:3446) Ole John Aske17 Mar