In best_access_path() change record_count to 1.0 if its less than 1.0.

In essence this means that we expect the user query to have at least
one matching row in the end.
This change will not affect the estimated rows for the plan, but will
ensure that the cost for adding a table is not neglected because of
record count being too low.

The reasons for this is that if we have table combination that
together has a very high selectivity then join record_count could
become very low (close to 0)

This would cause costs for all future tables to be so small that they
are irrelevant for the rest of the plan.
This has been shown to be the case in some performance benchmarks and
in a few mtr tests.

There is also still a problem in selectivity calculations as joining two
tables in different order causes a different estimation of total rows.
This can be seen in selectivity_innodb.test, test 'Q20' where joining
nation,supplier is expecting 1.111 rows_out while joining supplier,nation
is expecting 0.04 rows_out.

The reason for 0.04 is that the optimizer estimates 'supplier' to have
10 matching rows, and joining with nation (eq_ref) has 1 row. However
selectivity of n_name = 'UNITED STATES' makes the optimizer things
that there will be only 0.04 matching rows.

This patch avoids this "too low row count" to affect cost
caclulations.

10 files changed, 52 insertions, 44 deletions

diff --git a/mysql-test/main/innodb_ext_key,off.rdiff b/mysql-test/main/innodb_ext_key,off.rdiff
index d89161d1c51..ef11f9c05bc 100644
--- a/mysql-test/main/innodb_ext_key,off.rdiff
+++ b/mysql-test/main/innodb_ext_key,off.rdiff
@@ -278,10 +278,10 @@
 +          "key_length": "3066",
 +          "used_key_parts": ["f2"],
 +          "ref": ["const"],
+           "loops": 1,
            "rows": 1,
            "cost": "COST_REPLACED",
-           "filtered": 100,
-@@ -809,8 +810,8 @@
+@@ -810,8 +811,8 @@
            "access_type": "range",
            "possible_keys": ["k1"],
            "key": "k1",
@@ -289,6 +289,6 @@
 -          "used_key_parts": ["pk1", "f2", "pk2"],
 +          "key_length": "3007",
 +          "used_key_parts": ["pk1", "f2"],
+           "loops": 1,
            "rows": 1,
            "cost": "COST_REPLACED",
-           "filtered": 100,
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index 3b194579deb..8aee4d67b07 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -2813,7 +2813,7 @@ EXPLAIN
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.d"],
-          "loops": 0.471153846,
+          "loops": 1,
           "rows": 11,
           "cost": "COST_REPLACED",
           "filtered": 100
diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result
index cd2c636627e..8b40845cf04 100644
--- a/mysql-test/main/range_mrr_icp.result
+++ b/mysql-test/main/range_mrr_icp.result
@@ -2798,7 +2798,7 @@ EXPLAIN
           "key_length": "5",
           "used_key_parts": ["a"],
           "ref": ["test.t2.d"],
-          "loops": 0.471153846,
+          "loops": 1,
           "rows": 11,
           "cost": "COST_REPLACED",
           "filtered": 100
diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result
index aceaf0f98ab..2312fb593d1 100644
--- a/mysql-test/main/rowid_filter.result
+++ b/mysql-test/main/rowid_filter.result
@@ -1455,7 +1455,7 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
-          "loops": 0.101915071,
+          "loops": 1,
           "rows": 1,
           "cost": "COST_REPLACED",
           "filtered": 7.466666698,
@@ -1527,7 +1527,7 @@ ANALYZE
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
-          "loops": 0.101915071,
+          "loops": 1,
           "r_loops": 7,
           "rows": 1,
           "r_rows": 1,
@@ -1602,7 +1602,7 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
-          "loops": 0.101915071,
+          "loops": 1,
           "rows": 1,
           "cost": "COST_REPLACED",
           "filtered": 7.466666698,
@@ -1674,7 +1674,7 @@ ANALYZE
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
-          "loops": 0.101915071,
+          "loops": 1,
           "r_loops": 7,
           "rows": 1,
           "r_rows": 1,
@@ -2080,7 +2080,7 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["l_orderkey"],
           "ref": ["dbt3_s001.orders.o_orderkey"],
-          "loops": 0.760448,
+          "loops": 1,
           "rows": 4,
           "cost": "REPLACED",
           "filtered": "REPLACED",
