MDEV-30629 Add optimizer trace to main/order_by_innodb.test to diagnose debian build issuebb-10.11-MDEV-30629

	Debian S390 build issue.  Add optimizer trace to failing test
	(main.order_by_innodb) to help diagnose issue.

2 files changed, 486 insertions, 3 deletions

diff --git a/mysql-test/main/order_by_innodb.result b/mysql-test/main/order_by_innodb.result
index 741084c8a6c..673d216f996 100644
--- a/mysql-test/main/order_by_innodb.result
+++ b/mysql-test/main/order_by_innodb.result
@@ -229,6 +229,7 @@ id	id
 #
 # This must NOT have "Range checked for each record" without any
 #   provisions to produce rows in the required ordering:
+set optimizer_trace="enabled=on";
 explain
 select
 t1.id,t2.id
@@ -244,9 +245,489 @@ d1 > '2019-02-06 00:00:00'
 dd.d1, dd.d2, dd.id limit 1
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	#	Using index
-1	PRIMARY	t2	eq_ref	PRIMARY,id2	PRIMARY	4	func	#	Using where
-2	DEPENDENT SUBQUERY	dd	range	id2,for_latest_sort	for_latest_sort	6	NULL	#	Using where
+1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	3	Using index
+1	PRIMARY	t2	eq_ref	PRIMARY,id2	PRIMARY	4	func	1	Using where
+2	DEPENDENT SUBQUERY	dd	range	id2,for_latest_sort	for_latest_sort	6	NULL	3	Using where
+select trace from information_schema.OPTIMIZER_TRACE;
+trace
+{
+  "steps": [
+    {
+      "join_preparation": {
+        "select_id": 1,
+        "steps": [
+          {
+            "join_preparation": {
+              "select_id": 2,
+              "steps": [
+                {
+                  "expanded_query": "/* select#2 */ select dd.`id` from t2 dd where dd.id2 = t1.`id` and dd.d1 > '2019-02-06 00:00:00' order by dd.d1,dd.d2,dd.`id` limit 1"
+                }
+              ]
+            }
+          },
+          {
+            "expanded_query": "/* select#1 */ select t1.`id` AS `id`,t2.`id` AS `id` from (t1 left join t2 on(t2.id2 = t1.`id` and t2.`id` = (/* select#2 */ select dd.`id` from t2 dd where dd.id2 = t1.`id` and dd.d1 > '2019-02-06 00:00:00' order by dd.d1,dd.d2,dd.`id` limit 1)))"
+          }
+        ]
+      }
+    },
+    {
+      "join_optimization": {
+        "select_id": 1,
+        "steps": [
+          {
+            "build_equal_items": {
+              "condition": "ON expr",
+              "attached_to": "t2",
+              "resulting_condition": "t2.`id` = (/* select#2 */ select dd.`id` from t2 dd where dd.id2 = t1.`id` and dd.d1 > '2019-02-06 00:00:00' order by dd.d1,dd.d2,dd.`id` limit 1) and multiple equal(t2.id2, t1.`id`)"
+            }
+          },
+          {
+            "table_dependencies": [
+              {
+                "table": "t1",
+                "row_may_be_null": false,
+                "map_bit": 0,
+                "depends_on_map_bits": []
+              },
+              {
+                "table": "t2",
+                "row_may_be_null": true,
+                "map_bit": 1,
+                "depends_on_map_bits": ["0"]
+              }
+            ]
+          },
+          {
+            "ref_optimizer_key_uses": [
+              {
+                "table": "t2",
+                "index": "PRIMARY",
+                "field": "id",
+                "equals": "(/* select#2 */ select dd.`id` from t2 dd where dd.id2 = t1.`id` and dd.d1 > '2019-02-06 00:00:00' order by dd.d1,dd.d2,dd.`id` limit 1)",
+                "null_rejecting": true
+              },
+              {
+                "table": "t2",
+                "index": "id2",
+                "field": "id2",
+                "equals": "t1.`id`",
+                "null_rejecting": false
+              },
+              {
+                "table": "t2",
+                "index": "id2",
+                "field": "id",
+                "equals": "(/* select#2 */ select dd.`id` from t2 dd where dd.id2 = t1.`id` and dd.d1 > '2019-02-06 00:00:00' order by dd.d1,dd.d2,dd.`id` limit 1)",
+                "null_rejecting": true
+              }
+            ]
+          },
+          {
+            "eliminated_tables": []
+          },
+          {
+            "rows_estimation": [
+              {
+                "table": "t1",
+                "table_scan": {
+                  "rows": 3,
+                  "cost": 1
+                }
+              },
+              {
+                "table": "t2",
+                "table_scan": {
+                  "rows": 3,
+                  "cost": 1
+                }
+              }
+            ]
+          },
+          {
+            "considered_execution_plans": [
+              {
+                "plan_prefix": [],
+                "get_costs_for_tables": [
+                  {
+                    "best_access_path": {
+                      "table": "t1",
+                      "considered_access_paths": [
+                        {
+                          "access_type": "scan",
+                          "resulting_rows": 3,
+                          "cost": 1,
+                          "chosen": true
+                        }
+                      ],
+                      "chosen_access_method": {
+                        "type": "scan",
+                        "records": 3,
+                        "cost": 1,
+                        "uses_join_buffering": false
+                      }
+                    }
+                  }
+                ]
+              },
+              {
+                "plan_prefix": [],
+                "table": "t1",
+                "rows_for_plan": 3,
+                "cost_for_plan": 1.6,
+                "rest_of_plan": [
+                  {
+                    "plan_prefix": ["t1"],
+                    "get_costs_for_tables": [
+                      {
+                        "best_access_path": {
+                          "table": "t2",
+                          "considered_access_paths": [
+                            {
+                              "access_type": "eq_ref",
+                              "index": "PRIMARY",
+                              "rows": 1,
+                              "cost": 3,
+                              "chosen": true
+                            },
+                            {
+                              "access_type": "eq_ref",
+                              "index": "id2",
+                              "rows": 1,
+                              "cost": 3,
+                              "chosen": false,
+                              "cause": "cost"
+                            },
+                            {
+                              "type": "scan",
+                              "chosen": false,
+                              "cause": "cost"
+                            }
+                          ],
+                          "chosen_access_method": {
+                            "type": "eq_ref",
+                            "records": 1,
+                            "cost": 3,
+                            "uses_join_buffering": false
+                          }
+                        }
+                      }
+                    ]
+                  },
+                  {
+                    "plan_prefix": ["t1"],
+                    "table": "t2",
+                    "rows_for_plan": 3,
+                    "cost_for_plan": 5.2
+                  }
+                ]
+              }
+            ]
+          },
+          {
+            "best_join_order": ["t1", "t2"]
+          },
+          {
+            "substitute_best_equal": {
+              "condition": "WHERE",
+              "resulting_condition": "1"
+            }
+          },
+          {
+            "substitute_best_equal": {
+              "condition": "ON expr",
+              "attached_to": "t2",
+              "resulting_condition": "t2.id2 = t1.`id` and t2.`id` = (/* select#2 */ select dd.`id` from t2 dd where dd.id2 = t1.`id` and dd.d1 > '2019-02-06 00:00:00' order by dd.d1,dd.d2,dd.`id` limit 1)"
+            }
+          },
+          {
+            "condition_on_constant_tables": "1",
+            "computing_condition": []
+          },
+          {
+            "attaching_conditions_to_tables": {
+              "attached_conditions_computation": [],
+              "attached_conditions_summary": [
+                {
+                  "table": "t1",
+                  "attached": null
+                },
+                {
+                  "table": "t2",
+                  "attached": "trigcond(t2.id2 = t1.`id` and t2.`id` = (/* select#2 */ select dd.`id` from t2 dd where dd.id2 = t1.`id` and dd.d1 > '2019-02-06 00:00:00' order by dd.d1,dd.d2,dd.`id` limit 1))"
+                }
+              ]
+            }
+          },
+          {
+            "join_optimization": {
+              "select_id": 2,
+              "steps": [
+                {
+                  "condition_processing": {
+                    "condition": "WHERE",
+                    "original_condition": "dd.id2 = t1.`id` and dd.d1 > '2019-02-06 00:00:00'",
+                    "steps": [
+                      {
+                        "transformation": "equality_propagation",
+                        "resulting_condition": "dd.id2 = t1.`id` and dd.d1 > '2019-02-06 00:00:00'"
+                      },
+                      {
+                        "transformation": "constant_propagation",
+                        "resulting_condition": "dd.id2 = t1.`id` and dd.d1 > '2019-02-06 00:00:00'"
+                      },
+                      {
+                        "transformation": "trivial_condition_removal",
+                        "resulting_condition": "dd.id2 = t1.`id` and dd.d1 > '2019-02-06 00:00:00'"
+                      }
+                    ]
+                  }
+                },
+                {
+                  "table_dependencies": [
+                    {
+                      "table": "dd",
+                      "row_may_be_null": false,
+                      "map_bit": 0,
+                      "depends_on_map_bits": []
+                    }
+                  ]
+                },
+                {
+                  "ref_optimizer_key_uses": [
+                    {
+                      "table": "dd",
+                      "index": "id2",
+                      "field": "id2",
+                      "equals": "t1.`id`",
+                      "null_rejecting": false
+                    }
+                  ]
+                },
+                {
+                  "rows_estimation": [
+                    {
+                      "table": "dd",
+                      "range_analysis": {
+                        "table_scan": {
+                          "rows": 3,
+                          "cost": 3.6
+                        },
+                        "potential_range_indexes": [
+                          {
+                            "index": "PRIMARY",
+                            "usable": false,
+                            "cause": "not applicable"
+                          },
+                          {
+                            "index": "id2",
+                            "usable": false,
+                            "cause": "not applicable"
+                          },
+                          {
+                            "index": "for_latest_sort",
+                            "usable": true,
+                            "key_parts": ["d1", "d2", "id"]
+                          }
+                        ],
+                        "setup_range_conditions": [],
+                        "analyzing_range_alternatives": {
+                          "range_scan_alternatives": [
+                            {
+                              "index": "for_latest_sort",
+                              "ranges": ["(2019-02-06 00:00:00) < (d1)"],
+                              "rowid_ordered": false,
+                              "using_mrr": false,
+                              "index_only": false,
+                              "rows": 3,
+                              "cost": 3.745659139,
+                              "chosen": false,
+                              "cause": "cost"
+                            }
+                          ],
+                          "analyzing_roworder_intersect": {
+                            "cause": "too few roworder scans"
+                          },
+                          "analyzing_index_merge_union": []
+                        },
+                        "group_index_range": {
+                          "chosen": false,
+                          "cause": "correlated conditions"
+                        }
+                      }
+                    },
+                    {
+                      "table": "dd",
+                      "rowid_filters": [
+                        {
+                          "key": "for_latest_sort",
+                          "build_cost": 0.173617508,
+                          "rows": 3
+                        }
+                      ]
+                    },
+                    {
+                      "selectivity_for_indexes": [
+                        {
+                          "index_name": "for_latest_sort",
+                          "selectivity_from_index": 1
+                        }
+                      ],
+                      "selectivity_for_columns": [],
+                      "cond_selectivity": 1
+                    }
+                  ]
+                },
+                {
+                  "considered_execution_plans": [
+                    {
+                      "plan_prefix": [],
+                      "get_costs_for_tables": [
+                        {
+                          "best_access_path": {
+                            "table": "dd",
+                            "considered_access_paths": [
+                              {
+                                "access_type": "ref",
+                                "index": "id2",
+                                "rowid_filter_skipped": "cost_factor <= 0",
+                                "rows": 1,
+                                "cost": 2.00009765,
+                                "chosen": true
+                              },
+                              {
+                                "access_type": "scan",
+                                "resulting_rows": 3,
+                                "cost": 1,
+                                "chosen": true
+                              }
+                            ],
+                            "chosen_access_method": {
+                              "type": "scan",
+                              "records": 3,
+                              "cost": 1,
+                              "uses_join_buffering": false
+                            }
+                          }
+                        }
+                      ]
+                    },
+                    {
+                      "plan_prefix": [],
+                      "table": "dd",
+                      "rows_for_plan": 3,
+                      "cost_for_plan": 1.6
+                    }
+                  ]
+                },
+                {
+                  "best_join_order": ["dd"]
+                },
+                {
+                  "substitute_best_equal": {
+                    "condition": "WHERE",
+                    "resulting_condition": "dd.id2 = t1.`id` and dd.d1 > '2019-02-06 00:00:00'"
+                  }
+                },
+                {
+                  "attaching_conditions_to_tables": {
+                    "attached_conditions_computation": [
+                      {
+                        "table": "dd",
+                        "range_analysis": {
+                          "table_scan": {
+                            "rows": 3,
+                            "cost": 4.6
+                          },
+                          "potential_range_indexes": [
+                            {
+                              "index": "PRIMARY",
+                              "usable": false,
+                              "cause": "not applicable"
+                            },
+                            {
+                              "index": "id2",
+                              "usable": true,
+                              "key_parts": ["id2", "id"]
+                            },
+                            {
+                              "index": "for_latest_sort",
+                              "usable": true,
+                              "key_parts": ["d1", "d2", "id"]
+                            }
+                          ],
+                          "setup_range_conditions": [],
+                          "analyzing_range_alternatives": {
+                            "range_scan_alternatives": [
+                              {
+                                "index": "id2",
+                                "chosen": false,
+                                "cause": "depends on unread values"
+                              },
+                              {
+                                "index": "for_latest_sort",
+                                "ranges": ["(2019-02-06 00:00:00) < (d1)"],
+                                "rowid_ordered": false,
+                                "using_mrr": false,
+                                "index_only": false,
+                                "rows": 3,
+                                "cost": 3.745659139,
+                                "chosen": true
+                              }
+                            ],
+                            "analyzing_roworder_intersect": {
+                              "cause": "too few roworder scans"
+                            },
+                            "analyzing_index_merge_union": []
+                          },
+                          "group_index_range": {
+                            "chosen": false,
+                            "cause": "correlated conditions"
+                          },
+                          "chosen_range_access_summary": {
+                            "range_access_plan": {
+                              "type": "range_scan",
+                              "index": "for_latest_sort",
+                              "rows": 3,
+                              "ranges": ["(2019-02-06 00:00:00) < (d1)"]
+                            },
+                            "rows_for_plan": 3,
+                            "cost_for_plan": 3.745659139,
+                            "chosen": true
+                          }
+                        }
+                      }
+                    ],
+                    "attached_conditions_summary": [
+                      {
+                        "table": "dd",
+                        "attached": "dd.id2 = t1.`id` and dd.d1 > '2019-02-06 00:00:00'"
+                      }
+                    ]
+                  }
+                }
+              ]
+            }
+          }
+        ]
+      }
+    },
+    {
+      "join_execution": {
+        "select_id": 1,
+        "steps": [
+          {
+            "join_execution": {
+              "select_id": 2,
+              "steps": []
+            }
+          }
+        ]
+      }
+    }
+  ]
+}
 drop table t1,t2,t3;
 # End of 10.2 tests
 #
diff --git a/mysql-test/main/order_by_innodb.test b/mysql-test/main/order_by_innodb.test
index bdaef56672f..2156ef73ee0 100644
--- a/mysql-test/main/order_by_innodb.test
+++ b/mysql-test/main/order_by_innodb.test
@@ -217,6 +217,7 @@ from
 --echo # This must NOT have "Range checked for each record" without any
 --echo #   provisions to produce rows in the required ordering:
 --replace_column 9 #
+set optimizer_trace="enabled=on";
 explain
 select
   t1.id,t2.id
@@ -231,6 +232,7 @@ from
                  order by
                    dd.d1, dd.d2, dd.id limit 1
                 );
+select trace from information_schema.OPTIMIZER_TRACE;
 
 drop table t1,t2,t3;