Description:
Look at the codes in the function `JOIN::compare_costs_of_subquery_strategies`:
```c++
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`:
```c++
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.
Description: Look at the codes in the function `JOIN::compare_costs_of_subquery_strategies`: ```c++ 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`: ```c++ 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.