When a NATURAL JOIN operates on a derived table (or view with
derived_merge=off) having more than 64 columns, the optimizer's
generate_derived_keys_for_table() would:
1) Overflow a 32-bit shift: (key_part_map)(1 << parts) when parts >= 32
2) Create a derived key with more parts than Bitmap<64>/key_part_map can hold
3) Crash on DBUG_ASSERT(prefix_size <= width) in Bitmap<64>::is_prefix(65)
Fix: cap the number of hash-join KEYUSE entries for a materialized derived
table at the handler's max_key_parts(). Excess columns are excluded from the
key, so generate_derived_keys_for_table() never builds a key with more parts
than key_part_map (64 bits) or Bitmap<64> can represent. Also fix the shift
to cast before shifting: (key_part_map) 1 << parts.
Refine the logic of storing hints inside view definitions.
The same procedures are used for printing hints in both
EXPLAIN EXTENDED of regular statements and when storing view
definitions. For regular statements all hints (except QB_NAME)
are printed into the topmost SELECT with attachment of target
query block names. If some query blocks do not have explicit
names set with QB_NAME hints, implicit names `select#1`, `select#2`
are attached.
This approach does not work views, because view definitions do not
have such implicit numbering of query blocks. The solution is
to print hints related to a particular query block right
into this query block instead of printing them into
the topmost SELECT.
Problem:
Executing an `UPDATE ... FOR PORTION OF` statement on a table without
a defined period results in assertion failure. The code in
`SELECT_LEX::period_setup_conds` attempts to compare a NULL
period name identifier using `streq()`, which triggers the assertion
in `strnncoll` funtion.
Fix:
Replace `streq` with `streq_safe` in `SELECT_LEX::period_setup_conds`
to handle NULL pointers safely via an early-exit.
Remove the unscoped #pragma GCC diagnostic ignored
"-Wdeprecated-declarations" from include/violite.h and replace all
sprintf/vsprintf calls with snprintf/vsnprintf across the codebase.
Where possible, pass actual buffer sizes as function parameters
instead of using hardcoded constants or recomputing strlen().
Notable API changes:
- get_date(): added to_len parameter for buffer size
- nice_time(), end_timer() in client/mysql.cc: added buff_size
- generate_new_name(): added name_size parameter
- calc_md5(): added buffer_size parameter (fixes off-by-one)
- MD5_HASH_TO_STRING macro: added _size parameter
- Added MYSQL_UDF_MAX_RESULT_LENGTH constant in mysql_com.h
Also fixed a pre-existing bug in sql/rpl_mi.cc where the ellipsis
"..." was written to the wrong buffer (dbuff instead of buff).
Vendored code is excluded: extra/readline, extra/wolfssl,
wsrep-lib, libmariadb, storage/mroonga/vendor, zlib.
When evaluating (SELECT NULL, NULL) IN (SELECT 1, 2 FROM t), the result
was incorrectly 0 instead of NULL.
The IN-to-EXISTS transformation wraps comparison predicates with
trigcond() guards:
WHERE trigcond(NULL = 1) AND trigcond(NULL = 2)
During optimization, make_join_select() evaluated this as a "constant
condition". With guards ON, the condition evaluated to FALSE (NULL
treated as FALSE), triggering "Impossible WHERE". At runtime, guards
would be turned OFF for NULL columns, but the optimizer had already
marked the subquery as returning no rows.
Fix: check can_eval_in_optimize() instead of is_expensive() in
make_join_select(). Unlike is_expensive(), can_eval_in_optimize()
also verifies const_item(), which returns FALSE for Item_func_trig_cond
(Variant 3, more comments)
When multiple indexes produce ordering that matches the
ORDER BY ... LIMIT clause, like
INDEX idx1(kp1, kp2)
INDEX idx2(kp2)
and the query with
WHERE kp1=const ORDER BY kp2 LIMIT 2
then test_if_cheaper_ordering() in pre-11.0 versions will choose
the index with the smallest KEY::user_defined_key_parts (idx2 in
this example). This can produce a much worse query plan.
The fix is to do what MariaDB 11.0 would do: use the index for which
we have the cheapest access method. The cost of access method here
takes into account that we will stop after producing #LIMIT rows.
The fix is controlled by the
@@optimizer_adjust_secondary_key_costs=fix_order_by_index_choice
flag and is OFF by default.
Fixes unnecessary filesort on derived tables when ordered/grouped by a
field in the key. The data is inherently sorted, wrapping the result set
in a filesort is redundant.
The bug was that thd->lex->limit_rows_examined_cnt was not reset after
queries. It is reset in lex_start() at the start of the next query
execution.
This causes general_log_write(), which is called before lex_start(), to
exceed the limit. The effect is a crash or the next query would not be
executed.
Fixed by resetting limit_rows_examined_cnt at end of query.
Lex::save_list contents remain from a previous query that has invalid
syntax and isn't reset when processing a new query.
We initialize this structure along with it's peer in LEX::start.
MDEV-38295 Recursive CTE usage leaks memory when not used in a top level select
During st_select_lex::cleanup() we assume that leaf tables and any
associated recursive table references can only be present when a join
structure is present. After MDEV-37220 this is no longer true.
We shift our cleanup routine out of the test for a join.
We add Multidelete_prelocking_strategy::handle_end() in the same manner
as Multiupdate_prelocking_strategy::handle_end() (but with minimal
processing as yet) to populate these leaf_tables in the outermost
select when it is a delete statement.
We add in a call to free_underlaid_joins() to the error processing section
of Sql_cmd_dml::execute to free up temporary tables that were allocated
in prepare, but not freed up when execute is bypassed because an error
occured during prepare.
Sergei Petrunia: Better comments for MDEV-38272, MDEV-38272
Approved by: Sergei Petrunia (sergey@mariadb.com)
(Based on a patch by Yuchen Pei)
When computing table dependencies in simplify_joins(), do not make
LEFT JOIN-ed table (or join nest) to be dependent on all preceding tables.
For queries in form
t1
LEFT JOIN t2 ON t2.col=t1.col
LEFT JOIN t3 ON t3.col=t1.col
this allows the optimizer to construct join order of t1-t3-t2. Before
this patch, t1-t2-t3 was the only possible order.
Note that queries that use Oracle's Outer Join syntax (col1(+)=col2) are
converted into chained independent LEFT JOINs like the above.
The optimization is controlled by optimizer_switch='reorder_outer_joins=ON'
It is NOT enabled by default as it is known to expose problems with
join pruning. It is advised to set optimizer_prune_level=0 when setting
reorder_outer_joins=on.
Co-authored-by: Yuchen Pei <ycp@mariadb.com>
ft_handler isn't getting initialized for subqueries inside explain
delete/update queries. However, ft_handler is accessed inside ha_ft_read(),
and is the reason for NULL pointer exception.
This is not the case with non-explain delete/update queries, as
well as explain/non-explain select queries.
Follow the approach the SELECT statements are using in
JOIN::optimize_constant_subqueries(): remove SELECT_DESCRIBE
flag when invoking optimization of constant subqueries.
Single-table UPDATE/DELETEs have SELECT_LEX but don't have JOIN.
So, we make optimize_constant_subqueries() not to be a member
of JOIN class, and instead move it to SELECT_LEX, and then
invoke it from single-table UPDATE/DELETE as well as for SELECT queries.
Index merge and rowid filter should not be used together, however,
even if index merge is not chosen earlier in best_access_path, it may
be chosen again in make_join_select, inside ref_to_range. Therefore
this patch ensures that rowid filter is not used when index merge is
chosen there.
Rename `Item::clone_item()` to `clone_constant()`, and do
the same for any overloads in descendant items.
The function returns non-NULL only for items that represent
constant literals.
Send ok packet earlier for SELECT queries that does not have any updates.
This is done in select_send::send_eof()
Axel's select benchmarks shows that this has a notable speed improvement:
1 Thread: 28% TPS speedup
8 Threads: 23%
64 Threads 6%
This was meassured with t_oltp_point_select With InnoDB running the client
over sockets.
Other things:
- Moved error reporting of LIMIT ROWS EXAMINED from handle_select() to
check_limit_rows_examined(). This is to ensure that the error is
reported before send_eof() is called.
- Removed duplicate "Query execution was interrupted" messages for the
same query. killed_for_exceeding_limit_rows_warning_given was introduced
for this purpose. We cannot use 'killed' flag in
killed_for_exceeding_limit_rows() to detect if we already have produced
a warning as the 'killed' in this case is reset for each union.
Reviewer: Sergei Golubchik <serg@mariadb.org>
One can define a derived table with a zero-length field, for example:
(SELECT * FROM (SELECT '' FROM t) as x)
Then, the optimizer may create a zero-length index on it (this is not
allowed for regular tables). Attempt to use this index will fail an
assert in create_ref_for_key().
Fix this by marking zero-length index as an Ignored Index, so the
optimizer doesn't try to use it.
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.
