Fixes a scenario where an IN subquery returned the wrong result
because the pushed WHERE clause was not retained for downstream
result filtering. For example:
CREATE TABLE t1 (c1 TEXT, UNIQUE (c1(1)));
INSERT INTO t1 (c1) VALUES ('a');
SELECT 'abc' IN (SELECT c1 FROM t1);
Internally, he 'abc' IN subquery condition becomes the constant
condition:
'abc' = t1.c1 or t1.c1 is null
Prior to this patch, this condition was incorrectly removed when
converting the subquery engine to an index lookup-based engine.
Now eligible conditions are preserved during such engine rewrites.
SplM_opt_info::last_refills is never set, so remove it. It was used in
In JOIN_TAB::choose_best_splitting(), use local variable "refills" there,
instead.
The logic in the function is that we compute
startup_cost= refills * spl_plan->cost;
only when we have a splitting query plan which implies "refills" was set
accordingly.
Also
- Added a comment about what "refills" is.
- Updated derived_cond_pushdown.result: the change makes the
Split-Materialized plans more expensive, so the join order changes from
t3, <split-materialized(outer_ref)> to <split-materialized>, t3
in 11.4 multi_update::prepare() is called both for single and multi update.
as it's invoked before lock_tables(), it cannot do prepare_for_insert(),
which calls external_lock() and only can be used on an already locked table
During the optimization phase, when determining whether or not to materialize
a derived table all at once or in groups (split materialization), all key
equalities that reference the derived table have their cond Items pushed into
JOIN::spl_opt_info->inj_cond_list.
From there they are filtered (tables in join prefix) and injected into the
JOIN conditions (in JOIN::inject_best_splitting_cond()), where they might
be removed because they are involved ref access and they aren't needed.
These pushed items conditions were always Item_func_eq, whether or or
the key usage specifies null safe equality (<=>) or not.
The fix is to create an Item_func_equal condition when the key equality
is specified using <=>.
approved by Sergei Petrunia (sergey@mariadb.com) PR#4198
When populating the structure spl_opt_info for a TABLE, and evaluating a
key_field for inclusion in spl_opt_info->added_key_fields, the null_rejecting
attribute may be incorrectly set. Originally, this attribute was
assumed to be TRUE, then it was changed
Item *real= key_field->val->real_item();
if ((real->type() == Item::FIELD_ITEM) &&
((Item_field*)real)->field->maybe_null())
added_key_field->null_rejecting= true;
else
added_key_field->null_rejecting= false;
which also incorrectly assumed that the added key field depended on
whether the field was able to set to null.
The correct setting for this attribute is simply to pass it through from
the key being evaluated.
The result of an incorrect value is, in this test case, incorrect
equality conditions being pushed into our (lateral) derived table,
excluding rows that might legitimately contain NULL and thus returning
a wrong result.
Approved by Sergei Petrunia, PR#4140
Anonymous block is represented internally by the class sp_head,
so every statement inside an anonymous block is a SP instruction.
On the other hand, the anonymous block specified in the FROM clause of
the PREPARE statement is treated as a single statement. In result,
all parameter markers (represented by the character ?) are parts of
the anonymous block specified in the prepared statement and at the same
time parameter are markers, internally represented by instances of
the class Item_param and distributed among SP instructions representing
SQL statements (every SQL statement is represented by an instance of
the class sp_instr_stmt)
In case table metadata changed on running an anonymous block in prepared
statement mode, only SP instruction's statement is re-parsed. Before
re-parsing a SP's statement, all items are cleaned up including
instances of the class Item_param that represent positional parameters.
Unfortunately, this leads to presence of a dangling pointer in
Prepared_statement::param_array that references to the deleted
Item_param while invoking reset_stmt_params happening on every execution
of a prepared statement.
To fix the issue, no instances of Item_param created on re-parsings
a statement for failed SP instruction, rather instances of Item_param
left from first time parsing are re-used. As a consequence, all pointers
to instances of the class Item_param stored in the array
Prepared_statememt::param_array and possibly spread along the code base
(e.g. select_lex->limit_params.select_limit)
still point to valid Items.
Check stmt_da::is_error before calling stmt_da::sql_errno(which is what
the assertion ensures)
The bug did not have any negative effects in optimized builds.
Execution of UPDATE statement on a table that has an associated trigger
and the UPDATE statement tries to modify a column having the DEFAULT
clause, could result in either assert firing or incorrect issuing of
the ER_BAD_NULL_ERROR error.
The reason of such behaviour is that on opening of a table that has
an associated trigger, the method
Table_triggers_list::prepare_record_accessors
called to prepare Field objects referencing TABLE::record[1] instead
of record[0]. This method allocates a new array of Field objects
as copies of original table fields but updated null_ptr data
members pointing to an array of extra_null_bitmap allocated before
that on table's mem_root. Later switch_to_nullable_trigger_fields()
is called where table' fields is switched to the new array allocated at
Table_triggers_list::prepare_record_accessors().
After that, when fill_record() is invoked to fill table fields with
values, so the make_default_field is invoked to handle the clause
DEFAULT and the function make_default_field() called to create a field
object. The function make_default_field() creates a copy of Field
object and updates its data member prt/null_tr to position their to
right place of table's record buffer, but since the method
Table_triggers_list::prepare_record_accessors
has been invoked before, the expression
def_field->table->s->default_values - def_field->table->record[0]
used for pointers adjustment leads to pointing to arbitrary memory not
associated with the table.
To fix the issue, use the TABLE_SHARE fields for referencing to columns
default values.
MariaDB server crashes when a query includes a derived table
containing unnamed column (eg: `SELECT '' from t`). When `Item`
object representing such unnamed column was checked for valid,
non-empty name in `TABLE_LIST::create_field_translation`, the
server crahsed(assertion `item->name.str && item->name.str[0]`
failed).
This fix removes the redundant assertion. The assert was a strict
debug guard that's no longer needed because the code safely handles
empty strings without it.
Selecting `''` from a derived table caused `item->name.str`
to be an empty string. While the pointer itself wasn't `NULL`
(`item->name.str` is `true`), its first character (`item->name.str[0]`)
was null terminator, which evaluates to `false` and eventually made
the assert fail. The code immediately after the assert can safely
handle empty strings and the assert was guarding against something
which the code can already handle.
Includes `mysql-test/main/derived.test` to verify the fix.
This commit fixes a bug where Aria tables are used in
(master->slave1->slave2) and a backup is taken on slave2. In this case
it is possible that the replication position in the backup, stored in
mysql.gtid_slave_pos, will be wrong. This will lead to replication
errors if one is trying to use the backup as a new slave.
Analyze:
Replicated row events are committed with trans_commit_stmt() and
thd->transaction->all.ha_list != 0.
This means that backup_commit_lock is not taken for Aria tables,
which means the rows are committed and binary logged on the slave
under BLOCK_COMMIT which should not happen.
This issue does not occur on the master as thd->transaction->all.ha_list
is == 0 under AUTO_COMMIT, which sets 'is_real_trans' and 'rw_trans'
which in turn causes backup_commit_lock to be taken.
Fixed by checking in ha_check_and_coalesce_trx_read_only() if all handlers
supports rollback and if not, then wait for BLOCK_COMMIT also for
statement commit.
forever, cannot be killed
mysql_rm_table_no_locks() does TDC_RT_REMOVE_ALL which waits while
share is closed. The table normally is open only as OPEN_STUB, this is
what parser does for CREATE TABLE. But for SELECT the table is opened
not as a stub. If it is the same table name we anyway have two
TABLE_LIST objects: stub and not stub. So for "not stub"
TDC_RT_REMOVE_ALL sees open count and decides to wait until it is
closed. And it hangs because that was opened in the same thread.
The fix disables subqueries in CHECK expression at parser
level. Thanks to Sergei Golubchik <serg@mariadb.org> for the patch.
Oracle mode has different set operator precedence and handling (not by
standard). In Oracle mode the below test case is handled as-is, in
plain order from left to right. In MariaDB default mode follows SQL
standard and makes INTERSECT prioritized, so UNION is taken from
derived table which is INTERSECT result (here and below the same
applies for EXCEPT).
Non-distinct set operator (UNION ALL/INTERSECT ALL) works via unique
key release but it can be done only once. We cannot add index to
non-empty heap table (see heap_enable_indexes()). So every UNION ALL
before rightmost UNION DISTINCT works as UNION DISTINCT. That is
common syntax, MySQL, MSSQL and Oracle work that way.
There is union_distinct property which indicates the rightmost
distinct UNION (at least, so the algorithm works simple: it releases
the unique key after union_distinct in the loop
(st_select_lex_unit::exec()).
INTERSECT ALL code (implemented by MDEV-18844 in a896beb) does not
know about Oracle mode and treats union_distinct as the last
operation, that's why it releases unique key on union_distinct
operation. INTERSECT ALL requires unique key for it to work, so before
any INTERSECT ALL unique key must not be released (see
select_unit_ext::send_data()).
The patch tweaks INTERSECT ALL code for Oracle mode. In
disable_index_if_needed() it does not allow unique key release before
the last operation and it allows unfold on the last operation. Test
case with UNION DISTINCT following INTERSECT ALL at least does not
include invalid data, but in fact the whole INTERSECT ALL code could
be refactored for better semantical triggers.
The patch fixes typo in st_select_lex_unit::prepare() where
have_except_all_or_intersect_all masked eponymous data member which
wrongly triggered unique key release in st_select_lex_unit::prepare().
The patch fixes unknown error in case ha_disable_indexes() fails.
Note: optimize_bag_operation() does some operator substitutions, but
it does not run under PS. So if there is difference in test with --ps
that means non-optimized (have_except_all_or_intersect_all == true)
code path is not good.
Note 2: VIEW is stored and executed in normal mode (see
Sql_mode_save_for_frm_handling) hence when SELECT order is different
in Oracle mode (defined by parsed_select_expr_cont()) it must be
covered by --disable_view_protocol.
Without this increase the mtr test case pre/post conditions will
fail as the stack usage has increased under MSAN with clang-20.1.
ASAN takes a 11M stack, however there was no obvious gain in MSAN
test success after 2M.
The resulting behaviour observed on smaller stack size was a
SEGV normally.
Hide the default stack size from the sysvar tests that expose
thread-stack as a variable with its default value.
When table2myisam() prepares recinfo structures BIT field was skipped
because pack_length_in_rec() returns 0. Instead of BIT field
DB_ROW_HASH_1 field was taken into recinfo structure and its length
was added to reclength. This is wrong because not stored fields must
not be prepared as record columns (MI_COLUMNDEF) in storage
layer. 0-length fields are prepared in "reserve space for null bits"
branch.
The problem only occurs with tables where there is no data for the
main record outside of the null bits.
The fix updates minpos condition so we avoid fields after
stored_rec_length and these are not stored by
definition. share->reclength already includes not stored lengths from
CREATE TABLE so we cannot use it as minpos starting point.
In Aria there is no "reserve space for null bits" and it does not
create column definition for BIT. Also there is no
setup_vcols_for_repair() to reproduce the issue. But nonetheless it
creates column definition for not stored fields redundantly, so we
patch table2maria() as well. The test case for Aria tries to
demonstrate BIT field works, it does not reproduce any issues (as
redundant column definition for not stored field seem to not cause any
problems).
This is needed to make it easy for users to automatically ignore long
char and varchars when using ANALYZE TABLE PERSISTENT.
These fields can cause problems as they will consume
'CHARACTERS * MAX_CHARACTER_LENGTH * 2 * number_of_rows' space on disk
during analyze, which can easily be much bigger than the analyzed table.
This commit adds a new user variable, analyze_max_length, default value 4G.
Any field that is bigger than this in bytes, will be ignored by
ANALYZE TABLE PERSISTENT unless it is specified in FOR COLUMNS().
While doing this patch, I noticed that we do not skip GEOMETRY columns from
ANALYZE TABLE, like we do with BLOB. This should be fixed when merging
to the 'main' branch. At the same time we should add a resonable default
value for analyze_max_length, probably 1024, like we have for
max_sort_length.
In non-EXPLAIN queries with subqueries, the trace was flooded
with empty "join_execution":{} nodes. Now, they are gone.
The "Range checked for each record" optimization still prints
content into trace on join execution. Now, we wrap it into
"range-checked-for-each-record" to delimit the invocations.
This new object has fields "select_id" which corresponds to
the outer query block, and the "loop" which corresponds to
the inner query block iteration number. Additionally,
the field "row_estimation" which itself is an object has
"table", and "range_analysis" fields that were moved
from the old "join_execution"'s steps array.
Get rid of need of matherialization for usual INSERT (cache results in
Item_cache* if needed)
- subqueries in VALUE do not see new records in the table we are
inserting to
- subqueries in RETIRNING prohibited to use the table we are inserting to
inside the get_lookup_field_values when lower_case_table_names's value
is 2 this should reserve the case for the table's and database's names
so this commit changes the condition to lowercase only when the
lower_case_table_name's value is 1 not just 1 and 2 "any value not equal
0"
SELECT FROM information_schema.metadata_lock_info may sporadically
return MDL_SHARED lock acquired by InnoDB purge thread.
Fix proposed in a7bf0a42d0 wasn't enough to solve this problem:
apparently it did protect against purging old rows, but not against
locking the table.
Reverted a7bf0a42d0 in favour of solution proposed in e996f77cd8.
That is use wait_all_purged.inc to make sure purge is completed.
"t1 JOIN t2 USING(col1,...)" calls mark_common_columns() to mark the
listed columns as used in both used tables, t1 and t2.
Due to a typo bug, it would mark the wrong column in the second table
(t2): instead of t2.col1 it would mark the last column in t2.
The harmful effects included JOIN_TAB(t2)->covering_keys not being
set correctly. This changed the cost to access the table and then
caused different query plans depending on which table was the second
in the JOIN ... USING syntax.
When allocate_block() have failed Query_cache::insert_table() misses
to init node->parent which is then accessed with outdated data. There
is check in Query_cache::register_all_tables():
if (block_table->parent)
unlink_table(block_table);
So unlink_table() is avoided when parent is 0.
The problem is with window function which requires its own sorting but
it is impossible as table is updated while select is
progressing. That's it, multi-update queries do not use temporary
tables to store updated rows and substitute them at the end of the
query. Instead, updates are performed directly on the target table,
row by row, during query execution. MariaDB processes updates in a way
that ensures each row is updated only once, even if it matches
multiple conditions in the query. This behavior avoids redundant
updates and does not require intermediate storage in a temporary table.
The detailed cause of the loop invoked by window function was
explained by Yuchen Pei in MDEV-31647 comments.
The fix disables window functions for multi-update.
Note that MySQL throws ER_UPDATE_TABLE_USED in that case which is the
result of check_unique_table(). But this function is not used for
multi-update in MariaDB and this check cannot be done because some
level of SELECT expressions is allowed (MDEV-13911).
failed in find_field_in_table_ref
The main crash with segfault in find_field_in_tables() was fixed by
6aa47fae30 (MDEV-35276). This fix is for debug assertion.
Since Item_default_value is also Item_field there is nothing to be
done except adding DEFAULT_VALUE_ITEM type check.
Duplicated code from methods
sp_instr_cpush::get_expr_query
sp_instr_cursor_copy_struct::get_expr_query
were extracted to the standalone function get_cursor_query
Additionally, added correct parsing of a cursor definition with
a new line or TAB instead of the space immediately after FOR/IS.
Disallow range optimization for BETWEEN when casting one of the arguments
from STRING to a numeric type would be required to construct a range for
the query.
Adds a new method on Item_func_between called can_optimize_range_const
which allows range optimization when the types of the arguments to BETWEEN
would permit it.
The crash happened when ExtractValue() returning an empty string
as a result was passed to LCASE() or UCASE().
Item_func_xml_extractvalue::val_str() could return a String
{Ptr=0,str_value=0} in some cases, to mean an empty retult.
But virtual my_charset_handler_st functions caseup() and casedn()
do not expect {src=nullptr,srclen=0} as input and:
- raise a DBUG_ASSERT() in debug builds, or
- raise a "applying zero offset to null pointer" warning in UBSAN builds
Fixing Item_func_xml_extractvalue::val_str() to return
a String {Ptr="",str_length=0} instead of {Ptr=0,str_value=0}.
A similar fix was done earlier in Field_set::val_str(). See c69fb1a627.
During optimize_cond, we incorrectly removed the NOT LIKE condition when
attempting to remove any equality conditions.
Item_func_like's override of eq_cmp_result() returns COND_TRUE when
its collation is the binary collation. Item_bool_func2's implementation
of remove_eq_conds would then attempt to detect if both arguments were
equal to one another and return a NULL condition to optimize_conds. This
removes the condition from ever being evaluated (and
Item_func_like::val_bool is never called in this case), rendering the
incorrect result.
Fix this by checking the negated condition during eq_cmp_result() to
return either COND_FALSE in the negated==true case, or COND_TRUE in the
negated==false case which has the effect of not removing the NOT LIKE/LIKE
condition for the query.
check sequence privileges in Item_func_nextval::fix_fields(),
just like column privileges are checked in Item_field::fix_fields()
remove sequence specific hacks that kinda made sequence privilege
checks works, but not in all cases. And they were too lax,
didn't requre SELECT privilege for NEXTVAL. Also INSERT privilege looks
wrong here, UPDATE would've been more appropriate, but won't
change that for compatibility reasons.
also fixes
MDEV-36413 User without any privileges to a sequence can read from it and modify it via column default
Fixing the code adding MySQL _0900_ collations as _uca1400_ aliases
not to perform deep initialization of the corresponding _uca1400_
collations.
Only basic initialization is now performed which allows to watch
these collations (both _0900_ and _uca1400_) in queries to
INFORMATION_SCHEMA tables COLLATIONS and
COLLATION_CHARACTER_SET_APPLICABILITY,
as well as in SHOW COLLATION statements.
Deep initialization is now performed only when a collation
(either the _0900_ alias or the corresponding _uca1400_ collation)
is used for the very first time after the server startup.
Refactoring was done to maintain the code easier:
- most of the _uca1400_ code was moved from ctype-uca.c
to a new file ctype-uca1400.c
- most of the _0900_ code was moved from type-uca.c
to a new file ctype-uca0900.c
Change details:
- The original function add_alias_for_collation() added by the patch for
"MDEV-20912 Add support for utf8mb4_0900_* collations in MariaDB Server"
was removed from mysys/charset.c, as it had two two problems:
a. it forced deep initialization of the _uca1400_ collations
when adding _0900_ aliases for them at the server startup
(the main reported problem)
b. the collation initialization code in add_alias_for_collation()
was related more to collations rather than to memory management,
so /strings should be a better place for it than /mysys.
The code from add_alias_for_collation() was split into separate functions.
Cyclic dependency was removed. `#include <my_sys.h>` was removed
from /strings/ctype-uca.c. Collations are now added using a callback
function MY_CHARSET_LOADED::add_collation, like it is done for
user collations defined in Index.xml. The code in /mysys sets
MY_CHARSET_LOADED::add_collation to add_compiled_collation().
- The function compare_collations() was removed.
A new virtual function was added into my_collation_handler_st instead:
my_bool (*eq_collation)(CHARSET_INFO *self, CHARSET_INFO *other);
because it is the collation handler who knows how to detect equal
collations by comparing only some of CHARSET_INFO members without
their deep initialization.
Three implementations were added:
- my_ci_eq_collation_uca() for UCA collations, it compares
_0900_ collations as equal to their corresponding _uca1400_ collations.
- my_ci_eq_collation_utf8mb4_bin(), it compares
utf8mb4_nopad_bin and utf8mb4_0900_bin as equal.
- my_ci_eq_collation_generic() - the default implementation,
which compares all collations as not equal.
A C++ wrapper CHARSET_INFO::eq_collations() was added.
The code in /sql was changes to use the wrapper instead of
the former calls for the removed function compare_collations().
- A part of add_alias_for_collation() was moved into a new function
my_ci_alloc(). It allocates a memory for a new charset_info_st
instance together with the collation name and the comment using a single
MY_CHARSET_LOADER::once_alloc call, which points to my_once_alloc()
in the server.
- A part of add_alias_for_collation() was moved into a new function
my_ci_make_comment_for_alias(). It makes an "Alias for xxx" string,
e.g. "Alias for utf8mb4_uca1400_swedish_ai_ci" in case of
utf8mb4_sv_0900_ai_ci.
- A part of the code in create_tailoring() was moved to
a new function my_uca1400_collation_get_initialized_shared_uca(),
to reuse the code between _uca1400_ and _0900_ collations.
- A new function my_collation_id_is_mysql_uca0900() was added
in addition to my_collation_id_is_mysql_uca1400().
- Functions to build collation names were added:
my_uca0900_collation_build_name()
my_uca1400_collation_build_name()
- A shared function function was added:
my_bool
my_uca1400_collation_alloc_and_init(MY_CHARSET_LOADER *loader,
LEX_CSTRING name,
LEX_CSTRING comment,
const uca_collation_def_param_t *param,
uint id)
It's reused to add _uca1400_ and _0900_ collations, with basic
initialization (without deep initialization).
- The function add_compiled_collation() changed its return type from
void to int, to make it compatible with MY_CHARSET_LOADER::add_collation.
- Functions mysql_uca0900_collation_definition_add(),
mysql_uca0900_utf8mb4_collation_definitions_add(),
mysql_utf8mb4_0900_bin_add() were added into ctype-uca0900.c.
They get MY_CHARSET_LOADER as a parameter.
- Functions my_uca1400_collation_definition_add(),
my_uca1400_collation_definitions_add() were moved from
charset-def.c to strings/ctype-uca1400.c.
The latter now accepts MY_CHARSET_LOADER as the first parameter
instead of initializing a MY_CHARSET_LOADER inside.
- init_compiled_charsets() now initializes a MY_CHARSET_LOADER
variable and passes it to all functions adding collations:
- mysql_utf8mb4_0900_collation_definitions_add()
- mysql_uca0900_utf8mb4_collation_definitions_add()
- mysql_utf8mb4_0900_bin_add()
- A new structure was added into ctype-uca.h:
typedef struct uca_collation_def_param
{
my_cs_encoding_t cs_id;
uint tailoring_id;
uint nopad_flags;
uint level_flags;
} uca_collation_def_param_t;
It simplifies reusing the code for _uca1400_ and _0900_ collations.
- The definition of MY_UCA1400_COLLATION_DEFINITION was
moved from ctype-uca.c to ctype-uca1400.h, to reuse
the code for _uca1400_ and _0900_ collations.
- The definitions of "MY_UCA_INFO my_uca_v1400" and
"MY_UCA_INFO my_uca1400_info_tailored[][]" were moved from
ctype-uca.c to ctype-uca1400.c.
- The definitions/declarations of:
- mysql_0900_collation_start,
- struct mysql_0900_to_mariadb_1400_mapping
- mysql_0900_to_mariadb_1400_mapping
- mysql_utf8mb4_0900_collation_definitions_add()
were moved from ctype-uca.c to ctype-uca0900.c
- Functions
my_uca1400_make_builtin_collation_id()
my_uca1400_collation_definition_init()
my_uca1400_collation_id_uca400_compat()
my_ci_get_collation_name_uca1400_context()
were moved from ctype-uca.c to ctype-uca1400.c and ctype-uca1400.h
- A part of my_uca1400_collation_definition_init()
was moved into my_uca0520_builtin_collation_by_id(),
to make functions smaller.
The problem is that copy function was used in field list but never
copied in this execution path.
So copy should be performed before returning result.
Protection against uninitialized copy usage added.
