lp:maria

Author: Brandon Nesterenko
Author Date: 2022-07-18 16:12:57 UTC

MDEV-19415 Regression

This patch reproduces MDEV-19415.

Pushing this old commit so it isn't lost on my laptop.

Author: Oleg Smirnov
Author Date: 2023-10-02 05:12:15 UTC

MDEV-28506 Possible item replacement during fix_fields() is not taken into account when pushing conditions from HAVING to WHERE

1. During the 5th stage of pushdown_from_having_into_where() there is
a sequence of cleanup/fix_fields calls for items involved. fix_fields()
may sometimes replace the initial item, which is done, for example, for
Item_func_not. This commits adds the correct handling for such cases.

2. Sometimes fix_fields() calls are made with the 2nd parameter
(Item **ref) set to NULL. This may lead to access violation when the item
is of class Item_func_not or Item_func_sp. This commits add a proper check
before dereferencing **ref.

Author: Sergei Golubchik
Author Date: 2023-09-20 16:28:56 UTC

MDEV-32104 add removed command line options back as noops

Author: Alexander Barkov
Author Date: 2023-09-25 17:48:01 UTC

MDEV-12252 ROW data type for stored function return values

Adding support for the ROW data type in the stored function RETURNS clause:

- explicit ROW(..members...) for both sql_mode=DEFAULT and sql_mode=ORACLE

  CREATE FUNCTION f1() RETURNS ROW(a INT, b VARCHAR(32)) ...

- anchored "ROW TYPE OF [db1.]table1" declarations for sql_mode=DEFAULT

  CREATE FUNCTION f1() RETURNS ROW TYPE OF test.t1 ...

- anchored "[db1.]table1%ROWTYPE" declarations for sql_mode=ORACLE

  CREATE FUNCTION f1() RETURN test.t1%ROWTYPE ...

Adding support for anchored scalar data types in RETURNS clause:

- "TYPE OF [db1.]table1.column1" for sql_mode=DEFAULT

  CREATE FUNCTION f1() RETURNS TYPE OF test.t1.column1;

- "[db1.]table1.column1" for sql_mode=ORACLE

  CREATE FUNCTION f1() RETURN test.t1.column1%TYPE;

Details:

- Adding a new sql_mode_t parameter to
    sp_head::create()
    sp_head::sp_head()
    sp_package::create()
    sp_package::sp_package()
  to guarantee early initialization of sp_head::m_sql_mode.
  Before this change, this member was not initialized at all during
  CREATE FUNCTION/PROCEDURE/PACKAGE statements, and was not used.
  Now it needs to be initialized to write properly the
  mysql.proc.returns column, according to the create time sql_mode.

- Moving the code from the loop body in sp_rcontext::init_var_items()
  into a separate member Spvar_definition::make_item_field_row(),
  to reuse this code for variable/parameter definitions
  and for the RETURNS clause.

- Changing the data type of sp_head::m_return_field_def
  from Column_definition to Spvar_definition.
  So now it supports not only SQL column field types,
  but also explicit ROW and anchored ROW data types.

- Cleanup: adding the const qualifier to Spvar_definition::m_column_type_ref.
  Fixing the following methods accordingly:
  * Qualified_column_ident::resolve_type_ref()
  * LEX::sp_variable_declarations_column_type_finalize()

- Modifying sql_yacc.yy to support the new grammar.
  Adding new helper methods:
    * sf_return_fill_definition_row()
    * sf_return_fill_definition_rowtype_of()
    * sf_return_fill_definition_type_of()

- Fixing tests in:
  * Virtual_tmp_table::setup_field_pointers() in sql_select.cc
  * Send_field::normalize() in field.h
  to prevent calling Type_handler_row::field_type(),
  which is implemented a DBUG_ASSERT(0).
  Before this patch the affected methods were called only
  for scalar data types. Now ROW is also possible.

- Adding a new virtual method Field::cols()

- Overriding methods:
   Item_func_sp::cols()
   Item_func_sp::element_index()
   Item_func_sp::check_cols()
   Item_func_sp::bring_value()
  to support the ROW data type.

- Extending sp_returns_type() to support
  * explicit ROW and anchored ROW data types
  * anchored scalar data types

- Overriding Field_row::sql_type() to print
  the data type of an explicit ROW.

Author: THIRUNARAYANAN BALATHANDAYUTHAPANI
Author Date: 2023-09-28 13:01:07 UTC

MDEV-32017 Auto-increment no longer works for explicit FTS_DOC_ID

- InnoDB increments the next document id whenever InnoDB does
commit the fulltext sync operation. InnoDB should avoid the
sync commit operation when there is nothing in fulltext cache.

Author: Yuchen Pei
Author Date: 2023-09-28 06:16:06 UTC

MDEV-28998 remove a known reference to a SPIDER_CONN when it is freed

Author: Brandon Nesterenko
Author Date: 2023-09-27 20:40:34 UTC

MDEV-32265: seconds_behind_master is inaccurate for Delayed replication

This patch fixes this by separating the negation of
sql_thread_caught_up out of the timestamp-dependent block, so it is
called any time an idle parallel slave queues an event to a worker.

Reviewed By:
============
<TODO>

Author: Vlad Lesin
Author Date: 2023-09-12 19:17:13 UTC

MDEV-25163 Rowid filter does not process storage engine error correctly.

The fix is to return 3-state value from Range_rowid_filter::build()
call:
1. The filter was built successfully;
2. The filter was not built, but the error was not fatal, i.e. there is
no need to rollback transaction. For example, if the size of container to
storevrow ids is not enough;
3. The filter was not built because of fatal error, for example,
deadlock or lock wait timeout from storage engine. In this case we
should stop query plan execution and roll back transaction.

Reviewed by: Sergey Petrunya

Author: Vlad Lesin
Author Date: 2023-09-12 19:17:13 UTC

MDEV-25163 Rowid filter does not process storage engine error correctly.

The fix is to return 3-state value from Range_rowid_filter::build()
call:
1. The filter was built successfully;
2. The filter was not built, but the error was not fatal, i.e. there is
no need to rollback transaction. For example, if the size of container to
storevrow ids is not enough;
3. The filter was not built because of fatal error, for example,
deadlock or lock wait timeout from storage engine. In this case we
should stop query plan execution and roll back transaction.

Reviewed by: Sergey Petrunya

Author: Oleksandr "Sanja" Byelkin
Author Date: 2023-09-27 08:28:44 UTC

MDEV-29180: Description of log_warnings incorrectly mentions "general log"

Author: Yuchen Pei
Author Date: 2023-09-27 03:08:34 UTC

MDEV-32254 Make NULL an illegal value for redirect_url

Author: Yuchen Pei
Author Date: 2023-09-27 03:08:34 UTC

MDEV-32254 Make NULL an illegal value for redirect_url

Author: Oleksandr "Sanja" Byelkin
Author Date: 2023-09-26 17:59:50 UTC

MDEV-29289 uncleaned LIMIT ROWS EXAMINED

Clean thd->lex->limit_rows_examined_cnt at the end of dispatch_command to avoid side effects

Author: Daniel Black
Author Date: 2023-07-25 00:58:45 UTC

MDEV-30520 SELinux/Apparmor incorrectly labeled mariadbd

From 10.4 -> 10.5 renamed a few MariaDB executables.

The default selinux profile expects mariadbd to be installed in
/usr/libexec/mariadbd however our packages install in in /usr/sbin.

Change non-existant named executables in the apparmor and selinux
profiles to use the new names like mariadb-backup, mariadb, mbstream
etc and use the right paths.

Author: =?utf-8?q?Jan_Lindstr=C3=B6m?=
Author Date: 2023-08-15 07:12:34 UTC

MDEV-24912: post-fix for test regression

Problem is that mysql.galera_slave_pos table is replicated,
thus it should be InnoDB to allow rolling back in case
of replay.

Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

Author: Yuchen Pei
Author Date: 2023-09-26 06:34:32 UTC

MDEV-26247 [wip] Make spider_create_gbh go through append_join too

After this, we can get rid of append_from_and_tables().

There are two tricky issues remaining.

1. By the time the query reaches the spider gbh, the optimizer may
have modified the query so that it is no longer valid. See the test
tmppp.test

2. Somehow this change also fails spider/bg.ha and spider.ha because
failing links are no longer detected with the link status updated to
NG, causing spider to use them even though they are unavailable.

Author: Monty
Author Date: 2023-09-20 12:46:55 UTC

MDEV-32203 Raise notes when an index cannot be used on data type mismatch

