A WHERE conjunct that calls rand() but doesn't refer to a column, such
as rand() < 0, is checked on the last non-const top level base table
in the join order. A join of only const tables has no such table and
returns at most one row, so the conjunct is checked once before the
join starts, together with the conditions on columns of an outer
query.
When every top level base table is const and materialized semi-joins
follow them, the join order also has no non-const top level base
table, but only_const_tables() is false. The conjunct was never
checked, and such a query gave wrong results.
The check before the join now applies whenever the join order has no
non-const top level base table, which is the same condition under
which no table in the join order receives the conjunct. Such a join
also returns at most one row.
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
A join of only const tables stored the WHERE conjuncts that refer to a
column of an outer query or call rand() in pseudo_bits_cond. The
executor read pseudo_bits_cond only when the query needed no temporary
table, and read outer_ref_cond otherwise. A window function or
DISTINCT with ROLLUP needs a temporary table, so those conjuncts were
never checked, and the query returned rows that the WHERE clause
rejects.
pseudo_bits_cond came from the fix for LP bug 908269 in 2012. For a
join of only const tables, that fix also collected conjuncts that call
rand(), and it stored them in a new field without stating a reason.
The code that filled the two fields and the code that read them chose
between them by the same test. The tests have differed since
MDEV-8646 in 2016, which added the temporary table condition to the
executor's test. Later changes only display the field, in EXPLAIN
FORMAT=JSON (MDEV-19714) and in the optimizer trace (MDEV-21095). In
MDEV-19714, its author wrote that it was not clear why pseudo_bits_cond
was needed in addition to outer_ref_cond.
pseudo_bits_cond is removed, and outer_ref_cond holds these conjuncts
for every join. The executor checks it on both paths, so where the
conjuncts are stored no longer depends on the executor's test.
Conjuncts that call rand() are still collected only for a join of only
const tables. EXPLAIN FORMAT=JSON shows these conjuncts as
outer_ref_condition instead of pseudo_bits_condition.
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
When a versioned table stores its history in partitions, an implicit
query (no FOR SYSTEM_TIME) needs only current rows, which all live in
the current partition. vers_setup_conds() no longer adds a row_end
condition for such a table, and prune_partitions() restricts
read_partitions to the current partition instead.
read_partitions is reset to all partitions every statement in
open_table(), so the restriction must be applied on each execution.
prune_partitions() runs on each execution, also for merged derived
tables and views, where vers_setup_conds() is not called again. Both
functions guard with !vers_conditions.was_set(). In prune_partitions()
!is_set() would not do: set_all() sets type (flips is_set()) but not
orig_type (was_set()), so is_set() would be true on prepared-statement /
stored-procedure re-executions and skip the restriction, leaving history
partitions readable - rows matched then grow on each execute/call.
A query whose time comes from @@system_versioning_asof must still read
history, so the restriction is also skipped when from_sysvar is set.
init_from_sysvar() sets this new flag rather than orig_type, so
was_set(), print() and the write lock check still see only an explicit
FOR SYSTEM_TIME. Like orig_type, the flag persists to prepared-statement
re-execution.
EXPLAIN no longer shows "Using where" for such a query: correctness now
comes from partition selection rather than a row_end filter.
With sql_safe_updates, DELETE without WHERE on such a table is now
rejected even with LIMIT, as for a non-versioned table. With an index
on row_end, UPDATE or DELETE without WHERE (UPDATE also without LIMIT)
is now rejected too: it was accepted as a range scan on row_end. A
versioned table without SYSTEM_TIME partitions keeps the old behavior
(MDEV-41393).
1) take into account TMP_TABLE_ALL_COLUMNS when
we are modifying agg_item->result_field
2) remove unused now "bool materialized_subquery;"
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
When a select containing 32 ranges is made on a table containing a
compound key with 32 parts, the range optimizer can run off the end
of a stack variable, invalidly overwriting subsequent stack variables.
In the struct st_sel_arg_range_seq, we have an array
RANGE_SEQ_ENTRY stack[MAX_REF_PARTS];
MAX_REF_PARTS is 32.
check_quick_select
/ sel_arg_range_seq_init
initialises stack[0] as NOT a key part
/ sel_arg_range_seq_next
iterates through the key parts, adding key part n to stack[n+1]
key part #32 gets referenced by step_down_to(), setting seq->i off the
end of the array.
Fix: RANGE_SEQ_ENTRY stack[MAX_REF_PARTS+1];
The above change exposed an issue with key length calculation on
MS Windows. Calling make_prev_keypart_map(32) caused the resultant
bitmap to be calculated as (1UL << 32) - 1. Using the MSVC compiler
this resulted in an empty key length calculation during
handler::index_read_map, causing an assertion in ha_innobase::index_read().
As we only need 32 bits to represent our key map, we change the type thus
-typedef ulong key_part_map;
+typedef uint32 key_part_map;
We correct make_keypart_map() and make_prev_keypart_map() to call our
overflow safe my_set_bits(). We also correct bka_range_seq_next()
and bkah_range_seq_next() to use make_prev_keypart_map().
We also add some DBUG_ASSERTS in key_part_map processing elsewhere,
exposing some issues in our BNLH implementation. We cap the number of
keyuse parts here, altering the explain output of 2 of our tests.
Numerous places needed bit shift operations altered to use
make*keymap_part and various format strings needed to be corrected.
Problem:
Executing a range select using an index that includes virtual/generated columns
(e.g. SELECT ... WHERE c BETWEEN ... ORDER BY d) could trigger:
Assertion 'marked_for_read()' failed in Field access during execution.
Cause:
The keyread initialization path was populating table->read_set using
mark_index_columns(), which follows the non-recursive index marking path.
This only marks direct index fields and does not resolve dependencies of
virtual/generated columns.
As a result, when an index contained virtual columns, their dependent base
columns were not included in read_set, leading to invalid access during
later evaluation of virtual columns.
Fix:
Replace mark_index_columns() with mark_index_columns_for_read() in the
keyread initialization path. This ensures the recursive read-marking logic is
used, so virtual/generated column dependencies are correctly propagated into
read_set before execution.
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.
ROLLUP is defined as the UNION of grouping by every prefix of fields in
the original GROUP BY list. A ROLLUP query with an empty result set
returned zero rows when it should return a summary NULL, since grouping
by the empty prefix returns a single summary row.
The empty row case is handled in two separate locations. First, if the
optimizer is able to determine that no rows will be produced, e.g. due
to the table being empty or the WHERE condition resolving to false,
JOIN::send_row_on_empty_set is used to determine whether or not to send
an empty result row. Therefore, send_row_on_empty_set is modified to
include select_lex->olap == ROLLUP_TYPE.
Second, it may be the case that the absence of rows is not confirmed
until the execution phase. For instance when the WHERE condition is not
constant, or in the case of InnoDB where an empty table is not detected
during optimization. This is handled in both end_send_group and
end_write_group by this expression
join->first_record ||
(end_of_records && !join->group && !join->group_optimized_away)
The condition is extracted into need_empty_set_row and a second variable
empty_set_send_rollup_total is recorded to prevent running the default
rollup_send_data/rollup_write_data on the empty row case. This is
because the null summary row is already handled by send_data_with_check.
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.
