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.


mysql-test/r/join_nested.result:
  Added a test case for bug #16260.
mysql-test/t/join_nested.test:
  Added a test case for bug #16260.
sql/sql_select.cc:
  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.

3 files changed, 53 insertions, 1 deletions

diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index faad969fcd1..dfcfa35d31d 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -1481,3 +1481,26 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 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;
diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index 145edded486..8adcf05be93 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -914,3 +914,31 @@ explain select * from t1 left join
   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;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 95da0e6263a..63d46934555 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2172,7 +2172,8 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables, COND *conds,
 	  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.