Raise notes if indexes cannot be used:
- in case of data type or collation mismatch (diferent error messages)
- in case if a table field was replaced to something else
  (e.g. Item_func_conv_charset) during a condition rewrite

Added option to write warnings and notes to the slow query log for
slow queries.

New variables added/changed:

- note_verbosity, with is a set of the following options:
  normal - All old notes
  unusable_keys - Print warnings about keys that cannot be used
                     for select, delete or update.
  explain - Print unusable_keys warnings for EXPLAIN querys

  The default is 'normal,explain'.

- log_slow_verbosity has a new option 'warnings'. If this is set
  then warnings and notes generated are printed in the slow query log
  (up to log_slow_max_warnings times per statement).

- log_slow_max_warnings - Max number of warnings written to
                            slow query log.

Other things:
- One can now use =ALL for any 'set' variable to set all options at once.
  For example using "note_verbosity=ALL" in a config file or
  "SET @@note_verbosity=ALL' in SQL.
- mysqldump will in the future use @@note_verbosity=""' instead of
  @sql_notes=0 to disable notes.
- Added "enum class Data_type_compatibility" and changing the return type
  of all Field::can_optimize*() methods from "bool" to this new data type.

Reviewer & Co-author: Alexander Barkov <bar@mariadb.com>
- The code that prints out the notes comes mainly from Alexander

Author: =?utf-8?q?Jan_Lindstr=C3=B6m?=
Author Date: 2023-04-12 08:26:39 UTC

MDEV-31062 : Reduce number of wsrep calls to server code from InnoDB

Thread executing wsrep transaction can't change during transaction
execution. Similarly, thread executing high priority brute force
(BF) transaction does not change during transaction execution.
Therefore, in both cases there is no need to call server
code after transaction has initialized.

InnoDB already stores information is this wsrep transaction to
trx_t::wsrep and this is checked using trx->is_wsrep() function.
Because, brute force transaction is always a wsrep transaction
we can extend trx_t::wsrep variable so that value

0 == not wsrep transaction (and not BF)
1 == normal wsrep transaction
2 == high priority BF wsrep transaction
4 == high priority BF transaction is performing unique secondary index scan

These values can be set by calling server code on innobase_trx_init().
After that we can use trx_t::is_wsrep() and new function
introduced in this patch trx_t::is_wsrep_BF(). Unique
secondary index scan is determined later but it implies BF,
so this patch removes unnecessary variable trx_t::wsrep_UK_scan.
Patch introduces new functions trx_t::is_wsrep_UK_scan(),
wsrep_begin_UK_scan() and wsrep_end_UK_scan() to handle
unique secondary index scan.

This change reduces number of calls to server code from InnoDB and
reduces code bloat on performance critical stages like acquiring
record locks. Furthermore, it simplifies code on several locations.

This patch also removes unnecessary trx_t::wsrep_event that
was always set to NULL and not used.

Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

Author: THIRUNARAYANAN BALATHANDAYUTHAPANI
Author Date: 2023-09-20 14:15:24 UTC

MDEV-29989 binlog_do_db option breaks versioning table

Problem:
=========
During commit, server calls prepare_commit_versioned to
determine the transaction modified system-versioned data.
Due to binlog_do_db option, we disable the binlog for the
statement. But prepare_commit_versioned() is being
called only when binlog is enabled for the statement.

Fix:
===
prepare_commit_versioned() should happen irrespective
of binlog state. So if the server has any read-write operation
then we should call prepare_commit_versioned().

Author: Yuchen Pei
Author Date: 2023-09-25 06:55:59 UTC

MDEV-32234 Fix missing space in Spider variable descriptions

Author: Yuchen Pei
Author Date: 2023-09-25 01:19:11 UTC

MDEV-31936 Simplify deinit_spider.inc

Spider is part of the server, and there's no need to check the
version.

All spider plugins are uninstalled in clean_up_spider.inc

DROP SERVER IF EXISTS makes things easier

Author: Sergei Golubchik
Author Date: 2023-09-17 08:09:47 UTC

MDEV-3953 Add columns for ROWS_EXAMINED, ROWS_SENT, and ROWS_READ to I_S and processlist

Added ROWS_SENT to information_schema.processlist

This is to have the same information as Percona server (SENT_ROWS)

I also extended the display size for the columns in processlist to ensure
that most results will fit in the display size.

Author: VladislavVaintroub
Author Date: 2023-09-24 09:20:38 UTC

MDEV-25870 followup - some Windows ARM64 improvements

- optimize atomic store64/load64 implementation.
- allow CRC32 optimization. Do not allow pmull yet, as this fails like in
  https://stackoverflow.com/questions/54048837/how-to-perform-polynomial-multiplication-using-arm64

Author: Yuchen Pei
Author Date: 2023-09-22 03:11:12 UTC

MDEV-26247 [demo] address the first pass during gbh creation

Spider GBH does two passes of spider_db_print_item_type() for all
items in the query. The first pass during the GBH creation has
str==NULL, to construct a chain of fields pointing to table alias and
the correct associated ha_spider. The second pass during the
init_scan() walks the query again to translate to the query for remote
tables. See also MDEV-29447.

After re-implementing rewriting the FROM part of the query, we also
have to update the corresponding first pass part that processes FROM.

For the second pass to work, the two passes have to walk the queries
so that the items are in the exact same order. But the simple update
of the first pass in this patch seems to break that order: the test
spider.direct_right_left_join_nullable fails an assertion of the
order.

There are two ways to address this:

1. Just do one pass of walking the item. The complexity should be the
   same, up to a constant
2. Figure out why the order is wrong now, and fix it.

Of the two ideas, 1 is better, because it can significantly simplify
the code by getting rid of the first pass and the different behaviours
of the spider item printing functions depending on the nullness of
str, and it will also make it much easier to debug with ordering
issues, because we don't need to go back and forth between the two
passes, and it will make the code easier to maintain.

Author: Yuchen Pei
Author Date: 2023-09-21 10:58:14 UTC

MDEV-26247 [demo] gbh::init_scan() passes first table holder spider

With the assertion that one of the following is true:

- !str: we are creating a gbh, rather than doing a init_scan
- !use_fields: probably means the spider_db_open_item_field is called
  from outside of gbh, which also implies fields is nonnull
- fields->table_holder->spider == spider: the spider passed to
  spider_db_open_item_field() is the same as the first table_holder
  of fields

Basically this means the spider passed to spider_db_open_item_field is
irrelevant to the item_field during init_scan(), and we should find
another way of recovering the table the item_field belongs to.
Currently it does so by relying on the field chain from fields, which
was constructed during creation of the gbh (first pass). We need to
find another way, because this way is hacky and unstable when making
changes to query rewrite code and it would be better to skip the first
pass.

Author: Nikita Malyavin
Author Date: 2023-09-21 10:25:21 UTC

fix rdb_i_s.cc build

Author: THIRUNARAYANAN BALATHANDAYUTHAPANI
Author Date: 2023-09-20 14:02:07 UTC

MDEV-29989 binlog_do_db option breaks importing sql dumps

Problem:
=========
During commit, InnoDB does apply the buffered bulk insert
operation in innodb_prepare_commit_versioned(). Due to
binlog_do_db option, we disable the binlog for the
statement. But innodb_prepare_commit_versioned() is being
called only when binlog is enabled for the statement.

Fix:
===
innodb_prepare_commit_versioned() should happen irrespective
of binlog state. So if the server has any read-write operation
then we should call prepare_commit_versioned(). This eventually
fixes bulk insert operation.

versioning.trx_id : Added the option --binlog_do_db

Author: Yuchen Pei
Author Date: 2023-09-21 02:59:54 UTC

MDEV-26247 [demo] disabling the spider gbh fixes the issue

Before 10.10, the spider group by handler could be disabled by setting
spider_use_handler to nonzero, but this was removed in MDEV-27256.
That variable and its corresponding table param did a few other things
as well. We could introduce a system variable that disables group by
handler, something like spider_disable_group_by_handler, which can be
a workaround for bugs caused by spider gbh while we try to fix them.

Author: VladislavVaintroub
Author Date: 2023-09-19 16:30:57 UTC

MDEV-32189 Use icu for timezones on windows

Use ICU to work with timezones, to retrieve current timezone name,
abbreviation, and offset from GMT.

Author: THIRUNARAYANAN BALATHANDAYUTHAPANI
Author Date: 2023-09-20 06:29:13 UTC

MDEV-31826 InnoDB may fail to recover after being killed
  in fil_delete_tablespace()

- InnoDB is first setting the fil_space_t::STOPPING flag and
only then writing the FILE_DELETE record. Before writing the redo
log record, InnoDB server has been killed. So there are few pages
which were never written to the file because of STOPPING flag

