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) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.0.33 OS:Any
Assigned to: 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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.