From: Ole John Aske
Date: November 15 2010 2:39pm
Subject: bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch
 (ole.john.aske:3368 to 3369)
List-Archive: http://lists.mysql.com/commits/123919
Message-Id: <20101115143920.51B89222@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3369 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
 3368 Jan Wedvik	2010-11-15
      This commit adds and/or rewrites a couple of asserts.

    modified:
      storage/ndb/src/ndbapi/NdbQueryBuilder.cpp
      storage/ndb/src/ndbapi/NdbQueryOperation.cpp
      storage/ndb/src/ndbapi/NdbQueryOperationImpl.hpp
=== 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:38:45 +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:38:45 +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:38:45 +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);

No bundle (reason: useless for push emails).