| Bug #115794 | wrong plan may occur when EXISTS-subselect displays its correlated cache item | ||
|---|---|---|---|
| Submitted: | 8 Aug 2024 1:54 | Modified: | 8 Aug 2024 6:35 |
| Reporter: | tianfeng li (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.30,8.4.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contrbution | ||
[8 Aug 2024 1:59]
tianfeng li
adjust Item_cache in Item_in_optimizer when performing substitution (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-When-an-EXISTS-subselect-creates-an-Item_cache-on-it.patch (application/octet-stream, text), 48.21 KiB.
[8 Aug 2024 6:35]
MySQL Verification Team
Hello tianfeng li, Thank you for the report and contribution. regards, Umesh
Description: An wrong plan as follows, Select#2 has a `(<cache>(t1.id)` in its filter while this exists subselect is executed on table t2, can't see rows from t1. -> Inner hash join (t1.id = t2.id) (cost=0.70 rows=1) -> Table scan on t1 (cost=0.35 rows=1) -> Hash -> Filter: ((t2.a = 150007279) and <in_optimizer>(t2.id,<exists>(select #2))) (cost=0.35 rows=1) -> Table scan on t2 (cost=0.35 rows=1) -> Select #2 (subquery in condition; dependent) -> Limit: 1 row(s) -> Filter: ((count(1) > 0) and (<cache>(t1.id) = <ref_null_helper>(t0.c0))) -> Table scan on <temporary> -> Aggregate using temporary table -> Table scan on t0 (cost=0.35 rows=1) When an EXISTS subselect creates an Item_cache on its left_expr, and then the left_expr is replaced by equal items in substitute_for_best_equal_field(), the Item_cache is left behind, with its example item still being the old item. When using EXPLAIN FORMAT=TREE, users may encounter a seemingly incorrect plan where the old item in Item_cache belongs to tables that cannot be evaluated yet. Fortunately, this discrepancy is merely an illusion when explaining. Upon execution, the cache item will store the newest left_expr again. How to repeat: create table t1 (s1 int, id int); create table t2 (id int, a int); create table t0 (c0 int); explain format=tree select /*+JOIN_ORDER(t2,t1)*/ t1.s1 from t1 INNER join t2 on (t2.id = t1.id) where t2.a=150007279 and t1.id in ( select c0 from t0 GROUP BY c0 having count(1) > 0) ;