- InnoDB should write FILE_DELETE redo record before checking for
pending operations.

Author: THIRUNARAYANAN BALATHANDAYUTHAPANI
Author Date: 2023-09-18 12:49:48 UTC

MDEV-30802 Assertion `index->is_btree() || index->is_ibuf()'
   failed in btr_search_guess_on_hash

Problem:
=======
 - There is a race condition between purge and rollback of alter
operation. Alter rollback marks the index as corrupted.
At the same time, purge is working on the same index and leads
to assert failure. This is caused by
commit 7c0b9c6020101f7655c494327babb274d3af6dc3 (MDEV-15250).

Solution:
=======
 - After MDEV-15250, InnoDB logs the operation only at the
end of transaction commit and applies the log in commit phase
and also via dml thread. So there is no need for purge to
work on uncommitted index.

Added the test case to make the assert fail. But we need
to add the following in row_purge_remove_sec_if_poss_leaf()

  if (!index->is_committed()) sleep(5);

Author: Rex Johnston
Author Date: 2023-08-18 03:51:54 UTC

MDEV-31466 Add optional correlation column list for derived tables

extend Mariadb to support column name assignment in derived sytax
(some expression) [as|=] ident [comma separated column name list].

Author: Yuchen Pei
Author Date: 2023-09-13 00:03:28 UTC

MDEV-32157 MDEV-28856 Spider: Tests, documentation, small fixes and cleanups

Removed some redundant hint related string literals from
spd_db_conn.cc

Clean up SPIDER_PARAM_*_[CHAR]LEN[S]

Adding tests covering monitoring_kind=2. What it does is that it reads
from mysql.spider_link_mon_servers with matching db_name, table_name,
link_id, and does not do anything about that...

How monitoring_* can be useful: in the deprecated spider high
availability feature, when one remote fails, spider will try another
remote, which apparently makes use of these table parameters.

A test covering the query_cache_sync table param. Some further tests
on some spider table params.

Wrapper should be case insensitive.

Code documentation on spider priority binary tree.

Add an assertion that static_key_cardinality is always -1. All tests
pass still

Author: Yuchen Pei
Author Date: 2023-09-13 00:03:28 UTC

MDEV-32157 MDEV-28856 Spider: Tests, documentation, small fixes and cleanups

Removed some redundant hint related string literals from
spd_db_conn.cc

Clean up SPIDER_PARAM_*_[CHAR]LEN[S]

Adding tests covering monitoring_kind=2. What it does is that it reads
from mysql.spider_link_mon_servers with matching db_name, table_name,
link_id, and does not do anything about that...

How monitoring_* can be useful: in the deprecated spider high
availability feature, when one remote fails, spider will try another
remote, which apparently makes use of these table parameters.

A test covering the query_cache_sync table param. Some further tests
on some spider table params.

Wrapper should be case insensitive.

Code documentation on spider priority binary tree.

Add an assertion that static_key_cardinality is always -1. All tests
pass still

Author: Yuchen Pei
Author Date: 2023-09-13 00:03:28 UTC

MDEV-32157 MDEV-28856 Spider: Tests, documentation, small fixes and cleanups

Removed some redundant hint related string literals from
spd_db_conn.cc

Clean up SPIDER_PARAM_*_[CHAR]LEN[S]

Adding tests covering monitoring_kind=2. What it does is that it reads
from mysql.spider_link_mon_servers with matching db_name, table_name,
link_id, and does not do anything about that...

How monitoring_* can be useful: in the deprecated spider high
availability feature, when one remote fails, spider will try another
remote, which apparently makes use of these table parameters.

A test covering the query_cache_sync table param. Some further tests
on some spider table params.

Wrapper should be case insensitive.

Code documentation on spider priority binary tree.

Add an assertion that static_key_cardinality is always -1. All tests
pass still

Author: Yuchen Pei
Author Date: 2023-09-13 00:03:28 UTC

MDEV-32157 MDEV-28856 Spider: Tests, documentation, small fixes and cleanups

Removed some redundant hint related string literals from
spd_db_conn.cc

Clean up SPIDER_PARAM_*_[CHAR]LEN[S]

Adding tests covering monitoring_kind=2. What it does is that it reads
from mysql.spider_link_mon_servers with matching db_name, table_name,
link_id, and does not do anything about that...

How monitoring_* can be useful: in the deprecated spider high
availability feature, when one remote fails, spider will try another
remote, which apparently makes use of these table parameters.

A test covering the query_cache_sync table param. Some further tests
on some spider table params.

Wrapper should be case insensitive.

Code documentation on spider priority binary tree.

Add an assertion that static_key_cardinality is always -1. All tests
pass still

Author: Yuchen Pei
Author Date: 2023-09-15 03:26:22 UTC

MDEV-32157 MDEV-28856 Spider: Tests, documentation, small fixes and cleanups

Removed some redundant hint related string literals from
spd_db_conn.cc

Clean up SPIDER_PARAM_*_[CHAR]LEN[S]

Adding tests covering monitoring_kind=2. What it does is that it reads
from mysql.spider_link_mon_servers with matching db_name, table_name,
link_id, and does not do anything about that...

How monitoring_* can be useful: in the deprecated spider high
availability feature, when one remote fails, spider will try another
remote, which apparently makes use of these table parameters.

A test covering the query_cache_sync table param. Some further tests
on some spider table params.

Wrapper should be case insensitive.

Code documentation on spider priority binary tree.

Add an assertion that static_key_cardinality is always -1. All tests
pass still

Author: Yuchen Pei
Author Date: 2023-09-15 03:26:22 UTC

MDEV-32157 MDEV-28856 Spider: Tests, documentation, small fixes and cleanups

Removed some redundant hint related string literals from
spd_db_conn.cc

Clean up SPIDER_PARAM_*_[CHAR]LEN[S]

Adding tests covering monitoring_kind=2. What it does is that it reads
from mysql.spider_link_mon_servers with matching db_name, table_name,
link_id, and does not do anything about that...

How monitoring_* can be useful: in the deprecated spider high
availability feature, when one remote fails, spider will try another
remote, which apparently makes use of these table parameters.

A test covering the query_cache_sync table param. Some further tests
on some spider table params.

Wrapper should be case insensitive.

Code documentation on spider priority binary tree.

Add an assertion that static_key_cardinality is always -1. All tests
pass still

Author: Yuchen Pei
Author Date: 2023-09-14 04:52:43 UTC

MDEV-31673 MDEV-29502 Remove spider_db_handler::need_lock_before_set_sql_for_exec

This function trivially returns false

Author: Yuchen Pei
Author Date: 2023-09-12 01:57:07 UTC

MDEV-22534 Fix self-referencing Item_direct_view_ref

The loop is created in substitute_for_best_equal_field().

It calls Item_ref::transform (for the Item_direct_view_ref object)
which calls:

  Item *new_item= (*ref)->transform(thd, transformer, arg); // TRANSFORM-CALL

where transformer is Item::replace_equal_field().

Consider *vr1->ref==field, where vr1 is an Item_direct_view_ref, and
field is an Item_field that erroneously participates in a multiple
equality. If field->replace_equal_field() returns vr2, another
Item_direct_view_ref, with vr2->ref == vr1->ref. vr1->transform()
will, after the call on field->transform() that returns vr2 for
replacement, update its ref to point to vr2, so now *vr1->ref==vr2,
and since vr2->ref==vr1->ref, this gives us *vr2->ref == vr2, a self
reference.

This can be generalised to nested Item_direct_view_refs too, if
field->transform() returns an Item_direct_view_ref that has the same
ref field as the ref field of any Item_direct_view_ref in the chain.

On to the fix. If we maintain the rule that

  Objects inside Item_direct_view_ref do not participate in multiple
  equalities.

then the TRANSFORM-CALL will have new_item == *ref. That is, the
transform will be a no-op and no loop will be created.

The participation of multiple equalities is set during call to
Item::propagate_equal_fields(). This commit fixes
Item_ref::propagate_equal_fields() so that the rule is not violated
when a (non-Item_direct_view_ref) Item_ref points to an
Item_direct_view_ref:

Item_ref->Item_ref->...->Item_direct_view_ref->Item_direct_view_ref->...->Item_field

More specifically, we now delegate to the dereferenced
item (i.e. *ref) in such calls, rather than directly to the underlying
Item_field. By doing this, the call to propagate_equal_fields() on
toplevel Item_direct_view_ref will make it participate in the multiple
equality, instead of any of its downstream items.

Author: midenok
Author Date: 2023-09-11 17:52:37 UTC

TMP: innodb_fts.f for testing doc_id

Author: Sergey Petrunia
Author Date: 2023-09-14 12:29:47 UTC

MDEV-32113: utf8mb3_key_col=utf8mb4_value cannot be used for ref

Attempt of Implementation of Variant #1.

When the optimizer sees a condition in one of forms:

  CONVERT(tbl.mb3col USING utf8mb4_general_ci) = tbl2.mb4col
  CONVERT(tbl.mb3col USING utf8mb4_general_ci) = mb4expr

it will rewrite the first variant into

  CONVERT(mb3col USING utf8mb4_general_ci)=mb4col // the original cond
  AND
  mb3col= CONVERT_NARROW(mb4col USING utf8mb3_general_ci)

the second variant is just rewritten into:

  mb3col = CONVERT_NARROW(mb4expr)

Here CONVERT_NARROW is a special variant of Item_func_conv_charset that
converts UTF8MB4 into MB3 by replacing all non-BMP character with the
MY_CS_REPLACEMENT_CHARACTER (It is not supported by the parser. One
can only see it EXPLAIN or Optimizer trace).

Unlike Approach #1, We only needed to support narrowing conversion in
one place: class String_copier. It uses my_convert_fix() which now
accepts an extra argument: a custom decoder (my_charset_conv_mb_wc)
function.

The rewrite can inject CONVERT_NARROW() in addition to CONVERT(). In
order to avoid extra CPU overhead at run time, added a concept of
"equivalent extra items". An item can be marked as "equivalent extra
item". Plan generation functions in make_cond_for_table() will not
attach such items to any part of the query plan. They are assumed to
be guaranteed to be true.

Item::is_equivalent_extra()

Author: Yuchen Pei
Author Date: 2023-07-27 04:55:50 UTC

MDEV-31787 MDEV-26151 Add a test exercising non-0 spider_casual_read

Also:
- clean up spider_check_and_get_casual_read_conn() and
  spider_check_and_set_autocommit()
- remove a couple of commented out code blocks

Author: Yuchen Pei
Author Date: 2023-09-14 07:22:18 UTC

MDEV-31787 MDEV-26151 Add a test exercising non-0 spider_casual_read

Also:
- clean up spider_check_and_get_casual_read_conn() and
  spider_check_and_set_autocommit()
- remove a couple of commented out code blocks

Author: Yuchen Pei
Author Date: 2023-09-14 07:22:18 UTC

MDEV-31787 MDEV-26151 Add a test exercising non-0 spider_casual_read

Also:
- clean up spider_check_and_get_casual_read_conn() and
  spider_check_and_set_autocommit()
- remove a couple of commented out code blocks

Author: Yuchen Pei
Author Date: 2023-09-14 07:22:18 UTC

MDEV-31787 MDEV-26151 Add a test exercising non-0 spider_casual_read

Also:
- clean up spider_check_and_get_casual_read_conn() and
  spider_check_and_set_autocommit()
- remove a couple of commented out code blocks

Author: Yuchen Pei
Author Date: 2023-09-14 07:22:18 UTC

MDEV-31787 MDEV-26151 Add a test exercising non-0 spider_casual_read

Also:
- clean up spider_check_and_get_casual_read_conn() and
  spider_check_and_set_autocommit()
- remove a couple of commented out code blocks

Author: Yuchen Pei
Author Date: 2023-09-14 06:16:55 UTC

MDEV-29502 Fix some issues with spider direct aggregate

The direct aggregate mechanism sems to be only intended to work when
otherwise a full table scan query will be executed from the spider
node and the aggregation done at the spider node too. Typically this
happens in sub_select(). In the test spider.direct_aggregate_part
direct aggregate allows to send COUNT statements directly to the data
nodes and adds up the results at the spider node, instead of iterating
over the rows one by one at the spider node.

By contrast, the group by handler (GBH) typically sends aggregated
queries directly to data nodes, in which case DA does not improve the
situation here.

That is why we should fix it by disabling DA when GBH is used.

There are other reasons supporting this change. First, the creation of
GBH results in a call to change_to_use_tmp_fields() (as opposed to
setup_copy_fields()) which causes the spider DA function
spider_db_fetch_for_item_sum_funcs() to work on wrong items. Second,
the spider DA function only calls direct_add() on the items, and the
follow-up add() needs to be called by the sql layer code. In
do_select(), after executing the query with the GBH, it seems that the
required add() would not necessarily be called.

Disabling DA when GBH is used does fix the bug. There are a few
other things included in this commit to improve the situation with
spider DA:

1. Add a session variable that allows user to disable DA completely,
this will help as a temporary measure if/when further bugs with DA
emerge.

2. Move the increment of direct_aggregate_count to the spider DA
function. Currently this is done in rather bizarre and random
locations.

3. Fix the spider_db_mbase_row creation so that the last of its row
field (sentinel) is NULL. The code is already doing a null check, but
somehow the sentinel field is on an invalid address, causing the
segfaults. With a correct implementation of the row creation, we can
avoid such segfaults.

Author: Yuchen Pei
Author Date: 2023-09-14 06:16:23 UTC

MDEV-29502 Fix some issues with spider direct aggregate

The direct aggregate mechanism sems to be only intended to work when
otherwise a full table scan query will be executed from the spider
node and the aggregation done at the spider node too. Typically this
happens in sub_select(). In the test spider.direct_aggregate_part
direct aggregate allows to send COUNT statements directly to the data
nodes and adds up the results at the spider node, instead of iterating
over the rows one by one at the spider node.

By contrast, the group by handler (GBH) typically sends aggregated
queries directly to data nodes, in which case DA does not improve the
situation here.

That is why we should fix it by disabling DA when GBH is used.

There are other reasons supporting this change. First, the creation of
GBH results in a call to change_to_use_tmp_fields() (as opposed to
setup_copy_fields()) which causes the spider DA function
spider_db_fetch_for_item_sum_funcs() to work on wrong items. Second,
the spider DA function only calls direct_add() on the items, and the
follow-up add() needs to be called by the sql layer code. In
do_select(), after executing the query with the GBH, it seems that the
required add() would not necessarily be called.

Disabling DA when GBH is used does fix the bug. There are a few
other things included in this commit to improve the situation with
spider DA:

1. Add a session variable that allows user to disable DA completely,
this will help as a temporary measure if/when further bugs with DA
emerge.

2. Move the increment of direct_aggregate_count to the spider DA
function. Currently this is done in rather bizarre and random
locations.

3. Fix the spider_db_mbase_row creation so that the last of its row
field (sentinel) is NULL. The code is already doing a null check, but
somehow the sentinel field is on an invalid address, causing the
segfaults. With a correct implementation of the row creation, we can
avoid such segfaults.

Author: Yuchen Pei
Author Date: 2023-09-14 05:19:50 UTC

MDEV-29502 Fix some issues with spider direct aggregate

The direct aggregate mechanism sems to be only intended to work when
otherwise a full table scan query will be executed from the spider
node and the aggregation done at the spider node too. Typically this
happens in sub_select(). In the test spider.direct_aggregate_part
direct aggregate allows to send COUNT statements directly to the data
nodes and adds up the results at the spider node, instead of iterating
over the rows one by one at the spider node.

By contrast, the group by handler (GBH) typically sends aggregated
queries directly to data nodes, in which case DA does not improve the
situation here.

That is why we should fix it by disabling DA when GBH is used.

There are other reasons supporting this change. First, the creation of
GBH results in a call to change_to_use_tmp_fields() (as opposed to
setup_copy_fields()) which causes the spider DA function
spider_db_fetch_for_item_sum_funcs() to work on wrong items. Second,
the spider DA function only calls direct_add() on the items, and the
follow-up add() needs to be called by the sql layer code. In
do_select(), after executing the query with the GBH, it seems that the
required add() would not necessarily be called.

Disabling DA when GBH is used does fix the bug. There are a few
other things included in this commit to improve the situation with
spider DA:

1. Add a session variable that allows user to disable DA completely,
this will help as a temporary measure if/when further bugs with DA
emerge.

2. Move the increment of direct_aggregate_count to the spider DA
function. Currently this is done in rather bizarre and random
locations.

3. Fix the spider_db_mbase_row creation so that the last of its row
field (sentinel) is NULL. The code is already doing a null check, but
somehow the sentinel field is on an invalid address, causing the
segfaults. With a correct implementation of the row creation, we can
avoid such segfaults.

Author: Yuchen Pei
Author Date: 2023-09-14 05:19:50 UTC

MDEV-29502 Fix some issues with spider direct aggregate

The direct aggregate mechanism sems to be only intended to work when
otherwise a full table scan query will be executed from the spider
node and the aggregation done at the spider node too. Typically this
happens in sub_select(). In the test spider.direct_aggregate_part
direct aggregate allows to send COUNT statements directly to the data
nodes and adds up the results at the spider node, instead of iterating
over the rows one by one at the spider node.

By contrast, the group by handler (GBH) typically sends aggregated
queries directly to data nodes, in which case DA does not improve the
situation here.

That is why we should fix it by disabling DA when GBH is used.

There are other reasons supporting this change. First, the creation of
GBH results in a call to change_to_use_tmp_fields() (as opposed to
setup_copy_fields()) which causes the spider DA function
spider_db_fetch_for_item_sum_funcs() to work on wrong items. Second,
the spider DA function only calls direct_add() on the items, and the
follow-up add() needs to be called by the sql layer code. In
do_select(), after executing the query with the GBH, it seems that the
required add() would not necessarily be called.

Disabling DA when GBH is used does fix the bug. There are a few
other things included in this commit to improve the situation with
spider DA:

1. Add a session variable that allows user to disable DA completely,
this will help as a temporary measure if/when further bugs with DA
emerge.

2. Move the increment of direct_aggregate_count to the spider DA
function. Currently this is done in rather bizarre and random
locations.

3. Fix the spider_db_mbase_row creation so that the last of its row
field (sentinel) is NULL. The code is already doing a null check, but
somehow the sentinel field is on an invalid address, causing the
segfaults. With a correct implementation of the row creation, we can
avoid such segfaults.

Author: Yuchen Pei
Author Date: 2023-09-14 05:19:50 UTC

MDEV-29502 Fix some issues with spider direct aggregate

The direct aggregate mechanism sems to be only intended to work when
otherwise a full table scan query will be executed from the spider
node and the aggregation done at the spider node too. Typically this
happens in sub_select(). In the test spider.direct_aggregate_part
direct aggregate allows to send COUNT statements directly to the data
nodes and adds up the results at the spider node, instead of iterating
over the rows one by one at the spider node.

By contrast, the group by handler (GBH) typically sends aggregated
queries directly to data nodes, in which case DA does not improve the
situation here.

That is why we should fix it by disabling DA when GBH is used.

There are other reasons supporting this change. First, the creation of
GBH results in a call to change_to_use_tmp_fields() (as opposed to
setup_copy_fields()) which causes the spider DA function
spider_db_fetch_for_item_sum_funcs() to work on wrong items. Second,
the spider DA function only calls direct_add() on the items, and the
follow-up add() needs to be called by the sql layer code. In
do_select(), after executing the query with the GBH, it seems that the
required add() would not necessarily be called.

Disabling DA when GBH is used does fix the bug. There are a few
other things included in this commit to improve the situation with
spider DA:

1. Add a session variable that allows user to disable DA completely,
this will help as a temporary measure if/when further bugs with DA
emerge.

2. Move the increment of direct_aggregate_count to the spider DA
function. Currently this is done in rather bizarre and random
locations.

3. Fix the spider_db_mbase_row creation so that the last of its row
field (sentinel) is NULL. The code is already doing a null check, but
somehow the sentinel field is on an invalid address, causing the
segfaults. With a correct implementation of the row creation, we can
avoid such segfaults.

Author: Yuchen Pei
Author Date: 2023-09-14 05:19:50 UTC

MDEV-29502 Fix some issues with spider direct aggregate

The direct aggregate mechanism sems to be only intended to work when
otherwise a full table scan query will be executed from the spider
node and the aggregation done at the spider node too. Typically this
happens in sub_select(). In the test spider.direct_aggregate_part
direct aggregate allows to send COUNT statements directly to the data
nodes and adds up the results at the spider node, instead of iterating
over the rows one by one at the spider node.

By contrast, the group by handler (GBH) typically sends aggregated
queries directly to data nodes, in which case DA does not improve the
situation here.

That is why we should fix it by disabling DA when GBH is used.

There are other reasons supporting this change. First, the creation of
GBH results in a call to change_to_use_tmp_fields() (as opposed to
setup_copy_fields()) which causes the spider DA function
spider_db_fetch_for_item_sum_funcs() to work on wrong items. Second,
the spider DA function only calls direct_add() on the items, and the
follow-up add() needs to be called by the sql layer code. In
do_select(), after executing the query with the GBH, it seems that the
required add() would not necessarily be called.

Disabling DA when GBH is used does fix the bug. There are a few
other things included in this commit to improve the situation with
spider DA:

1. Add a session variable that allows user to disable DA completely,
this will help as a temporary measure if/when further bugs with DA
emerge.

2. Move the increment of direct_aggregate_count to the spider DA
function. Currently this is done in rather bizarre and random
locations.

3. Fix the spider_db_mbase_row creation so that the last of its row
field (sentinel) is NULL. The code is already doing a null check, but
somehow the sentinel field is on an invalid address, causing the
segfaults. With a correct implementation of the row creation, we can
avoid such segfaults.

Author: Yuchen Pei
Author Date: 2023-09-14 01:41:34 UTC

MDEV-27438 [testing] add an abort() to xarecover_do_commit_or_rollback

We don't have a reproducible testcase, so let's add an abort() to
find out which test reaches the path, so that we can attempt to create
a testcase...

Author: Yuchen Pei
Author Date: 2023-09-13 05:08:05 UTC

MDEV-32046 [testing] a no-op change to kick CI for failure reprod

Author: Yuchen Pei
Author Date: 2023-09-13 01:49:57 UTC

MDEV-15935 Adding global/session system var redirect_url

Adding a global/session var `redirect_url' of string type. The initial
value is empty. Can be supplied in mysqld with --redirect-url or set
in --init-connect. A valid redirect_url should be of the format

