Changing the code handling sql_mode-dependent function DECODE():

- removing parser tokens DECODE_MARIADB_SYM and DECODE_ORACLE_SYM
- removing the DECODE() related code from sql_yacc.yy/sql_yacc_ora.yy
- adding handling of DECODE() with help of a new Create_func_func_decode

If a replica is actively delaying a transaction when restarted (STOP
SLAVE/START SLAVE), when the sql thread is back up,
Seconds_Behind_Master will present as 0 until the configured
MASTER_DELAY has passed. That is, before the restart,
last_master_timestamp is updated to the timestamp of the delayed
event. Then after the restart, the negation of sql_thread_caught_up
is skipped because the timestamp of the event has already been used
for the last_master_timestamp, and their update is grouped together
in the same conditional block.

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.

Note that sql_thread_caught_up is still left in the check for internal
events, as SBM should remain idle in such case to not "magically" begin
incrementing.

Reviewed By:
============
Andrei Elkin <andrei.elkin@mariadb.com>

it did nothing for 14 years

New mtr option --skip-not-found makes it to show not found tests
as skipped

main.a                                   [ skipped ]  not found

(but only if the test was specified with the suite name)
and not error out early with

mysql-test-run: *** ERROR: Could not find 'a' in 'main' suite

This is useful in buildbot, on builders that generate the list
of tests dynamically.

MDEV-32541 Assertion `!(thd->server_status & (1U | 8192U))' failed in MDL_context::release_transactional_locks

SERVER_STATUS_IN_TRANS_READONLY should never be set without
SERVER_STATUS_IN_TRANS.

They're set together, must be removed together.

standard table KEY_COLUMN_USAGE should only show keys where
a user has some privileges on every column of the key

standard table TABLE_CONSTRAINTS should show tables where
a user has any non-SELECT privilege on the table or on any column
of the table

standard table REFERENTIAL_CONSTRAINTS is defined in terms of
TABLE_CONSTRAINTS, so the same rule applies. If the user
has no rights to see the REFERENCED_TABLE_NAME value, it should be NULL

SHOW INDEX (and STATISTICS table) is non-standard, but it seems
reasonable to use the same logic as for KEY_COLUMN_USAGE.

MDEV-31792 Assertion fails in MDL_context::acquire_lock upon parallel replication of CREATE SEQUENCE

The assert's reason was in missed FL_DDL flagging of CREATE-or-REPLACE
Query event.
MDEV-27365 fixes covered only the non-pre-existing table execution branch so
did not see a possibility of implicit commit in
the middle of execution in a rollback branch when the being CREATEd
sequence table is actually replaced.
The pre-existing table branch cleared the DDL modification
flag so the query lost FL_DDL in binlog and its parallel execution
on slave may have ended up with the assert to indicate the query
is raced by a following in binlog order event.

Fixed with applying the MDEV-27365 pattern.
An mtr test is added to cover the rollback situation.
The description test [ pass ] with a generous number of mtr parallel
reties.

Tested multiple major version upgrades with sql_safe_updates enabled, and
confirmed the issue is resolved.

Reviewer: Daniel Black

Backporting a part of MDEV-32026 (which also fixed MDEV-32025 in 11.3)
from 11.3 to 10.4.

The reported crash happened with --lower-case-table-names=2
on statements like:

ALTER DATABASE Db1 DEFAULT CHARACTER SET utf8;
ALTER DATABASE `#mysql50#D+b1` UPGRADE DATA DIRECTORY NAME;

lock_schema_name() expects a normalized database name
and assert if a non-normalized name comes.

mysql_alter_db_internal() and mysql_upgrade_db() get
a non-normalized database name in the parameter.
Fixing them to normalize the database name before passing
it to lock_schema_name().

Problem was that total order isolation (TOI) is started before
we know sequence implementing storage engine. This led to
situation where table implementing persistent storate
for sequence in case of MyISAM was created on applier causing
errors later in test execution.

Therefore, in both CREATE SEQUENCE and ALTER TABLE to implementing
persistent storage we need to check implementing storage engine
after open_tables and this check must be done in both master
and applier, because if implementing storage engine is MyISAM
it does not support rollback.

Added tests to make sure that if sequence implementing storage
engine is MyISAM or we try to alter it to MyISAM user gets error
and changes are not replicated.
Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

Problem:
========
InnoDB fails to find the foreign key index for the
foreign key relation in the table while iterating the
foreign key constraints during alter operation. This is
caused by commit 5f09b53b
(MDEV-31086).

Fix:
====
In check_col_is_in_fk_indexes(), while iterating through
the foreign key relationship, InnoDB should consider that
foreign key relation may not have foreign index when
foreign key check is disabled.

