List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:May 5 2010 1:25pm
Subject:bzr push into mysql-5.1-telco-7.0-spj branch (ole.john.aske:3145 to 3146)
View as plain text  
 3146 Ole John Aske	2010-05-05
      The abstract_query_plan.cc module failed to recognice outer referrences to table outside 
      the current scope available from JOIN and JOIN_TAB[].
      
      Outer referrence information is available through the 'table_map Item::used_tables()' 
      metode which will include OUTER_REF_TABLE_BIT in the returned table_map if Item is an
      outer referrence.
      
      Changed Join_plan::get_referred_table_access to use ::used_tables() instead of 
      directly accessing 'field->table->map' - Added 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
 3145 Jonas Oreland	2010-04-30 [merge]
      ndb spj - merge DbspjMain extension to interpreted programs

    modified:
      storage/ndb/include/kernel/signaldata/DbspjErr.hpp
      storage/ndb/include/kernel/signaldata/QueryTree.hpp
      storage/ndb/include/kernel/signaldata/ScanTab.hpp
      storage/ndb/src/kernel/blocks/dbspj/Dbspj.hpp
      storage/ndb/src/kernel/blocks/dbspj/DbspjMain.cpp
      storage/ndb/src/kernel/blocks/dbtup/DbtupExecQuery.cpp
      storage/ndb/test/tools/Makefile.am
      storage/ndb/test/tools/test_spj.cpp
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2010-04-13 12:01:49 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2010-05-05 13:20:48 +0000
@@ -1541,6 +1541,20 @@ xor c > 5;
 a	b	c
 3	2	2
 6	2	2
+explain
+select t1.a, (select straight_join X.a from t1 as X join t1 as Y on X.a=Y.b where Y.a = t1.b) from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	
+2	DEPENDENT SUBQUERY	X	ALL	PRIMARY	NULL	NULL	NULL	6	
+2	DEPENDENT SUBQUERY	Y	eq_ref	PRIMARY,b	PRIMARY	4	test.t1.b	1	Using where
+select t1.a, (select straight_join X.a from t1 as X join t1 as Y on X.a=Y.b where Y.a = t1.b) from t1;
+a	(select straight_join X.a from t1 as X join t1 as Y on X.a=Y.b where Y.a = t1.b)
+3	1
+6	1
+5	NULL
+1	NULL
+2	NULL
+4	NULL
 drop table t1;
 create table t1 (a int primary key, b int) engine = ndb;
 create table t2 (a int primary key, b int) engine = myisam;

=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2010-04-13 12:01:49 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2010-05-05 13:20:48 +0000
@@ -1002,6 +1002,15 @@ from t1 where b in 
   (select x.a from t1 as x join t1 as y on (y.a = x.b))
 xor c > 5;
 
+##############
+## Subqueries with EQ_REFs in subquery containing an outer referrences
+## to 't1.b' should not be pushed as outer referrences are outside
+## the scope of our JOIN_TAB's
+##############
+explain
+select t1.a, (select straight_join X.a from t1 as X join t1 as Y on X.a=Y.b where Y.a = t1.b) from t1;
+select t1.a, (select straight_join X.a from t1 as X join t1 as Y on X.a=Y.b where Y.a = t1.b) from t1;
+
 drop table t1;
 
 # mixed engines

=== modified file 'sql/abstract_query_plan.cc'
--- a/sql/abstract_query_plan.cc	2010-03-24 00:18:08 +0000
+++ b/sql/abstract_query_plan.cc	2010-05-05 13:20:48 +0000
@@ -85,10 +85,16 @@ namespace AQP
     DBUG_ASSERT(start_tab >= m_table_accesses &&
                 start_tab < m_table_accesses+get_access_count());
 
+    table_map used_tables = field_item->used_tables();
+
+    /* Early elimination of outer referrences. */
+//  if (used_tables & OUTER_REF_TABLE_BIT)
+//    return NULL;
+
     const Table_access* end_tab= m_table_accesses+get_access_count();
     for (const Table_access* tab= start_tab; tab < end_tab; tab++)
     {
-      if (tab->get_join_tab()->table->map == field_item->field->table->map)
+      if (tab->get_join_tab()->table->map == used_tables)
         return tab;
     }
     return NULL;


Attachment: [text/bzr-bundle] bzr/ole.john.aske@sun.com-20100505132048-836cup78lyrnc73k.bundle
Thread
bzr push into mysql-5.1-telco-7.0-spj branch (ole.john.aske:3145 to 3146) Ole John Aske5 May