Bug #115208 Last_query_cost could be negative number due to unmatched cost values in JOIN
Submitted: 4 Jun 8:51 Modified: 4 Jun 9:07
Reporter: Hope Lee (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.33 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[4 Jun 8:51] Hope Lee
Look at the codes in the function `JOIN::compare_costs_of_subquery_strategies`:

bool JOIN::compare_costs_of_subquery_strategies(Subquery_strategy *method) {
  Opt_trace_object trace_subqmat(
      trace, "execution_plan_for_potential_materialization");
  const double saved_best_read = best_read;
  const ha_rows saved_best_rowcount = best_rowcount;
  POSITION *const saved_best_pos = best_positions;

  if (in_pred->in2exists_added_to_where()) {
    if (optimize_semijoin_nests_for_materialization(this)) return true;

    if (Optimize_table_order(thd, this, nullptr).choose_table_order())
      return true;
  if (mat_chosen) {
    *method = Subquery_strategy::SUBQ_MATERIALIZATION;
  } else {
    best_read = saved_best_read;
    best_rowcount = saved_best_rowcount;
    best_positions = saved_best_pos;
      Don't restore JOIN::positions or best_ref, they're not used
      afterwards. best_positions is (like: by get_sj_strategy()).
  return false;

This function firstly save the value of `JOIN::best_read` and restore it after `Optimize_table_order::choose_table_order` is called. The problem is that we don't save and restore the value of `JOIN::sort_cost`, which could be assigned with some values in `Optimize_table_order::choose_table_order` -> `Optimize_table_order::consider_plan`. Thus when the function restores the old value of `JOIN::best_read`, the value doesn't match with `JOIN::sort_cost`.

After that, the following codes are called in the function `JOIN::optimize`:

bool JOIN::optimize(bool finalize_access_paths) {
    If we decided to not sort after all, update the cost of the JOIN.
    Windowing sorts are handled elsewhere
  if (sort_cost > 0.0 &&
      !explain_flags.any(ESP_USING_FILESORT, ESC_WINDOWING)) {
    best_read -= sort_cost;
    sort_cost = 0.0;

When the values of `best_read` and `sort_cost` don't match, it could cause `best_read` to become negative, finally causing `Last_query_cost` to be a negative number in function `accumulate_statement_cost`.

How to repeat:
As above.

Suggested fix:
Save and restore the whole cost values in JOIN.
[4 Jun 8:52] Hope Lee
Bugfix Last_query_cost could be negative number due to  unmatched cost values in JOIN

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-Bugfix-Last_query_cost-could-be-negative-number-due-.patch (application/octet-stream, text), 1.28 KiB.

[4 Jun 9:07] MySQL Verification Team
Hello Lee,

Thank you for the report and contribution.