Bug #115400 | The hint /+ NO_BNL(t0)/ does not always work as expected | ||
---|---|---|---|
Submitted: | 21 Jun 2024 4:14 | Modified: | 21 Jun 2024 6:47 |
Reporter: | hongjun xiao | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 8.4.0 LTS | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | hint, NO_BNL |
[21 Jun 2024 4:14]
hongjun xiao
[21 Jun 2024 6:47]
MySQL Verification Team
Hello 鸿骏 萧, Thank you for the report and test case. regards, Umesh
[2 Jul 2024 14:49]
huahua xu
It may be a feasible solution that the optimizer should initialize the derived table hints from the owning query block.
Attachment: set_table_hints_for_derived_tables.patch (application/octet-stream, text), 615 bytes.
[2 Jul 2024 14:56]
huahua xu
After applying the patch, mysql> explain format=tree SELECT DISTINCT t0.c0 AS ref0 FROM t0 WHERE EXISTS (SELECT 1); +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on <temporary> (cost=5.58..5.58 rows=1) -> Temporary table with deduplication (cost=3.07..3.07 rows=1) -> Inner hash join (no condition) (cost=2.97 rows=1) -> Table scan on derived_1_2 (cost=2.61..2.61 rows=1) -> Materialize (cost=0.1..0.1 rows=1) -> Rows fetched before execution (cost=0..0 rows=1) -> Hash -> Table scan on t0 (cost=0.35 rows=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> explain format=tree SELECT /*+ NO_BNL()*/ DISTINCT t0.c0 AS ref0 FROM t0 WHERE EXISTS (SELECT 1); +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on <temporary> (cost=5.58..5.58 rows=1) -> Temporary table with deduplication (cost=3.06..3.06 rows=1) -> Nested loop inner join (cost=2.96 rows=1) -> Table scan on t0 (cost=0.35 rows=1) -> Limit: 1 row(s) (cost=2.61..2.61 rows=1) -> Table scan on derived_1_2 (cost=2.61..2.61 rows=1) -> Materialize (cost=0.1..0.1 rows=1) -> Rows fetched before execution (cost=0..0 rows=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
[2 Jul 2024 14:58]
huahua xu
After applying the patch, the other test cases: mysql> explain format=tree SELECT /*+ NO_BNL(t0)*/ DISTINCT t0.c0 AS ref0 FROM t0 WHERE EXISTS (SELECT 1); +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on <temporary> (cost=5.58..5.58 rows=1) -> Temporary table with deduplication (cost=3.07..3.07 rows=1) -> Inner hash join (no condition) (cost=2.97 rows=1) -> Table scan on derived_1_2 (cost=2.61..2.61 rows=1) -> Materialize (cost=0.1..0.1 rows=1) -> Rows fetched before execution (cost=0..0 rows=1) -> Hash -> Table scan on t0 (cost=0.35 rows=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> explain format=tree SELECT /*+ NO_BNL(derived_1_2)*/ DISTINCT t0.c0 AS ref0 FROM t0 WHERE EXISTS (SELECT 1); +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on <temporary> (cost=5.58..5.58 rows=1) -> Temporary table with deduplication (cost=3.06..3.06 rows=1) -> Nested loop inner join (cost=2.96 rows=1) -> Table scan on t0 (cost=0.35 rows=1) -> Limit: 1 row(s) (cost=2.61..2.61 rows=1) -> Table scan on derived_1_2 (cost=2.61..2.61 rows=1) -> Materialize (cost=0.1..0.1 rows=1) -> Rows fetched before execution (cost=0..0 rows=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.01 sec) mysql> explain format=tree SELECT /*+ NO_BNL(t0, derived_1_2)*/ DISTINCT t0.c0 AS ref0 FROM t0 WHERE EXISTS (SELECT 1); +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on <temporary> (cost=5.58..5.58 rows=1) -> Temporary table with deduplication (cost=3.06..3.06 rows=1) -> Nested loop inner join (cost=2.96 rows=1) -> Table scan on t0 (cost=0.35 rows=1) -> Limit: 1 row(s) (cost=2.61..2.61 rows=1) -> Table scan on derived_1_2 (cost=2.61..2.61 rows=1) -> Materialize (cost=0.1..0.1 rows=1) -> Rows fetched before execution (cost=0..0 rows=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)