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: | |
| 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
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.
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.