All tests prefixed with main.subselect pass:

mtr --suite main --do-test=subselect

pending CI for regressions.

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.

Skip exists2in transformations if we are in a ps execution

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.

Caveat:

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

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.

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.

The code however treated param title the same way as param value when
assigning, and have nonsensical fields like delim_title_len and
delim_title. We remove these.

We also clean up the spider comment connection string parsing,
including:

- Factoring out some code from the parsing function
- Rewriting the struct `st_spider_param_string_parse`, including
  replacing its messy methods with cleaner ones
- And any necessary changes caused by the above changes

Fix spider init bugs (MDEV-22979, MDEV-27233, MDEV-28218) while
preventing regression on old ones (MDEV-30370, MDEV-29904)

Two things are changed:

First, Spider initialisation is made fully synchronous, i.e. it no
longer happens in a background thread. Adapted from the original fix
by nayuta for MDEV-27233. This change itself would cause failure when
spider is initialised early, by plugin-load-add, due to dependency on
Aria and udf function creation, which are fixed in the second and
third parts below. Requires SQL Service, thus porting earlier versions
requires MDEV-27595

Second, if spider is initialised before udf_init(), create udf by
inserting into `mysql.func`, otherwise do it by `CREATE FUNCTION` as
usual. This change may be generalised in MDEV-31401.

Also factor out some clean-up queries from deinit_spider.inc for use
of spider init tests.

A minor caveat is that early spider initialisation will fail if the
server is bootstrapped for the first time, due to missing `mysql`
database which needs to be created by the bootstrap script.

Removing procedures that were created and dropped during init.

This also fixes a race condition where mtr test with
plugin-load-add=ha_spider.so causes post test check to fail as it
expects the procedures to still be there.

There are several plugins in ha_spider: spider, spider_alloc_mem,
spider_wrapper_protocols, spider_rewrite etc.

INSTALL PLUGIN foo SONAME ha_spider causes all the other ones to be
installed by the init queries where foo is any of the plugins.

This introduces unnecessary complexiy. For example it reads
mysql.plugins to find all other plugins, causing the hack of moving
spider plugin init to a separate thread.

To install all spider related plugins, install soname ha_spider should
be used instead.

This also fixes spurious rows in mysql.plugin when installing say only
the spider plugin with `plugin-load-add=SPIDER=ha_spider.so`:

select * from mysql.plugin;
name	dl
spider_alloc_mem	ha_spider.so # should not be here
spider_wrapper_protocols	ha_spider.so # should not be here

Adapted from part of the reverted commit
c160a115.

We introduce simple plugin dependency. A plugin init function may
return HA_ERR_RETRY_INIT. If this happens during server startup when
the server is trying to initialise all plugins, the failed plugins
will be retried, until no more plugins succeed in initialisation or
want to be retried.

This will fix spider init bugs which is caused in part by its
dependency on Aria for initialisation.

The reason we need a new return code, instead of treating every
failure as a request for retry, is that it may be impossible to clean
up after a failed plugin initialisation. Take InnoDB for example, it
has a global variable `buf_page_cleaner_is_active`, which may not
satisfy an assertion during a second initialisation try, probably
because InnoDB does not expect the initialisation to be called
twice.

The existing (incorrect) overriding mechanism is:

Non-minus-one var value overrides table param overrides default value.

Before MDEV-27169, unspecified var value is -1. So if the user sets
both the var to be a value other than -1 and the table param, the var
value will prevail, which is incorrect.

After MDEV-27169, unspecified var value is default value. So if the
user does not set the var but sets the table param, the default value
will prevail, which is even more incorrect.

This patch fixes it so that table param, if specified, always
overrides var value, and the latter if not specified or set to -1,
falls back to the default value

We achieve this by replacing all such overriding in spd_param.cc with
macros that override in the correct way, and removing all the
"overriding -1" lines involving table params in
spider_set_connect_info_default() except for those table params not
defined as sysvar/thdvar in spd_params.cc

We also introduced macros for non-overriding sysvar and thdvar, so
that the code is cleaner and less error-prone

In server versions where MDEV-27169 has not been applied, we also
backport the patch, that is, replacing -1 default values with real
default values