In MDEV-31086, SET FOREIGN_KEY_CHECKS=0 cannot bypass checks that
make column types of foreign keys incompatible. An unfortunate
consequence is that adding an AUTO_INCREMENT is considered
incompatible in Field_{num,decimal}::is_equal and for the purpose
of FK checks this isn't relevant.

innodb.foreign_key - pragmaticly left wait_until_count_sessions.inc at
end of test to match the second line of test.

Reporter: horrockss@github - https://github.com/MariaDB/mariadb-docker/issues/528
Co-Author: Marko Mäkelä <marko.makela@mariadb.com>
Reviewer: Nikita Malyavin

For the future reader this was attempted:

Removing AUTO_INCREMENT checks from Field_{num,decimal}::is_equals
failed in the following locations (noted for future fixing):
* MyISAM and Aria (not InnoDB) don't adjust AUTO_INCREMENT next number
  correctly, hence added a test to main.auto_increment to catch
  the next person that attempts this fix.
* InnoDB must perform an ALGORITHM=COPY to populate NULL values of
  an original table (MDEV-19190 mtr test period.copy), this requires
  ALTER_STORED_COLUMN_TYPE to be set in fill_alter_inplace_info
  which doesn't get hit because field->is_equal is true.
* InnoDB must not perform the change inplace (below patch)
* innodb.innodb-alter-timestamp main.partition_innodb test would
  also need futher investigation.

InnoDB ha_innobase::check_if_supported_inplace_alter to support the
removal of Field_{num,decimal}::is_equal AUTO_INCREMENT checks would need the following change

diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index a5ccb1957f3..9d778e2d39a 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -2455,10 +2455,15 @@ ha_innobase::check_if_supported_inplace_alter(
                        /* An AUTO_INCREMENT attribute can only
                        be added to an existing column by ALGORITHM=COPY,
                        but we can remove the attribute. */
-                       ut_ad((MTYP_TYPENR((*af)->unireg_check)
-                              != Field::NEXT_NUMBER)
-                             || (MTYP_TYPENR(f->unireg_check)
-                                 == Field::NEXT_NUMBER));
+                       if ((MTYP_TYPENR((*af)->unireg_check)
+                              == Field::NEXT_NUMBER)
+                             && (MTYP_TYPENR(f->unireg_check)
+                                 != Field::NEXT_NUMBER))
+                       {
+                               ha_alter_info->unsupported_reason = my_get_err_msg(
+                                       ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_AUTOINC);
+                               DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED);
+                       }

With this change the main.auto_increment test for bug #14573, under
innodb, will pass without the 2 --error ER_DUP_ENTRY entries.

The function header comment was updated to reflect the MDEV-31086
changes.

10.6+ already unmasked.
Tested on 10.4 and 10.5 and passes on all 3 tests now occur with
particular attention to the causing MDEVs:

- MDEV-19511 Big endian issue in libmariadb
  -  has been resolved;
- CONC-208 Client tests (main.mysql_client_test, main.mysql_client_test_nonblock, main.mysql_client_test_comp) fail in buildbot on Power
  - observed passing in 10.5
- MDEV-19369 main.mysql_client_test, main.mysql_client_test_comp failed on ASAN build with error: 5888, status: 23, errno: 2
  - passing on ASAN builder

Some calls of extend_space() perl function miss the last parameter. Some
cases in the test check corrupted pages info absence in the output
file if the table is dropped. We should also test if the
corruption presents before testing its absence.

Reviewed by: Vladislav Vaintroub

While cleaning up a failed CREATE TABLE LIKE <sequence>, `mysql_rm_table_no_locks`
erroneously attempted to remove all tables involved in the query, including
the source table (sequence).

Fix to temporarily modify `table_list` to ensure that only the intended
table is removed during the cleanup.

Clean up after main.log_slow_debug.

Without pam compiled there will be no auth_pam_tool_dir, so check this
before attempting something that will error.

Reviewer: Sergei Golubchik / Daniel Black

In the hexlo function, the element type of the array hex_lo_digit is not
explicitly declared as signed char, causing elements with a value of -1
to be converted to 255 on Arm64. The problem occurs because "char" is
unsigned by default on Arm64 compiler, but signed on x86 compiler. This
problem can be seen in https://godbolt.org/z/rT775xshj

The above issue causes "use-after-poison" exception in my_mb_wc_filename
function. The code snippet where the error occurred is shown below,
copied from below link.
https://github.com/MariaDB/server/blob/5fc19e71375fb39eb85354321bf852d998aecf81/strings/ctype-utf8.c#L2728

