MDEV-17752: Plan changes from hash_index_merge to index_merge with new optimizer defaults

The code in best_access_path function, when it does not find a key suitable for ref access
and join_cache_level is set to a value so that hash_join is possible we build a hash key.
Later in the function we compare the cost of ref access with table scan (or index scan
or quick selects). No need to do this when we have got the hash key.

4 files changed, 69 insertions, 2 deletions

diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 01339f7c191..cf23979b49a 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -2885,6 +2885,37 @@ Klaipeda	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 Panevezys	Lithuania	xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 set join_cache_level=default;
 set join_buffer_size=default;
+#
+# MDEV-17752: Plan changes from hash_index_merge to index_merge with new optimizer defaults
+#
+set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @save_use_stat_tables=@@use_stat_tables;
+set optimizer_use_condition_selectivity=4;
+set use_stat_tables='preferably';
+use world;
+set join_cache_level=4;
+CREATE INDEX City_Name ON City(Name);
+ANALYZE TABLE City, Country;
+EXPLAIN
+SELECT Country.Name, Country.Population, City.Name, City.Population
+FROM Country LEFT JOIN City
+ON City.Country=Country.Code AND City.Population > 5000000
+WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	Country	range	Name	Name	302	NULL	15	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	City	hash_range	Population,Country	#hash#Country:Population	3:4	world.Country.Code	25	Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join)
+EXPLAIN
+SELECT Country.Name, Country.Population, City.Name, City.Population
+FROM Country LEFT JOIN City
+ON City.Country=Country.Code AND
+(City.Population > 5000000 OR City.Name LIKE 'Za%')
+WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	Country	range	Name	Name	302	NULL	15	Using index condition; Using where; Rowid-ordered scan
+1	SIMPLE	City	hash_index_merge	Population,Country,City_Name	#hash#Country:Population,City_Name	3:4,35	world.Country.Code	96	Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join)
+set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set @@use_stat_tables=@save_use_stat_tables;
+set join_cache_level=default;
 DROP DATABASE world;
 use test;
 CREATE TABLE t1(
diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result
index bac8e1cb7db..51073bf0f1d 100644
--- a/mysql-test/r/join_nested_jcl6.result
+++ b/mysql-test/r/join_nested_jcl6.result
@@ -1342,7 +1342,7 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON c21=c31) ON c11=c21;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t2	hash_ALL	NULL	#hash#$hj	5	test.t1.c11	0	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	0	Using where; Using join buffer (incremental, BNL join)
+1	SIMPLE	t3	hash_ALL	NULL	#hash#$hj	5	test.t1.c11	0	Using where; Using join buffer (incremental, BNLH join)
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (goods int(12) NOT NULL, price varchar(128) NOT NULL);
 INSERT INTO t1 VALUES (23, 2340), (26, 9900);
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 24dd637052c..48f129c676d 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -967,6 +967,40 @@ SELECT City.Name, Country.Name, Country.PopulationBar FROM City,Country
 set join_cache_level=default;
 set join_buffer_size=default;
 
+
+--echo #
+--echo # MDEV-17752: Plan changes from hash_index_merge to index_merge with new optimizer defaults
+--echo #
+
+set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @save_use_stat_tables=@@use_stat_tables;
+set optimizer_use_condition_selectivity=4;
+set use_stat_tables='preferably';
+
+use world;
+set join_cache_level=4;
+CREATE INDEX City_Name ON City(Name);
+
+--disable_result_log
+ANALYZE TABLE City, Country;
+--enable_result_log
+
+EXPLAIN
+SELECT Country.Name, Country.Population, City.Name, City.Population
+  FROM Country LEFT JOIN City
+       ON City.Country=Country.Code AND City.Population > 5000000
+   WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
+
+EXPLAIN
+SELECT Country.Name, Country.Population, City.Name, City.Population
+  FROM Country LEFT JOIN City
+       ON City.Country=Country.Code AND
+          (City.Population > 5000000 OR City.Name LIKE 'Za%')
+  WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000;
+set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set @@use_stat_tables=@save_use_stat_tables;
+set join_cache_level=default;
+
 DROP DATABASE world;
 
 use test;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index c1bec0f1c20..7426540c8d7 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -6396,7 +6396,8 @@ best_access_path(JOIN      *join,
         'range' access using index IDX, and the best way to perform 'ref' 
         access is to use the same index IDX, with the same or more key parts.
         (note: it is not clear how this rule is/should be extended to 
-        index_merge quick selects)
+        index_merge quick selects). Also if we have a hash join we prefer that
+        over a table scan
     (3) See above note about InnoDB.
     (4) NOT ("FORCE INDEX(...)" is used for table and there is 'ref' access
              path, but there is no quick select)
@@ -6412,6 +6413,7 @@ best_access_path(JOIN      *join,
         be used for cases with small datasets, which is annoying.
   */
   if ((records >= s->found_records || best > s->read_time) &&            // (1)
+      !(best_key && best_key->key == MAX_KEY) &&                         // (2)
       !(s->quick && best_key && s->quick->index == best_key->key &&      // (2)
         best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2)
       !((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) &&   // (3)