bk commit into 5.0 tree (igor:1.1944) BUG#11284

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1944 05/06/15 05:56:19 igor@stripped +3 -0
  join_nested.result, join_nested.test:
    Added a teast case for bug #11284.
  sql_select.cc:
    Fixed bug #11284.
    Optimization with empty inner table currently cannot be
    used in the case of nested outer join.

  mysql-test/r/join_nested.result
    1.11 05/06/15 05:54:52 igor@stripped +22 -0

  mysql-test/t/join_nested.test
    1.7 05/06/15 05:53:39 igor@stripped +18 -0
    Added a teast case for bug #11284.

  sql/sql_select.cc
    1.338 05/06/15 05:42:39 igor@stripped +1 -1
    Fixed bug #11284.
    Optimization with empty inner table currently cannot be
    used in the case of nested outer join.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/dev/mysql-5.0-0

--- 1.337/sql/sql_select.cc	Wed Jun 15 02:06:20 2005
+++ 1.338/sql/sql_select.cc	Wed Jun 15 05:42:39 2005
@@ -2158,7 +2158,7 @@
     if (*s->on_expr_ref)
     {
       /* s is the only inner table of an outer join */
-      if (!table->file->records)
+      if (!table->file->records && !embedding)
       {						// Empty table
         s->dependent= 0;                        // Ignore LEFT JOIN depend.
 	set_position(join,const_count++,s,(KEYUSE*) 0);

--- 1.10/mysql-test/r/join_nested.result	Wed Nov  3 02:39:31 2004
+++ 1.11/mysql-test/r/join_nested.result	Wed Jun 15 05:54:52 2005
@@ -1321,3 +1321,25 @@
 18	NULL	NULL
 19	NULL	NULL
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (c11 int);
+CREATE TABLE t2 (c21 int);
+CREATE TABLE t3 (c31 int);
+INSERT INTO t1 VALUES (4), (5);
+SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;
+c11	c21
+4	NULL
+5	NULL
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
+c11	c21	c31
+4	NULL	NULL
+5	NULL	NULL
+EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	0	
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	0	
+DROP TABLE t1,t2,t3;

--- 1.6/mysql-test/t/join_nested.test	Thu Aug 12 23:33:48 2004
+++ 1.7/mysql-test/t/join_nested.test	Wed Jun 15 05:53:39 2005
@@ -752,3 +752,21 @@
 SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
 
 DROP TABLE t1,t2,t3;
+
+#
+# Test for bug #11284: empty table in a nested left join
+# 
+
+CREATE TABLE t1 (c11 int);
+CREATE TABLE t2 (c21 int);
+CREATE TABLE t3 (c31 int);
+
+INSERT INTO t1 VALUES (4), (5);
+
+SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON c11=c21;
+
+SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
+EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
+
+DROP TABLE t1,t2,t3;