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)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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>