@@ -2156,7 +2156,7 @@ ANALYZE
           "key_length": "4",
           "used_key_parts": ["l_orderkey"],
           "ref": ["dbt3_s001.orders.o_orderkey"],
-          "loops": 0.760448,
+          "loops": 1,
           "r_loops": 1,
           "rows": 4,
           "r_rows": 6,
@@ -2238,7 +2238,7 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["l_orderkey"],
           "ref": ["dbt3_s001.orders.o_orderkey"],
-          "loops": 0.760448,
+          "loops": 1,
           "rows": 4,
           "cost": "REPLACED",
           "filtered": "REPLACED",
@@ -2314,7 +2314,7 @@ ANALYZE
           "key_length": "4",
           "used_key_parts": ["l_orderkey"],
           "ref": ["dbt3_s001.orders.o_orderkey"],
-          "loops": 0.760448,
+          "loops": 1,
           "r_loops": 1,
           "rows": 4,
           "r_rows": 6,
diff --git a/mysql-test/main/rowid_filter_aria.result b/mysql-test/main/rowid_filter_aria.result
index d0db583168c..2478e787a6d 100644
--- a/mysql-test/main/rowid_filter_aria.result
+++ b/mysql-test/main/rowid_filter_aria.result
@@ -1456,7 +1456,7 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
-          "loops": 0.090591174,
+          "loops": 1,
           "rows": 1,
           "cost": "COST_REPLACED",
           "filtered": 8.466666222,
@@ -1528,7 +1528,7 @@ ANALYZE
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
-          "loops": 0.090591174,
+          "loops": 1,
           "r_loops": 7,
           "rows": 1,
           "r_rows": 1,
@@ -1603,7 +1603,7 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
-          "loops": 0.090591174,
+          "loops": 1,
           "rows": 1,
           "cost": "COST_REPLACED",
           "filtered": 8.466666222,
@@ -1675,7 +1675,7 @@ ANALYZE
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
-          "loops": 0.090591174,
+          "loops": 1,
           "r_loops": 7,
           "rows": 1,
           "r_rows": 1,
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index dadd1280e5f..78fc80e6ad7 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -1403,7 +1403,7 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
-          "loops": 0.101915071,
+          "loops": 1,
           "rows": 1,
           "cost": "COST_REPLACED",
           "filtered": 5.666666508,
@@ -1475,7 +1475,7 @@ ANALYZE
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
-          "loops": 0.101915071,
+          "loops": 1,
           "r_loops": 7,
           "rows": 1,
           "r_rows": 1,
@@ -1550,7 +1550,7 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
-          "loops": 0.101915071,
+          "loops": 1,
           "rows": 1,
           "cost": "COST_REPLACED",
           "filtered": 5.666666508,
@@ -1622,7 +1622,7 @@ ANALYZE
           "key_length": "4",
           "used_key_parts": ["o_orderkey"],
           "ref": ["dbt3_s001.lineitem.l_orderkey"],
-          "loops": 0.101915071,
+          "loops": 1,
           "r_loops": 7,
           "rows": 1,
           "r_rows": 1,
@@ -2007,7 +2007,7 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["l_orderkey"],
           "ref": ["dbt3_s001.orders.o_orderkey"],
-          "loops": 0.849155556,
+          "loops": 1,
           "rows": 4,
           "cost": "REPLACED",
           "filtered": "REPLACED",
@@ -2083,7 +2083,7 @@ ANALYZE
           "key_length": "4",
           "used_key_parts": ["l_orderkey"],
           "ref": ["dbt3_s001.orders.o_orderkey"],
-          "loops": 0.849155556,
+          "loops": 1,
           "r_loops": 1,
           "rows": 4,
           "r_rows": 6,
@@ -2165,7 +2165,7 @@ EXPLAIN
           "key_length": "4",
           "used_key_parts": ["l_orderkey"],
           "ref": ["dbt3_s001.orders.o_orderkey"],
-          "loops": 0.849155556,
+          "loops": 1,
           "rows": 4,
           "cost": "REPLACED",
           "filtered": "REPLACED",
@@ -2241,7 +2241,7 @@ ANALYZE
           "key_length": "4",
           "used_key_parts": ["l_orderkey"],
           "ref": ["dbt3_s001.orders.o_orderkey"],
