The idea is to have simple functions that the user can combine to produce
the exact result one wants, whether the user wants JSON object that has
common keys with another JSON object, or same key/value pair etc. So
making simpler function helps here.

We accomplish this by making three separate functions.
1) JSON_OBJECT_FILTER_KEYS(Obj, Arr_keys):
Put keys ( which are basically strings ) in hash, go over the object and
get key one by one. If the key is present in the hash,
add the key-value pair to result.

2) JSON_OBJECT_TO_ARRAY(Obj) : Create a string variable, Go over the json
object, and add each key value pair as an array into the result.

3) JSON_ARRAY_INTERSECT(arr1, arr2) :
Go over one of the json and add each item of the array
in hash (after normalizing each item). Go over the second array,
search the normalized item one by one in the hash. If item is found,
add it to the result.

Implementation Idea: Holyfoot ( Alexey Botchkov)
Author: tanruixiang and Rucha Deodhar

objects

Idea behind implementation:
We get the json object specified by the json path. Then, transform it into
key-value pairs by going over the json. Get each key-value pair
one-by-one and return the result.

- Patch does shrinking the system tablespace during startup
process. Steps for shrinking the system tablespace:

1) Find the last used extent in the system tablespace
by iterating through the BITMAP in extent descriptor page

2) Check whether tablespace is being used within fixed size
and If the last used extent is lesser than fixed size then
set the desired target size to fixed size

3) Flush all the pages belong to system tablespace in flush list

4) Truncate the truncated pages from FSP_FREE and FSP_FREE_FRAG list

5) Reset the bitmap in descriptor pages for the truncated pages

6) Update the FSP_SIZE and FSP_FREE_LIMIT in header page

7) In case of multiple files, calculate the truncated last
file size and do the truncation in last file

- recv_sys_t::apply(): Handle the truncation of the system
tablespace only if we have recv_size of tablespace exists

After further I/O on a tablespace has been stopped
(for example due to DROP TABLE or an operation that
rebuilds a table), page cleaner thread tries to
flush the pending writes for the tablespace and
releases the tablespace reference even though it was not
acquired.

fil_space_t::flush(): Don't release the tablespace when it is
being stopped and closed

Thanks to Marko Mäkelä for suggesting this patch.

view creation (that mysqltest automatically does in --view protocol)
can cause sp cache invalidation, and that will cause the test to fail
(because the test checks that invalidation did not happen).

disable view protocol in the parts of the test where this is the case

The follow-up patch to check in mtr tests that recompilation of
a SP's instruction doesn't lead to eviction of SP from sp_cache.

This patch adds the debug keyword 'check_sp_cache_not_invalidated'
checked in sp_cache_flush_obsolete. In case this debug keyword
is set the macros DBUG_SUICIDE() called to cause test failure.

The function sp_cache_flush_obsolete() is called on opening
a stored routine. So setting this keyword before second execution
of some stored routine that supposed to cause recompilation of
SP's statement will guarantee that this stored routing not evicted
from sp_cache.

Suggested approach has one limitation - the statement
 CREATE/ALTER/DROP VIEW
forces invalidation of the whole sp_cache (by invoking the function
 sp_cache_invalidate()).
So, for those tests (actually, there are very small number of such tests)
that create/alter/drop a view before the second execution of some stored
routine, the debug keyword 'check_sp_cache_not_invalidated' isn't set.

The proposal to add some way a check that a stored routine is not force out
from sp_cache on re-parsing a failing statement of a stored routine was
done during reiew, that is the reason the proposed change has been formatted
as a separate patch.

Added mtr tests for MDEV-5816

Fix of existing mtr tests.

This patch fixes the issue with missing warnings generated on re-parsing
a failing SP instruction's statement. That is, any warning generated
on re-parsing a statement was discarded after SP instruction's a statement
has been successfully re-parsed.

The reason for discarding warnings after re-parsing is that the method
  THD::set_query_id()
called every time when a failing SP instruction re-parsed.
In result,
  Warning_info::m_warn_id != thd->query_id
and when the method
  Diagnostics_area::opt_clear_warning_info(thd->query_id)
is invoked from sp_head::execute all warnings accumulated during
execution of the current SP instruction is cleared.

So, to fix the issue invokes the method THD::set_query_id()
once per SP instruction, on its first execution. Re-parsing of failing
SP instruction and following run of it don't invoke the method
THD::set_query_id().

This patch is the second part of implementation for cursor's statement
re-parsing. The patch does the following changes:
  - on re-parsing a failed SP instruction that does need to get access
    to LEX a new lex is instantiated for every SP instruction except
    cursor relating SP instructions.
  - items created on re-parsing a cursor relating statement are moved
    to the free_list of sp_lex_cursor.

Added re-parsing of a failing cursor body. Re-parsing of a failing
SP statement is implemented by the method validate_lex_and_exec_core(),
therefore invocation of the method reset_lex_and_exec_core() inside
  sp_lex_keeper::cursor_reset_lex_and_exec_core
was replaced by the method validate_lex_and_exec_core().

Re-parsing of a failed SP statement is relied upon interface provided
by the class sp_lex_instr (the methods used for this goal are
is_invalid(), parse_expr(), invalidate(), get_query(), get_expr_query()).
To provide access to these methods on opening a cursor, the signature of
the method
  sp_lex_keeper::cursor_reset_lex_and_exec_core
