New Feature:
========
This feature adds a safe replacement to the
MASTER_USE_GTID=Current_Pos option for CHANGE MASTER TO as
MASTER_DEMOTE_TO_SLAVE=<bool>. The use case of Current_Pos is to
transition a master to become a slave; however, can break
replication state if the slave executes local transactions due to
actively updating gtid_current_pos with gtid_binlog_pos and
gtid_slave_pos.

MASTER_DEMOTE_TO_SLAVE changes this use case by forcing users to set
Using_Gtid=Slave_Pos and merging gtid_binlog_pos into gtid_slave_pos
once at CHANGE MASTER TO time. Note that if gtid_slave_pos is more
recent than gtid_binlog_pos (as in the case of chain replication),
the replication state should be preserved.

Additionally, deprecate the `Current_Pos` option of MASTER_USE_GTID
to suggest the safe alternative option MASTER_DEMOTE_TO_SLAVE=TRUE.

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

MDEV-19801: Change defaults for CHANGE MASTER TO so that GTID-based replication is used by default if master supports it

This commit makes replicas crash-safe by default by changing the
Using_Gtid value to be Slave_Pos on a fresh slave start and after
RESET SLAVE is issued. If the primary server does not support GTIDs
(i.e., version < 10), the replica will fall back to Using_Gtid=No on
slave start and after RESET SLAVE.

The following additional informational messages/warnings are added:

 1. When Using_Gtid is automatically changed. That is, if RESET
SLAVE reverts Using_Gtid back to Slave_Pos, or Using_Gtid is
inferred to No from a CHANGE MASTER TO given with log coordinates
without MASTER_USE_GTID.
 2. If options are ignored in CHANGE MASTER TO. If CHANGE MASTER TO
is given with log coordinates, yet also specifies
MASTER_USE_GTID=Slave_Pos, a warning message is given that the log
coordinate options are ignored.

Additionally, an MTR macro has been added for RESET SLAVE,
reset_slave.inc, which provides modes/options for resetting a slave
in log coordinate or gtid modes. When in log coordinates mode, the
macro will execute CHANGE MASTER TO MASTER_USE_GTID=No after the
RESET SLAVE command. When in GTID mode, an extra parameter,
reset_slave_keep_gtid_state, can be set to reset or preserve the
value of gtid_slave_pos.

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

Part #2: Extend heuristic pruning to use multiple tables as the
"Model tables".

Before the patch, heuristic pruning uses only one "Model table":
The table which had the best cost AND record became the "Model table".
After that, if a table's cost and record were both worse than
those of the Model Table, the table would be pruned away.

This didn't work well when the first table (the optimizer sorts them
by record_count) had low record_count but relatively high cost: nothing
could be pruned afterwards.

The patch adds the two additional "Model tables": one with the least
cost and the other with the least record_count.
(In both cases, a table can be pruned away if BOTH its cost and
record_count are worse than those of a Model table)

The new pruning is active when the number of tables to consider for
the prefix is higher than @@optimizer_extra_pruning_depth.

One can see the new pruning in the Optimizer Trace as
- "pruned_by_heuristic":"min_record_count", or
- "pruned_by_heuristic":"min_read_time".
Old heuristic pruning shows as "pruned_by_heuristic":1.

Dep_analysis_context::create_unique_pseudo_key_if_needed() was using
std::set which allocated memory outside MEM_ROOT and so required
explicit deallocation. This has been solved by replacing std::set with
MY_BITMAP which is allocated on MEM_ROOT

SELECT_LEX::first_select()->join is NULL for degenerate derived tables
which are known to have just one row and so were already materialized
by the optimizer.
This commit adds a check for this.

Elimination of unnecessary tables from SQL queries is already present
in MariaDB. But it only works for regular tables and not for derived ones.

Imagine we have a view:
  CREATE VIEW v1 AS SELECT a, b, max(c) AS maxc FROM t1 GROUP BY a, b

Due to "GROUP BY a, b" the values of combinations {a, b} are unique,
and this fact can be treated as like derived table "v1" has a unique key
on fields {a, b}.

Suppose we have a SQL query:
  SELECT t2.* FROM t2 LEFT JOIN v1 ON t2.a=v1.a and t2.b=v1.b

