Bug #112737 Conditions refering to const table were not added to RANGE SCAN
Submitted: 16 Oct 2023 9:07 Modified: 16 Oct 2023 9:31
Reporter: Xingyu Yang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:8.0, 8.0.34 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[16 Oct 2023 9:07] Xingyu Yang
Description:
Index conditions referring to const table should be added to RANGE SCAN
as the condition value can be read before execution.

Bug#32976857 omitted to pass the const table map when judging the 
condition-related table, resulting in the index condition referencing 
the const table not being added to the range scan, and the range scan 
needed to scan more data.

How to repeat:
CREATE TABLE t1 (a INTEGER, b INTEGER, PRIMARY KEY (a));
CREATE TABLE t2 (a INTEGER, b INTEGER, PRIMARY KEY (a, b));
INSERT INTO t1 VALUES (1, 1), (2, 2);
INSERT INTO t2 VALUES (2, 1), (2, 2), (2, 3), (2, 4);
ANALYZE TABLE t1, t2;
EXPLAIN FORMAT=TREE
SELECT t1.b FROM t1 JOIN t2
  WHERE t1.a = t2.a AND t2.a = 2 AND t2.b >= t1.b AND t2.b <= t1.b+2;

-- b<=4 was not added to RANGE SCAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: ((t2.a = 2) and (t2.b >= '2') and (t2.b <= <cache>(('2' + 2))))  (cost=0.86 rows=3)
    -> Covering index range scan on t2 using PRIMARY over (a = 2 AND 2 <= b)  (cost=0.86 rows=3)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Suggested fix:
Fix it by taking the const table map into account when adding
conditions to RANGE SCAN.
[16 Oct 2023 9:23] Xingyu Yang
The const table map was obtained through qep_tab in test_quick_select(). 
But bug #32976857 removed the qep_tab in test_quick_select(), and did 
not pass the const table map to test_quick_select().

Here is the commit message of Bug #32976857

Bug #32976857: REMOVE QEP_TAB_STANDALONE [range optimizer, noclose]
```
Remove the QEP_TAB dependency from test_quick_select() (ie., the range
optimizer).

Change-Id: Ie0fcce71dfc813920711c43c3d62635dae0d7d20
```
[16 Oct 2023 9:31] MySQL Verification Team
Hello Xingyu Yang,

Thank you for the report and contribution.
Please ensure to upload patch via "Contribution" tab. Thank you.

regards,
Umesh
[16 Oct 2023 9:33] Xingyu Yang
bugfix based on version 8.0.30

(*) 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-issue-594-Index-conditions-referencing-the-co.patch (application/octet-stream, text), 17.87 KiB.

[23 Apr 16:11] Marc Reilly
Hi Umesh,
Any chance the contribution to fix this regression could get merged in the next minor? It is still affecting the latest minor on 8.0. There is also a detailed write up on it here:  https://github.com/enhancedformysql/blogs/blob/main/performance_degradation.md
Thanks,
Marc