{mysql,mariadb}://host[:port]

where <host> is an arbitrary string not containing colons, and <port>
is a number between 0 and 65535 inclusive.

The variable will be used by the server to notify clients that they
should connect to another server, specified by the value of the
variable, if not empty.

The notification is done by the inclusion of the variable in
session_track_system_variable.

Author: Yuchen Pei
Author Date: 2023-09-12 07:22:57 UTC

MDEV-28856 Add remaining Spider table options

MDEV-27106 added REMOTE_TABLE, REMOTE_DATABASE, REMOTE_SERVER spider
table options. In this commit, we add all remaining options for table
params that are not marked to be deprecated.

All these options are parsed as strings from sql statements and have
string values at the sql level, so that we can determine whether it is
specified by checking its nullness.

The string values are further parsed by Spider into their actual types
in the SPIDER_SHARE, including string list, bounded nonnegative int,
bounded nonnegative int list, nonnegative longlong, boolean, and key
hints. Except for string lists, all other types are validated during
this parsing process.

Most of the options are backward compatible, i.e. they accept any
values that is accepted by there corresponding param parser. The only
exception is the index hint IDX which corresponds to the idxNNN param
name. For example,

'idx000 "f PRIMARY", idx001 "u k1"'

translates to

IDX="f PRIMARY u k1".