-          "loops": 0.849155556,
+          "loops": 1,
           "r_loops": 1,
           "rows": 4,
           "r_rows": 6,
diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result
index 617aa350206..8e2188d74b9 100644
--- a/mysql-test/main/selectivity_innodb.result
+++ b/mysql-test/main/selectivity_innodb.result
@@ -599,15 +599,15 @@ and n_name = 'UNITED STATES'
 order by s_name
 limit 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	supplier	ALL	PRIMARY,i_s_nationkey	NULL	NULL	NULL	10	100.00	Using where; Using filesort
-1	PRIMARY	nation	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	4.00	Using where
-1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	70	100.00	Using where
-1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	35.71	Using where; FirstMatch(nation)
+1	PRIMARY	nation	ALL	PRIMARY	NULL	NULL	NULL	25	4.00	Using where; Using temporary; Using filesort
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	100.00	
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	7.03	Using where
+1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	7.11	Using where; FirstMatch(supplier)
 4	DEPENDENT SUBQUERY	lineitem	ref	i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey	i_l_suppkey_partkey	10	dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey	8	14.40	Using where
 Warnings:
 Note	1276	Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
 Note	1276	Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2
-Note	1003	/* select#1 */ select sql_calc_found_rows `dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address` from `dbt3_s001`.`supplier` semi join (`dbt3_s001`.`part` join `dbt3_s001`.`partsupp`) join `dbt3_s001`.`nation` where `dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey` and `dbt3_s001`.`nation`.`n_name` = 'UNITED STATES' and `dbt3_s001`.`part`.`p_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey` and `dbt3_s001`.`partsupp`.`ps_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey` and `dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((/* select#4 */ select 0.5 * sum(`dbt3_s001`.`lineitem`.`l_quantity`) from `dbt3_s001`.`lineitem` where `dbt3_s001`.`lineitem`.`l_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey` and `dbt3_s001`.`lineitem`.`l_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey` and `dbt3_s001`.`lineitem`.`l_shipDATE` >= <cache>(cast('1993-01-01' as date)) and `dbt3_s001`.`lineitem`.`l_shipDATE` < <cache>(cast('1993-01-01' as date) + interval '1' year))) and `dbt3_s001`.`part`.`p_name` like 'g%' order by `dbt3_s001`.`supplier`.`s_name` limit 10
+Note	1003	/* select#1 */ select sql_calc_found_rows `dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address` from `dbt3_s001`.`supplier` semi join (`dbt3_s001`.`part` join `dbt3_s001`.`partsupp`) join `dbt3_s001`.`nation` where `dbt3_s001`.`supplier`.`s_nationkey` = `dbt3_s001`.`nation`.`n_nationkey` and `dbt3_s001`.`nation`.`n_name` = 'UNITED STATES' and `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`partsupp`.`ps_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey` and `dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((/* select#4 */ select 0.5 * sum(`dbt3_s001`.`lineitem`.`l_quantity`) from `dbt3_s001`.`lineitem` where `dbt3_s001`.`lineitem`.`l_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey` and `dbt3_s001`.`lineitem`.`l_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey` and `dbt3_s001`.`lineitem`.`l_shipDATE` >= <cache>(cast('1993-01-01' as date)) and `dbt3_s001`.`lineitem`.`l_shipDATE` < <cache>(cast('1993-01-01' as date) + interval '1' year))) and `dbt3_s001`.`part`.`p_name` like 'g%' order by `dbt3_s001`.`supplier`.`s_name` limit 10
 select sql_calc_found_rows
 s_name, s_address
 from supplier, nation
@@ -654,15 +654,15 @@ and n_name = 'UNITED STATES'
 order by s_name
 limit 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	supplier	ALL	PRIMARY,i_s_nationkey	NULL	NULL	NULL	10	100.00	Using where; Using filesort
-1	PRIMARY	nation	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	4.00	Using where
-1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	70	100.00	Using where
-1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	35.71	Using where; FirstMatch(nation)
+1	PRIMARY	nation	ALL	PRIMARY	NULL	NULL	NULL	25	4.00	Using where; Using temporary; Using filesort
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	100.00	
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	7.81	Using where
+1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	6.40	Using where; FirstMatch(supplier)
 4	DEPENDENT SUBQUERY	lineitem	ref	i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey	i_l_suppkey_partkey	10	dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey	8	14.40	Using where
 Warnings:
 Note	1276	Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
 Note	1276	Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2
-Note	1003	/* select#1 */ select sql_calc_found_rows `dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address` from `dbt3_s001`.`supplier` semi join (`dbt3_s001`.`part` join `dbt3_s001`.`partsupp`) join `dbt3_s001`.`nation` where `dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey` and `dbt3_s001`.`nation`.`n_name` = 'UNITED STATES' and `dbt3_s001`.`part`.`p_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey` and `dbt3_s001`.`partsupp`.`ps_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey` and `dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((/* select#4 */ select 0.5 * sum(`dbt3_s001`.`lineitem`.`l_quantity`) from `dbt3_s001`.`lineitem` where `dbt3_s001`.`lineitem`.`l_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey` and `dbt3_s001`.`lineitem`.`l_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey` and `dbt3_s001`.`lineitem`.`l_shipDATE` >= <cache>(cast('1993-01-01' as date)) and `dbt3_s001`.`lineitem`.`l_shipDATE` < <cache>(cast('1993-01-01' as date) + interval '1' year))) and `dbt3_s001`.`part`.`p_name` like 'g%' order by `dbt3_s001`.`supplier`.`s_name` limit 10
+Note	1003	/* select#1 */ select sql_calc_found_rows `dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address` from `dbt3_s001`.`supplier` semi join (`dbt3_s001`.`part` join `dbt3_s001`.`partsupp`) join `dbt3_s001`.`nation` where `dbt3_s001`.`supplier`.`s_nationkey` = `dbt3_s001`.`nation`.`n_nationkey` and `dbt3_s001`.`nation`.`n_name` = 'UNITED STATES' and `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`partsupp`.`ps_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey` and `dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((/* select#4 */ select 0.5 * sum(`dbt3_s001`.`lineitem`.`l_quantity`) from `dbt3_s001`.`lineitem` where `dbt3_s001`.`lineitem`.`l_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey` and `dbt3_s001`.`lineitem`.`l_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey` and `dbt3_s001`.`lineitem`.`l_shipDATE` >= <cache>(cast('1993-01-01' as date)) and `dbt3_s001`.`lineitem`.`l_shipDATE` < <cache>(cast('1993-01-01' as date) + interval '1' year))) and `dbt3_s001`.`part`.`p_name` like 'g%' order by `dbt3_s001`.`supplier`.`s_name` limit 10
 select sql_calc_found_rows
 s_name, s_address
 from supplier, nation
