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
This patch implements an Oracle style function to_number()
with the following signatures:
- to_number(number_or_string_subject)
- to_number(string_subject, string_format)
The function is implemented in sql/item_numconvfunc.cc.
The function returns the DOUBLE data type for
all signatures and input data types.
The format parser understands the following components:
- Digits: 0, 9
- Hex digits: X
- Group separators: comma (,) and G
- Decimal delimiers: period (.) and D
- Approximate number signature: EEEE
- Currency/numeric flags: $ and B
- Currency signatures: C, L, U
- Sign signatures: S, MI, PR
- Special format signatures: V, TM, TM9, TME
- Format flag: FM
Note, the parser was implemented assuming that we'll also have the
oppostite function to_char() soon for numeric input.
So it parser all known components.
However, the function to_number() does not support:
- Formats V, TM, TM9, TME. to_number() returns NULL if the format
string has these components.
These componens are supported only by to_char() in Oracle.
Features not inclided into this patch:
- The ON CONVERSION ERROR clause
- The third parameter (nlsparam)
- Internationalized components: G, D, C, L, U.
These features will be implemented later, under terms of MDEV-36978.
Notable changes in the other files:
- item_func.h: adding Item_handled_func::Handler_double
- simple_parser.h: adding a number of *CONTAINER* templates
They help to save on duplicate code when creating classes
suitable for passing into parsing templates such as OPT, OR2C, OR3C, etc
- simple_parser.h: Adding parsing templates OR4C and OR5C
- simple_parser.h: Moving the template "OPT" towars the beginning of the file
Rule parsing templates TOKEN, TokenChoice, AND2, OR2C, OR3C, OR4C, OR5C, LIST
now provide a sub-class Opt, to parse its optional rule.
- simple_parser.h: Adding "explicit" to all "operator bool" definitions
- Renaming Parser_templates::TOKEN to Parser_templates::TokenParser
- Adding "explicit" to all "operator bool()" templates/classes,
to avoid hidden implicit conversion (to int, void*, etc).
- Renaming the LIST template parameter ELEMENT to ELEMENT_PARSER,
to make it clearer what it is for.
- Renaming the OPT template parameter RULE to RULE_PARSER,
to make it clearer what it is for.
This commit implements optimizer hints allowing to affect the order
of joining tables:
- JOIN_FIXED_ORDER similar to existing STRAIGHT_JOIN hint;
- JOIN_ORDER to apply the specified table order;
- JOIN_PREFIX to hint what tables should be first in the join;
- JOIN_SUFFIX to hint what tables should be last in the join.
It places a limit N (a timeout value in milliseconds) on how long
a statement is permitted to execute before the server terminates it.
Syntax:
SELECT /*+ MAX_EXECUTION_TIME(milliseconds) */ ...
Only top-level SELECT statements support the hint.