We include a test with all options specified, and tests involving
spider table options of all actual types.

Any table options, if present, will cause comments to be ignored with
a warning. The warning can be disabled by setting a new spider
global/session system variable spider_suppress_comment_ignored_warning
to 1.

Another global/session variable introduced is spider_ignore_comments,
which if set to 1, will cause COMMENT and CONNECTION strings to be
ignored unconditionally, whether or not table options are specified.

Author: Sergey Petrunia
Author Date: 2023-06-19 14:53:16 UTC

MDEV-31496: Make optimizer handle UCASE(varchar_col)=...

(Review input addressed)
(Added handling of UPDATE/DELETE and partitioning w/o index)

If the properties of the used collation allow, do the following
equivalent rewrites:

1. UPPER(key_col)=expr -> key_col=expr
   expr=UPPER(key_col) -> expr=key_col
   (also rewrite both sides of the equality at the same time)

2. UPPER(key_col) IN (constant-list) -> key_col IN (constant-list)

- Mark utf8mb{3,4}_general_ci as collations that allow this.
- Add optimizer_switch='sargable_casefold=ON' to control this.
  (ON by default in this patch)
- Cover the rewrite in Optimizer Trace, rewrite name is
  "sargable_casefold_removal".

Author: =?utf-8?q?Jan_Lindstr=C3=B6m?=
Author Date: 2023-09-12 11:27:48 UTC

