From: Ole John Aske
Date: December 7 2010 11:56am
Subject: bzr push into mysql-5.1-telco-7.0-spj-scan-vs-scan branch
 (ole.john.aske:3388 to 3389) Bug#58422
List-Archive: http://lists.mysql.com/commits/126197
X-Bug: 58422
Message-Id: <20101207115658.A01F1222@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3389 Ole John Aske	2010-12-07
      SPJ-scan-scan: Cherry picked fix for bug#58422: Incorrect result when OUTER JOIN'ing with an empty table
      
      Fixed incorrect checks in join_read_const_table() for when to 
      accept a non-existing, or empty const-row as a part of the const'ified 
      set of tables.
      
      Intention of this test is to only accept NULL-rows if this table is outer joined
      into the resultset. (In case of an inner-join we can conclude at this point that 
      resultset will be empty, end we want to return 'error' to signal this.)
      
      Initially 'maybe_null' is set to the same value as 'outer_join' in 
      setup_table_map(), mysql_priv.h ~line 2424. Later simplify_joins() will
      attemp to replace outer joins by inner join whenever possible. This
      will cause 'outer_join' to be updated. However, 'maybe_null' is *not* updated
      to reflect this rewrite as this field is used to currectly set the 'nullability'
      property for the columns in the resultset.
      
      We should therefore change join_read_const_table() to check the 'outer_join'
      property instead of 'maybe_null', as this correctly reflect the nullability of
      the *execution plan* (not *resultset*).

    modified:
      mysql-test/r/select.result
      mysql-test/t/select.test
      sql/sql_select.cc
 3388 Ole John Aske	2010-12-06
      SPJ-scan-scan: Cherry picked fix for bug#58750.
      
       If ha_ndbcluster::read_range_first_to_buf() had to do a ::full_table_scan()
       on a UNIQUE_INDEX, the resulting ScanOperation was not properly closed at end
       of its lifetime. If lots of such operations is executed within the
       same transaction, we will eventually run out of operation or transaction objects.
       (Transaction objects is due to a dumnmy Hupp'ed transaction object is allocated as
       part of a ScanOperation)
      
       This fix ensures that ::close_scan() is called for any open cursors at start of
       ha_ndbcluster::read_range_first_to_buf().

    modified:
      mysql-test/suite/ndb/r/ndb_index_unique.result
      mysql-test/suite/ndb/t/ndb_index_unique.test
      sql/ha_ndbcluster.cc
=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result	2010-06-24 08:00:48 +0000
+++ b/mysql-test/r/select.result	2010-12-07 11:56:10 +0000
@@ -4797,4 +4797,68 @@ SELECT 1 FROM t1 ORDER BY a COLLATE lati
 1
 1
 DROP TABLE t1;
+#
+# Bug #58422: Incorrect result when OUTER JOIN'ing 
+# with an empty table
+#
+CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
+EXPLAIN
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON TRUE)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON TRUE)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+pk	i	pk	i	pk	i
+EXPLAIN
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 CROSS JOIN t_empty)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 CROSS JOIN t_empty)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+pk	i	pk	i	pk	i
+EXPLAIN
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+pk	i	pk	i	pk	i
+DROP TABLE t1,t2,t_empty;
 End of 5.1 tests

=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test	2010-06-24 17:13:08 +0000
+++ b/mysql-test/t/select.test	2010-12-07 11:56:10 +0000
@@ -4088,4 +4088,74 @@ SELECT 1 FROM t1 ORDER BY a COLLATE lati
 DROP TABLE t1;
 
 
+
+--echo #
+--echo # Bug #58422: Incorrect result when OUTER JOIN'ing 
+--echo # with an empty table
+--echo #
+
+CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
+
+EXPLAIN
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 INNER JOIN t_empty ON TRUE)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 INNER JOIN t_empty ON TRUE)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+
+EXPLAIN
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 CROSS JOIN t_empty)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 CROSS JOIN t_empty)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+
+EXPLAIN
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+
+
+DROP TABLE t1,t2,t_empty;
+
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-12-03 14:55:15 +0000
+++ b/sql/sql_select.cc	2010-12-07 11:56:10 +0000
@@ -11920,7 +11920,7 @@ join_read_const_table(JOIN_TAB *tab, POS
       /* Mark for EXPLAIN that the row was not found */
       pos->records_read=0.0;
       pos->ref_depend_map= 0;
-      if (!table->maybe_null || error > 0)
+      if (!table->pos_in_table_list->outer_join || error > 0)
 	DBUG_RETURN(error);
     }
   }
@@ -11941,7 +11941,7 @@ join_read_const_table(JOIN_TAB *tab, POS
       /* Mark for EXPLAIN that the row was not found */
       pos->records_read=0.0;
       pos->ref_depend_map= 0;
-      if (!table->maybe_null || error > 0)
+      if (!table->pos_in_table_list->outer_join || error > 0)
 	DBUG_RETURN(error);
     }
   }

No bundle (reason: useless for push emails).