Bug #114970 prev_record_reads() use stale cardinality to calculate after plan enumeration

• Submitted: 13 May 2024 3:42
• Modified: 13 May 2024 6:03
• Reporter: Hope Lee (OCA)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.33
• OS: Any
• CPU Architecture: Any
• Tags: Contribution

[13 May 2024 3:42] Hope Lee

Description:
In function prev_record_reads(), use cardinality stored in JOIN::positions to calculate the number of different row combinations.

```c++
static double prev_record_reads(JOIN *join, uint idx, table_map found_ref) {
  double found = 1.0;
  POSITION *pos_end = join->positions - 1;
  for (POSITION *pos = join->positions + idx - 1; pos != pos_end; pos--) {
    const double fanout = pos->rows_fetched * pos->filter_effect;
    ...
  }
  return found;
}    
```

This function could be called in the following call chain: Optimize_table_order::fix_semijoin_strategies -> Optimize_table_order::semijoin_mat_scan_access_paths -> Optimize_table_order::best_access_path -> Optimize_table_order::find_best_ref -> prev_record_reads. 

In this case, the optimizer has completed the greedy search process and stored the optimal plan during the enumeration process in JOIN::best_positions, while JOIN::positions may store some stale plans that were pruned during the enumeration process. It will be inaccurate if we use the cardinality stored in JOIN::positions to calculate the result. This may cause some table access methods to be modified unexpectedly, ultimately turning into a bad plan.

How to repeat:
Every plan that chooses a semijoin materialized execution strategy may encounter this problem. It depends on whether some pruned plans are stored in JOIN::positions after the optimal plan is obtained.

Suggested fix:
During the plan enumeration process, it can use JOIN::positions to calculate the result in prev_record_reads(). But after we have gotten the final plan, it should use cardinality stored in JOIN::best_positions to calculate the result.

[13 May 2024 3:42] Hope Lee

Bugfix prev_record_reads() use stale cardinality to calculate after plan enumeration

(*) 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-prev_record_reads-use-stale-cardinality-to-ca.patch (application/octet-stream, text), 3.87 KiB.

[13 May 2024 6:03] MySQL Verification Team

Hello Lee,

Thank you for the report and contribution.

regards,
Umesh