MDEV-29775 : Assertion `0' failed in void Protocol::end_statement() when adding data to the MyISAM table after setting wsrep_mode=replicate_myisam

Firstly, variables wsrep_forced_binlog_format, wsrep_replicate_myisam
should not be able to set if wsrep_on=OFF.

Secondly, if wsrep_replicate_myisam=ON we allow wsrep_forced_binlog_format
to be [DEFAULT|ROW].

Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

Author: Marko Mäkelä
Author Date: 2023-09-12 09:26:28 UTC

MDEV-32068 Some calls to buf_read_ahead_linear() seem to be useless

The linear read-ahead (enabled by nonzero innodb_read_ahead_threshold)
works best if index leaf pages or undo log pages have been allocated
on adjacent page numbers. The read-ahead is assumed not to be helpful
in other types of page accesses, such as non-leaf index pages.

buf_page_get_low(): Do not invoke buf_page_t::set_accessed(),
buf_page_make_young_if_needed(), or buf_read_ahead_linear().
We will invoke them in those callers of buf_page_get_gen() or
buf_page_get() where it makes sense: the access is not
one-time-on-startup and the page and not going to be freed soon.

btr_copy_blob_prefix(), btr_pcur_move_to_next_page(),
trx_undo_get_prev_rec_from_prev_page(),
trx_undo_get_first_rec(), btr_cur_t::search_leaf(),
btr_cur_t::open_leaf(): Invoke buf_read_ahead_linear().

We will not invoke linear read-ahead in functions that would
essentially allocate or free pages, because allocated pages
should be reinitialized by buf_page_create(), eliminating the
need to read anything from the file system. Likewise, freeing
pages should not involve accessing any sibling pages, except
for freeing singly-linked lists of BLOB pages.

We will not invoke read-ahead in btr_cur_t::pessimistic_search_leaf()
or in a pessimistic operation of btr_cur_t::open_leaf(), because
it is assumed that pessimistic operations should be preceded by
optimistic operations, which should already have invoked read-ahead.

Author: Sergey Petrunia
Author Date: 2023-09-11 17:09:21 UTC

MDEV-32113: utf8mb3_key_col=utf8mb4_value cannot be used for ref

Attempt of Implementation of Variant #2.

* When calling Item_func_eq::fix_fields() for an object representing a
comparison in form "utf8_mb3_col = utf8_mb4_col", do not wrap the mb3_col
with CONVERT( ... USING utfmb4_general_ci).

* Set the comparison to use the MB4 collation, so it "just works" as
before.

* When the equality is used to create a lookup value for MB3 column,
perform special conversion by my_mb_wc_utf8mb4_bmp_only() to
my_convert_fix()

There are multiple places where conversion is done:
1. CharsetNarrowing#1: class store_key_field. Uses Field::do_field_string.
2. CharsetNarrowing#2: class store_key_item, copy_inner()
3. CharsetNarrowing#3: class store_key_item_const
1. CharsetNarrowing#4: in Field_str::get_mm_leaf()

Author: Yuchen Pei
Author Date: 2023-09-12 08:06:40 UTC

MDEV-28856 Spider: Tests, documentation, small fixes and cleanups

Removed some redundant hint related string literals from
spd_db_conn.cc

Clean up SPIDER_PARAM_*_[CHAR]LEN[S]

Adding tests covering monitoring_kind=2. What it does is that it reads
from mysql.spider_link_mon_servers with matching db_name, table_name,
link_id, and does not do anything about that...

How monitoring_* can be useful: in the deprecated spider high
availability feature, when one remote fails, spider will try another
remote, which apparently makes use of these table parameters.

A test covering the query_cache_sync table param. Some further tests
on some spider table params.

Wrapper should be case insensitive.

Code documentation on spider priority binary tree.

Add an assertion that static_key_cardinality is always -1. All tests
pass still

Author: Yuchen Pei
Author Date: 2023-09-12 07:22:57 UTC

MDEV-28856 Add remaining Spider table options

MDEV-27106 added REMOTE_TABLE, REMOTE_DATABASE, REMOTE_SERVER spider
table options. In this commit, we add all remaining options for table
params that are not marked to be deprecated.

All these options are parsed as strings from sql statements and have
string values at the sql level, so that we can determine whether it is
specified by checking its nullness.

The string values are further parsed by Spider into their actual types
in the SPIDER_SHARE, including string list, bounded nonnegative int,
bounded nonnegative int list, nonnegative longlong, boolean, and key
hints. Except for string lists, all other types are validated during
this parsing process.

Most of the options are backward compatible, i.e. they accept any
values that is accepted by there corresponding param parser. The only
exception is the index hint IDX which corresponds to the idxNNN param
name. For example,

'idx000 "f PRIMARY", idx001 "u k1"'

translates to

IDX="f PRIMARY u k1".

We include a test with all options specified, and tests involving
spider table options of all actual types.

Any table options, if present, will cause comments to be ignored with
a warning. The warning can be disabled by setting a new spider
global/session system variable spider_suppress_comment_ignored_warning
to 1.

Another global/session variable introduced is spider_ignore_comments,
which if set to 1, will cause COMMENT and CONNECTION strings to be
ignored unconditionally, whether or not table options are specified.

Author: Yuchen Pei
Author Date: 2023-09-12 06:53:15 UTC

MDEV-15935 Adding global/session system var redirect_url

Adding a global/session var `redirect_url' of string type. The initial
value is empty. Can be supplied in mysqld with --redirect-url or set
in --init-connect. A valid redirect_url should be of the format

{mysql,mariadb}://host[:port]

where <host> is an arbitrary string not containing colons, and <port>
is a number between 0 and 65535 inclusive.

The variable will be used by the server to notify clients that they
should connect to another server, specified by the value of the
variable, if not empty.

The notification is done by the inclusion of the variable in
session_track_system_variable.

Author: Yuchen Pei
Author Date: 2023-09-12 01:57:07 UTC

MDEV-22534 Fix self-referencing Item_direct_view_ref

The loop is created in substitute_for_best_equal_field().

It calls Item_ref::transform (for the Item_direct_view_ref object)
which calls:

  Item *new_item= (*ref)->transform(thd, transformer, arg); // TRANSFORM-CALL

where transformer is Item::replace_equal_field().

Consider *vr1->ref==field, where vr1 is an Item_direct_view_ref, and
field is an Item_field that erroneously participates in a multiple
equality. If field->replace_equal_field() returns vr2, another
Item_direct_view_ref, with vr2->ref == vr1->ref. vr1->transform()
will, after the call on field->transform() that returns vr2 for
replacement, update its ref to point to vr2, so now *vr1->ref==vr2,
and since vr2->ref==vr1->ref, this gives us *vr2->ref == vr2, a self
reference.

This can be generalised to nested Item_direct_view_refs too, if
field->transform() returns an Item_direct_view_ref that has the same
ref field as the ref field of any Item_direct_view_ref in the chain.

On to the fix. If we maintain the rule that

  Objects inside Item_direct_view_ref do not participate in multiple
  equalities.

then the TRANSFORM-CALL will have new_item == *ref. That is, the
transform will be a no-op and no loop will be created.

The participation of multiple equalities is set during call to
Item::propagate_equal_fields(). This commit fixes
Item_ref::propagate_equal_fields() so that the rule is not violated
when a (non-Item_direct_view_ref) Item_ref points to an
Item_direct_view_ref:

Item_ref->Item_ref->...->Item_direct_view_ref->Item_direct_view_ref->...->Item_field

More specifically, we now delegate to the dereferenced
item (i.e. *ref) in such calls, rather than directly to the underlying
Item_field. By doing this, the call to propagate_equal_fields() on
toplevel Item_direct_view_ref will make it participate in the multiple
equality, instead of any of its downstream items.

Author: Oleg Smirnov
Author Date: 2023-09-04 06:15:06 UTC

MDEV-27277 Add a warning when max_sort_length is reached

During a query execution some sorting and grouping operations
on strings may be involved. System variable max_sort_length defines
the maximum number of bytes to use when comparing strings during
sorting/grouping. Thus, the comparable parts of strings may be less
than their actual size, so the results of the query may be not
sorted/grouped properly.
To indicate that some comparisons were done on a truncated lengths,
a new warning has been introduced with this commit.

Author: Lena Startseva
Author Date: 2023-09-11 14:03:39 UTC

Added a new option for MTR:
  ps-protocol-switch=# Set type of "--ps-protocol":
                          0 - prepare statement with "SELECT" query is executed
                              twice and result of 2nd execution is compared
                              with verification file (use by default)
                          1 - all prepare statements are executed only one
                              time

Author: Yuchen Pei
Author Date: 2023-08-22 02:28:55 UTC

