From: igor
Date: January 28 2006 5:21am
Subject: bk commit into 5.0 tree (igor:1.2010) BUG#16260
List-Archive: http://lists.mysql.com/commits/1768
X-Bug: 16260
Message-Id: <20060128052115.DE74022902F@rurik.mysql.com>

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.2010 06/01/27 21:20:28 igor@stripped +3 -0
  Fixed bug #16260.
  The problem has manifested itself in the cases when we have a nested outer join
  for which it can be inferred that one of the inner tables is a single row table.

  sql/sql_select.cc
    1.389 06/01/27 21:20:09 igor@stripped +2 -1
    Fixed bug #16260.
    The problem has manifested itself in the cases when we have a nested outer join
    for which it can be inferred that one of the inner tables is a single row table.
    A table is never considered as a const table if it is used in a nested join 
    that serves as an inner operand of an outer join.  

  mysql-test/t/join_nested.test
    1.18 06/01/27 21:20:09 igor@stripped +28 -0
    Added a test case for bug #16260.

  mysql-test/r/join_nested.result
    1.22 06/01/27 21:20:09 igor@stripped +23 -0
    Added a test case for bug #16260.

# 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.388/sql/sql_select.cc	2006-01-24 04:48:13 -08:00
+++ 1.389/sql/sql_select.cc	2006-01-27 21:20:09 -08:00
@@ -2172,7 +2172,8 @@
 	  if (eq_part.is_prefix(table->key_info[key].key_parts) &&
 	      ((table->key_info[key].flags & (HA_NOSAME | HA_END_SPACE_KEY)) ==
 	       HA_NOSAME) &&
-              !table->fulltext_searched)
+              !table->fulltext_searched && 
+              !table->pos_in_table_list->embedding)
 	  {
 	    if (const_ref == eq_part)
 	    {					// Found everything for ref.

--- 1.21/mysql-test/r/join_nested.result	2006-01-13 12:55:22 -08:00
+++ 1.22/mysql-test/r/join_nested.result	2006-01-27 21:20:09 -08:00
@@ -1481,3 +1481,26 @@
 1	SIMPLE	t2	ref	a	a	5	test.t1.a	1	
 1	SIMPLE	t3	ref	a	a	5	test.t2.a	1	
 drop table t1, t2, t3;
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10));
+CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10));
+CREATE TABLE t3 (cid int NOT NULL PRIMARY KEY,
+id int NOT NULL,
+pid int NOT NULL);
+INSERT INTO t1 VALUES (1, 'A'), (3, 'C');
+INSERT INTO t2 VALUES (1, 'A'), (3, 'C');
+INSERT INTO t3 VALUES (1, 1, 1), (3, 3, 3);
+SELECT * FROM t1 p LEFT JOIN (t3 JOIN t1)
+ON (t1.id=t3.id AND t1.type='B' AND p.id=t3.id)
+LEFT JOIN t2 ON (t3.pid=t2.pid)
+WHERE p.id=1;
+id	type	cid	id	pid	id	type	pid	type
+1	A	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+CREATE VIEW v1 AS
+SELECT t3.* FROM t3 JOIN t1 ON t1.id=t3.id AND t1.type='B';
+SELECT * FROM t1 p LEFT JOIN v1 ON p.id=v1.id
+LEFT JOIN t2 ON v1.pid=t2.pid
+WHERE p.id=1;
+id	type	cid	id	pid	pid	type
+1	A	NULL	NULL	NULL	NULL	NULL
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;

--- 1.17/mysql-test/t/join_nested.test	2006-01-13 12:55:22 -08:00
+++ 1.18/mysql-test/t/join_nested.test	2006-01-27 21:20:09 -08:00
@@ -914,3 +914,31 @@
   on (t1.a = t2.a);
 drop table t1, t2, t3;
 
+#
+# Bug #16260: single row table in the inner nest of an outer join  
+#
+
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10));
+CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10));
+CREATE TABLE t3 (cid int NOT NULL PRIMARY KEY,
+                 id int NOT NULL,
+                 pid int NOT NULL);
+
+INSERT INTO t1 VALUES (1, 'A'), (3, 'C');
+INSERT INTO t2 VALUES (1, 'A'), (3, 'C');
+INSERT INTO t3 VALUES (1, 1, 1), (3, 3, 3);
+
+SELECT * FROM t1 p LEFT JOIN (t3 JOIN t1)
+                     ON (t1.id=t3.id AND t1.type='B' AND p.id=t3.id)
+                   LEFT JOIN t2 ON (t3.pid=t2.pid)
+  WHERE p.id=1;
+
+CREATE VIEW v1 AS
+  SELECT t3.* FROM t3 JOIN t1 ON t1.id=t3.id AND t1.type='B';
+
+SELECT * FROM t1 p LEFT JOIN v1 ON p.id=v1.id
+                   LEFT JOIN t2 ON v1.pid=t2.pid
+  WHERE p.id=1;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;