1. Since {v1.a, v1.b} is unique and both these fields are bound to t2,
   "v1" is functionally dependent on t2.
   This means every record of "t2" will be either joined with
   a single record of "v1" or NULL-complemented.
2. No fields of "v1" are present on the SELECT list

These two facts allow the server to completely exclude (eliminate)
the derived table "v1" from the query.

Replaced Cost_estimate prefix_cost with a double as prefix_cost was
only used to store and retrive total prefix cost.

This also speeds up things (a bit) as don't have to call
Cost_estimate::total_cost() for every access to the prefix_cost.

Sizeof POSITION decreased from 304 to 256.

This helps a lot when trying to find out why a table combination was pruned

MDEV-28073 Slow query performance in MariaDB when using many table

The idea is to prefer and chain EQ_REF tables (tables that uses an
unique key to find a row) when searching for the best table combination.
This significantly reduces row combinations that has to be examined.
This is optimization is enabled when setting optimizer_prune_level=2
(which is now default).

Implementation:
- optimizer_prune_level has a new level, 2, which enables EQ_REF
  optimization in addition to the pruning done by level 1.
  Level 2 is now default.
- Added JOIN::eq_ref_tables that contains bits of tables that could use
  potentially use EQ_REF access in the query.  This is calculated
  in sort_and_filter_keyuse()

Under optimizer_prune_level=2:
- When the greedy_optimizer notices that the preceding table was an
  EQ_REF table, it tries to add an EQ_REF table next. If an EQ_REF
  table exists, only this one will be considered at this level.
  We also collect all EQ_REF tables chained by the next levels and these
  are ignored on the starting level as we have already examined these.
  If no EQ_REF table exists, we continue as normal.

This optimization speeds up the greedy_optimizer combination test with
~25%

Other things:
- I ported the changes in MySQL 5.7 to greedy_optimizer.test to MariaDB
  to be able to ensure we can handle all cases that MySQL can do.
- I have run all tests with --mysqld=--optimizer_prune_level=1 to verify that
  there where no test changes.

The dependency checking is not needed when using
best_extension_by_limited_search() as this function ensures
that we don't use tables that are depending on any of the remaning
tables.

"Get the tables that one is allowed to have as the next table in the
current plan"

Main author: Sergei Petrunia <sergey@mariadb.com>
Co author: Monty

MDEV-28073 Slow query performance in MariaDB when using many tables

The faster we can find a good query plan, the more options we have for
finding and pruning (ignoring) bad plans.

This patch adds sorting of plans to best_extension_by_limited_search().
The plans, from best_access_path() are sorted according to the numbers
of found rows.  This allows us to faster find 'good tables' and we are
thus able to eliminate 'bad plans' faster.

One side effect of this patch is that if two tables have equal cost,
the table that which was used earlier in the query is preferred.
This allows users to improve plans by reordering eq_ref tables in the
order they would like them to be uses.

Result changes caused by the patch:
- Traces are different as now we print the cost for using tables before
  we start considering them in the plan.
- Table order are changed for some plans. In most cases this is because
  the plans are equal and tables are in this case sorted according to
  their usage in the original query.
- A few plans was changed as the optimizer was able to find a better
  plan (that was pruned by the original code).

Other things:

- Added a new statistic variable: "optimizer_join_prefixes_check_calls",
  which counts number of calls to best_extension_by_limited_search().
  This can be used to check the prune efficiency in greedy_search().
- Added variable "JOIN_TAB::embedded_dependent" to be able to handle
  XX IN (SELECT..) in the greedy_optimizer.  The idea is that we
  should prune a table if any of the tables in embedded_dependent is
  not yet read.
- When using many tables in a query, there will be some additional
  memory usage as we need to pre-allocate table of
  table_count*table_count*sizeof(POSITION) objects (POSITION is 312
  bytes for now) to hold the pre-calculated best_access_path()
  information.  This memory usage is offset by the expected
  performance improvement when using many tables in a query.
- Removed the code from an earlier patch to keep the table order in
  join->best_ref in the original order.  This is not needed anymore as we
  are now sorting the tables for each best_extension_by_limited_search()
  call.

It will go into 10.11.