MDEV-22534 Fix self-referencing Item_direct_view_ref

When the Item_ref points to another Item_ref, delegate the
propagate_equal_fields to the latter.

This fixes the inconsitency in equal_field of the underlying
Item_field, when an Item_ref points to a Item_direct_view_ref, as the
latter has its own propagate_equal_fields() that may "move" the
equal_field of the Item_field to itself.

Author: Yuchen Pei
Author Date: 2023-09-11 00:45:05 UTC

MDEV-22534 [DEMO] Swapping the order in Item_cond_and causes infinite item_ref loop

Author: Rex Johnston
Author Date: 2023-09-10 23:51:45 UTC

MDEV-31983 jointable materialization subquery optimization ignoring errors, then failing ASSERT.

Add in thread error check after get_quick_record_count() checking for
errors in constructing SEL_TREE.

Author: Yuchen Pei
Author Date: 2023-09-11 00:45:05 UTC

MDEV-22534 [DEMO] Swapping the order in Item_cond_and causes infinite item_ref loop

Author: Daniel Black
Author Date: 2023-09-08 23:28:56 UTC

MDEV-16641: renable mysql_client_test* tests

Author: Yuchen Pei
Author Date: 2023-09-08 06:39:22 UTC

MDEV-22534 [DEMO] Unpatching propagate_equal_fields to reprod self-referencing item_ref

Author: Daniel Black
Author Date: 2023-09-08 05:48:43 UTC

MDEV-16641: renable mysql_client_test* tests

See if buildbot still fails on these.

Author: Yuchen Pei
Author Date: 2023-09-08 05:20:01 UTC

MDEV-22534 Decorrelate IN subquery

Transform

