From: Jan Wedvik
Date: September 21 2012 7:25am
Subject: bzr push into mysql-5.5-cluster-7.2 branch (jan.wedvik:4002 to 4003)
 Bug#14644936
List-Archive: http://lists.mysql.com/commits/144842
X-Bug: 14644936
Message-Id: <20120921072544.10066.62414.4003@atum17.no.oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 4003 Jan Wedvik	2012-09-21
      This commit is a backport of the fix for Bug#14644936 
      "INEFFICIENT AND INCORRECT EXECUTION OF PUSHABLE OUTER JOINS".
      
      This commit also adds a regression test,
      since this bug can only be reproduced in cluster.

    modified:
      mysql-test/suite/ndb/r/ndb_join_pushdown_default.result
      mysql-test/suite/ndb/t/ndb_join_pushdown.inc
      sql/sql_select.cc
 4002 magnus.blaudd@stripped	2012-09-20
      ndb
       - Make at least one symbol in ndbclient exported on Windows to make
        sure that export lib is generated

    modified:
      storage/ndb/src/ndbclient_exports.cpp
=== modified file 'mysql-test/suite/ndb/r/ndb_join_pushdown_default.result'
--- a/mysql-test/suite/ndb/r/ndb_join_pushdown_default.result	2012-06-12 09:13:41 +0000
+++ b/mysql-test/suite/ndb/r/ndb_join_pushdown_default.result	2012-09-21 07:24:28 +0000
@@ -5719,6 +5719,40 @@ id	select_type	table	type	possible_keys
 select x.a from t1 as x join t1 as y on y.a = x.b where x.a=4;
 a
 drop table t1;
+CREATE TABLE t1 (
+a int NOT NULL,
+b int DEFAULT NULL,
+c int NOT NULL,
+d int NOT NULL,
+PRIMARY KEY (`a`)
+) ENGINE=ndbcluster;
+create unique index ix1 on t1(b,c) using hash;
+Warnings:
+Warning	1121	Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
+insert into t1 values (1,NULL,1,1);
+explain extended select * from t1 as x1 left join (t1 as x2 join t1 as x3 on x2.d=x3.c) on x1.b=x3.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	x1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	x2	ALL	NULL	NULL	NULL	NULL	2	100.00	Parent of 2 pushed join@1
+1	SIMPLE	x3	ref	ix1	ix1	9	test.x1.b,test.x2.d	1	100.00	Child of 'x2' in pushed join@1
+Warnings:
+Note	1003	Can't push table 'x2' as child, 'type' must be a 'ref' access
+Note	1003	Can't push table 'x3' as child of 'x1', column 'x2.d' is outside scope of pushable join
+Note	1003	select `test`.`x1`.`a` AS `a`,`test`.`x1`.`b` AS `b`,`test`.`x1`.`c` AS `c`,`test`.`x1`.`d` AS `d`,`test`.`x2`.`a` AS `a`,`test`.`x2`.`b` AS `b`,`test`.`x2`.`c` AS `c`,`test`.`x2`.`d` AS `d`,`test`.`x3`.`a` AS `a`,`test`.`x3`.`b` AS `b`,`test`.`x3`.`c` AS `c`,`test`.`x3`.`d` AS `d` from `test`.`t1` `x1` left join (`test`.`t1` `x2` join `test`.`t1` `x3`) on(((`test`.`x1`.`b` = `test`.`x3`.`b`) and (`test`.`x2`.`d` = `test`.`x3`.`c`))) where 1
+create temporary table scan_count
+select * from information_schema.global_status 
+where variable_name = 'Ndb_scan_count';
+select * from t1 as x1 left join (t1 as x2 join t1 as x3 on x2.d=x3.c) on x1.b=x3.b;
+a	b	c	d	a	b	c	d	a	b	c	d
+1	NULL	1	1	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+select scan_count.VARIABLE_NAME,
+old.VARIABLE_VALUE - scan_count.VARIABLE_VALUE  
+from scan_count,information_schema.global_status as old 
+where old.variable_name = 'Ndb_scan_count';
+VARIABLE_NAME	old.VARIABLE_VALUE - scan_count.VARIABLE_VALUE
+NDB_SCAN_COUNT	2
+drop table scan_count;
+drop table t1;
 create temporary table spj_counts_at_end
 select counter_name, sum(val) as val 
 from ndbinfo.counters 