was changed to accept a pointer to the class sp_lex_instr instead of
the class sp_instr, and the new method get_push_instr() was added
into the class sp_cursor. This method is to get access to an instance
of the class sp_instr_cpush on opening a cursor (on handling the statement
OPEN cursors_name).

Default implementation of this method just returns NULL pointer of
the type sp_instr_cpush. This method is overridden in the class
sp_instr_cpush with trivial implementation
  { return this; }

On handling the statement DECLARE CURSOR FOR the new instruction of
the type sp_instr_cpush is added into sp_head. The class sp_instr_cpush
holds a text of SELECT query referencing by a cursor declaration.
When a cursor is being opened (on handling the statement 'OPEN cur_name')
a pointer to sp_instr_cpush is returned by the method
  sp_cursor::get_push_instr()
and this pointer is passed to the method
  sp_lex_keeper::cursor_reset_lex_and_exec_core
in order to open a cursor and provide access to an interface required
for SP  statement re-parsing in case metadata changes took place.
Since real access to a lex object is required on handling instruction
sp_instr_cpush (an instance of this class is created during parsing of
cursor declaration statement), calling of the method sp_cursor::open
is moved from the method
  sp_instr_copen::exec_core
into the method
  sp_instr_cpush::exec_core.

Additionally, updated the methods get_query/get_expr_query in the classes
sp_instr_cpush, sp_instr_cursor_copy_struct in order to return correct text of
cursor's body taking into account that lexer treated the clause CURSOR FOR/
CURSOR IS as two different tokens following one after another. So, to return
a correct text of SELECT statement specified in CURSOR declaration statement,
the token FOR/IS should be skipped and text following it should be returned as
a text of cursors's query.

Introduced the new data member new_query_arena_is_set of the class sp_head.
This data member is used as a protection against double invocation of
the method restore_thd_mem_root that is called for restoration of the
current query arena. Previously, the data member sp_head::m_thd
is used for this goal but after support for re-compilation of a failed
stored routine statement has been added the data member sp_head::m_thd
can't be used for this goal. The reason is that on a statement re-compilation
after the method restore_thd_mem_root() is called the method
sp_head::add_instr() invoked to add a new instruction for just re-compiled
statement. The method sp_head::add_instr() de-references m_thd to access
the free_list data member. If m_thd was used as a guard against double
invocation it would result in a crash on dereferencing null pointer.

Fixed memory leakage taken place on execution of the statement
  SHOW CREATE PACKAGE `pkg_name`
The memory leak was caused by implementation of sp_compile()
where a memory root for a stored routine was allocated but
a pointer to the new memory root wasn't passed to sp_package::create
for subsequent forwarding to the constructor of sp_package.
Instead, another one mempory root was allocated and the pointer to
the original memory root was missed.

Re-designed a way by that Item_trigger_field objects are arranged in memory.

Item_trigger_field objects created on parsing a trigger's statement
is now stored in a per statement list. All lists of Item_trigger_field
objects created on parsing the whole trigger's body are organized
in the structure "list of lists". So, to iterate every
Item_trigger_field object created on parsing a trigger body,
it required to use binary cycle.

To organize the list of lists structure the new data member
  Item_trigger_field::next_trig_field_list
is introduced that links lists in this hierarchy structure.

This re-design is performed in order to avoid refences to already
deleted items on re-compilation of failed trigger's statememt.
Referencing to already deleted item could take place on re-parsing
a trigger's statement since every Item created for a statement
being re-parsed is deleted before the statement is re-parsed,
but deleted items are still referenced from sp_head. So, to avoid
access to dangling references a per statement list of Item_trigger_field
objects is cleared right after the current SP statement is cleaned up
and before re-parsing is started.

Added re-parsing of failed statements inside a stored routine.

General idea of the patch is to install an instance of the class
Reprepare_observer before executing a next SP instruction and
re-parse a statement of this SP instruction in case of
its execution failure.

To implement the described approach the class sp_lex_keeper
has been extended with the method validate_lex_and_exec_core()
that is just a wrapper around the method reset_lex_and_exec_core()
with additional setting/resetting an instance of the class
Reprepare_observer on each iteration of SP instruction
execution.

If reset_lex_and_exec_core() returns error and an instance
of the class Reprepare_observer is installed before running
a SP instruction then a number of attempts to re-run the SP
instruction is checked against a max. limit and in case it doesn't
reach the limit a statement for the failed SP instruction is re-parsed.

Re-parsing of a statement for the failed SP instruction is implemented
by the new method sp_le_inst::parse_expr() that prepends
a SP instruction's statement with the clause 'SELECT' and parse it.
Own SP instruction MEM_ROOT and a separate free_list is used for
parsing of a SP statement. On successful re-parsing of SP instruction's
statement the virtual methods adjust_sql_command() and
on_after_expr_parsing() of the class sp_lex_instr is called
to update the SP instruction state with a new data created
on parsing the statement.

Few words about reason for prepending a SP instruction's statement
with the clause 'SELECT' - this is required step to produce a valid
SQL statement, since for some SP instructions the instructions statement
is not a valid SQL statement. Wrapping such text into 'SELECT ( )'
produces a correct operator from SQL syntax point of view.

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.