pFad - Phone/Frame/Anonymizer/Declutterfier! Saves Data!


--- a PPN by Garber Painting Akron. With Image Size Reduction included!

URL: http://github.com/MariaDB/server/pull/4692

hub.githubassets.com/assets/actions-109fb3a41bacb1c2.css" /> MDEV-38045: Implement implicit query block names for optimizer hints by Olernov · Pull Request #4692 · MariaDB/server · GitHub
Skip to content
/ server Public

MDEV-38045: Implement implicit query block names for optimizer hints#4692

Open
Olernov wants to merge 2 commits intomainfrom
12.4-MDEV-38045-impl-qb-name
Open

MDEV-38045: Implement implicit query block names for optimizer hints#4692
Olernov wants to merge 2 commits intomainfrom
12.4-MDEV-38045-impl-qb-name

Conversation

@Olernov
Copy link
Contributor

@Olernov Olernov commented Feb 24, 2026

This patch implements support for implicit query block (QB) names in
optimizer hints, allowing hints to reference query blocks and tables
within derived tables, views and CTEs without requiring explicit
QB_NAME hints.

Examples.

-- Addressing a table inside a derived table using implicit QB name
select /*+ no_index(t1@dt) */ *
  from (select * from t1 where a > 10) as DT;
-- this is an equivalent to:
select /*+ no_index(t1@dt) */ * from
  (select /*+ qb_name(dt)*/ * from t1 where a > 10) as DT;
-- Addressing a query block corresponding to the derived table
select /*+ no_bnl(@dt) */ *
  from (select * from t1, t2 where t.1.a > t2.a) as DT;

-- View
create view v1 as select * from t1 where a > 10 and b > 100;
-- referencing a table inside a view by implicit QB name:
select /*+ index_merge(t1@v1 idx_a, idx_b) */ *
  from v1, t2 where v1.a = t2.a;
-- equivalent to:
create view v1 as select /*+ qb_name(qb_v1) */ *
  from t1 where a > 10 and b > 100;
select /*+ index_merge(t1@qb_v1 idx_a, idx_b) */ *
  from v1, t2 where v1.a = t2.a;

-- CTE
with aless100 as (select a from t1 where b <100)
  select /*+ index(t1@aless100) */ * from aless100;
-- equivalent to:
with aless100 as (select /*+ qb_name(aless100) */ a from t1 where b <100)
  select /*+ index(t1@aless100) */ * from aless100;

Limitations:

  • Only SELECT statements support implicit QB names. DML operations
    (UPDATE, DELETE, INSERT) only support explicit QB names

1. `TABLE_LIST::init_derived` is called twice during derived tables
processing: first time from `mysql_derived_init` and the second
time from `mysql_derived_prepare`. Both times there is a check
for whether an optimizer hint or switch setting allows a derived
table to be merged.
However, it is not necessary to restrict merging during the
initialization call, it is enough to apply hints/switch setting
during the preparation phase.

2. `open_normal_and_derived_tables()` runs processing of all phases
of derived tables handling with a single call. But for future
commits it is required to separate DT initialization from other
phases. This commit implements the separation.
if (const Qb_name_hint &qb_hint= hint)
{
if (qb_hint.resolve(pc))
return true;

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Is it possible (and worthwhile) to exercise this code path? In general, do we have any test cases that exercise what happens when hint resolution fails?

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Hmm, it doesn't look possible because Qb_name_hint::resolve() always returns false. Probably the function could be converted to void but I'd prefer to leave it as is to maintain uniformity with other resolve() functions. In fact, other resolve() functions are also not easy to test in this way, because they return true only in the case of malformed logic (when the switch cannot find a proper hint), and there's also DBUG_ASSERT(0) for such scenarios.

else if (const Qb_name_hint &qb_hint __attribute__((unused)) = hint)
{
// QB_NAME hints have been resolved earlier
continue;

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Can we assert here that the hint is resolved?

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Good idea, added an assertion

sql/sql_lex.h Outdated
#include "sql_class.h" // enum enum_column_usage
#include "select_handler.h"
#include "rpl_master_info_file.h" // Master_info_file
#include "opt_hints_structs.h"

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I was able to build "from scratch" without this #include, are you sure we need it?

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Indeed, this include is not needed any more. Removed

sql/sql_lex.h Outdated
create_info.set(options);
return main_select_push() || check_create_options(options);
}

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Consider reverting this line and, if the #include above isn't needed, then this file as a whole will have no changes in this commit.

This patch implements support for implicit query block (QB) names in
optimizer hints, allowing hints to reference query blocks and tables
within derived tables, views and CTEs without requiring explicit
QB_NAME hints.

Examples.
-- Addressing a table inside a derived table using implicit QB name
select /*+ no_index(t1@dt) */ *
  from (select * from t1 where a > 10) as DT;
-- this is an equivalent to:
select /*+ no_index(t1@dt) */ * from
  (select /*+ qb_name(dt)*/ * from t1 where a > 10) as DT;
-- Addressing a query block corresponding to the derived table
select /*+ no_bnl(@dt) */ *
  from (select * from t1, t2 where t.1.a > t2.a) as DT;

-- View
create view v1 as select * from t1 where a > 10 and b > 100;
-- referencing a table inside a view by implicit QB name:
select /*+ index_merge(t1@v1 idx_a, idx_b) */ *
  from v1, t2 where v1.a = t2.a;
-- equivalent to:
create view v1 as select /*+ qb_name(qb_v1) */ *
  from t1 where a > 10 and b > 100;
select /*+ index_merge(t1@qb_v1 idx_a, idx_b) */ *
  from v1, t2 where v1.a = t2.a;

-- CTE
with aless100 as (select a from t1 where b <100)
  select /*+ index(t1@aless100) */ * from aless100;
-- equivalent to:
with aless100 as (select /*+ qb_name(aless100) */ a from t1 where b <100)
  select /*+ index(t1@aless100) */ * from aless100;

Limitations:
  - Only SELECT statements support implicit QB names. DML operations
    (UPDATE, DELETE, INSERT) only support explicit QB names
@Olernov Olernov force-pushed the 12.4-MDEV-38045-impl-qb-name branch from a1c43dc to 1fb571d Compare February 25, 2026 18:12
Copy link
Member

@DaveGosselin-MariaDB DaveGosselin-MariaDB left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM but please let @spetrunia have a look before submitting.

@brief
Perform "Hint Resolution" for Optimizer Hints (see opt_hints.h for
definition)
definition) for both early and late stages.

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

There's no more early or late stage distinction, please update the comment.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Development

Successfully merging this pull request may close these issues.

2 participants

pFad - Phonifier reborn

Pfad - The Proxy pFad © 2024 Your Company Name. All rights reserved.





Check this box to remove all script contents from the fetched content.



Check this box to remove all images from the fetched content.


Check this box to remove all CSS styles from the fetched content.


Check this box to keep images inefficiently compressed and original size.

Note: This service is not intended for secure transactions such as banking, social media, email, or purchasing. Use at your own risk. We assume no liability whatsoever for broken pages.


Alternative Proxies:

Alternative Proxy

pFad Proxy

pFad v3 Proxy

pFad v4 Proxy