@@ -5751,8 +5785,8 @@ where new.variable_name = old.variable_n
 order by new.variable_name;
 variable_name	new.variable_value - old.variable_value
 NDB_PRUNED_SCAN_COUNT	8
-NDB_PUSHED_QUERIES_DEFINED	409
-NDB_PUSHED_QUERIES_DROPPED	8
+NDB_PUSHED_QUERIES_DEFINED	411
+NDB_PUSHED_QUERIES_DROPPED	9
 NDB_PUSHED_QUERIES_EXECUTED	541
 NDB_SORTED_SCAN_COUNT	11
 drop table server_counts_at_startup;

=== modified file 'mysql-test/suite/ndb/t/ndb_join_pushdown.inc'
--- a/mysql-test/suite/ndb/t/ndb_join_pushdown.inc	2012-05-31 15:19:49 +0000
+++ b/mysql-test/suite/ndb/t/ndb_join_pushdown.inc	2012-09-21 07:24:28 +0000
@@ -4196,6 +4196,49 @@ select x.a from t1 as x join t1 as y on
 
 drop table t1; 
 
+####################################################
+# Regression test for Bug#14644936 "INEFFICIENT AND INCORRECT EXECUTION OF 
+# PUSHABLE OUTER JOINS".
+####################################################
+
+connection ddl;
+CREATE TABLE t1 (
+  a int NOT NULL,
+  b int DEFAULT NULL,
+  c int NOT NULL,
+  d int NOT NULL,
+  PRIMARY KEY (`a`)
+) ENGINE=ndbcluster;
+
+create unique index ix1 on t1(b,c) using hash;
+
+connection spj;
+
+insert into t1 values (1,NULL,1,1);
+
+
+explain extended select * from t1 as x1 left join (t1 as x2 join t1 as x3 on x2.d=x3.c) on x1.b=x3.b; 
+
+# Record value before query.
+create temporary table scan_count
+       select * from information_schema.global_status 
+       where variable_name = 'Ndb_scan_count';
+
+# Query gives wrong result on mysql-trunk-cluster, and extra scan on both
+# mysql-5.5-cluster-7.2 and mysql-trunk-cluster.
+select * from t1 as x1 left join (t1 as x2 join t1 as x3 on x2.d=x3.c) on x1.b=x3.b; 
+
+# Bug causes value to increase by 3 rather than 2.
+select scan_count.VARIABLE_NAME,
+       old.VARIABLE_VALUE - scan_count.VARIABLE_VALUE  
+       from scan_count,information_schema.global_status as old 
+       where old.variable_name = 'Ndb_scan_count';
+
+drop table scan_count;
+
+connection ddl;
+drop table t1;
+
 ########################################
 # Verify DBSPJ counters for entire test:
 # Note: These tables are 'temporary' withing 'connection spj'

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-08-13 09:33:22 +0000
+++ b/sql/sql_select.cc	2012-09-21 07:24:28 +0000
@@ -13075,6 +13075,19 @@ join_read_linked_first(JOIN_TAB *tab)
   if (!table->file->inited)
     table->file->ha_index_init(tab->ref.key, tab->sorted);
 
+#ifndef MCP_BUG14644936
+  /**
+   * Backport of fix in (non-cluster) server code. To be removed when merged
+   * with server code.
+   */
+  /* Perform "Late NULLs Filtering" (see internals manual for explanations) */
+  for (uint i= 0 ; i < tab->ref.key_parts ; i++)
+  {
+    if ((tab->ref.null_rejecting & 1 << i) && tab->ref.items[i]->is_null())
+      DBUG_RETURN(-1);
+  }
+#endif
+
   if (cp_buffer_from_ref(tab->join->thd, table, &tab->ref))
   {
     table->status=STATUS_NOT_FOUND;

No bundle (reason: useless for push emails).