recompute_join_cost_with_limit() computes the cost of the first table's
partial join as best_read*fraction - pos->read_time*fraction. When the
two costs are nearly equal and large (e.g. with a huge
optimizer_scan_setup_cost, or when fraction is close to 1), the two
products are rounded independently. The difference can then be a small
negative number whose magnitude exceeds the absolute DBL_EPSILON
tolerance used by the debug assertion, even though it is only a
floating-point rounding artifact.
Fix: scale the assertion tolerance with the magnitude of the operands
(DBL_EPSILON * pos->read_time * fraction). Negative values are still
clamped to 0.0 as before.
Add test cases to optimizer_crash.test, one with a very large
optimizer_scan_setup_cost and optimizer_join_limit_pref_ratio=1, and one
with a large LIMIT on a 30000-row table.
setup_group no longer writes Item::marker. The ONLY_FULL_GROUP_BY
check now tests GROUP BY membership by walking the GROUP BY list.
A query that defines a WINDOW but never refers to it could crash in
add_key_field, for example
WITH cte AS (SELECT i FROM (SELECT i FROM t1 GROUP BY i) dt
WINDOW w AS (PARTITION BY i))
SELECT a.i FROM cte a JOIN cte b ON a.i=b.i WHERE a.i != 5;
A query that defines a WINDOW goes through setup_group, which set
marker to MARKER_UNDEF_POS (-1) on each GROUP BY expression so that
the ONLY_FULL_GROUP_BY check could skip it. Other code reads marker
as a set of flag bits (-1 sets all bits).
Item_direct_view_ref::grouping_field_transformer_for_where then took
the ref as flagged for substitution and followed a path that ends in
the crash.
The ONLY_FULL_GROUP_BY check was the only reader of that value, so
MARKER_UNDEF_POS is removed. The necessary check is local to the
setup_group function.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
An assert was present in method recompute_join_cost_with_limit()
to make sure the recomputed cost is always >= 0.
Although, the assert was correct, it was failing in
CLang compiled versions due to floating point comparison,
when we set sql_select_limit=1, and
optimizer_join_limit_pref_ratio=1;
In GCC compiled versions, the partial_join_cost was computed to +0.0.
However, in CLang version, the cost turned out to be -0.0.
Changed the assert such that partial_join_cost, would now be checked for
a value >= -DBL_EPSILON. Following it, we set the partial_join_cost to
0, if it has a negative value.
Introduces new dbug_print() functions for the following types:
dbug_print(NESTED_JOIN)
dbug_print(JOIN)
dbug_print(TABLE_LIST)
dbug_print(JOIN_TAB)
dbug_print(List<Item>)
Typically pointers to instances of the given types are passed.
The dbug_print(List<Item>) is intended to show result row before
sending to client (e.g., invoke in debugger while tracing end_send)
but may work in other contexts.
These functions produce nicely formatted output, please use them
in conjunction with the formatted printfs available in GDB or LLDB:
(gdb) printf "%s", dbug_print(join_tab)
(lldb) p printf("%s", dbug_print(table))
Example output from dbug_print(JOIN) in LLDB. The last line with
value 1302 is the number of characters produced by the printf
command.
(lldb) p printf("%s",dbug_print(join))
JOIN [0x15801bfe8] table_count=3 const_tables=0
join_list 0x158018340 [1 element(s)]:
--- #0 ---
[0x15801af10] "(nest_last_join)" join=INNER (outer_join=0) nested_join=0x15801b618 (elements=2)
NESTED_JOIN [0x15801b618]
used_tables: 0x0
not_null_tables: 0x0
n_tables: 0
counter: 0
nest_type: 1 (JOIN_OP_NEST)
nj_map: 0x0
join_list:
join_list 0x15801b618 [2 element(s)]:
--- #0 ---
[0x15801a308] "t3" join=LEFT (outer_join=1) map=0x4
ON: `test`.`t2`.`a` = `test`.`t3`.`a`
--- #1 ---
[0x158019b30] "(nest_last_join)" join=INNER (outer_join=0) nested_join=0x15801a238 (elements=2)
NESTED_JOIN [0x15801a238]
used_tables: 0x0
not_null_tables: 0x0
n_tables: 0
counter: 0
nest_type: 0
nj_map: 0x0
join_list:
join_list 0x15801a238 [2 element(s)]:
--- #0 ---
[0x1580187c0] "t1" join=RIGHT (outer_join=2) map=0x1
ON: `test`.`t1`.`a` = `test`.`t2`.`a`
--- #1 ---
[0x158018f08] "t2" join=INNER (outer_join=0) map=0x2
(int) 1302
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.
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.
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.
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.
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.
With_element::prepare_unreferenced() contains query from recursive CTE
which is upper owner of unreferenced CTE itself. Such recursive
derived table is resolved into its expression and it tries to resolve
fields or any other data from subqueries which are not yet
prepared. These subqueries are preparing in upper frames of the same
stack after prepare_unreferenced() finishes.
That does not happen when recursion happens in referenced CTE because
in that case there are links between the derived tables defined by the
parser and descending into such recursion descends further into all
the dependency queries. No such link exists in unreferenced query
therefore it misses its upper dependencies.
The fix moves prepare_unreferenced() later after JOIN::prepare() so it
has the chance to access all the prepared data.
There may be another fix of failing unreferenced queries with error as
such kind of queries seem to be not usable.
Fix rownum in HAVING clause or subquery
Description
==========
When rownum used in HAVING without GROUP BY, accepted_rows should be
incremented even if the having cond's value is 0.
When rownum used in subquery
1. Forbid subquery transformed to semi join.
2. New conditions must be added into having clause when doing in2exists.
They were based on the maximum possible key tuple length, which can be
much larger than the real data size.
The return value is used by handler::keyread_time(), which is used
to estimate the cost of range access.
This could cause range access not to be picked, even if it uses
the clustered PK and reads about 8% of the table.
The fix is to add KEY::stat_storage_length (next to KEY::rec_per_key) and
have the storage engine fill it in handler::info(HA_STATUS_CONST).
Currently, only InnoDB fills this based on its internal statistics:
index->stat_index_size and ib_table->stat_n_rows.
Also changed:
- In handler::calculate_costs(), use ha_keyread_clustered_time() when
computing clustered PK read cost, not ha_keyread_time().
The fix is OFF by default and enabled by setting FIX_INDEX_LOOKUP_COST flag
in @@new_mode.
Problem:
After the re-design of `UPDATE` and `DELETE` in MDEV-28883, the call
to find select handler is missing. This prevents the server from
handring over multi-update/multi-delete queries to storage engines
capable of executng such queries on their own (e.g., ColumnStore).
MDEV-32382 introduced a check in `find_select_handler_inner` function,
that blocked pushdown of queries involving CTEs, without checking if
the storage engine is capable of handling such queries.
Fix:
Add a call to find the select handler for the engine involved in the
multi-update/multi-delete query, allowing the storage engines to
execute such queries.
Fix the `find_select_handler_inner` function by allowing the storage
engine's create_select functions to decide the pushdown of queries
involving CTEs.
The NULL-aware index statistics fix is now controlled by the
FIX_INDEX_STATS_FOR_ALL_NULLS flag and disabled by default
for preserving execution plan stability in stable versions.
To enable:
SET @@new_mode = 'FIX_INDEX_STATS_FOR_ALL_NULLS';
Or via command line:
--new-mode=FIX_INDEX_STATS_FOR_ALL_NULLS
Or in configuration file:
[mysqld]
new_mode=FIX_INDEX_STATS_FOR_ALL_NULLS
`all_nulls_key_parts` bitmap is now calculated at set_statistics_for_table()
Variant 2: "Don't call optimize_stage2 too early"
The affected query had Split-Materialized derived table in another
derived table:
select * -- select#1
from (
select * -- select#2
from t1,
(select * from t2 ... group by t2.group_col) DT -- select#3
where t1.col=t2.group_col
) TBL;
The optimization went as follows:
JOIN::optimize() calls select_lex->handle_derived(DT_OPTIMIZE) which
calls JOIN::optimize() for all (direct and indirect) children SELECTs.
select#1->handle_derived() calls JOIN::optimize() for select#2 and #3;
select#2->handle_derived() calls JOIN::optimize() for select#3 the second
time. That call would call JOIN::optimize_stage2(), assuming the query
plan choice has been finalized for select#3.
But after that, JOIN::optimize() for select#2 would continue and consider
Split-Materialized for select#3. This would attempt to pick another join
order and cause a crash.
The fix is to have JOIN::optimize() not call optimize_stage2() ever.
Finalizing the query plan choice is now done by a separate call:
select_lex->handle_derived(thd->lex, DT_OPTIMIZE_STAGE2)
which invokes JOIN::optimize_stage2() and saves the query plan.
When all values in an indexed column are NULL, EITS statistics show
avg_frequency == 0. This commit adds logic to distinguish between
"no statistics available" and "all values are NULL" scenarios.
For NULL-rejecting conditions (e.g., t1.col = t2.col), when statistics
confirm all indexed values are NULL, the optimizer can now return a
very low cardinality estimate (1.0) instead of unknown (0.0), since
NULL = NULL never matches.
For non-NULL-rejecting conditions (e.g., t1.col <=> t2.col),
normal cardinality estimation continues to apply since matches are possible.
Changes:
- Added KEY::rec_per_key_null_aware() to check nulls_ratio from column
statistics when avg_frequency is 0
- Modified best_access_path() in sql_select.cc to use the new
rec_per_key_null_aware() method for ref access cost estimation
- The optimization works with single-column and composite indexes,
checking each key part's NULL-rejecting status via notnull_part bitmap
Wrong result is produced when split-materialized optimization is used for
grouping with order by and limit.
The fix is to not let Split-Materialized optimization to happen
when the sub-query has an ORDER BY with LIMIT, by returning FALSE early
in the method opt_split.cc#check_for_splittable_materialized()
However, with just the above change, there is a side-effect of
NOT "using index for group by" in the scenario when
all the following conditions are met: -
1. The query has derived table with GROUP BY and ORDER BY LIMIT
2. joined in a way that would allow Split-Materialized
if ORDER BY LIMIT wasn't present
3. An index suitable for using "index for group-by"
4. No where clause so that, "using for group by" is applicable,
but the index is not included in "possible_keys".
The reason being, join_tab's "keys" field wasn't being set in
sql_select.cc#make_join_select(). So, made this change as well
as part of this PR.
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.
A nested select query is crashing in when optimizer_join_limit_pref_ratio=10
and optimizer_search_depth=1 due to an assertion failure in
JOIN::dbug_verify_sj_inner_tables().
In sql_select.cc#choose_plan(), there are 2 back-2-back calls to
greedy_search(). The first one is invoked to build a join plan
that can short-cut ORDER BY...LIMIT, while the second invocation
to not consider short-cut.
The greedy_search() should start with a value of join->cur_sj_inner_tables
set to 0. However, the first greedy_search() call left the value of
join->cur_sj_inner_tables to "6". This caused the assert to fail in
dbug_verify_sj_inner_tables() as soon as the second greedy_search() started,
where in it was expecting a value of 0.
Similar problem is noticed with cur_embedding_map in the case of nested
joins, and nested_join counter.
introduced init_join_plan_search_state() which is called
from start of greedy_search() and optimize_straight join
that does all the needed initialization by :
1. setting 0's to cur_sj_inner_tables, and cur_embedding_map
2. invoking reset_nj_counters().
- 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.
