| 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 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)
Description: The hint /+ NO_BNL(t0)/ does not always work as expected How to repeat: DROP DATABASE IF EXISTS database9; CREATE DATABASE database9; USE database9; CREATE TABLE IF NOT EXISTS t0(c0 varchar(255)) ; INSERT IGNORE INTO t0(c0) VALUES(NULL); -- When executing this SQL statement, the hint /+ NO_BNL()/ works well -- INSERT IGNORE INTO t0(c0) VALUES(1); SET SESSION optimizer_switch = 'subquery_to_derived=on'; ALTER TABLE t0 ORDER BY c0; explain analyze SELECT DISTINCT t0.c0 AS ref0 FROM t0 WHERE EXISTS (SELECT 1); explain analyze SELECT /*+ NO_BNL()*/ DISTINCT t0.c0 AS ref0 FROM t0 WHERE EXISTS (SELECT 1); here is the example: mysql> explain analyze SELECT DISTINCT t0.c0 AS ref0 FROM t0 WHERE EXISTS (SELECT 1); +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on <temporary> (cost=5.58..5.58 rows=1) (actual time=0.027..0.0271 rows=1 loops=1) -> Temporary table with deduplication (cost=3.07..3.07 rows=1) (actual time=0.0266..0.0266 rows=1 loops=1) -> Inner hash join (no condition) (cost=2.97 rows=1) (actual time=0.0164..0.0167 rows=1 loops=1) -> Table scan on derived_1_2 (cost=2.61..2.61 rows=1) (actual time=700e-6..800e-6 rows=1 loops=1) -> Materialize (cost=0.1..0.1 rows=1) (never executed) -> Rows fetched before execution (cost=0..0 rows=1) (never executed) -> Hash -> Table scan on t0 (cost=0.35 rows=1) (actual time=0.0086..0.0103 rows=1 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> explain analyze 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) (actual time=0.0187..0.0188 rows=1 loops=1) -> Temporary table with deduplication (cost=3.07..3.07 rows=1) (actual time=0.0184..0.0184 rows=1 loops=1) -> Inner hash join (no condition) (cost=2.97 rows=1) (actual time=0.0137..0.014 rows=1 loops=1) -> Table scan on derived_1_2 (cost=2.61..2.61 rows=1) (actual time=400e-6..500e-6 rows=1 loops=1) -> Materialize (cost=0.1..0.1 rows=1) (never executed) -> Rows fetched before execution (cost=0..0 rows=1) (never executed) -> Hash -> Table scan on t0 (cost=0.35 rows=1) (actual time=0.0071..0.0087 rows=1 loops=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -- When executing this SQL statement, the hint /+ NO_BNL()/ works well mysql> INSERT IGNORE INTO t0(c0) VALUES(1); Query OK, 1 row affected (0.01 sec) mysql> explain analyze SELECT /*+ NO_BNL()*/ DISTINCT t0.c0 AS ref0 FROM t0 WHERE EXISTS (SELECT 1); +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on <temporary> (cost=4.53..5.79 rows=2) (actual time=0.0255..0.0258 rows=2 loops=1) -> Temporary table with deduplication (cost=3.26..3.26 rows=2) (actual time=0.0251..0.0251 rows=2 loops=1) -> Nested loop inner join (cost=3.06 rows=2) (actual time=0.0133..0.0178 rows=2 loops=1) -> Table scan on derived_1_2 (cost=2.61..2.61 rows=1) (actual time=900e-6..0.0011 rows=1 loops=1) -> Materialize (cost=0.1..0.1 rows=1) (never executed) -> Rows fetched before execution (cost=0..0 rows=1) (never executed) -> Table scan on t0 (cost=0.45 rows=2) (actual time=0.0119..0.0157 rows=2 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>