2728    if ((byte1= hexlo(byte1)) >= 0 &&
2729     (byte2= hexlo(byte2)) >= 0)
  	{
2731    	int byte3= hexlo(s[3]);
    		…
  	}

At line 2729, when byte2 is 0, which indicates the end of the string s.
(1) On x86, hexlo(0) return -1 and line 2731 is skipped, as expected.
(2) On Arm64, hexlo(0) return 255 and line 2731 is executed, not as
expected, accessing s[3] after the null character of string s, thus
raising the "user-after-poison" error.

The problem was discovered when executing the main.mysqlcheck test.
Signed-off-by: Xiaotong Niu <xiaotong.niu@arm.com>

fil_page_compress_low returns 0 for both innodb_compression_algorithm=0
and where there is compression errors. On the two callers to this
function, don't increment the compression errors if the algorithm was
none.

Reviewed by: Marko Mäkelä

The memory allocated for an instance of the class Item_direct_ref_to_item
was leaked on second execution of a query run as a prepared statement and
involving conversion of strings with different character sets.

The reason of leaking the memory was caused by the fact that a statement
arena could be already set by the moment the method
Type_std_attributes::agg_item_set_converter() is called.

MDEV-24283 Assertion `bitmap_is_set(&m_part_info->read_partitions, m_part_spec.start_part)' failed in ha_partition::handle_ordered_index_scan

ha_partition should not try to search the index
if all partitions were pruned away.

The fix originally by Nayuta Yanagisawa

MyISAM tables no longer take transactional metadata locks
unless there already is an active transaction.

MDEV-27523 main.delayed fails with wrong error code or timeout when executed after main.deadlock_ftwrl

don't forget to reset mdl_context.m_deadlock_overweight when
taking the THD out of the cache - the history of previous connections
should not affect the weight in deadlock victim selection

(small cleanup of the test to help the correct merge)

try harder to punch holes on xfs, don't rely on its heuristics to do
the right thing

Due to the different command line format of the timeout
utility on FreeBSD and Linux, SST scripts for mariabackup
may not work on FreeBSD. This commit fixes this problem
by adding a different command to test options on FreeBSD
and adding proper formatting for the utility options.

When aggregating pairs BIT+NULL and NULL+BIT for result, e.g.
in COALESCE(), preserve the BIT data type (ignore explicit NULLs).

The same fix applied to YEAR.

This bug could affect queries with IN subqueries in WHERE clause and using
derived tables to which split optimization potentially could be applied.

When looking for the best split of a splittable derived table T any key
access from a semi-join materialized table used for lookups S to table T
must be excluded from consideration because in the current implementation
of such tables as S the values from its records cannot be used to access
other tables.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

Author: Sergei Petrunia <sergey@mariadb.com>
Date:   Wed Oct 11 19:02:25 2023 +0300

    MDEV-32301: Server crashes at Arg_comparator::compare_row

    In  Item_bool_rowready_func2::build_clone(): if we're setting
      clone->cmp.comparators=0
    also set
      const_item_cache=0
    as the Item is currently in a state where one cannot compute it.

A subquery in form "(SELECT not_null_value LIMIT 1 OFFSET 1)" will
produce no rows which will translate into scalar SQL NULL value.

The code in Item_singlerow_subselect::fix_length_and_dec() failed to
take the LIMIT/OFFSET clause into account and used to set
item_subselect->maybe_null=0, despite that SQL NULL will be produced.

If such subselect was used in ORDER BY, this would cause a crash in
filesort() code when it would get a NULL value for a not-nullable item.

also made subselect_engine::no_tables() const function.

In  Item_bool_rowready_func2::build_clone(): if we're setting
  clone->cmp.comparators=0
also set
  const_item_cache=0
as the Item is currently in a state where one cannot compute it.

The code inside Item_subselect::fix_fields() could fail to check
that left expression had an Item_row, like this:

  (('x', 1.0) ,1) IN (SELECT 'x', 1.23 FROM ... UNION ...)

In order to hit the failure, the first SELECT of the subquery had
to be a degenerate no-tables select. In this case, execution will
not enter into Item_in_subselect::create_row_in_to_exists_cond()
and will not check if left_expr is composed of scalars.

But the subquery is a UNION so as a whole it is not degenerate.
We try to create an expression cache for the subquery.
We create a temp.table from left_expr columns. No field is created
for the Item_row. Then, we crash when trying to add an index over a
non-existent field.

Fixed by moving the left_expr cardinality check to a point in
check_and_do_in_subquery_rewrites() which gets executed for all
cases.
It's better to make the check early so we don't have to care about
subquery rewrite code hitting Item_row in left_expr.