join_nested.test, join_nested.result:

  Added a case for bug #4976 when one of the inner tables is empty.
select.result, join.result:
  Reversed the previous change of the erronious fix for bug #4976.
sql_select.cc:
  The previous fix for bug 4976 was reversed as it
  erroniously converted an outer join into an innner join
  when on_expression does not refer to outer tables.
  This is not valid if inner tables return an empty set.
  Setting dependency on outer tables was added for the above cases.
  To fix the crash in the test case of bug #4976  
  a guard was added that blocks running the crashing code for
  nested outer joins.


sql/sql_select.cc:
  The previous fix for bug 4976 was reversed as it
  erroniously converted an outer join into an innner join
  when on_expression does not refer to outer tables.
  This is not valid if inner tables return an empty set.
  Setting dependency on outer tables was added.
  The crash of the test case for bug #4976 was fixed
  adding a guard that blocks running the code for
  nested outer joins.
mysql-test/r/join.result:
  Reversed the previous change of the erronious fix for bug #4976.
mysql-test/r/select.result:
  Reversed the previous change of the erronious fix for bug #4976.
mysql-test/r/join_nested.result:
  Added a case for bug #4976 when one of the inner tables is empty.
mysql-test/t/join_nested.test:
  Added a case for bug #4976 when one of the inner tables is empty.

3 files changed, 103 insertions, 7 deletions

diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index d33205c418b..db9b051a58f 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -59,9 +59,11 @@ id	count(t2.id)
 107	1
 select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
 id	id
+NULL	75
 explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
 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
+1	SIMPLE	t1	const	PRIMARY	NULL	NULL	NULL	1	Impossible ON condition
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 explain select t1.id, t2.id from t1, t2 where t2.id = t1.id and t1.id <0 and t1.id > 0;
 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
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index 81ca53cb727..09c3e67ae70 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -1218,12 +1218,106 @@ INSERT INTO t2 VALUES (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
 INSERT INTO t3 VALUES (0), (1), (2), (3), (4), (5);
 EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON c < 3 and b = c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	index	c	c	5	NULL	6	Using where; Using index
-1	SIMPLE	t2	ref	b	b	5	test.t3.c	2	Using where; Using index
 1	SIMPLE	t1	index	NULL	a	5	NULL	21	Using index
+1	SIMPLE	t3	index	c	c	5	NULL	6	Using index
+1	SIMPLE	t2	ref	b	b	5	test.t3.c	2	Using index
 EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	b	b	5	NULL	3	Using where; Using index
-1	SIMPLE	t3	ref	c	c	5	test.t2.b	2	Using where; Using index
 1	SIMPLE	t1	index	NULL	a	5	NULL	21	Using index
+1	SIMPLE	t3	index	c	c	5	NULL	6	Using index
+1	SIMPLE	t2	ref	b	b	5	test.t3.c	2	Using index
+SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
+a	b	c
+NULL	0	0
+NULL	1	1
+NULL	2	2
+0	0	0
+0	1	1
+0	2	2
+1	0	0
+1	1	1
+1	2	2
+2	0	0
+2	1	1
+2	2	2
+3	0	0
+3	1	1
+3	2	2
+4	0	0
+4	1	1
+4	2	2
+5	0	0
+5	1	1
+5	2	2
+6	0	0
+6	1	1
+6	2	2
+7	0	0
+7	1	1
+7	2	2
+8	0	0
+8	1	1
+8	2	2
+9	0	0
+9	1	1
+9	2	2
+10	0	0
+10	1	1
+10	2	2
+11	0	0
+11	1	1
+11	2	2
+12	0	0
+12	1	1
+12	2	2
+13	0	0
+13	1	1
+13	2	2
+14	0	0
+14	1	1
+14	2	2
+15	0	0
+15	1	1
+15	2	2
+16	0	0
+16	1	1
+16	2	2
+17	0	0
+17	1	1
+17	2	2
+18	0	0
+18	1	1
+18	2	2
+19	0	0
+19	1	1
+19	2	2
+DELETE FROM t3;
+EXPLAIN SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	21	Using index
+1	SIMPLE	t3	index	c	c	5	NULL	0	Using index
+1	SIMPLE	t2	ref	b	b	5	test.t3.c	2	Using index
+SELECT a, b, c FROM t1 LEFT JOIN (t2, t3) ON b < 3 and b = c;
+a	b	c
+NULL	NULL	NULL
+0	NULL	NULL
+1	NULL	NULL
+2	NULL	NULL
+3	NULL	NULL
+4	NULL	NULL
+5	NULL	NULL
+6	NULL	NULL
+7	NULL	NULL
+8	NULL	NULL
+9	NULL	NULL
+10	NULL	NULL
+11	NULL	NULL
+12	NULL	NULL
+13	NULL	NULL
+14	NULL	NULL
+15	NULL	NULL
+16	NULL	NULL
+17	NULL	NULL
+18	NULL	NULL
+19	NULL	NULL
 DROP TABLE t1,t2,t3;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 3f4cfa815d3..16b12a436d3 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -2181,10 +2181,10 @@ a	a	a
 select * from (t1 as t2 left join t1 as t3 using (a)) left outer join t1 on t1.a>1;
 a	a	a
 1	1	2
-2	2	2
-3	3	2
 1	1	3
+2	2	2
 2	2	3
+3	3	2
 3	3	3
 select * from t1 left outer join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;
 a	a	a