Author: Luis Eduardo Oliveira Lizardo <108760288+mariadb-LuisLizardo@users.noreply.github.com>
Date:   Mon Jul 18 17:48:01 2022 +0200

    MDEV-28926 Add time spent on query optimizer to JSON ANALYZE (#2193)

    * Add query optimizer timer to ANALYZE FORMAT=JSON

    * Adapt tests and results

    * Change logic to always close the writer after printing query blocks

* Add query optimizer timer to ANALYZE FORMAT=JSON

* Adapt tests and results

* Change logic to always close the writer after printing query blocks

recv_sys_t::recover_deferred(): Hold the exclusive page latch until
the tablespace has been set up. Otherwise, the write of the page
may be lost due to non-existent tablespace. This race only affects
the recovery of the first page in a newly created tablespace.

This race condition was introduced in MDEV-24626.

Ever since commit 9608773f
the InnoDB persistent statistics are enabled on all InnoDB tables
by default. We must filter out any output that indicates that the
statistics tables are being internally accessed by InnoDB.

The issue was that flush_tables() didn't take a MDL lock on cached
TABLE_SHARE before calling open_table() to do a HA_EXTRA_FLUSH call.
Most engines seams to have no issue with it, but apparantly this conflicts
with InnoDB in 10.6 when using TRUNCATE

Fixed by taking a MDL lock before trying to open the table in
flush_tables().

There is no test case as it hard to repeat the scheduling that causes
the error. I did run the test case in MDEV-28897 to verify
that the bug is fixed.

This test failure is similar to encryption.innodb-redo-badkey,
which was fixed in commit 0f0a45b2.

This commit fixes some inaccuracies introduced by
the automatic merge process when migrating changes
from previous versions to 10.9.

i_s_dict_fill_sys_tablestats(): Read all fields of dict_table_t
while holding dict_sys.latch.

dict_sys_t::allow_eviction(): Remove.

prepare_inplace_alter_table_dict(): If the table will not be rebuilt,
preserve all of the original ROW_FORMAT, including the compressed
page size flags related to ROW_FORMAT=COMPRESSED.

hex_to_ascii(): Add #if around the definition to avoid
clang -Wunused-function. Avoid GCC 5 -Wconversion with a cast.

btr_root_raise_and_insert(), btr_lift_page_up(),
rtr_page_split_and_insert(): Reset DB_FAIL from a failure to
copy records on a ROW_FORMAT=COMPRESSED page to DB_SUCCESS
before retrying.

This fixes a regression that was introduced by
commit 0b47c126 (MDEV-13542).

btr_root_raise_and_insert(): Remove a redundant condition.
btr_page_split_and_insert() will invoke btr_page_split_and_insert()
if needed.

Now INSERT, UPDATE, ALTER statements involving incompatible data type pairs, e.g.:

    UPDATE TABLE t1 SET col_inet6=col_int;
    INSERT INTO t1 (col_inet6) SELECT col_in FROM t2;
    ALTER TABLE t1 MODIFY col_inet6 INT;

consistently return an error at the statement preparation time:

    ERROR HY000: Illegal parameter data types inet6 and int for operation 'SET'

and abort the statement before starting interating rows.

This error is the same with what is raised for queries like:
    SELECT col_inet6 FROM t1 UNION SELECT col_int FROM t2;
    SELECT COALESCE(col_inet6, col_int) FROM t1;

Before this change the error was caught only during the execution time,
when a Field_xxx::store_xxx() was called for the very firts row.
The behavior was not consistent between various statements and could do different things:
- abort the statement
- set a column to the data type default value (e.g. '::' for INET6)
- set a column to NULL

A typical old error was:

    ERROR 22007: Incorrect inet6 value: '1' for column `test`.`t1`.`a` at row 1

EXCEPTION:

Note, there is an exception: a multi-row INSERT..VALUES, e.g.:
    INSERT INTO t1 (col_a,col_b) VALUES (a1,b1),(a2,b2);
checks assignment compability at the preparation time for the very first row only:
    (col_a,col_b) vs (a1,b1)

Other rows are still checked at the execution time and return the old warnings
or errors in case of a failure. This is done because catching all rows at the
preparation time would change behavior significantly. So it still works
according to the STRICT_XXX_TABLES sql_mode flags and the table transaction ability.

This is too late to change this behavior in 10.7.
There is no a firm decision yet if a multi-row INSERT..VALUES
behavior will change in later versions.

On FreeBSD, tests run on persistent storage, and no asynchronous I/O
has been implemented. Warnings about 205-second waits on dict_sys.latch
may occur.