@@ -709,15 +709,15 @@ and n_name = 'UNITED STATES'
 order by s_name
 limit 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	supplier	ALL	PRIMARY,i_s_nationkey	NULL	NULL	NULL	10	100.00	Using where; Using filesort
-1	PRIMARY	nation	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.supplier.s_nationkey	1	4.00	Using where
-1	PRIMARY	partsupp	ref	PRIMARY,i_ps_partkey,i_ps_suppkey	i_ps_suppkey	4	dbt3_s001.supplier.s_suppkey	70	100.00	Using where
-1	PRIMARY	part	eq_ref	PRIMARY	PRIMARY	4	dbt3_s001.partsupp.ps_partkey	1	35.71	Using where; FirstMatch(nation)
+1	PRIMARY	nation	ALL	PRIMARY	NULL	NULL	NULL	25	4.00	Using where; Using temporary; Using filesort
+1	PRIMARY	supplier	ref	PRIMARY,i_s_nationkey	i_s_nationkey	5	dbt3_s001.nation.n_nationkey	1	100.00	
+1	PRIMARY	part	ALL	PRIMARY	NULL	NULL	NULL	200	7.81	Using where
+1	PRIMARY	partsupp	eq_ref	PRIMARY,i_ps_partkey,i_ps_suppkey	PRIMARY	8	dbt3_s001.part.p_partkey,dbt3_s001.supplier.s_suppkey	1	6.40	Using where; FirstMatch(supplier)
 4	DEPENDENT SUBQUERY	lineitem	ref	i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey	i_l_suppkey_partkey	10	dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey	8	14.40	Using where
 Warnings:
 Note	1276	Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2
 Note	1276	Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2
