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>
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 11.8.
Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>
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>
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.
Fixes unnecessary filesort on derived tables when ordered/grouped by a
field in the key. The data is inherently sorted, wrapping the result set
in a filesort is redundant.
One can have data loss in multi-master setups when 1) both masters
update the same table, 2) ALTER TABLE is run on one master which
re-arranges the column ordering, and 3) transactions are binlogged
in ROW binlog_format.
This is because the slave assumes that all columns are in the same
order on the master and slave and all columns on the master also
exists on the slave. This happens even if binlog_row_metadata=FULL is
used. If this is not the case, this will lead to silent data loss.
A new option for slave_type_conversions bit field,
ERROR_IF_MISSING_FIELD, has been added, along with a new error,
ER_SLAVE_INCOMPATIBLE_TABLE_DEF. This allows the user to define if
the slave should abort replication if it is missing some field that
existed on the master. The option is off by default to keep things
compatible with earlier versions.
If a field is missing on the slave and log_warnings >= 1, a warning
will be logged to the error log.
This patch fixes this, when binlog_row_metadata=FULL is used on the
master, by mapping fields with identical names on the master and slave.
If slave has fields that does not exist in the row event, these will
be set to their default value.
The main idea is that we added two conversion tables:
m_tabledef.master_to_slave_map[master_column_index] -> slave_column_index
and m_tabledef.master_to_slave_error[master_column_index] which contains
an error number if the master_column does not exist on the slave or
it is not possible to convert the master data to the slave column.
master_to_slave_error[#] contains 0 if the column exists and is compatible.
General code changes:
- Instead of looping over row fields in the order of slave table
we are instead looping over fields in the order of the binary log.
- We are using table->write_set to know which fields should be updated
on the slave. This is reflected in unpack_row
- We are calling TABLE::mark_columns_per_binlog_row_image() to ensure
that rpl_write_set is properly set. This is needed if the slave also
is doing binary logging.
- Before replication aborted if the master and slave tables were too
different. Now replication is only aborted if the row actually uses
columns that does not exists on the slave (and ERROR_IF_MISSING_FIELD
is used) or uses columns that cannot be converted.
- Instead of giving errors in compatible_with(), used when table is
accessed by first the row event, we are instead giving errors
when we examine a row event and notice that it is accessing
a not existing or not compatible field.
Other code changes:
- Removed conv_table argument from compatible_with() and store it
directly in RPL_TABLE_LIST->m_conv_table
- table_def::compatible_with() returns now 1 on error (not 0).
- Remove m_width and skip arguments from prepare_record() as we are
now using table->write_set() to check which elements need a default
value.
- Moved DBUG_ENTER() to it's proper place (after variable
declarations) in a few functions.
- Some changes in unpack_row():
- Replaced null_mask and null_ptr with an indexed bit check for
simplicity.
- Removed check of rgi == null and table_found which never worked.
- Updated comments to reflect current code.
- Indentation changes as the code now uses 'continue' instead of
'if-else' in the main loop.
- The code to throw away 'extra master fields' is not needed as we
are now looping over fields in binary log, not over fields in
slave table.
- Simplified get_table_data(TABLE *table_arg) by returning found
table_list.
- Errors for row events are now initialized in compatible_with(),
checked in check_wrong_column_usage() and reported in
give_compatibility_error().
Co-authored-by: Brandon Nesterenko <brandon.nesterenko@mariadb.com>
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.
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.
record per key statistics on keys generated for derived tables are missing,
causing a fallback to the usage of MATCHING_ROWS_IN_OTHER_TABLE in
best_access_path().
This can grossly overestimate the number of rows expected during a join
and cause a bad join order. This patch tries to infer a few simple
things about the derived table, like when the key being created matches
the group by list inside (if any) inside the derived table.
Approved by Sergei Petrunia (sergey@mariadb.com)
Tested by Lena Startseva (lena.startseva@mariadb.com)
- 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.
This commit implements optimizer hints allowing to affect the order
of joining tables:
- JOIN_FIXED_ORDER similar to existing STRAIGHT_JOIN hint;
- JOIN_ORDER to apply the specified table order;
- JOIN_PREFIX to hint what tables should be first in the join;
- JOIN_SUFFIX to hint what tables should be last in the join.
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.
(Variant 3) (commit in 11.4)
When a derived table has a GROUP BY clause:
SELECT ...
FROM (SELECT ... GROUP BY col1, col2) AS tbl
The optimizer would use inner join's output cardinality as an estimate
of derived table size, ignoring the fact that GROUP BY operation would
produce much fewer groups.
Add code to produce tighter bounds:
- The GROUP BY list is split into per-table lists. If GROUP BY list has
expressions that refer to multiple tables, we fall back to join output
cardinality.
- For each table, the first cardinality estimate is join_tab->read_records.
- Then, we try to get a tighter bound by using index statistics.
- If indexes do not cover all GROUP BY columns, we try to use per-column
EITS statistics.
(Review input addressed)
After this patch, the optimizer can handle virtual column expressions
in WHERE/ON clauses. If the table has an indexed virtual column:
ALTER TABLE t1
ADD COLUMN vcol INT AS (col1+1),
ADD INDEX idx1(vcol);
and the query uses the exact virtual column expression:
SELECT * FROM t1 WHERE col1+1 <= 100
then the optimizer will be able use index idx1 for it.
This is achieved by walking the WHERE/ON clauses and replacing instances
of virtual column expression (like "col1+1" above) with virtual column's
Item_field (like "vcol"). The latter can be processed by the optimizer.
Replacement is considered (and done) only in items that are potentially
usable to the range optimizer.
The problem was caused by this scenario: The query had both SELECT DISTINCT
and ORDER BY. DISTINCT was converted into GROUP BY. Then, vector index was
used to resolve the GROUP BY.
When join_read_first() initialized vector index scan, it used the ORDER BY
clause instead of GROUP BY, which caused a crash.
Fixed by making test_if_skip_sort_order() remember which ordering the scan
produces in JOIN_TAB::full_index_scan_order, and join_read_first() using that.
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>
