Fixed a cost estimation bug introduced into in the function best_access_path

of the 5.3 code line after a merge with 5.2 on 2010-10-28
in order not to allow the cost to access a joined table to be equal
to 0 ever.

Expanded data sets for many test cases to get the same execution plans
as before.

1 files changed, 49 insertions, 36 deletions

diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result
index d11955226ae..ff5a31b599b 100644
--- a/mysql-test/r/join_nested_jcl6.result
+++ b/mysql-test/r/join_nested_jcl6.result
@@ -850,25 +850,26 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t1` join `test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) where (`test`.`t1`.`a` <= 2)
+INSERT INTO t2 VALUES  (-1,9,0), (-3,10,0), (-2,8,0), (-4,11,0), (-5,15,0);
 CREATE INDEX idx_b ON t2(b);
 EXPLAIN EXTENDED
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
 FROM (t3,t4)
 LEFT JOIN              
 (t1,t2)
-ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
+ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b AND t2.a>0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	
 1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
 1	SIMPLE	t2	ref	idx_b	idx_b	5	test.t3.b	2	100.00	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (incremental, BNL join)
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t3`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`b` is not null))) where 1
+Note	1003	select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b` from `test`.`t3` join `test`.`t4` left join (`test`.`t1` join `test`.`t2`) on(((`test`.`t2`.`a` > 0) and (`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t3`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`b`) and (`test`.`t3`.`b` is not null))) where 1
 SELECT t2.a,t2.b,t3.a,t3.b,t4.a,t4.b
 FROM (t3,t4)
 LEFT JOIN              
 (t1,t2)
-ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b;
+ON t3.a=1 AND t3.b=t2.b AND t2.b=t4.b AND t2.a>0;
 a	b	a	b	a	b
 4	2	1	2	3	2
 4	2	1	2	4	2
@@ -887,7 +888,7 @@ LEFT JOIN
 t2
 LEFT JOIN              
 (t3, t4)
-ON t3.a=1 AND t2.b=t4.b,
+ON t3.a=1 AND t2.b=t4.b AND t2.a>0,
 t5 
 LEFT JOIN 
 ( 
@@ -916,16 +917,18 @@ t0.b=t1.b AND
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t0.b	3	100.00	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t8	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	hash_ALL	NULL	#hash#$hj	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t2`.`a` > 0) and (`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t3`.`b` = `test`.`t4`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+INSERT INTO t4 VALUES (-3,12,0), (-4,13,0), (-1,11,0), (-3,11,0), (-5,15,0);
+INSERT INTO t5 VALUES (-3,11,0), (-2,12,0), (-3,13,0), (-4,12,0);
 CREATE INDEX idx_b ON t4(b);
 CREATE INDEX idx_b ON t5(b);
 EXPLAIN EXTENDED
@@ -937,7 +940,7 @@ LEFT JOIN
 t2
 LEFT JOIN              
 (t3, t4)
-ON t3.a=1 AND t2.b=t4.b,
+ON t3.a=1 AND t2.b=t4.b AND t2.a>0 AND t4.a>0,
 t5 
 LEFT JOIN 
 ( 
@@ -946,7 +949,7 @@ LEFT JOIN
 t8
 ON t7.b=t8.b AND t6.b < 10
 )
-ON t6.b >= 2 AND t5.b=t7.b 
+ON t6.b >= 2 AND t5.b=t7.b AND t5.a>0 
 )
 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
@@ -966,16 +969,17 @@ t0.b=t1.b AND
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t0.b	3	100.00	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t4	ref	idx_b	idx_b	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	7	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t8	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
-1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t2`.`a` > 0) and (`test`.`t4`.`a` > 0) and (`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t5`.`a` > 0) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`)))
+INSERT INTO t8 VALUES (-3,12,0), (-1,14,0), (-5,15,0), (-1,11,0), (-4,13,0);
 CREATE INDEX idx_b ON t8(b);
 EXPLAIN EXTENDED
 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
@@ -986,16 +990,16 @@ LEFT JOIN
 t2
 LEFT JOIN              
 (t3, t4)
-ON t3.a=1 AND t2.b=t4.b,
+ON t3.a=1 AND t2.b=t4.b AND t2.a>0 AND t4.a>0,
 t5 
 LEFT JOIN 
 ( 
 (t6, t7)
 LEFT JOIN 
 t8
-ON t7.b=t8.b AND t6.b < 10
+ON t7.b=t8.b AND t6.b < 10 AND t8.a>=0
 )
-ON t6.b >= 2 AND t5.b=t7.b 
+ON t6.b >= 2 AND t5.b=t7.b AND t5.a>0
 )
 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
