For those SP instructions that need to get access to LEX object on execution,
added storing of their original sql expressions inside classes derived
from the class sp_lex_instr.

Stored sql expression is returned by the abstract method
  sp_lex_instr::get_expr_query
redefined in derived classes.

Since an expression constituting a SP instruction can be not valid
SQL statement in general case (not parseable statement), the virtual method
sp_lex_instr::get_query() is introduced to return a valid string
for a statement that corresponds to the given instruction.

Additionally, introduced the rule remember_start_opt in the grammar.
The new rule intended to get correct position of a current
token taking into attention the fact whether lookahead was done or not.

This is the prerequisite patch introducing the class sp_lex_instr
that encapsulates access to an instance of the class sp_lex_keeper.
Every SP instruction that need to get access to a LEX object on its
processing should inherit this class and implement two abstract methods:
  is_invalid(),
  invalidate().

These methods will be used in subsequent patches to implement recompilation of
SP instructions on failure.

Currently, the following instructions are derived from the class sp_lex_instr:
  sp_instr_stmt,
  sp_instr_set,
  sp_instr_set_trigger_field,
  sp_instr_jump_if_not,
  sp_instr_freturn,
  sp_instr_cpush,
  sp_instr_cursor_copy_struct,
  sp_instr_set_case_expr

Additionally, this patch converts the class sp_instr_opt_meta
to base abstract class (that is, not inherited from the class
sp_instr). Since this class originally was designed to provide a way
for opimizer to update a destination address for jump SP-instructions,
the only useful method at the interface of this class is set_destination
and therefore inheritance from the class sp_instr is meaningless.

Every jump SP instruction now must be inherited directly from
the class sp_instr_opt_meta and additionally from either the class
sp_lex_instr or sp_instr depending on whether this SP instruction
need to get access to a LEX object or not.

Moreover, the class sp_cursor doesn't own a data member of
the class sp_lex_keeper any more. Instead, the virtual method
get_lex_keeper() has been added to the class sp_cursor() that
returns nullptr and this method is overridden in the derived class
sp_instr_cpush to provide a pointer to a real instance of
the class sp_lex_keeper. Doing this way we exclude duplication
of a data member of the type sp_lex_keeper at the class sp_instr_cpush
since it is derived both from sp_lex_instr and sp_cursor, and sp_lex_instr
already encapsulates a data member of the class sp_lex_keeper.

This is the prerequisite patch to change a signature of the virtual
method opt_move() in the base class sp_instr and its derived classes.
The parameterized type of the instuctions list returned in the second
argument is changed from sp_instr to sp_instr_opt_meta since only
jump instructions are placed in this list on returning from
the method call.

This is the prerequisite patch to move the data member
LEX::trg_table_fields to the class sp_head and rename it as
m_trg_table_fields.

This data member is used for handling OLD/NEW pseudo-rows inside
a trigger body and in order to be able to re-parse a trigger body
the data member must be moved from the struct LEX to the class sp_head.

This is the prerequisite patch to remove the data member
sp_head::m_trg_table_fields and the method is_fields_updated_in_trigger
that used it but is not called anywhere in the source code.

The commit 5f1f2fc0 introduced the
data member sp_head::m_trg_table_fields and the method
Table_triggers_list::is_fields_updated_in_trigger() that used this data member.

The method Table_triggers_list::is_fields_updated_in_trigger() was invoked
by the method partition_info::can_prune_insert() also introduced by
the same commit 5f1f2fc0

The method partition_info::can_prune_insert() is not called anywhere
in the code and later these  methods were removed from the source code
but the data member sp_head::m_trg_table_fields wasn't.
So, remove the data member sp_head::m_trg_table_fields and declaration of
the method is_fields_updated_in_trigger() for purpose of code cleaning up.

This is the prerequisite patch to move the sp_instr class and classes derived
from it into the files sp_instr.cc/sp_instr.h. The classes sp_lex_cursor and
sp_lex_keeper are also moved to the files files sp_instr.cc/sp_instr.h.

Additionally,
  * all occurrences of macroses NULL, FALSE, TRUE are replaced
    with the corresponding C++ keywords nullptr, false, true.
  * the keyword 'override' is added in and the keyword 'virtual' is removed
    from signatures of every virtual method implemented in classes derived
    from the base class sp_instr.
  * the keyword 'final' is added into declaration of the class sp_lex_keeper
    since this class shouldn't have a derived class by design.
  * the function cmp_rqp_locations is made static since it is not called
    outside the file sp_instr.cc.
  * the function subst_spvars() is moved into the file sp_instr.cc since this
    function used only by the class sp_ins

This is the prerequisite patch to make interface of the class
Reprepare_observer more similar to the one used by MySQL.

