This merges the patch series that fixes a lot of missing validation
checks for read_log_event(). The merge includes a few added fixes that
are needed in new code introduced in 12.3.
Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>
With save_merged false, Item_equal::merge_into_list() merges this
object's Item_equal instances into another, linking *this object's
items into the target. However this allowed callers to free an
item via one list but later read it through the other; a case of
use-after-free.
If save_merged is false then delete the items from the source.
EXCHANGE PARTITION fails with ERROR 1736 (Tables have different definitions)
when tables contain generated columns with AND/OR conditions, even when the
expressions are logically equivalent. This occurs because when expressions are
re-parsed (e.g., via CREATE TABLE ... LIKE), the order of arguments in AND/OR
conditions may change, but the comparison was order-sensitive.
The Item_cond::eq() method was implemented to perform set-based comparison
for commutative AND/OR operations. The set-based comparison algorithm ensures
that two Item_cond expressions are considered equal if they contain the same set
of equivalent arguments, regardless of order.
Added comprehensive test case covering:
- Generated columns with OR conditions
- Generated columns with AND conditions
- Multiple generated columns with different AND/OR combinations
- Nested AND/OR conditions
The fix allows EXCHANGE PARTITION to succeed when expressions are logically
equivalent but have different argument ordering, which is correct behavior
since AND/OR operations are commutative.
MDEV-38757 Limit unordered vcol condition comparison to EXCHANGE PARTITION
Review follow-up to the previous commit, which made Item_cond::eq()
compare AND/OR argument lists as sets for every caller. That changed
equality semantics globally and broke main.derived_cond_pushdown, where
conditions that eq() started reporting as equal were dropped from
attached_condition. Reordering AND/OR operands also changes evaluation
order, which is observable when operands are functions, so the relaxed
comparison must not be the default.
Item::Eq_config gains an unordered_conditions flag, defaulting to false,
next to the existing binary_cmp and omit_table_names flags.
Item_cond::eq() compares its argument lists as sets only when that flag
is set, and otherwise reports two distinct Item_cond objects as unequal,
as it did before this patch series. The flag is threaded through
Virtual_column_info::is_equal() and a new mysql_compare_tables()
parameter, and only Sql_cmd_alter_table_exchange_partition passes it as
true, so the relaxed comparison stays confined to the EXCHANGE PARTITION
metadata check.
The set comparison tests containment in both directions rather than
comparing element counts, so an expression also matches a form that
repeats one of its terms, for example
col1 > 10 and col2 < 100 or col3 > 50
col3 > 50 or col1 > 10 and col2 < 100 or col3 > 50
Test 5 of parts.partition_exchange_generated_columns covers that case.
When equality propagation (build_equal_items()) merges an equality that
contains a subquery, such as "t1.a = (SELECT ...)", with an outer join's
ON equality, it can inject a reference to that subquery into the ON
expression. If the join columns have compatible types the subquery ends
up as the constant of a multiple equality (which Item::walk() skips); if
they differ (e.g. BIGINT vs INT) the field cannot be merged and the
subquery is substituted in as a plain "tbl.col = (SELECT ...)" argument.
In the latter case, if that outer join is removed by table elimination,
mark_as_eliminated() walks the ON expression and flags the shared
Item_subselect as eliminated. The subquery, however, still lives in
another part of the query and has to be executed, tripping
DBUG_ASSERT(!eliminated) in Item_subselect::exec() (and, in release
builds, disabling the subquery cache and hiding it from EXPLAIN).
The surviving reference can be:
- a WHERE/HAVING/select-list/ORDER/GROUP expression (subquery written
there and pushed down into the eliminated ON), or
- the ON expression of an outer join that was not eliminated (subquery
written in a surviving outer ON and pushed down into an eliminated
inner one).
Fix: after table elimination, walk the expressions that survive into
execution (WHERE, HAVING, select list, ORDER/GROUP BY and the ON
expressions of outer joins that were not eliminated) and clear the
"eliminated" flag on any subquery still reachable from them.
Because a subquery can also be the constant of a multiple equality, and
Item::walk() does not visit an Item_equal's constant, Item_equal gets an
unmark_as_eliminated_processor() override that descends into its constant
explicitly.
Assisted by Claude Opus
"NOT column" rewrites itself into "column = 0" in fix_fields().
However the rewrite is not done when the "column" is wrapped in an Item_ref.
Later, pushdown_from_having_into_where() will strip the Item_ref object
and will call
Item_func_not(Item_field())->fix_fields(thd, ref=NULL);
This call will attempt the rewrite and crash when writing into NULL pointer.
Fixed this in two places:
* Make Item_func_not::fix_fields() use real_item() and so recognize fields
wrapped in Item_ref-like objects.
* Make pushdown_from_having_into_where() pass the right pointer as
fix_fields() argument.
Problem:
Queries returning JSON from hybrid functions (`NULLIF`, `IF`, `NVL2`)
crash. This is a regression from MDEV-36716, which added early calls
to `Item_hybrid_func_fix_attributes()` inside
- `Item_func_nullif::fix_length_and_dec()` and
- `Item_func_case_abbreviation2::cache_type_info()`
to block `ROW` arguments.
Standard hybrid functions populate their `m_type_handler` by calling
`aggregate_for_result()` prior to fixing attributes. However, since
`NULLIF` and `IF` deduce their return type from a single argument,
they optimize away the `aggregate_for_result()` step.
This caused `Item_hybrid_func_fix_attributes()` to execute while
`m_type_handler` was still in its uninitialized default state,
triggering a debug assertion failure in the JSON handler.
Fix:
Explicitly set the type handler from the source argument before
calling `Item_hybrid_func_fix_attributes()`.
Rename cloning methods of class Item and its descendants
in the following way:
(from) (to)
do_build_clone -> deep_copy
build_clone -> deep_copy_with_checks
do_get_copy -> shallow_copy
get_copy -> shallow_copy_with_checks
to better reflect their functionality.
Also make Item::deep_copy() and shallow_copy() protected.
Outside users should call deep_copy_with_checks()
and shallow_copy_with_checks().
- 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.
Parser changes made by Alexander Barkov <bar@mariadb.com>.
Part of the tests made by Iqbal Hassan <iqbal@hasprime.com>.
Initially marking with ORA_JOIN flag made also by
Iqbal Hassan <iqbal@hasprime.com>.
Main idea is that parser mark fields with (+) with a flag
(ORA_JOIN).
During Prepare the flag bring to the new created items if needed.
Later after preparing (fix_firlds()) WHERE confition the
relations betweel the tables analyzed and tables reordered
so to make JOIN/LEFT JOIN operators in chain equivalent to
the query with oracle outer join operator (+).
Then the flags of (+) removed.
- Moving the definition of "class Type_handler_row" into a new file
sql_type_row.h. Also moving *some* of its methods into sql_type_row.cc.
The rest of the methods will be moved in the patch for MDEV-34319.
Moving the definition of my_var_sp_row_field into sql_type_row.cc.
- Fixing the grammar for function_call_generic to get the first
production as "ident_cli_func" rather than "ident_func".
The upcoming patch needs to know the position of the function name
within the client query.
- Adding new data types to store data types defined by "TYPE" declarations:
* sp_type_def
* sp_type_def_list
sp_pcontext now derives from sp_type_def_list
- A new virtual method in Field:
virtual Item_field *make_item_field_spvar(THD *thd,
const Spvar_definition &def);
Using it in sp_rcontext::init_var_items().
- Fixing my_var_sp to get sp_rcontext_addr in the parameter
instead of two separate parameters (rcontext_handler + offset).
- Adding new virtual methods in my_var:
virtual bool set_row(THD *thd, List<Item> &select_list);
It's used when a select_list record is assigned to a
single composite variable, such as ROW, specified in the INTO clause.
Using it in select_dumpvar::send_data().
virtual bool check_assignability(THD *thd,
const List<Item> &select_list,
bool *assign_as_row) const;
It's used to check if the select_list is compatible with
a single INTO variable, in select_dumpvar::prepare().
- Fixing LEX methods create_outvar() to get identifiers
a Lex_ident_sys_st values instead of generic LEX_CSTRING values.
- Adding virtual methods in Type_handler:
// Used in Item_func_null_predicate::check_arguments()
virtual bool has_null_predicate() const;
// Used in LEX::sp_variable_declarations_finalize()
virtual bool sp_variable_declarations_finalize(THD *thd,
LEX *lex, int nvars,
const Column_definition &def)
const;
// Handle SELECT 1 INTO spvar;
virtual my_var *make_outvar(THD *thd,
const Lex_ident_sys_st &name,
const sp_rcontext_addr &addr,
sp_head *sphead,
bool validate_only) const;
// Handle SELECT 1 INTO spvar.field;
virtual my_var *make_outvar_field(THD *thd,
const Lex_ident_sys_st &name,
const sp_rcontext_addr &addr,
const Lex_ident_sys_st &field,
sp_head *sphead,
bool validate_only) const;
// create the value in: DECLARE var rec_t DEFAULT rec_t(1,'c');
virtual Item *make_typedef_constructor_item(THD *thd,
const sp_type_def &def,
List<Item> *arg_list) const;
- A new helper method:
Row_definition_list *Row_definition_list::deep_copy(THD *thd) const;
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.
SQL Standard specifies that a <simple case> should return the <result>
from the left-most <simple when clause> where the comparison evaluates
to TRUE.
That is, it should not stop comparing on NULLs
The patch for SYS_REFCURSOR (MDEV-20034) overrode these methods:
- Item_func_case_searched::check_arguments()
- Item_func_if::check_arguments()
to validate WHEN-style arguments (e.g. args[0] in case of IF) for being
able to return a boolean result.
However, this unintentionally removed the test for the THEN-style arguments
that they are not expressions of the ROW data type.
This led to a crash inside Type_handler_hybrid_field_type::aggregate_for_result
on a DBUG_ASSERT that arguments are not of the ROW data type.
Fix:
The fix restores blocking ROW expressions in the not supported cases,
to avoid the DBUG_ASSERT and to raise an SQL error instead.
Blocking ROW_RESULT expressions is done per Item_func_case_expression
descendant individually, instead of blocking any ROW_RESULT arguments
at the Item_func_case_expression level.
The fix is done taking into account the upcoming patch for associative arrays
(MDEV-34319). It should be possible to pass associative array expressions into
some hybrid type functions, where ROW type expressions are not possible.
As a side effect, some lecagy ER_OPERAND_COLUMNS changed to
a newer ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
Changes in the top affected class Item_func_case_expression:
- item_func.h:
Overriding Item_func_case_expression::check_arguments() to return false,
without checking any arguments. Descendant validate arguments
in a various different ways. No needs to block all non-scalar data type at
this level, to prevent disallowing associative arrays.
Changes in descendants:
- item_cmpfunc.cc:
Adding a test in Item_func_case_simple::aggregate_switch_and_when_arguments()
preventing passing ROW_RESULT expression in predicant and WHEN in a
simple CASE:
CASE predicant WHEN when1 THEN .. WHEN when2 THEN .. END;
This is not supported yet. Should be preferrably fixed before MDEV-34319.
- item_cmpfunc.cc:
Calling args[0]->type_handler()->Item_hybrid_func_fix_attributes()
from Item_func_nullif::fix_length_and_dec().
This prevents a ROW expression to be passed to args[0] of NULLIF().
But will allow to pass associative arrays.
args[1] is still only checked to be comparable with args[0].
No needs to add additional tests for it.
- item_cmpfunc.h:
Adding a call for Item_hybrid_func_fix_attributes() in
Item_func_case_abbreviation2::cache_type_info().
This prevents calling the descendant functions with
a ROW expression in combination with an explicit NULL
in the THEN-style arguments (but will allow to pass associative arrays):
IFNULL(row_expression, NULL)
IFNULL(NULL, row_expression)
IF(switch, row_expression, NULL)
IF(switch, NULL, row_expression)
NVL2(switch, row_expression, NULL)
NVL2(switch, NULL, row_expression)
Adding a THD* argument into involved methods.
- item_cmpfunc.h:
Overriding Item_func_case_abbreviation2_switch::check_arguments() to
check that the first argument in IF() and NVL2() can return bool.
Removing Item_func_if::check_arguments(), as it become redundant.
- sql_type.cc:
Fixing sql_type.cc not to disallow items[0] with ROW_RESULT.
This makes it call Item_hybrid_func_fix_attributes() at the end,
which block ROW arguments into THEN-style arguments of hybrid functions.
But this will allow to pass Type_handler_assoc_array expressions.
- sql_type.cc:
Changing Type_handler_row::Item_hybrid_func_fix_attributes to raise the
ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION error instead of the DBUG_ASSERT.
This patch adds support for SYS_REFCURSOR (a weakly typed cursor)
for both sql_mode=ORACLE and sql_mode=DEFAULT.
Works as a regular stored routine variable, parameter and return value:
- can be passed as an IN parameter to stored functions and procedures
- can be passed as an INOUT and OUT parameter to stored procedures
- can be returned from a stored function
Note, strongly typed REF CURSOR will be added separately.
Note, to maintain dependencies easier, some parts of sql_class.h
and item.h were moved to new header files:
- select_results.h:
class select_result_sink
class select_result
class select_result_interceptor
- sp_cursor.h:
class sp_cursor_statistics
class sp_cursor
- sp_rcontext_handler.h
class Sp_rcontext_handler and its descendants
The implementation consists of the following parts:
- A new class sp_cursor_array deriving from Dynamic_array
- A new class Statement_rcontext which contains data shared
between sub-statements of a compound statement.
It has a member m_statement_cursors of the sp_cursor_array data type,
as well as open cursor counter. THD inherits from Statement_rcontext.
- A new data type handler Type_handler_sys_refcursor in plugins/type_cursor/
It is designed to store uint16 references -
positions of the cursor in THD::m_statement_cursors.
- Type_handler_sys_refcursor suppresses some derived numeric features.
When a SYS_REFCURSOR variable is used as an integer an error is raised.
- A new abstract class sp_instr_fetch_cursor. It's needed to share
the common code between "OPEN cur" (for static cursors) and
"OPER cur FOR stmt" (for SYS_REFCURSORs).
- New sp_instr classes:
* sp_instr_copen_by_ref - OPEN sys_ref_curor FOR stmt;
* sp_instr_cfetch_by_ref - FETCH sys_ref_cursor INTO targets;
* sp_instr_cclose_by_ref - CLOSE sys_ref_cursor;
* sp_instr_destruct_variable - to destruct SYS_REFCURSOR variables when
the execution goes out of the BEGIN..END block
where SYS_REFCURSOR variables are declared.
- New methods in LEX:
* sp_open_cursor_for_stmt - handles "OPEN sys_ref_cursor FOR stmt".
* sp_add_instr_fetch_cursor - "FETCH cur INTO targets" for both
static cursors and SYS_REFCURSORs.
* sp_close - handles "CLOSE cur" both for static cursors and SYS_REFCURSORs.
- Changes in cursor functions to handle both static cursors and SYS_REFCURSORs:
* Item_func_cursor_isopen
* Item_func_cursor_found
* Item_func_cursor_notfound
* Item_func_cursor_rowcount
- A new system variable @@max_open_cursors - to limit the number
of cursors (static and SYS_REFCURSORs) opened at the same time.
Its allowed range is [0-65536], with 50 by default.
- A new virtual method Type_handler::can_return_bool() telling
if calling item->val_bool() is allowed for Items of this data type,
or if otherwise the "Illegal parameter for operation" error should be raised
at fix_fields() time.
- New methods in Sp_rcontext_handler:
* get_cursor()
* get_cursor_by_ref()
- A new class Sp_rcontext_handler_statement to handle top level statement
wide cursors which are shared by all substatements.
- A new virtual method expr_event_handler() in classes Item and Field.
It's needed to close (and make available for a new OPEN)
unused THD::m_statement_cursors elements which do not have any references
any more. It can happen in various moments in time, e.g.
* after evaluation parameters of an SQL routine
* after assigning a cursor expression into a SYS_REFCURSOR variable
* when leaving a BEGIN..END block with SYS_REFCURSOR variables
* after setting OUT/INOUT routine actual parameters from formal
parameters.
normalize_cond() translated `WHERE col` into `WHERE col<>0`
But the opetator "not equal to 0" does not necessarily exists
for all data types.
For example, the query:
SELECT * FROM t1 WHERE inet6col;
was translated to:
SELECT * FROM t1 WHERE inet6col<>0;
which further failed with this error:
ERROR : Illegal parameter data types inet6 and bigint for operation '<>'
This patch changes the translation from `col<>0` to `col IS TRUE`.
So now
SELECT * FROM t1 WHERE inet6col;
gets translated to:
SELECT * FROM t1 WHERE inet6col IS TRUE;
Details:
1. Implementing methods:
- Field_longstr::val_bool()
- Field_string::val_bool()
- Item::val_int_from_val_str()
If the input contains bad data,
these methods raise a better error message:
Truncated incorrect BOOLEAN value
Before the change, the error was:
Truncated incorrect DOUBLE value
2. Fixing normalize_cond() to generate Item_func_istrue/Item_func_isfalse
instances instead of Item_func_ne/Item_func_eq
3. Making Item_func_truth sargable, so it uses the range optimizer.
Implementing the following methods:
- get_mm_tree(), get_mm_leaf(), add_key_fields() in Item_func_truth.
- get_func_mm_tree(), for all Item_func_truth descendants.
4. Implementing the method negated_item() for all Item_func_truth
descendants, so the negated item has a chance to be sargable:
For example,
WHERE NOT col IS NOT FALSE -- this notation is not sargable
is now translated to:
WHERE col IS FALSE -- this notation is sargable
MDEV-32329 (patch) pushdown from having into where: Server crashes at sub_select
When generating an Item_equal with a Item_ref that refers to a field
outside of a subselect, remove_item_direct_ref() causes the dependency
(depended_from) on the outer select to be lost, which causes trouble
for code downstream that can no longer determine the scope of the Item.
Not calling remove_item_direct_ref() retains the Item's dependency.
Test cases from MDEV-32395 and MDEV-32329 are included.
Some fixes from other developers:
Monty:
- Fixed wrong code in Item_equal::create_pushable_equalities()
that could cause wrong item to be used if there was no matching items.
Daniel Black:
- Added test cases from MDEV-32329
Igor Babaev:
- Provided fix for removing call to remove_item_direct_ref() in
eliminate_item_equal()
MDEV-32395: update_depend_map_for_order: SEGV at /mariadb-11.3.0/sql/sql_select.cc:16583
Include test cases from MDEV-32329.