in (select inner_col' from inner_table where inner_col = outer_col)

to

, outer_col in (select inner_col', inner_col from inner_table)

Achieved by implementing Item_in_subselect::exists2in_processor(),
accompanied with comprehensive test coverage. Factored out common code
between the two transformations.

Also avoid inconsistent Item_ref::propagate_equal_fields

When the Item_ref points to another Item_ref, delegate the
propagate_equal_fields to the latter.

This fixes the inconsitency in equal_field of the underlying
Item_field, when an Item_ref points to a Item_direct_view_ref, as the
latter has its own propagate_equal_fields() that may "move" the
equal_field of the Item_field to itself. It will prevent infinite
recursions like in MDEV-3881, when the Item_ref::transform() called
with the replace_equal_field transformer depends on the correct
Item_field of the underlying field.

Also include a workaround for MDEV-31269: skip exists2in
transformations if we are in a ps execution and at least one of the
chosen equalities contains an item of type ITEM_REF on either side.

Caveat:

- Cannot recognise bad item mismatch in equalities that causes
  materialization to not materialize down the road

Author: Vlad Lesin
Author Date: 2023-09-07 14:47:40 UTC

MDEV-24035 Failing assertion: UT_LIST_GET_LEN(lock.trx_locks) == 0 causing disruption and replication failure

This commit is for custom build.

The transaction, which holds implicit record lock, must also hold IX
table lock.

Author: Alexander Barkov
Author Date: 2023-09-07 03:33:26 UTC

A followup for MDEV-31431 support sql standard <explicit table> expressions

Make `ANALYZE TABLE t1` parse the `TABLE t1` part through
the `explicit_table` table rule.

Still in progress: some statements crash with prepared statements.

Author: Georg Richter
Author Date: 2023-09-07 06:21:29 UTC

MDEV-31994:

Added additional verification option for secure connections by checking the
client certificate fingerprint. The fingerprint option implies REQUIRE X509
and REQUIRE SSL.

The fingerprint has to be passed as SHA224, SHA256, SHA384 or SHA512
hexadecimal hash.

Example:
CREATE USER tlsuser@localhost REQUIRE
FINGERPRINT X'82E26FC8E2B1ACE188EE27DD1FCB98A78B1F1390B10F3788497561D1';

Author: Rex Johnston
Author Date: 2023-09-05 22:55:23 UTC

MDEV-31431 support sql standard <explicit table> expressions

Support SQL 2016 syntax <explicit table> ::= TABLE <table or query name>
Simplified version banning "analyze table _tablename_"
from being interpreted as "analyze (table _tablename_)"

Author: Sergey Petrunia
Author Date: 2023-09-05 11:32:51 UTC

SergeiP Fixup for:

commit 3eeab3c4508d26fa850d68434bbc00853ac8c380
Author: Yuchen Pei <ycp@mariadb.com>
Date: Tue Aug 22 12:28:55 2023 +1000

(more details pending)

Author: Marko Mäkelä
Author Date: 2023-09-05 10:03:36 UTC

MDEV-32096 Parallel replication lags because innobase_kill_query() may fail to interrupt a lock wait

lock_sys_t::cancel(trx_t*): Remove, and merge to its only caller
innobase_kill_query().

innobase_kill_query(): Before reading trx->lock.wait_lock,
do acquire lock_sys.wait_mutex, like we did before
commit e71e6133535da8d5eab86e504f0b116a03680780 (MDEV-24671).
In this way, we should not miss a recently started lock wait
by the killee transaction.

lock_wait(): Invoke trx_is_interrupted() before entering the wait,
in case innobase_kill_query() was invoked some time earlier and
some longer-running operation did not check for interrupts.
Avoid some redundant loads of trx->lock.wait_lock; cache the value
in the local variable wait_lock.

trx_t::error_state: Correctly document the data member.

trx_lock_t::was_chosen_as_deadlock_victim: Clarify that other threads
may set the field (or flags in it) while holding lock_sys.wait_mutex.

Thanks to Johannes Baumgarten for reporting the problem and testing
the fix, as well as to Kristian Nielsen for suggesting the fix.

Author: Rex Johnston
Author Date: 2023-09-04 19:28:00 UTC

MDEV-32080 Blank table alias weirdness

Simple check to make sure table aliases have length != 0

Author: Marko Mäkelä
Author Date: 2023-09-01 08:44:35 UTC

WIP MDEV-32067 InnoDB linear read ahead had better be logical

The linear read-ahead (enabled by nonzero innodb_read_ahead_threshold)
only works if index leaf pages or undo log pages have been allocated
on adjacent page numbers. That is not always the case. We will introduce
a logical read-ahead for accessing leaf-level B-tree pages. It is
enabled by default by setting innodb_read_ahead_threshold>0.

buf_page_get_low(): Do not invoke buf_page_t::set_accessed(),
buf_page_make_young_if_needed(), or buf_read_ahead_linear().
We will invoke them in those callers of buf_page_get_gen() or
buf_page_get() where it makes sense: the access is not
one-time-on-startup and the page and not going to be freed soon.

btr_copy_blob_prefix(), btr_pcur_move_to_next_page(),
trx_undo_get_prev_rec_from_prev_page(),
trx_undo_get_first_rec(): Invoke buf_read_ahead_linear().

We will not invoke linear read-ahead in functions that would
essentially allocate or free pages, because allocated pages
should be reinitialized by buf_page_create(), eliminating the
need to read anything from the file system. Likewise, freeing
pages should not involve accessing any sibling pages, except
for freeing singly-linked lists of BLOB pages.

buf_read_ahead_logical(): A new function, currently invoked in
btr_cur_t::search_leaf() and when opening the leftmost leaf page
in btr_cur_t::open_leaf().

We will not invoke buf_read_ahead_logical() in
btr_cur_t::pessimistic_search_leaf() or in a pessimistic operation
of btr_cur_t::open_leaf(), because it is assumed that pessimistic
operations should be preceded by optimistic operations, which should
already have invoked read-ahead.

TODO: In btr_cur_t::search_leaf(), instead of reading ahead up all
leaf pages from the key onwards (up to children[16] pages), try to
limit the read-ahead to the range of interest, by referring to the
end_key that is passed to handler::read_range_first().

Author: midenok
Author Date: 2023-08-30 13:52:52 UTC

Review

Needless MDEV-31042_3 as it tests only --list-files
FIXME: converge MDEV-31042_1 and MDEV-31042_2

Author: Yuchen Pei
Author Date: 2023-08-30 00:26:04 UTC

MDEV-28856 [demo] static table options vs dynamic spider attributes

Author: Alexander Barkov
Author Date: 2023-08-29 07:51:01 UTC

MDEV-31303 Key not used when IN clause has both signed and usigned values

Summary:

This patch enables possible index optimization when
the WHERE clause has an IN condition of the form:

signed_or_unsigned_column IN (signed_or_unsigned_constant,
                              signed_or_unsigned_constant
                              [,signed_or_unsigned_constant]*)

when the IN list constants are of different signess, e.g.:
  WHERE signed_column IN (signed_constant, unsigned_constant ...)
  WHERE unsigned_column IN (signed_constant, unsigned_constant ...)

Details:

In a condition like:
   WHERE unsigned_predicant IN (1, LONGLONG_MAX + 1)

comparison handlers for individual (predicant,value) pairs are
calculated as follows:

* unsigned_predicant and 1 produce &type_handler_newdecimal
* unsigned_predicant and (LONGLONG_MAX + 1) produce &type_handler_slonglong

The old code decided that it could not use bisection because
the two pairs had different comparison handlers.
As a result, bisection was not allowed, and, in case of
an indexed integer column predicant the index on the column was not used.

The new code catches special cases like:
    signed_predicant IN (signed_constant, unsigned_constant)
    unsigned_predicant IN (signed_constant, unsigned_constant)

It enables bisection using in_longlong, which supports a mixture
of predicant and values of different signess.
In case when the predicant is an indexed column this change
automatically enables index range optimization.

Thanks to Vicențiu Ciorbaru for proposing the idea and for preparing MTR tests.

Author: Brandon Nesterenko
Author Date: 2023-08-28 18:15:56 UTC

MDEV-31177: Review Round 2

Test change:
- Switched save variables from MTR to user

Author: Yuchen Pei
Author Date: 2023-08-28 05:34:06 UTC

MDEV-28856 [demo] We cannot determine if any options have been specified

...because of how options associated with sysvars work. They are added
to option_list when the sysvar value is non-default.

This makes it impossible to determine whether the an option comes from
a SET statement (session level), or specified for the table (table
level). Given we are delaying deprecating the comment parsing to after
MDEV-28856, the only solution is to let comment parsing override
option values.

Consider this case:

set session spider_read_only_mode = 1;
create table t1 (c int) ENGINE=Spider COMMENT='read_only_mode "0", WRAPPER "mysql", srv "srv",TABLE "t2"';

The correct thing to do is to use the table level value 0 specified in
the comment. However, during the create table query, there no way to
tell whether option_struct->read_only (== 1) is set with a

`SET SESSION spider_read_only_mode = 1`

or as a create table option

SPIDER_READ_ONLY=1

And the only way to guarantee this works is to let comment parsing
override table options.

Author: THIRUNARAYANAN BALATHANDAYUTHAPANI
Author Date: 2023-08-25 07:42:49 UTC

MDEV-32008 auto_increment value on table increments by one after restart

- Update the current auto increment value rather than
next auto increment value.

Author: Rex Johnston
Author Date: 2023-08-24 03:29:15 UTC

MDEV-31431 support sql standard <explicit table> expressions

Initial support for sql standard <explicit table> expressions
The <explicit table>
    TABLE <table or query name>
    is equivalent to the <query specification>
    SELECT * FROM <table or query name>
Deal with the conflict caused by ANALYZE TABLE t1; being both
a request for table statistics and analyzing an SQL statement.

Author: Sergey Petrunia
Author Date: 2023-06-19 14:53:16 UTC

MDEV-31496: Make optimizer handle UCASE(varchar_col)=...

(Review input addressed)
(added handling of UPDATE/DELETE and partitioning w/o index)
If the properties of the used collation allow, do the following
equivalent rewrites:

1. UPPER(key_col)=expr -> key_col=expr
   expr=UPPER(key_col) -> expr=key_col
   (also rewrite both sides of the equality at the same time)

2. UPPER(key_col) IN (constant-list) -> key_col IN (constant-list)

- Mark utf8mb{3,4}_general_ci as collations that allow this.
- Add optimizer_switch='sargable_casefold=ON' to control this.
  (OFF by default in this patch)
- Cover the rewrite in Optimizer Trace, rewrite name is
  "sargable_casefold_removal".

Author: Oleg Smirnov
Author Date: 2023-08-23 05:40:32 UTC

MDEV-27201 Refactoring and cleanup

Extract function mark_column_if_needed() from find_field_in_table_ref().
Fix Windows build errors

Author: Sergey Petrunia
Author Date: 2023-08-23 09:40:17 UTC

MDEV-31975: UCASE(varchar_col)=... not handled for partition tables

Enable sargable casefolding rewrite when the column is a part of
partitioning expression.

Author: Yuchen Pei
Author Date: 2023-08-23 02:43:34 UTC

MDEV-28856 [demo] static_key_cardinality is always -1

All tests pass still

Author: Yuchen Pei
Author Date: 2023-07-05 07:55:30 UTC

MDEV-31117 Fix spider connection info parsing

Spider connection string is a comma-separated parameter definitions,
where each definition is of the form "<param_title> <param_value>",
where <param_value> is quote delimited on both ends, with backslashes
acting as an escaping prefix.

Despite the simple syntax, the existing spider connection string
parser was poorly-written, complex, hard to reason and error-prone,
causing issues like the one described in MDEV-31117. For example it
treated param title the same way as param value when assigning, and
have nonsensical fields like delim_title_len and delim_title.

Thus as part of the bugfix, we clean up the spider comment connection
string parsing, including:

- Factoring out some code from the parsing function
- Simplify the struct `st_spider_param_string_parse`
- And any necessary changes caused by the above changes

Author: Yuchen Pei
Author Date: 2023-07-05 07:55:30 UTC

MDEV-31117 Fix spider connection info parsing

Spider connection string is a comma-separated parameter definitions,
where each definition is of the form "<param_title> <param_value>",
where <param_value> is quote delimited on both ends, with backslashes
acting as an escaping prefix.

Despite the simple syntax, the existing spider connection string
parser was poorly-written, complex, hard to reason and error-prone,
causing issues like the one described in MDEV-31117. For example it
treated param title the same way as param value when assigning, and
have nonsensical fields like delim_title_len and delim_title.

Thus as part of the bugfix, we clean up the spider comment connection
string parsing, including:

- Factoring out some code from the parsing function
- Simplify the struct `st_spider_param_string_parse`
- And any necessary changes caused by the above changes

Author: Yuchen Pei
Author Date: 2023-07-05 07:55:30 UTC

MDEV-31117 Fix spider connection info parsing

Spider connection string is a comma-separated parameter definitions,
where each definition is of the form "<param_title> <param_value>",
where <param_value> is quote delimited on both ends, with backslashes
acting as an escaping prefix.

Despite the simple syntax, the existing spider connection string
parser was poorly-written, complex, hard to reason and error-prone,
causing issues like the one described in MDEV-31117. For example it
treated param title the same way as param value when assigning, and
have nonsensical fields like delim_title_len and delim_title.

Thus as part of the bugfix, we clean up the spider comment connection
string parsing, including:

- Factoring out some code from the parsing function
- Simplify the struct `st_spider_param_string_parse`
- And any necessary changes caused by the above changes

Author: Yuchen Pei
Author Date: 2023-07-05 07:55:30 UTC

MDEV-31117 Fix spider connection info parsing

Spider connection string is a comma-separated parameter definitions,
where each definition is of the form "<param_title> <param_value>",
where <param_value> is quote delimited on both ends, with backslashes
acting as an escaping prefix.

Despite the simple syntax, the existing spider connection string
parser was poorly-written, complex, hard to reason and error-prone,
causing issues like the one described in MDEV-31117. For example it
treated param title the same way as param value when assigning, and
have nonsensical fields like delim_title_len and delim_title.

Thus as part of the bugfix, we clean up the spider comment connection
string parsing, including:

- Factoring out some code from the parsing function
- Simplify the struct `st_spider_param_string_parse`
- And any necessary changes caused by the above changes

Author: Yuchen Pei
Author Date: 2023-07-05 07:55:30 UTC

MDEV-31117 Fix spider connection info parsing

Spider connection string is a comma-separated parameter definitions,
where each definition is of the form "<param_title> <param_value>",
where <param_value> is quote delimited on both ends, with backslashes
acting as an escaping prefix.

Despite the simple syntax, the existing spider connection string
parser was poorly-written, complex, hard to reason and error-prone,
causing issues like the one described in MDEV-31117. For example it
treated param title the same way as param value when assigning, and
have nonsensical fields like delim_title_len and delim_title.

Thus as part of the bugfix, we clean up the spider comment connection
string parsing, including:

- Factoring out some code from the parsing function
- Simplify the struct `st_spider_param_string_parse`
- And any necessary changes caused by the above changes

Author: Yuchen Pei
Author Date: 2023-08-23 01:48:14 UTC

[after merge change] MDEV-31117 Fix spider connection info parsing