This patch adds the method can_retry() to the class Reprepare_observer
that returns true in case max. number of attempts to re-run a failing
statement is not yet reached. To control the number of re-run attempts
already done the data member m_attempt has been introduced. Doing this way,
we encapsulate activity with incrementing a counter on every statement
run and checking whether it reaches a limit or not inside implementation
of the class Reprepare_observer instead duplicating such boiler plate code
in every place where controlling for reaching a limit of max. number attempts
for re-running failed statement is required.

* IS_USER_TEMP_TABLE() was misleading, name didn't match the code
* list of temp tables was rescanned number_of_databases times
* some temporary tables were not shown (from nonexistent databases)
* some temporary tables were shown more than once (e.g. after self-joins)
* sys.table_exists() - avoid querying I_S twice
* fix handling of temporary MERGE tables - it's pointless to fully open
  them, they're not in thd->temporary_tables, so they simply fail to
  open and are skipped. Relax the assertion instead.

MDEV-28343: sys.create_synonym_db fails with ER_VIEW_SELECT_TMPTABLE when schema contains temporary tables

- MDEV-28342 raised the error in case temporary table shadows base table
- Now we are allowed to shadow base tables with temporary tables and
`sys.create_synonym_db()` can easily check for existance of temporary table and
ignore view creation, since it is not supported to create view from
temporary table.
Reviewed-by: &lt;monty@mariadb.org&gt;, <vicentiu@mariadb.org>

Reviewed-by: <vicentiu@mariadb.org>

This commit updates sysschema to work with the new behaviour of show
tables and information_schema.tables table showing temporary tables for
current connection.
Co-authored-by: Monty <monty@mariadb.org>
Reviewer: <vicentiu@mariadb.org>

Additionally fixes the bugs uncovered in:
  - `MDEV-28332: Alter on temporary table causes ER_TABLE_EXISTS_ERROR note`
    Since there is no `warning` issued by shadowing of base table, this MDEV
    is irrelevant. Keeping for review purposes and for future development
    in case shadowing is going to be implemented
  - `MDEV-28334: SHOW TABLE STATUS shows all temporary tables ignoring database and conditions`
  - `MDEV-28453: SHOW commands are inconsistent for temporary tables`

Reviewed by: <monty@mariadb.org>,
             <vicentiu@mariadb.org>

Reviewer: <vicentiu@mariadb.org>

Allow ALTER TABLE ... IMPORT TABLESPACE without creating the table
followed by discarding the tablespace.

That is, assuming we want to import table t1 to t2, instead of

CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 DISCARD TABLESPACE;
FLUSH TABLES t1 FOR EXPORT;
--copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg
--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd
UNLOCK TABLES;
ALTER TABLE t2 IMPORT TABLESPACE;

We can simply do

FLUSH TABLES t1 FOR EXPORT;
--copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg
--copy_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/t2.frm
--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd
UNLOCK TABLES;
ALTER TABLE t2 IMPORT TABLESPACE;

We achieve this by creating a "stub" table in the second scenario
while opening the table, where t2 does not exist but needs to import
from t1. The "stub" table is similar to a table that is created but
then instructed to discard its tablespace.

We include tests with various row formats, encryption, with indexes
and auto-increment.
Signed-off-by: Yuchen Pei <yuchen.pei@mariadb.com>

Turn the remaining three `binlog*` options binlog_do_db, binlog_ignore_db,
binlog_rows_event_max_size into global variables so that they can be
visible from the SQL user level. This is for audit / secure
configuration check purposes.

Create new MTR tests to make sure that the newly created global
variables can be visible from the command line interface.

Behavior before the code change:

    MariaDB [(none)]> SHOW GLOBAL VARIABLES WHERE
        -> Variable_name LIKE 'binlog_do_db' OR
        -> Variable_name LIKE 'binlog_ignore_db' OR
        -> Variable_name LIKE 'binlog_row_event_max_size';
    Empty set (0.001 sec)

Behavior after the code change:

    MariaDB [(none)]> SHOW GLOBAL VARIABLES WHERE
        -> Variable_name LIKE 'binlog_do_db' OR
        -> Variable_name LIKE 'binlog_ignore_db' OR
        -> Variable_name LIKE 'binlog_row_event_max_size';
    +---------------------------+-------+
    | Variable_name             | Value |
    +---------------------------+-------+
    | binlog_do_db              |       |
    | binlog_ignore_db          |       |
    | binlog_row_event_max_size | 8192  |
    +---------------------------+-------+
    3 rows in set (0.001 sec)

Note:

