MDEV-23677: Optimizer trace: remove "no predicate for first keypart" (not)

Don't remove (reasons given in Jira), instead add test coverage.
Improve other printout in best_access_path.

3 files changed, 73 insertions, 7 deletions

diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index 8f912e0500d..36bbea8ae78 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -4010,6 +4010,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3	{
                         {
                           "access_type": "ref",
                           "index": "a",
+                          "rec_per_key_stats_missing": true,
                           "used_range_estimates": false,
                           "cause": "not better than ref estimates",
                           "rows": 2,
@@ -8793,5 +8794,42 @@ json_detailed(json_extract(trace, '$**.in_to_subquery_conversion'))
 ]
 set in_predicate_conversion_threshold=@tmp;
 drop table t0;
+#
 # End of 10.5 tests
+#
+#
+# MDEV-23677: Optimizer trace ... (test coverage)
+#
+create table t1(a int, b int, c int, primary key (a,b,c));
+insert into t1 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4);
+create table t2 (a int, b int);
+insert into t2 values (1,1),(2,2);
+create table t3 (a int, b int, c int);
+insert into t3 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4);
+explain select * from t2,t1,t3 where t2.b= t1.b and t1.a=t3.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t1	ref	PRIMARY	PRIMARY	8	test.t3.a,test.t2.b	1	Using index
+set @trace=(select trace from information_schema.optimizer_trace);
+set @path= (select json_search(@trace, 'one', 'no predicate for first keypart'));
+set @sub_path= substr(@path, 2, locate('.best_access_path', @path)-2);
+select @sub_path;
+@sub_path
+$.steps[1].join_optimization.steps[4].considered_execution_plans[0].rest_of_plan[0]
+select 
+json_detailed(json_extract(
+@trace,
+concat(@sub_path,'.best_access_path.considered_access_paths[0]')
+)) as S;
+S
+{
+    "access_type": "ref",
+    "index": "PRIMARY",
+    "rows": 1.79769e308,
+    "cost": 1.79769e308,
+    "chosen": false,
+    "cause": "no predicate for first keypart"
+}
+drop table t1,t2,t3;
 set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index ecb6658e338..83fd5ac0f40 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -696,5 +696,35 @@ from information_schema.optimizer_trace;
 set in_predicate_conversion_threshold=@tmp;
 drop table t0;
 
+--echo #
 --echo # End of 10.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-23677: Optimizer trace ... (test coverage)
+--echo #
+create table t1(a int, b int, c int, primary key (a,b,c));
+insert into t1 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4);
+
+create table t2 (a int, b int);
+insert into t2 values (1,1),(2,2);
+
+create table t3 (a int, b int, c int);
+insert into t3 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4);
+
+explain select * from t2,t1,t3 where t2.b= t1.b and t1.a=t3.a;
+
+set @trace=(select trace from information_schema.optimizer_trace);
+set @path= (select json_search(@trace, 'one', 'no predicate for first keypart'));
+set @sub_path= substr(@path, 2, locate('.best_access_path', @path)-2);
+
+select @sub_path;
+select 
+  json_detailed(json_extract(
+     @trace,
+     concat(@sub_path,'.best_access_path.considered_access_paths[0]')
+  )) as S;
+
+drop table t1,t2,t3;
+
 set optimizer_trace='enabled=off';
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 28b4a5f9b36..5447c398b66 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7637,6 +7637,7 @@ best_access_path(JOIN      *join,
             {
               if (!(records= keyinfo->actual_rec_per_key(key_parts-1)))
               {                                   /* Prefer longer keys */
+                trace_access_idx.add("rec_per_key_stats_missing", true);
                 records=
                   ((double) s->records / (double) rec *
                    (1.0 +
@@ -7663,7 +7664,7 @@ best_access_path(JOIN      *join,
                   records > (double) table->opt_range[key].rows)
               {
                 records= (double) table->opt_range[key].rows;
-                trace_access_idx.add("used_range_estimates", true);
+                trace_access_idx.add("used_range_estimates", "clipped down");
               }
               else
               {
@@ -7780,19 +7781,15 @@ best_access_path(JOIN      *join,
                     if (!found_ref &&                                  // (1)
                         records < rows)                                // (3)
                     {
-                      trace_access_idx.add("used_range_estimates", true);
+                      trace_access_idx.add("used_range_estimates", "clipped up");
                       records= rows;
                     }
                   }
-                  else /* (table->quick_key_parts[key] < max_key_part) */
-                  {
-                    trace_access_idx.add("chosen", true);
-                    cause= "range uses less keyparts";
-                  }
                 }
               }
               else
               {
+                trace_access_idx.add("rec_per_key_stats_missing", true);
                 /*
                   Assume that the first key part matches 1% of the file
                   and that the whole key matches 10 (duplicates) or 1
@@ -7856,6 +7853,7 @@ best_access_path(JOIN      *join,
                                                            const_part)) &&
                   records > (double) table->opt_range[key].rows)
               {
+                trace_access_idx.add("used_range_estimates", true);
                 records= (double) table->opt_range[key].rows;
               }
             }