MDEV-21626: Optimizer misses the details about the picked join order

Added cost of sorting estimate to the optimizer trace

3 files changed, 191 insertions, 0 deletions

diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result
index f43290651f9..be358e69c47 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -441,6 +441,7 @@ select * from v2	{
                       },
                       "rows_for_plan": 1,
                       "cost_for_plan": 2.4044,
+                      "cost_for_sorting": 1,
                       "estimated_join_cardinality": 1
                     }
                   ]
@@ -765,6 +766,7 @@ explain select * from v1	{
                       },
                       "rows_for_plan": 10,
                       "cost_for_plan": 4.022,
+                      "cost_for_sorting": 10,
                       "estimated_join_cardinality": 10
                     }
                   ]
@@ -1449,6 +1451,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3  group by a	{
                 },
                 "rows_for_plan": 0.5849,
                 "cost_for_plan": 3.4291,
+                "cost_for_sorting": 0.5849,
                 "estimated_join_cardinality": 0.5849
               }
             ]
@@ -1650,6 +1653,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id	{
                 },
                 "rows_for_plan": 16,
                 "cost_for_plan": 5.2313,
+                "cost_for_sorting": 16,
                 "estimated_join_cardinality": 16
               }
             ]
@@ -1840,6 +1844,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id	{
                 },
                 "rows_for_plan": 16,
                 "cost_for_plan": 5.2313,
+                "cost_for_sorting": 16,
                 "estimated_join_cardinality": 16
               }
             ]
@@ -8292,5 +8297,173 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives'))
     ]
 ]
 drop table t1;
+#
+# MDEV-21626: Optimizer misses the details about the picked join order
+#
+CREATE TABLE t1(a INT, b INT, key(a));
+INSERT INTO t1 SELECT seq, seq from seq_1_to_10;
+CREATE TABLE t2(a INT, b INT, key(a));
+INSERT INTO t2 SELECT seq, seq from seq_1_to_100;
+SET OPTIMIZER_TRACE=1;
+EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a ORDER BY t2.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	10	Using where; Using temporary; Using filesort
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	1	
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans'))
+[
+    
+    [
+        
+        {
+            "plan_prefix": 
+            [
+            ],
+            "table": "t1",
+            "best_access_path": 
+            {
+                "considered_access_paths": 
+                [
+                    
+                    {
+                        "access_type": "scan",
+                        "resulting_rows": 10,
+                        "cost": 2.022,
+                        "chosen": true
+                    }
+                ],
+                "chosen_access_method": 
+                {
+                    "type": "scan",
+                    "records": 10,
+                    "cost": 2.022,
+                    "uses_join_buffering": false
+                }
+            },
+            "rows_for_plan": 10,
+            "cost_for_plan": 4.022,
+            "rest_of_plan": 
+            [
+                
+                {
+                    "plan_prefix": 
+                    [
+                        "t1"
+                    ],
+                    "table": "t2",
+                    "best_access_path": 
+                    {
+                        "considered_access_paths": 
+                        [
+                            
+                            {
+                                "access_type": "ref",
+                                "index": "a",
+                                "used_range_estimates": false,
+                                "cause": "not available",
+                                "rows": 1,
+                                "cost": 20,
+                                "chosen": true
+                            },
+                            
+                            {
+                                "access_type": "scan",
+                                "resulting_rows": 100,
+                                "cost": 2.2197,
+                                "chosen": false
+                            }
+                        ],
+                        "chosen_access_method": 
+                        {
+                            "type": "ref",
+                            "records": 1,
+                            "cost": 20,
+                            "uses_join_buffering": false
+                        }
+                    },
+                    "rows_for_plan": 10,
+                    "cost_for_plan": 26.022,
+                    "cost_for_sorting": 10,
+                    "estimated_join_cardinality": 10
+                }
+            ]
+        },
+        
+        {
+            "plan_prefix": 
+            [
+            ],
+            "table": "t2",
+            "best_access_path": 
+            {
+                "considered_access_paths": 
+                [
+                    
+                    {
+                        "access_type": "scan",
+                        "resulting_rows": 100,
+                        "cost": 2.2197,
+                        "chosen": true,
+                        "use_tmp_table": true
+                    }
+                ],
+                "chosen_access_method": 
+                {
+                    "type": "scan",
+                    "records": 100,
+                    "cost": 2.2197,
+                    "uses_join_buffering": false
+                }
+            },
+            "rows_for_plan": 100,
+            "cost_for_plan": 22.22,
+            "rest_of_plan": 
+            [
+                
+                {
+                    "plan_prefix": 
+                    [
+                        "t2"
+                    ],
+                    "table": "t1",
+                    "best_access_path": 
+                    {
+                        "considered_access_paths": 
+                        [
+                            
+                            {
+                                "access_type": "ref",
+                                "index": "a",
+                                "used_range_estimates": false,
+                                "cause": "not available",
+                                "rows": 1,
+                                "cost": 200,
+                                "chosen": true
+                            },
+                            
+                            {
+                                "access_type": "scan",
+                                "resulting_rows": 10,
+                                "cost": 2.022,
+                                "chosen": true
+                            }
+                        ],
+                        "chosen_access_method": 
+                        {
+                            "type": "scan",
+                            "records": 10,
+                            "cost": 2.022,
+                            "uses_join_buffering": true
+                        }
+                    },
+                    "rows_for_plan": 1000,
+                    "cost_for_plan": 224.24,
+                    "pruned_by_cost": true
+                }
+            ]
+        }
+    ]
+]
+DROP TABLE t1,t2;
 # End of 10.4 tests
 set optimizer_trace='enabled=off';
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index 712c58dd2d3..d1a8fedc635 100644
--- a/mysql-test/main/opt_trace.test
+++ b/mysql-test/main/opt_trace.test
@@ -1,4 +1,5 @@
 --source include/not_embedded.inc
+--source include/have_sequence.inc
 SELECT table_name, column_name FROM information_schema.columns where table_name="OPTIMIZER_TRACE";
 show variables like 'optimizer_trace';
 set optimizer_trace="enabled=on";
@@ -579,6 +580,20 @@ select * from t1 force index(kp1) where (kp1=2 and kp2 >=4);
 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
 drop table t1;
 
+--echo #
+--echo # MDEV-21626: Optimizer misses the details about the picked join order
+--echo #
+
+CREATE TABLE t1(a INT, b INT, key(a));
+INSERT INTO t1 SELECT seq, seq from seq_1_to_10;
+CREATE TABLE t2(a INT, b INT, key(a));
+INSERT INTO t2 SELECT seq, seq from seq_1_to_100;
+
+SET OPTIMIZER_TRACE=1;
+EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a ORDER BY t2.b;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+DROP TABLE t1,t2;
+
 --echo # End of 10.4 tests
 
 set optimizer_trace='enabled=off';
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 568a3ed6062..040c9acd2ae 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -9593,12 +9593,15 @@ best_extension_by_limited_search(JOIN      *join,
         if (join->sort_by_table &&
             join->sort_by_table !=
             join->positions[join->const_tables].table->table)
+        {
           /*
              We may have to make a temp table, note that this is only a
              heuristic since we cannot know for sure at this point.
              Hence it may be wrong.
           */
+          trace_one_table.add("cost_for_sorting", current_record_count);
           current_read_time= COST_ADD(current_read_time, current_record_count);
+        }
         trace_one_table.add("estimated_join_cardinality",
                             partial_join_cardinality);
         if (current_read_time < join->best_read)