Bug #110306 Incorrect costing in test_if_cheaper_ordering
Submitted: 8 Mar 2023 22:08 Modified: 9 Mar 2023 6:47
Reporter: Manuel Ung Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.32 OS:Any
Assigned to: CPU Architecture:Any

[8 Mar 2023 22:08] Manuel Ung
Description:
In the function test_if_cheaper_ordering, we try to figure out which index has the cheapest ordering, and this is done by looping through all keys like so:

bool test_if_cheaper_ordering(...) {
...
  for (nr = 0; nr < table->s->keys; nr++) {
...
}

However, within each loop, we not resetting select_limit to its original value. This means that for each loop the value of select_limit could be different due to code that modifies it like this: https://github.com/mysql/mysql-server/blob/1bfe02bdad6604d54913c62614bde57a055c8332/sql/sq...

From code inspection, it should be clear that this is not intentional, since we want to know what was the LIMIT value for the query, which does not change as you are looping through the indexes.

How to repeat:
I can provide a test case if necessary, but hopefully the bug should be obvious by code inspection.

Suggested fix:
Reset select_limit to the original value at the beginning of the for loop.

eg.

bool test_if_cheaper_ordering(...) {
...
  ha_rows orig_select_limit = select_limit;
  for (nr = 0; nr < table->s->keys; nr++) {
    select_limit = orig_select_limit;
...
}
[9 Mar 2023 6:47] MySQL Verification Team
Hello Manuel Ung,

Thank you for the report and feedback.
Also, if possible please provide the test case as developers prefer reproducible test case.

regards,
Umesh