@@ -1015,16 +1019,17 @@ t0.b=t1.b AND
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t0.b	3	100.00	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t4	ref	idx_b	idx_b	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
-1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	7	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t2`.`a` > 0) and (`test`.`t4`.`a` > 0) and (`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t8`.`a` >= 0) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t5`.`a` > 0) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`)))
+INSERT INTO t1 VALUES (-1,133,0), (-2,12,0), (-3,11,0), (-5,15,0);
 CREATE INDEX idx_b ON t1(b);
 CREATE INDEX idx_a ON t0(a);
 EXPLAIN EXTENDED
@@ -1049,7 +1054,7 @@ ON t6.b >= 2 AND t5.b=t7.b
 )
 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
-(t1.a != 2),
+(t1.a != 2) AND t1.a>0,
 t9
 WHERE t0.a=1 AND
 t0.b=t1.b AND          
@@ -1065,16 +1070,16 @@ t0.b=t1.b AND
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ref	idx_a	idx_a	5	const	1	100.00	Using where
 1	SIMPLE	t1	ref	idx_b	idx_b	5	test.t0.b	2	100.00	Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t4	ref	idx_b	idx_b	5	test.t2.b	2	100.00	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t5	ALL	idx_b	NULL	NULL	NULL	7	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t7	hash_ALL	NULL	#hash#$hj	5	test.t5.b	2	100.00	Using where; Using join buffer (incremental, BNLH join)
 1	SIMPLE	t6	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t8	ref	idx_b	idx_b	5	test.t7.b	2	100.00	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	t9	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
 Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t7`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`)))
+Note	1003	select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t3`.`a` = 1) and (`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t2`.`b` is not null))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t6`.`b` < 10) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t7`.`b` is not null)))) on(((`test`.`t6`.`b` >= 2) and (`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t5`.`b` is not null)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and (((`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t5`.`b` = `test`.`t0`.`b`)) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2) and (`test`.`t1`.`a` > 0))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t8`.`b` = `test`.`t9`.`b`) or isnull(`test`.`t8`.`c`)))
 SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,
 t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b
 FROM t0,t1
@@ -1096,7 +1101,7 @@ ON t6.b >= 2 AND t5.b=t7.b
 )
 ON (t3.b=2 OR t3.c IS NULL) AND (t6.b=2 OR t6.c IS NULL) AND
 (t1.b=t5.b OR t3.c IS NULL OR t6.c IS NULL or t8.c IS NULL) AND
-(t1.a != 2),
+(t1.a != 2) AND t1.a>0,
 t9
 WHERE t0.a=1 AND
 t0.b=t1.b AND          
@@ -1110,15 +1115,15 @@ t0.b=t1.b AND
 (t8.b=t9.b OR t8.c IS NULL) AND
 (t9.a=1);
 a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b	a	b
-1	2	3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2	1	2
 1	2	3	2	4	2	1	2	4	2	2	2	6	2	2	2	0	2	1	2
+1	2	3	2	4	2	1	2	3	2	2	2	6	2	2	2	0	2	1	2
 1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL	1	1
-1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	1
 1	2	3	2	4	2	1	2	3	2	3	1	6	2	1	1	NULL	NULL	1	2
+1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	1
 1	2	3	2	4	2	1	2	4	2	3	1	6	2	1	1	NULL	NULL	1	2
 1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
-1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	3	2	4	2	1	2	3	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
+1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	1
 1	2	3	2	4	2	1	2	4	2	3	3	NULL	NULL	NULL	NULL	NULL	NULL	1	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	2	2	6	2	2	2	0	2	1	2
 1	2	3	2	5	3	NULL	NULL	NULL	NULL	3	1	6	2	1	1	NULL	NULL	1	1
@@ -1133,6 +1138,11 @@ a	b
 3	3
 4	2
 5	3
+-1	9
+-3	10
+-2	8
+-4	11
+-5	15
 SELECT t3.a,t3.b
 FROM t3;
 a	b
@@ -1850,6 +1860,9 @@ INSERT INTO t5 VALUES (1,1,0), (2,2,0), (3,3,0);
 INSERT INTO t6 VALUES (1,2,0), (3,2,0), (6,1,0);
 INSERT INTO t7 VALUES (1,1,0), (2,2,0);
 INSERT INTO t8 VALUES (0,2,0), (1,2,0);
+INSERT INTO t6 VALUES (-1,12,0), (-3,13,0), (-6,11,0), (-4,14,0);
+INSERT INTO t7 VALUES (-1,11,0), (-2,12,0), (-3,13,0), (-4,14,0), (-5,15,0);
+INSERT INTO t8 VALUES (-3,13,0), (-1,12,0), (-2,14,0), (-5,15,0), (-4,16,0);
 EXPLAIN
 SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
 FROM t5 
@@ -1858,14 +1871,14 @@ LEFT JOIN
 (t6, t7)
 LEFT JOIN 
 t8
-ON t7.b=t8.b AND t6.b < 10
+ON t7.b=t8.b AND t6.b < 10 
 )
 ON t6.b >= 2 AND t5.b=t7.b AND
-(t8.a > 0 OR t8.c IS NULL);
+(t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	3	
-1	SIMPLE	t7	ref	b_i	b_i	5	test.t5.b	2	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
-1	SIMPLE	t6	ALL	b_i	NULL	NULL	NULL	3	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t7	ref	PRIMARY,b_i	b_i	5	test.t5.b	2	Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
+1	SIMPLE	t6	ALL	PRIMARY,b_i	NULL	NULL	NULL	7	Using where; Using join buffer (incremental, BNL join)
 1	SIMPLE	t8	ref	b_i	b_i	5	test.t5.b	2	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
 FROM t5 
@@ -1877,7 +1890,7 @@ t8
 ON t7.b=t8.b AND t6.b < 10
 )
 ON t6.b >= 2 AND t5.b=t7.b AND
-(t8.a > 0 OR t8.c IS NULL);
+(t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0;
 a	b	a	b	a	b	a	b
 2	2	1	2	2	2	1	2
 2	2	3	2	2	2	1	2