-Note	1003	/* select#1 */ select sql_calc_found_rows `dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address` from `dbt3_s001`.`supplier` semi join (`dbt3_s001`.`part` join `dbt3_s001`.`partsupp`) join `dbt3_s001`.`nation` where `dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey` and `dbt3_s001`.`nation`.`n_name` = 'UNITED STATES' and `dbt3_s001`.`part`.`p_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey` and `dbt3_s001`.`partsupp`.`ps_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey` and `dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((/* select#4 */ select 0.5 * sum(`dbt3_s001`.`lineitem`.`l_quantity`) from `dbt3_s001`.`lineitem` where `dbt3_s001`.`lineitem`.`l_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey` and `dbt3_s001`.`lineitem`.`l_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey` and `dbt3_s001`.`lineitem`.`l_shipDATE` >= <cache>(cast('1993-01-01' as date)) and `dbt3_s001`.`lineitem`.`l_shipDATE` < <cache>(cast('1993-01-01' as date) + interval '1' year))) and `dbt3_s001`.`part`.`p_name` like 'g%' order by `dbt3_s001`.`supplier`.`s_name` limit 10
+Note	1003	/* select#1 */ select sql_calc_found_rows `dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address` from `dbt3_s001`.`supplier` semi join (`dbt3_s001`.`part` join `dbt3_s001`.`partsupp`) join `dbt3_s001`.`nation` where `dbt3_s001`.`supplier`.`s_nationkey` = `dbt3_s001`.`nation`.`n_nationkey` and `dbt3_s001`.`nation`.`n_name` = 'UNITED STATES' and `dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey` and `dbt3_s001`.`partsupp`.`ps_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey` and `dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((/* select#4 */ select 0.5 * sum(`dbt3_s001`.`lineitem`.`l_quantity`) from `dbt3_s001`.`lineitem` where `dbt3_s001`.`lineitem`.`l_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey` and `dbt3_s001`.`lineitem`.`l_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey` and `dbt3_s001`.`lineitem`.`l_shipDATE` >= <cache>(cast('1993-01-01' as date)) and `dbt3_s001`.`lineitem`.`l_shipDATE` < <cache>(cast('1993-01-01' as date) + interval '1' year))) and `dbt3_s001`.`part`.`p_name` like 'g%' order by `dbt3_s001`.`supplier`.`s_name` limit 10
 select sql_calc_found_rows
 s_name, s_address
 from supplier, nation
diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result
index a6905d5d819..677bdfb5ec7 100644
--- a/mysql-test/main/subselect_sj.result
+++ b/mysql-test/main/subselect_sj.result
@@ -2499,9 +2499,9 @@ SELECT * FROM t1, t2
 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ref	a	a	5	const	1	Using index
+1	PRIMARY	t1	ref	a	a	5	const	1	Using index
+1	PRIMARY	t2	ref	a	a	5	func	1	Using index
 1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	0	FirstMatch(t2); Using join buffer (flat, BNL join)
-1	PRIMARY	t1	ref	a	a	5	func	1	Using index
 SELECT * FROM t1, t2
 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4);
 a	a
diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result
index 78b2518891f..b416ae210bf 100644
--- a/mysql-test/main/subselect_sj_jcl6.result
+++ b/mysql-test/main/subselect_sj_jcl6.result
@@ -2505,9 +2505,9 @@ SELECT * FROM t1, t2
 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ref	a	a	5	const	1	Using index
+1	PRIMARY	t1	ref	a	a	5	const	1	Using index
+1	PRIMARY	t2	ref	a	a	5	func	1	Using index
 1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	0	FirstMatch(t2); Using join buffer (flat, BNL join)
-1	PRIMARY	t1	ref	a	a	5	func	1	Using index
 SELECT * FROM t1, t2
 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4);
 a	a
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index fe239dbf949..ee23804e222 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8101,6 +8101,14 @@ best_access_path(JOIN      *join,
   Json_writer_object trace_wrapper(thd, "best_access_path");
   DBUG_ENTER("best_access_path");
 
+  /*
+    Assume that there is at least one accepted row from previous table combinations.
+    This fixes a problem when the selectivity for the preceding table combinations
+    becomes so high that record_count becomes << 1.0, which makes the cost for the
+    current table so low that it does not matter when calculating the best plans.
+   */
+  set_if_bigger(record_count, 1.0);
+
   best.cost= DBL_MAX;
   best.records= DBL_MAX;
   best.records_read= DBL_MAX;