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.
Partial commit of the greater MDEV-34348 scope.
MDEV-34348: MariaDB is violating clang-16 -Wcast-function-type-strict
The functions queue_compare, qsort2_cmp, and qsort_cmp2
all had similar interfaces, and were used interchangable
and unsafely cast to one another.
This patch consolidates the functions all into the
qsort_cmp2 interface.
Reviewed By:
============
Marko Mäkelä <marko.makela@mariadb.com>
create templates
thd->alloc<X>(n) to use instead of (X*)thd->alloc(sizeof(X)*n)
and the same for thd->calloc(). By the default the type is char,
so old usage of thd->alloc(size) works too.
