bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3366)

[Ole John Aske]

#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

 3366 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-11 13:07:41 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown.result	2010-11-15 14:27:20 +0000
@@ -4497,6 +4497,28 @@ pk1	pk2	pk1	pk2
 3	6	6	9
 1	3	3	6
 drop table t;
+create table t (k int, uq int, unique key ix1 (uq)) engine = ndb;
+insert into t values (1,3), (3,NULL), (6,9), (9,1);
+explain extended
+select straight_join * from t as a join t as b 
+on a.uq=b.uq or b.uq is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	a	ALL	ix1	NULL	NULL	NULL	4	50.00	
+1	SIMPLE	b	ref_or_null	ix1	ix1	5	test.a.uq	2	100.00	Using where
+Warnings:
+Note	1644	Table 'b' is not pushable: This table access method can not be pushed.
+Note	1003	select straight_join `test`.`a`.`k` AS `k`,`test`.`a`.`uq` AS `uq`,`test`.`b`.`k` AS `k`,`test`.`b`.`uq` AS `uq` from `test`.`t` `a` join `test`.`t` `b` where ((`test`.`b`.`uq` = `test`.`a`.`uq`) or isnull(`test`.`b`.`uq`))
+select straight_join * from t as a join t as b 
+on a.uq=b.uq or b.uq is null;
+k	uq	k	uq
+1	3	1	3
+1	3	3	NULL
+3	NULL	3	NULL
+6	9	3	NULL
+6	9	6	9
+9	1	3	NULL
+9	1	9	1
+drop table t;
 create table tc(
 a varchar(10) not null,
 b varchar(10),
@@ -4589,7 +4611,7 @@ LOCAL+REMOTE READS_SENT
 drop table spj_counts_at_startup;
 drop table spj_counts_at_end;
 scan_count
-2021
+2029
 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-11 13:07:41 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.test	2010-11-15 14:27:20 +0000
@@ -2897,6 +2897,25 @@ select * from t as t1 join t as t2
 drop table t;
 
 #######
+# Testcase using 'REF_OR_NULL'
+# 'ref_or_null' contains elements of left outer join wo/ being identical.
+# 
+create table t (k int, uq int, unique key ix1 (uq)) engine = ndb;
+insert into t values (1,3), (3,NULL), (6,9), (9,1);
+
+# Currently we do not handle 'ref_or_null' correctly.
+# It is therefore disabled as pushable
+explain extended
+select straight_join * from t as a join t as b 
+  on a.uq=b.uq or b.uq is null;
+
+--sorted_result
+select straight_join * from t as a join t as b 
+  on a.uq=b.uq or b.uq is null;
+
+drop table t;
+
+#######
 # Test of varchar query parameteres.
 #######
 

=== modified file 'sql/abstract_query_plan.cc'
--- a/sql/abstract_query_plan.cc	2010-11-11 13:19:27 +0000
+++ b/sql/abstract_query_plan.cc	2010-11-15 14:27:20 +0000
@@ -327,7 +327,6 @@ namespace AQP
       break;
 
     case JT_REF:
-    case JT_REF_OR_NULL:
     {
       DBUG_ASSERT(join_tab->ref.key >= 0);
       DBUG_ASSERT(join_tab->ref.key < MAX_KEY);


Attachment: [text/bzr-bundle] bzr/[e-mail]