MDEV-16465 Invalid (old?) table or database name or hang in ha_innobase::delete_table and log semaphore wait upon concurrent DDL with foreign keys

ALTER TABLE locks the table with TL_READ_NO_INSERT, to prevent the
source table modifications while it's being copied. But there's an
indirect way of modifying a table, via cascade FK actions.

After previous commits, an attempt to modify an FK parent table
will cause FK children to be prelocked, so the table-being-altered
cannot be modified by a cascade FK action, because ALTER holds a
lock and prelocking will wait.

But if a new FK is being added by this very ALTER, then the target
table is not locked yet (it's a temporary table). So, we have to
lock FK parents explicitly.

Backport of 794f71cb

table_already_fk_prelocked() was looking for a table in the wrong
list (not the complete list of prelocked tables, but only in its tail,
starting from the current table - which is always empty for the last
added table), so for circular FKs it kept adding same tables to the list
indefinitely.

Backport of d6d7e169

Backport of f1362910

Use TABLE::init_one_table(), don't duplicate it.
Put additional initializations into TABLE::init_one_table_for_prelocking()

Backport of f1362910

instead of returning strings for CASCADE/RESTRICT
from every storage engine, use enum values

Backport of a3614d33

Missed one file in last push...

The problem was that join_columns creation was not finished due to error of notfound column in USING, but next execution tried to use join_columns lists.

Solution is cleanup the lists on error. It can eat memory in statement MEM_ROOT but it is an error and error will be fixed or statement/procedure removed/altered.

Problem was that SQL level tried to read a record with rnd_pos()
that was already deleted by the same statement.
In the case where the page for the record had been deleted, this
caused an assert.
Fixed by extending the assert to also handle empty pages and
return HA_ERR_RECORD_DELETED for reads to deleted pages.

            optimizer_use_condition_selectivity>=3

Selectivity analysis should be disabled for Geometrical columns
for the case like geometric_field= string_constant.

Currently for selectivity calculation we perform range analysis for a column even when we don't have any statistics(EITS).
This makes less sense but is used to catch contradiction for WHERE condition.

So the solution is to not perform range analysis for selectivity calculation for columns that do not have statistics.

No need to read statistics for tables that are not USER tables.
We allocate memory for structures to collect statistics only for USER TABLES.

Warning:
'delete' applied to a pointer that was allocated with 'new[]';
did you mean 'delete[]'?

Avoids compile errors of the form:

/storage/connect/jdbconn.cpp:1473:41: error: cannot initialize a parameter of type 'jboolean *' (aka 'unsigned char *') with an rvalue of type 'jboolean' (aka 'unsigned char')
   name = env->GetStringUTFChars(label, (jboolean)false);
                                        ^~~~~~~~~~~~~~~
/usr/lib/jvm/java-8-oracle/include/jni.h:1616:58: note: passing argument to parameter 'isCopy' here
    const char* GetStringUTFChars(jstring str, jboolean *isCopy) {

Problem was that Create_field::create_length_to_internal_length()
calculated a different pack_length for NEWDECIMAL compared to
Field_new_decimal constructor which lead to some unused bytes
in the middle of the record, which Aria didn't like.

Problem was that the number of NULL bit's was record wrong in the
.frm file because there could be more fields marked NOT_NULL after the
number of not_null fields where recorded.

Fixed by copying test for virtual fields from prepare_create_field()
The code change, only the test, doesn't have to be merged to 10.3
as this is fixed there.

MDEV-17022: check if mtr --mem location is writeable

No need to read statistics for tables that are not USER tables.
We allocate memory for structures to collect statistics only for USER TABLES.

MDEV-17039: Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4
            and use_stat_tables= PREFERABLY

Currently the code that calculates selectivity for a table does not take into account the case when
we can have GROUP BY optimization (looses index scan).

The common case for this function is that both types are the same.

The Item_result defination from include/mysql.h.pp is the following enum
   enum Item_result
   {
     STRING_RESULT=0, REAL_RESULT, INT_RESULT, ROW_RESULT, DECIMAL_RESULT,
     TIME_RESULT
   };

The compilers aren't quite smart enough to optimize to this shortcut so
this makes it quicker.

Before the change:

0000000000012730 <item_cmp_type(Item_result, Item_result)>:
   12730:       89 f0                   mov    %esi,%eax
   12732:       09 f8                   or     %edi,%eax
   12734:       74 4c                   je     12782 <item_cmp_type(Item_result, Item_result)+0x52>
   12736:       83 ff 02                cmp    $0x2,%edi
   12739:       75 0a                   jne    12745 <item_cmp_type(Item_result, Item_result)+0x15>
   1273b:       b8 02 00 00 00          mov    $0x2,%eax
   12740:       83 fe 02                cmp    $0x2,%esi
   12743:       74 3c                   je     12781 <item_cmp_type(Item_result, Item_result)+0x51>
   12745:       83 ff 03                cmp    $0x3,%edi
   12748:       b8 03 00 00 00          mov    $0x3,%eax
   1274d:       74 32                   je     12781 <item_cmp_type(Item_result, Item_result)+0x51>
   1274f:       83 fe 03                cmp    $0x3,%esi
   12752:       74 2d                   je     12781 <item_cmp_type(Item_result, Item_result)+0x51>
   12754:       83 ff 05                cmp    $0x5,%edi
   12757:       b8 05 00 00 00          mov    $0x5,%eax
   1275c:       74 23                   je     12781 <item_cmp_type(Item_result, Item_result)+0x51>
   1275e:       83 fe 05                cmp    $0x5,%esi
   12761:       74 1e                   je     12781 <item_cmp_type(Item_result, Item_result)+0x51>
   12763:       83 ff 04                cmp    $0x4,%edi
   12766:       74 05                   je     1276d <item_cmp_type(Item_result, Item_result)+0x3d>
   12768:       83 ff 02                cmp    $0x2,%edi
   1276b:       75 0f                   jne    1277c <item_cmp_type(Item_result, Item_result)+0x4c>
   1276d:       b8 04 00 00 00          mov    $0x4,%eax
   12772:       83 fe 02                cmp    $0x2,%esi
   12775:       74 0a                   je     12781 <item_cmp_type(Item_result, Item_result)+0x51>
   12777:       83 fe 04                cmp    $0x4,%esi
   1277a:       74 05                   je     12781 <item_cmp_type(Item_result, Item_result)+0x51>
   1277c:       b8 01 00 00 00          mov    $0x1,%eax
   12781:       c3                      retq
   12782:       31 c0                   xor    %eax,%eax
   12784:       c3                      retq

After, noting the short cut and the beginning of the function:

0000000000012730 <item_cmp_type(Item_result, Item_result)>:
   12730:       39 f7                   cmp    %esi,%edi
   12732:       75 03                   jne    12737 <item_cmp_type(Item_result, Item_result)+0x7>
   12734:       89 f8                   mov    %edi,%eax
   12736:       c3                      retq
   12737:       83 ff 03                cmp    $0x3,%edi
   1273a:       b8 03 00 00 00          mov    $0x3,%eax
   1273f:       74 32                   je     12773 <item_cmp_type(Item_result, Item_result)+0x43>
   12741:       83 fe 03                cmp    $0x3,%esi
   12744:       74 2d                   je     12773 <item_cmp_type(Item_result, Item_result)+0x43>
   12746:       83 ff 05                cmp    $0x5,%edi
   12749:       b8 05 00 00 00          mov    $0x5,%eax
   1274e:       74 23                   je     12773 <item_cmp_type(Item_result, Item_result)+0x43>
   12750:       83 fe 05                cmp    $0x5,%esi
   12753:       74 1e                   je     12773 <item_cmp_type(Item_result, Item_result)+0x43>
   12755:       83 ff 04                cmp    $0x4,%edi
   12758:       74 05                   je     1275f <item_cmp_type(Item_result, Item_result)+0x2f>
   1275a:       83 ff 02                cmp    $0x2,%edi
   1275d:       75 0f                   jne    1276e <item_cmp_type(Item_result, Item_result)+0x3e>
   1275f:       b8 04 00 00 00          mov    $0x4,%eax
   12764:       83 fe 02                cmp    $0x2,%esi
   12767:       74 0a                   je     12773 <item_cmp_type(Item_result, Item_result)+0x43>
   12769:       83 fe 04                cmp    $0x4,%esi
   1276c:       74 05                   je     12773 <item_cmp_type(Item_result, Item_result)+0x43>
   1276e:       b8 01 00 00 00          mov    $0x1,%eax
   12773:       c3                      retq
Signed-off-by: Daniel Black <daniel@linux.vnet.ibm.com>

Item_subselect::is_expensive() used to return FALSE (Inexpensive) whenever
it saw that one of SELECTs in the Subquery's UNION is degenerate. It
ignored the fact that other parts of the UNION might not be inexpensive,
including the case where pther parts of the UNION have no query plan yet.

For a subquery in form col >= ANY (SELECT 'foo' UNION SELECT 'bar')
this would cause the query to be considered inexpensive when there is
no query plan for the second part of the UNION, which in turn would
cause the SELECT 'foo' to compute and free itself while still inside
JOIN::optimize for that SELECT (See MDEV comment for full description).

MDEV-15475: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed on EXPLAIN EXTENDED with constant table and view

Print constant ISNULL value independent.
Fix of printing of view FRM and CREATE VIEW output

Use real DACL instead of NULL DACL.
Grant Everyone just read/write access to pipe
(instead of all access like previously with NULL ACL)

truncate incorrect values in convert_period_to_month() so that
PERIOD_DIFF never returns a value outside of 2^23 range.

And, for safety, increase buffer sizes for int10_to_str
to be sufficienly big for any int10_to_str result.

not empty

We should swap the list only when list is not empty.

with spatial index

So the issue is since it is spatial index , at the time of searching index
for key (Rows_log_event::find_row) we use wrong field image we use
Field::itRAW while we should be using Field::itMBR

fts_query(): Remove a redundant condition (result will never be NULL),
and instead check if *result is NULL, to prevent SIGSEGV in
fts_query_free_result().

The functions fts_ast_visit() and fts_query() inside
InnoDB FULLTEXT INDEX query processing are not checking
for THD::killed (trx_is_interrupted()), like anything
that potentially takes a long time should do.

This is a port of the following change from MySQL 5.7.23,
with a completely rewritten test case.

commit c58c6f8f66ddd0357ecd0c99646aa6bf1dae49c8
Author: Aakanksha Verma <aakanksha.verma@oracle.com>
Date:   Fri May 4 15:53:13 2018 +0530

Bug #27155294 MAX_EXECUTION_TIME NOT INTERUPTED WITH FULLTEXT SEARCH USING MECAB