Aggregates lacked the SQL-standard FILTER clause,
forcing CASE-based workarounds that reduced readability across (sum, avg, count, …).
This update introduces the ability to specify a FILTER clause for aggregate functions,
allowing for more granular control over which rows are included in the aggregation.
Also, improves standards compliance and makes queries clearer and more readable.
The FILTER(WHERE ...) condition may contain any expression allowed in regular WHERE clauses,
except window functions, and outer references.
Functions that record actions on base tables accept TABLE* argument and
use table->s->{db,table_name} as the table's name.
The only different usage is in store_optimizer_context() where
we encounter a TABLE_LIST object which may be a base table or a VIEW.
The problem is that handler->multi_range_read_info_const() call in
check_quick_select() returned rows equal to HA_POS_ERROR, both during
context capture and replay. However, we never stored the ranges info
into the context when rows=HA_POS_ERROR. However, during replay, we try
to infuse stats for the given range, and since no match was found in the
context, we produced a warning.
Solution is to store range_info for all the ranges even when they get
HA_POS_ERROR number of rows.
- Introduce init_optimizer_context_recorder_if_needed() which is called
at query start. We n longer create the recorder lazily.
- Recorder functions no longer take MEM_ROOT to store data on as an
argument. The recorder knows the MEM_ROOT it should use.
This task loads the stats of the tables that are used in a query,
from the trace into the optimizer. This feature is controlled
by optimizer_replay_context, and points to an user defined variable name,
that has the context information.
The stats such as num_of_records present in the table, indexes if present,
their names, along with the average number of records_per_key
with in each index, cost of reading an index are loaded from the trace.
Additionally, stats from range analysis i.e. ranges, and the
corresponding number of records, along with their costs are also
loaded from the trace.
The trace context which is in JSON format is firstly set into the
user defined session variable, and this variable name is set to
optimizer_replay_context system variable.
Later, when a user issues a query, the contents of the json context is
parsed and an in memory representation is built using the class
Optimizer_context_replay. This class is then used by the optimizer
to update and save original values of the tables, indexes, and range stats
in the methods "set_statistics_for_table()" of sql_statistics.cc, and
"check_quick_select()" of opt_range.cc.
After the query gets finished, the statistics that were updated
in the optimizer are restored back to the saved original values.
The entry point for parsing the json structure is in
"mysql_execute_command()" of sql_parse.cc, and similarly exit point
i.e. to restore the saved stats of the optimizer is at the end
of the same method.
Two new warning/error messages are introduced:
ER_JSON_OPTIMIZER_REPLAY_CONTEXT_PARSE_FAILED, and
ER_JSON_OPTIMIZER_REPLAY_CONTEXT_MATCH_FAILED
This feature stores the basic stats of the base tables that are used in
a query, to the optimizer trace. This feature is also controlled by
optimizer_record_context, and is not enabled by default. The stats
such as num_of_records present in the table, indexes if present
then their names, along with the average number of records_per_key
with in each index are dumped to the trace. Additionally, stats from
range analysis of the queries are also dumped into the trace
The approach taken here is to extend the existing function
store_tables_context_in_trace() and add new dump_table_stats_to_trace()
to opt_trace_ddl_info.cc. For storing the range analysis stats into the
trace, they are first recorded in the hash defined in
Optimizer_Stats_Context_Recorder, and is used later while dumping the
stats into the trace from dump_table_stats_to_trace().
Several new tests are also added to opt_trace_store_stats.test
When an uncommitted transaction inserts rows into a table and
another statement locks rows in the same table (SELECT ... FOR UPDATE)
while computing a MIN or MAX, then:
1. In a Debug build, the server aborts on an assertion
2. In a Release build, the server returns wrong results
These errors occur because, while reading a group of rows for computing
a MAX, the transaction timeout error was swallowed.
Under the scenario described above and captured in the new test at this
commit, QUICK_GROUP_MIN_MAX_SELECT::next_max() emits a lock timeout error
during QUICK_GROUP_MIN_MAX_SELECT::get_next() but the error was suppressed
if we computed a MIN.
The InnoDB storage engine has an unwritten convention that after it has
returned a fatal error (which is any error except HA_ERR_END_OF_FILE
or HA_ERR_KEY_NOT_FOUND), then the SQL layer should not try to make
any further reads. This is because InnoDB might have rolled back
the current transaction already. So in the case of an error, return
immediately from QUICK_GROUP_MIN_MAX_SELECT::get_next().
An incorrectly backported test from 11.x revealed an UBSAN error in 10.11, so
fix that problem by preventing a division-by-zero from happening.
Remove the other incorrectly backported tests and relabel the retained test
in terms of the current ticket.
The code in QUICK_SELECT_DESC::get_next() had the logic that
"if index_last() call has returned nothing, then the table must be empty
so return HA_ERR_END_OF_FILE"
with ICP, index_last() may also return HA_ERR_END_OF_FILE when none of
the rows in the range matched ICP condition. But there can be matching
rows in other ranges, so continue the scan to examine them.
For some range condition x > N, a descending scan will walk off of the
left edge of the range and scan to the beginning of the index if we don't
set an endpoint on the storage engine.
In this patch, the logic to set such an endpoint is used in two places
now, so it's factored to a helper function.
In QUICK_SELECT_DESC::get_next(), when starting to scan a range with
no start endpoint, like "key < 10", clear the end_range on the storage engine.
We could have scanned another range before, like "key BETWEEN 20 and 30"
and after this the engine's end_range points to its left endpoint, the "20".
This is necessary especially when there are multiple ranges considered and
a later range indicates that there's no minimum endpoint (such as in the
attached test case), thus an earlier range endpoint must be removed/cleared.
QUICK_SELECT_DESC::get_next() sets a stale end range on the storage engine.
When checking the first range, it sets last_range to the first range and
then, if it cannot avoid descending scan, it sets the minimum endpoint on
the storage engine. However, get_next() may decide that this range is not
a match after updating the minimum endpoint on the storage engine. In this
case, it will continue to the next range. When continuing, it needs to
reset not only the state of 'last_range' to point to the next range that
it's checking, but it also needs to clear the now-stale end range set on
the storage engine. While this explanation covers the first and second
ranges, the issue at hand extends to the general case of ranges 1...N-1 and N.
Before this change and when get_next() decided that a range was not a
match, it would clear last_range at each loop continuation point.
Rather than clearing the minimum endpoint on the storage engine at
each continuation, we move all loop cleanup to for loop update clause.
This consolidates any logic that needs to be evaluated on loop
continuation to one place and reduces code duplication.
MySQL fixed this same problem at sha b65ca959efd6ec5369165b1849407318b4886634
with a different implementation.
When the optimizer_max_sel_arg_weight is set to 1, a nested query
crashed while tracing.
SEL_ARG object has a field named 'field', that is not set when the
type is other than KEY_RANGE. But, the field was accessed to store
its name, and weight to the trace. This resulted in a crash due to NULL
pointer.
Added a check to access field if the type is KEY_RANGE, and if not, just
trace the type.
Loose Index Scan optimization can only be used when the index it is
uses covers all columns used by the query. So, it would not be
attempted for indexes that are not present in table->covering_keys.
Before commit ed0a723566 (pushed to 11.0), Clustered PK would be
present in table->covering_keys if primary key columns are covering.
However, that commit gave Clustered PKs special treatment: they are
no longer present in table->covering_keys. This caused Loose Scan not
to be considered for Clustered PKs.
Fixed this by making the check in get_best_group_min_max() to allow
Loose Scan if all fields used by the query are parts of the Clustered PK.
Also made changes:
- The logic in JOIN_TAB::save_explain_data() assumed that Loose Scan
could only be used for index-only scans. This is not the case for
clustered PKs.
- One query in group_min_max_innodb started to use Loose Scan. It did so
in 10.11, too.
(Variant 2)
SEL_TREE* tree_or(SEL_TREE *X, SEL_TREE *Y) tries to conserve memory by
reusing object *X for the return value when possible.
MDEV-34620 has added logic to disable construction of index_merge plans
for N-way ORs with large N. That logic interfered with object reuse logic:
for the parameters of:
X = SEL_TREE{ trees=[key1_treeA, key2_treeB]}
Y = SEL_TREE{ trees=[key1_treeC]}
we would decide to reuse object X.
For key1, we would produce key_or(key1_treeA, key1_treeC)
but key2_treeB would be just left there.
Then, we would construct a range scan from key2_treeB.
Fixed by moving the "disable building index_merge plans" logic into a
location where it would not interfere with object reuse logic.
The "FETCH FIRST n ROWS WITH TIES" was not enforced when the SELECT used
"using index for group-by".
This was caused by an optimization which removed the ORDER BY clause
when the GROUP BY clause prescribed a compatible ordering.
Other GROUP BY strategies used workarounds to still handle WITH TIES,
see comment to using_with_ties_and_group_min_max() in this patch for
details. QUICK_GROUP_MIN_MAX_SELECT didn't have a workaround.
Fix this by disabling removal of ORDER BY when
QUICK_GROUP_MIN_MAX_SELECT is used.
Introduces NO_INDEX_MERGE and INDEX_MERGE, which control whether or
not index merge strategies are used during query optimization. Here
is an example query from the tests:
SET optimizer_switch='index_merge_intersection=off';
EXPLAIN SELECT /*+ INDEX_MERGE(t1 f4, f2) */ COUNT(*) FROM t1
WHERE f4 = 'h' AND f2 = 2;
with the hint in place, the query plan will employ the index_merge
intersect strategy (abbreviated EXPLAIN output):
type Extra
index_merge Using intersect(f2,f4); Using where; Using index
The presence of the [NO_]INDEX_MERGE hint overrides the optimizer's
choice of keys during the index merge optimization. As we see in
the above example, keys f2 and f4 and given and the optimizer will
consider only those keys for this query.
When the hint is given without any particular keys, as in
INDEX_MERGE(table), then all keys are considered. In this case, the
cheapest index merge among the keys should be used. When
NO_INDEX_MERGE(table) is given, then index merge is disabled for
that table.
When the hint is given with one or more keys, then only those keys
are considered. In the case of NO_INDEX_MERGE, those keys are
excluded. This can lead to no merged indexes at all, because
there may not be sufficient row-ordered read columns available for
consideration.
The index merge strategies of intersection, union, and sort union
cannot themselves be directly controlled via the hints. In combination
with the optimizer switches for the same, the strategy may be
indirectly controlled but this is not guaranteed.
When the hint directs the optimizer such that insufficient ROR scans
are available, thus leading to a situation where the INDEX_MERGE hint
cannot be honored, the server will emit a warning to that effect.
In the hints module (opt_hints*{cc,h}), this commit adds some
index merge-specific functionality to make interpreting hint state
at callsites in the optimizer cleaner and more intuitive.
Particularly, we add a bit field to the table hints class which
indicates the keys that are marked by an [NO_]INDEX_MERGE hint, if
present. A new function, index_merge_hint (and associated new
helper functions) relies on this field when interpreting index merge
hint state for the optimizer.
If there are no index merges available prior to attemping to find
a suitable union/sort union, then the optimizer will not attempt
it. This change results in optimizer trace output which does not
include the 'analyzing_index_merge_union' block when there are no
merges.
Parts of this implementation based on MySQL commit
ebcb981807e3d91a64782e89d48e1a25622eafea
Introduces code cleanup and small refactorings, such as:
- Flip keys_to_use.is_clear_all() and de-dent
- Rename best_trp to best_read_plan
- Fix key_parts assignment spacing
- find_shortest_key now keeps happy path to the left
- Cleanup in the get_best_ror_intersect, remove_nonrange_trees,
and restore_nonrange_trees functions
- Reorganization of locals within test_quick_select
- De-dent some large code blocks by changing
if (key)
{
LOTS-OF-CODE;
}
into
if (!key)
continue;
LOTS-OF-CODE;
This commit introduces two optimizer hints: ROWID_FILTER and NO_ROWID_FILTER.
They allow explicit control over whether certain indexes are used to build
rowid filters.
Syntax:
[NO_]ROWID_FILTER(tbl_name@query_block_name [index_name [, index_name] ...])
Hints can be applied at two levels:
Table-level (only table name specified): enables or disables rowid filters
for the given table.
Index-level (table and index names specified): forces the server to use
or ignore the listed indexes when building rowid filters.
Examples:
SELECT /*+ rowid_filter(t1)*/ a FROM t1 WHERE a < 'e' AND b > 't';
SELECT /*+ no_rowid_filter(t1)*/ a FROM t1 WHERE a < 'e' AND b > 't';
SELECT /*+ rowid_filter(t1 key_a, key_b)*/ a FROM t1 WHERE a < 'e' AND b > 't';
SELECT /*+ no_rowid_filter(t1 key_c)*/ a FROM t1 WHERE a < 'e' AND b > 't';
===== Interaction with other index hints =====
If [NO_]INDEX, [NO_]JOIN_INDEX hints are specified for the same table,
then only indexes whitelisted by INDEX() or JOIN_INDEX() can be considered
for rowid filters. ROWID_FILTER() hint cannot force the use of indexes that are
disabled by NO_INDEX() / NO_JOIN_INDEX() or omitted from INDEX() / JOIN_INDEX().
For example, if one index should be used for data access and another for
a rowid filter, the filter index must be mentioned in both hints:
SELECT /*+ index(t1 key_a, key_b) rowid_filter(t1 key_b) a FROM t1 ....
- Removed duplicate words, like "the the" and "to to"
- Removed duplicate lines (one double sort line found in mysql.cc)
- Fixed some typos found while searching for duplicate words.
Command used to find duplicate words:
egrep -rI "\s([a-zA-Z]+)\s+\1\s" | grep -v param
Thanks to Artjoms Rimdjonoks for the command and pointing out the
spelling errors.
main/statistics_json.result is updated for f8ba5ced55 (MDEV-36099)
The test uses 'delete from t1' in many places and then populates
the table again. The natural order of rows in a MyISAM table is well
defined and the test was implicitly relying on that.
before f8ba5ced55 delete was deleting rows one by one, using
ha_myisam::delete_row() because the connection was stuck in rbr mode.
This caused rows to be shown in the reverse insertion order (because of
the delete link list).
MDEV-36099 fixes this bug and the server now correctly uses
ha_myisam::delete_all_rows(). This makes rows to be shown in the
insertion order as expected.