In server versions where MDEV-28006 has not been applied, we do the
same for udf params

This fixes mdev_26541.test, and the new clean_up_spider.inc will be
useful for other tests where part of deinit_spider does not apply,
e.g. those testing spider initialisation only.

generated by the language server ccls

The server needs to have a unique name

Will re-enable once MDEV-31101 is no longer blocked by MDEV-22979,
as the patch for the latter might fix the former.

spider_db_mbase_util::open_item_func() is a monster function.
It is difficult to maintain while it is expected that we need to
modify it when a new SQL function or a new func_type is added.

We split the function into two distinct functions: one handles the
case of str != NULL and the other handles the case of str == NULL.

This refactoring was done in a conservative way because we do not
have comprehensive tests on the function.

It also fixes MDEV-29447 and MDEV-31338 where field items that are
arguments of a func item may be used before created / initialised.

Note this commit is adapted from a patch by Nayuta for MDEV-26285.

Extract the indexed string memcopy pattern in spd_trx.cc to a static
inline function.

Also updated the ubsan check in mdev_26541.test (h/t roel).

The error message for user connections using insecure transport when secured transport is required is very uninformative and doesn't mention the requirement of secure
transport at all.

To make the error message more relevant, introduce a new error
'ER_SECURE_TRANSPORT_REQUIRED', copy of MySQL error message with the
error code 08004 (SQL-server rejected establishment SQL-connection).

Move the code of 'require_secure_transport' to be executed before
authentication verification, as it's not part of authentication but
rather verifying if connection should be allowed in the first place.

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.

Signed-off-by: Kristian Nielsen <knielsen@knielsen-hq.org>

* invoke parent's cleanup()
* don't reinit memroot, if already inited (causes memory leak)

also move free_root() from destructor to cleanup() to not accumulate
allocations from prepare and multiple executes

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.

50-mariadb_safe.cnf was only ever intended to be a reference file.
With mariadbd-safe falling into disuse in Debian since systemd became
a service it was assumed this files wasn't used except on a few old
legacy init systems. Its assumed these users will have a configuration
already set.

It is however read by the mariabackup galera-sst script. This forks
off the logger process to write the output to /dev/log, which on a
systemd service the journalctl is reading anyway.

The real harm is on containers where there is now a MariaDB operator
that runs Galera based containers. In containers there is no /dev/log,
so information gets lost.

It would be much more direct just to go straight to stdout/error
like what would happen without this configuration.

Rather than risk the galera-sst scripts moving to the [mariadbd-safe]
group name introduced in a1211a4e, we
remove the file for cleanness.

MDEV-31661: Assertion `thd->lex == sp_instr_lex' failed in LEX* sp_lex_instr::parse_expr(THD*, sp_head*, LEX*)

This is the follow-up patch for the task MDEV-5816 that fixes assert failure
that happened after recompilation of a stored routine containing a cursor
on its second execution.

The reason of assertion hit is that a state of the SP instruction sp_instr_cpush
wasn't reset after its SQL statement re-compiled.

To fix the issue the virtual method sp_lex_instr::on_after_expr_parsing
is overridden in the derived class sp_instr_cpush. Implementation of this method
does resetting of the data member sp_instr_cpush::m_metadata_changed

Additionally, implementation of the method
 sp_instr_set_trigger_field::on_after_expr_parsing
has been slightly modified to set the data member
 sp_instr_set_trigger_field::value
just before successful return. This data member is used to check whether this
SP instruction is still valid or should be re-compiled.
Resetting this data member before an instance of the class Item_trigger_field
be successfully allocated theoretically could lead to clearing of instruction's
state despite the fact that memory allocation was failed.

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

Author: Sergei Golubchik

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 routine 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 a SP instruction's 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 memory 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, use binary cycle to iterate every
Item_trigger_field object created on parsing a trigger body.

To organize the data structure 'list of lists' 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 items 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 be 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 are cleared right after the current SP statement be cleaned up
and before re-parsing be 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 ia LEX object
on execution, added storing of their original sql expressions inside
classes derived from the class sp_lex_instr.

A 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 invalid
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 a 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 classes derived from it.
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.