Oleg Smirnov 08c5f0f4cb MDEV-38045: Implement QB_NAME hint with path syntax for nested query blocks ...

Extended QB_NAME hint to support path-based addressing of query blocks
nested within views, derived tables, and CTEs, following TiDB's syntax.

New syntax:
  QB_NAME(name, query_block_path), where
    query_block_path ::= query_block_path_element
                          [ {. query_block_path_element }... ]
    query_block_path_element ::= @ qb_path_element_select_num |
                                    qb_path_element_view_sel
    qb_path_element_view_sel ::= qb_path_element_view_name
                                  [ @ qb_path_element_select_num ]

For example,
  `SELECT /*+ qb_name(qb_v1, v1) */* FROM v1`
The name `qb_v1` is assigned to the inner query block of the view `v1`.

  `SELECT /*+ qb_name(qb_v1, v1@sel_1) */* FROM v1`
Means the same but specifies that `v1` is present in SELECT#1 of the current
query block.

  `SELECT /*+ qb_name(qb_v1, v1@sel_1 .@sel_2) */* FROM v1`
This means SELECT#2 of view `v1`, which is present in SELECT#1 of
the current query block, gets the name `qb_v1`.

It is possible to specify not only view names but also derived tables
and CTE's in the path.

Views and derived tables may be nested on multiple levels, for example:
  `SELECT /*+ qb_name(dt2_dt1_v1_1, dt1 .dt2 .v2 .@SEL_2)
              no_index(t1@dt2_dt1_v1_1)*/ v1.*
  FROM v1 JOIN (SELECT v1.* FROM v1 JOIN (SELECT * FROM v2) dt2) dt1`

Limitations:
  - Only SELECT statements support QB names with path. DML operations
    (UPDATE, DELETE, INSERT) only support explicitly defined QB names

2026-04-30 17:22:36 +07:00

36 KiB

Raw Permalink Blame History

View Raw