For `binlog_do_db` and `binlog_ignore_db`, we add a new class
`Sys_var_binlog_filter` to handle the dynamically-composable command line
options for `binlog_do_db` and `binlog_ignore_db`. Below
is the motivation:

When the users start the server with the option
    --binlog-do-db="database1" --binlog-do-db="database2"
The expected behavior is that the system should allow replication for
both `database1` and `database2`, which is the logic of the original
code.

However, when turning the variables into system variables, the
functionality does not exist any more, since system variables will only
handle the last occurrence of the option, and in this case, the system
will only be able to handle `database2`.

Copyright:

All new code of the whole pull request, including one or several files
that are either new files or modified ones, are contributed under the BSD-new
license. I am contributing on behalf of my employer Amazon Web Services, Inc.

The test gcol.gcol_purge would reliably hang on 10.6 on a
Microsoft Windows builder without this adjustment.
A similar adjustment was applied in
commit 3e40f9a7
to the tests innodb.dml_purge and innodb.instant_alter_purge.

innodb_undo_log_truncate_update(): A callback function. If
SET GLOBAL innodb_undo_log_truncate=ON, invoke
srv_wake_purge_thread_if_not_active().

srv_wake_purge_thread_if_not_active(): If innodb_undo_log_truncate=ON,
always wake up the purge subsystem.

srv_do_purge(): If the history is empty, invoke
trx_purge_truncate_history() in order to free undo log pages.

trx_purge_truncate_history(): If head.trx_no==0, consider the
cached undo logs to be free.

trx_purge(): Remove the parameter "bool truncate" and let the
caller invoke trx_purge_truncate_history() directly.

Reviewed by: Vladislav Lesin

purge_sys_t::sees(): Wrapper for view.sees().

trx_purge_truncate_history(): Invoke purge_sys.sees() instead of
comparing to head.trx_no, to determine if undo pages can be safely freed.

The test innodb.cursor-restore-locking was adjusted by Vladislav Lesin,
as was the the debug instrumentation in row_purge_del_mark().

Reviewed by: Vladislav Lesin

This test case exposed 2 different bugs:
- When replacing a range with an index scan on a covering key
  in test_if_skip_sort_order() we didn't disable filtering.
  Filtering does not make much sense in this case.
  - Fixed by disabling filtering in this case.
- Range_rowid_filter::fill() did not take into account that keyread
  could already active, which caused an assert when it tried to
  activate another keyread.
  - Fixed by remembering old keyread state at start and restoring it
    at end.

Other things:
- ha_start_keyread() allowed multiple calls. This is wrong, especially
  as we do no check if the index changed!
  I added an assert() to ensure that we don't call it there is already
  an active keyread.
- ha_end_keyread() always called ha_extra(), even if keyread was not
  active.  Added a check to avoid the extra call.

This patch also fixes
MDEV-31391 Assertion `((best.records_out) == 0.0 ... failed

Cost changes caused by this change:
- range queries with join buffer now have a notable smaller cost.
- range ranges are bit more expensive as the MULTI_RANGE_COST is now
  properly applied to it in all cases (this extra cost is equal to a
  key lookup).
- table scan cost is slight smaller as we now assume data is cached in
  the engine after the first scan pass. (We did this before for range
  scans and other access methods).
- partition tables had wrong values for max_row_blocks and
  max_index_blocks.  Correcting this, causes range access on
  partitioned tables to have slightly higher cost because of the
  increased estimated IO.
- Using first match + join buffer caused 'filtered' to be calcualted
  wrong.  (Only affected EXPLAIN, not query costs).
- Added cost_without_join_buffer to optimizer_trace.
- check_quick_select() adjusted the number of rows according to persistent
  statistics, but did not adjust cost. Now fixed.

The big change in the patch are:

- In best_access_path(), where we now are using storing the cost in
  'ALL_READ_COST cost' and only converting it to a double at the end.
   This allows us to more exactly calculate the effect of the join_cache.
- In JOIN_TAB::estimate_scan_time(), store the cost also in a
  ALL_READ_COST object.

One of effect if this change is that when joining very small tables:

t1    some_access_method
t2    range
t3    ALL         Use join buffer

This is swiched to

t1      some_access_method
t3      ALL
t2      range      use join buffer

Both plans has the same cost, but as table scan in this case has less
cost than rang, the table scan will be considered first and thus have
precidence.

Test case changes:
- optimizer_trace          - Addition of cost_without_join_buffer
- subselect_mat_cost_bugs  - Small tables and scan versus range
- range & range_mrr_icp    - Range + join_cache is faster than ref
- optimizer_trace          - cost_without_join_buffer, smaller scan cost,
                             range setup cost.
- mrr                      - range+join_buffer used as smaller cost