GitLab

1. For INSERT..SELECT statements: don't include table/view the data
   is inserted into in the list of leaf tables
2. Remove duplicated and dead code related to table_count

(Try 2) (Cherry-pick back into 10.3)

The code that updates semi-join optimization state for a join order prefix
had several bugs. The visible effect was bad optimization for FirstMatch or
LooseScan strategies: they either weren't considered when they should have
been, or considered when they shouldn't have been.

In order to hit the bug, the optimizer needs to consider several different
join prefixes in a certain order. Queries with "obvious" query plans which
prune all join orders except one are not affected.

Internally, the bugs in updates of semi-join state were:
1. restore_prev_sj_state() assumed that
  "we assume remaining_tables doesnt contain @tab"
  which wasn't true.
2. Another bug in this function: it did remove bits from
   join->cur_sj_inner_tables but never added them.
3. greedy_search() adds tables into the join prefix but neglects to update
   the semi-join optimization state. (It does update nested outer join
   state, see this call:
     check_interleaving_with_nj(best_table)
   but there's no matching call to update the semi-join state.
   (This wasn't visible because most of the state is in the POSITION
    structure which is updated. But there is also state in JOIN, too)

The patch:
- Fixes all of the above
- Adds JOIN::dbug_verify_sj_inner_tables() which is used to verify the
  state is correct at every step.
- Renames advance_sj_state() to optimize_semi_joins().
  = Introduces update_sj_state() which ideally should have been called
    "advance_sj_state" but I didn't reuse the name to not create confusion.

(This is the assert that was added in fix for MDEV-26047)

Table elimination may remove an ON expression from an outer join.
However SELECT_LEX::update_used_tables() will still call

  item->walk(&Item::eval_not_null_tables)

for eliminated expressions. If the subquery is constant and cheap
Item_cond_and will attempt to evaluate it, which will trigger an
assert.
The fix is not to call update_used_tables() or eval_not_null_tables()
for ON expressions that were eliminated.

This bug may affect the queries that uses a grouping derived table with
grouping list containing references to columns from different tables if
the optimizer decides to employ the split optimization for the derived
table. In some very specific cases it may affect queries with a grouping
derived table that refers only one base table.
This bug was caused by an improper fix for the bug MDEV-25128. The fix
tried to get rid of the equality conditions pushed into the where clause
of the grouping derived table T to which the split optimization had been
applied. The fix erroneously assumed that only those pushed equalities
that were used for ref access of the tables referenced by T were needed.
In fact the function remove_const() that figures out what columns from the
group list can be removed if the split optimization is applied can uses
other pushed equalities as well.
This patch actually provides a proper fix for MDEV-25128. Rather than
trying to remove invalid pushed equalities referencing the fields of SJM
tables with a look-up access the patch attempts not to push such equalities.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

The problem was caused by the following scenario:

Subquery's table has two indexes, KEY a(a), KEY a_b(a,b)

- LATERAL DERIVED optimization decides to use index a.
  = The subquery uses ref access over key a.
- test_if_skip_sort_order() sees that KEY a_b satisfies the
  subquery's GROUP BY clause, and attempts to switch to it.
  = It fails to do so, because KEYUSE objects for index a_b
    are switched off.

Fixed by disallowing to change the ref access key if it uses KEYUSE
objects injected by LATERAL DERIVED optimization.

The assertion failed in handler::ha_reset upon SELECT under
READ UNCOMMITTED from table with index on virtual column.

This was the debug-only failure, though the problem is mush wider:
* MY_BITMAP is a structure containing my_bitmap_map, the latter is a raw
 bitmap.
* read_set, write_set and vcol_set of TABLE are the pointers to MY_BITMAP
* The rest of MY_BITMAPs are stored in TABLE and TABLE_SHARE
* The pointers to the stored MY_BITMAPs, like orig_read_set etc, and
 sometimes all_set and tmp_set, are assigned to the pointers.
* Sometimes tmp_use_all_columns is used to substitute the raw bitmap
 directly with all_set.bitmap
* Sometimes even bitmaps are directly modified, like in
TABLE::update_virtual_field(): bitmap_clear_all(&tmp_set) is called.

The last three bullets in the list, when used together (which is mostly
always) make the program flow cumbersome and impossible to follow,
notwithstanding the errors they cause, like this MDEV-17556, where tmp_set
pointer was assigned to read_set, write_set and vcol_set, then its bitmap
was substituted with all_set.bitmap by dbug_tmp_use_all_columns() call,
and then bitmap_clear_all(&tmp_set) was applied to all this.

To untangle this knot, the rule should be applied:
* Never substitute bitmaps! This patch is about this.
 orig_*, all_set bitmaps are never substituted already.

This patch changes the following function prototypes:
* tmp_use_all_columns, dbug_tmp_use_all_columns
 to accept MY_BITMAP** and to return MY_BITMAP * instead of my_bitmap_map*
* tmp_restore_column_map, dbug_tmp_restore_column_maps to accept
 MY_BITMAP* instead of my_bitmap_map*

These functions now will substitute read_set/write_set/vcol_set directly,
and won't touch underlying bitmaps.

The assertion failed in handler::ha_reset upon SELECT under
READ UNCOMMITTED from table with index on virtual column.

This was the debug-only failure, though the problem is mush wider:
* MY_BITMAP is a structure containing my_bitmap_map, the latter is a raw
 bitmap.
* read_set, write_set and vcol_set of TABLE are the pointers to MY_BITMAP
* The rest of MY_BITMAPs are stored in TABLE and TABLE_SHARE
* The pointers to the stored MY_BITMAPs, like orig_read_set etc, and
 sometimes all_set and tmp_set, are assigned to the pointers.
* Sometimes tmp_use_all_columns is used to substitute the raw bitmap
 directly with all_set.bitmap
* Sometimes even bitmaps are directly modified, like in
TABLE::update_virtual_field(): bitmap_clear_all(&tmp_set) is called.

The last three bullets in the list, when used together (which is mostly
always) make the program flow cumbersome and impossible to follow,
notwithstanding the errors they cause, like this MDEV-17556, where tmp_set
pointer was assigned to read_set, write_set and vcol_set, then its bitmap
was substituted with all_set.bitmap by dbug_tmp_use_all_columns() call,
and then bitmap_clear_all(&tmp_set) was applied to all this.

To untangle this knot, the rule should be applied:
* Never substitute bitmaps! This patch is about this.
 orig_*, all_set bitmaps are never substituted already.

This patch changes the following function prototypes:
* tmp_use_all_columns, dbug_tmp_use_all_columns
 to accept MY_BITMAP** and to return MY_BITMAP * instead of my_bitmap_map*
* tmp_restore_column_map, dbug_tmp_restore_column_maps to accept
 MY_BITMAP* instead of my_bitmap_map*

These functions now will substitute read_set/write_set/vcol_set directly,
and won't touch underlying bitmaps.

MDEV-17066: Bytes lost or Assertion `status_var.local_memory_used == 0 after DELETE with subquery with ROLLUP

The issue here is when records are read from the temporary file
(filesort result in this case) via a cache(rr_from_cache).
The cache is initialized with init_rr_cache.
For correlated subquery the cache allocation is happening at each execution
of the subquery but the deallocation happens only once and that was
when the query execution was done.

So generally for subqueries we do two types of cleanup

1) Full cleanup: we should free all resources of the query(like temp tables).
   This is done generally when the query execution is complete or the subquery
   re-execution is not needed (case with uncorrelated subquery)

2) Partial cleanup: Minor cleanup that is required if
   the subquery needs recalculation. This is done for all the structures that
   need to be allocated for each execution (example SORT_INFO for filesort
   is allocated for each execution of the correlated subquery).

The fix here would be free the cache used by rr_from_cache in the partial
cleanup phase.

A temporary table is needed for window function computation but if only a NAMED WINDOW SPEC
is used and there is no window function, then there is no need to create a temporary
table as there is no stage to compute WINDOW FUNCTION

MDEV-22006 runtime error: null pointer passed as argument 2, which is declared to never be null in JOIN::copy_ref_ptr_array()

Do not memcpy() a zero-length buffer

The issue here is for degenerate joins we should execute the window
function but it is not getting executed in all the cases.

To get the window function values window function needs to be executed
always. This currently does not happen in few cases
where the join would return 0 or 1 row like
  1) IMPOSSIBLE WHERE
  2) MIN/MAX optimization
  3) EMPTY CONST TABLE

The fix is to make sure that window functions get executed
and the temporary table is setup for the execution of window functions

best_access_path() is called from two optimization phases:

1. Plan choice phase, in choose_plan(). Here, the join prefix being
   considered is in join->positions[]

2. Plan refinement stage, in fix_semijoin_strategies_for_picked_join_order
   Here, the join prefix is in join->best_positions[]

It used to access join->positions[] from stage #2. This didnt cause any
valgrind or asan failures (as join->positions[] has been written-to before)
but the effect was similar to that of reading the random data:
The join prefix we've picked (in join->best_positions) could have
nothing in common with the join prefix that was last to be considered
(in join->positions).

* Replace LINT_INIT for non-struct types with ctor initializers;
* Check BUILD_DEPS list is not empty so REMOVE_DUPLICATES won't throw
  error.

* Update wrong zip-code

note: Inherit String from Sql_alloc,
to get operators new and new[] in sync

in rocksdb gcc was complaining that non-lvalue was cast to const.

There were two newly enabled warnings:
1. cast for a function pointers. Affected sql_analyse.h, mi_write.c
   and ma_write.cc, mf_iocache-t.cc, mysqlbinlog.cc, encryption.cc, etc

2. memcpy/memset of nontrivial structures. Fixed as:
* the warning disabled for InnoDB
* TABLE, TABLE_SHARE, and TABLE_LIST got a new method reset() which
  does the bzero(), which is safe for these classes, but any other
  bzero() will still cause a warning
* Table_scope_and_contents_source_st uses `TABLE_LIST *` (trivial)
  instead of `SQL_I_List<TABLE_LIST>` (not trivial) so it's safe to
  bzero now.
* added casts in debug_sync.cc and sql_select.cc (for JOIN)
* move assignment method for MDL_request instead of memcpy()
* PARTIAL_INDEX_INTERSECT_INFO::init() instead of bzero()
* remove constructor from READ_RECORD() to make it trivial
* replace some memcpy() with c++ copy assignments

If a splittable materialized derived table / view T is used in a inner nest
of an outer join with impossible ON condition then T is marked as a
constant table. Yet the execution plan to build T is still searched for
in spite of the fact that is not needed. So it should be set.

Reuse the fix for MDEV-17518 here, too.

preserve positions if the multi-update join is using tmp table:

* store positions in the tmp table if needed
  JOIN::add_fields_for_current_rowid()

* take positions from the tmp table, not from file->position():
  multi_update::prepare2()

introduce Item_temptable_rowid() that is used to store
table->file->position() in the temporary table record

The issue here is that the window function execution is not called for the correct join tab, when we have GROUP BY
where we create extra temporary tables then we need to call window function execution for the last join tab. For doing
so the current code does not take into account the JOIN::aggr_tables.
Fixed by introducing a new function JOIN::total_join_tab_cnt that takes in account the temporary tables also.

no matching operator delete found; memory will not be freed if initialization throws an exception

Added a no-op delete() for MEM_ROOT based placement-new()

Virtial_tmp_table did not set the "field_index" member for its Fields.
Fixing Virtual_tmp_table::add() to set "field_index" to the Field's ordinal position
inside the table, like a normal TABLE does, for consistency.

Although, this flaw did not seem to cause any bugs, having field_index properly
set is helpful for debugging purposes.

This was done in, among other things:
- thd->db and thd->db_length
- TABLE_LIST tablename, db, alias and schema_name
- Audit plugin database name
- lex->db
- All db and table names in Alter_table_ctx
- st_select_lex db

Other things:
- Changed a lot of functions to take const LEX_CSTRING* as argument
  for db, table_name and alias. See init_one_table() as an example.
- Changed some function arguments from LEX_CSTRING to const LEX_CSTRING
- Changed some lists from LEX_STRING to LEX_CSTRING
- threads_mysql.result changed because process list_db wasn't always
  correctly updated
- New append_identifier() function that takes LEX_CSTRING* as arguments
- Added new element tmp_buff to Alter_table_ctx to separate temp name
  handling from temporary space
- Ensure we store the length after my_casedn_str() of table/db names
- Removed not used version of rename_table_in_stat_tables()
- Changed Natural_join_column::table_name and db_name() to never return
  NULL (used for print)
- thd->get_db() now returns db as a printable string (thd->db.str or "")

MDEV-15107 Add virtual Field::sp_prepare_and_store_item(), make sp_rcontext symmetric for scalar and ROW

After MDEV-14212, the Virtual_tmp_table instance that stores a ROW
variable elements is accessible from the underlying Field_row
(rather than Item_field_row).

This patch makes some further changes by moving the code from
sp_instr_xxx, sp_rcontext, Item_xxx to Virtual_tmp_table and Field_xxx.

The data type specific code (scalar vs ROW) now resides in
a new virtual method Field_xxx::sp_prepare_and_store_item().
The the code in sp_rcontext::set_variable() and sp_eval_expr()
is now symmetric for scalar and ROW values.
The code in sp_rcontext::set_variable_row_field(), sp_rcontext::set_variable_row_field(), sp_rcontext::set_variable_row()
is now symmetric for ROW elements (i.e. scalar and ROW elements inside a ROW).

Rationale:

Prepare the code to implement these tasks soon easier:

- MDEV-12252 ROW data type for stored function return values
- MDEV-12307 ROW data type for built-in function return values
- MDEV-6121 Data type: Array
- MDEV-10593 sql_mode=ORACLE: TYPE .. AS OBJECT: basic functionality
- ROW with ROW fields (no MDEV yet)

Details:

1. Moving the code in sp_eval_expr() responsible to backup/restore
   thd->count_cuted_fields, thd->abort_on_warning,
   thd->transaction.stmt.modified_non_trans_table
   into a new helper class Sp_eval_expr_state, to reuse it easier.
   Fixing sp_eval_expr() to use this new class.

2. Moving sp_eval_expr() and sp_prepare_func_item() from public functions
   to methods in THD, so they can be reused in *.cc files easier without
   a need to include "sp_head.h".

   Splitting sp_prepare_func_item() into two parts.
   Adding a new function sp_fix_func_item(), which fixes
   the underlying items, but does not do check_cols() for them.
   Reusing sp_fix_func_item() in Field_row::sp_prepare_and_store_item().

3. Moving the code to find ROW fields by name from Item to Virtual_tmp_table

   Moving the code searching for ROW fields by their names
   from Item_field_row::element_index_by_name() to a new method
   Item_field_row to Virtual_tmp_table::sp_find_field_by_name().

   Adding wrapper methods sp_rcontext::find_row_field_by_name() and
   find_row_field_by_name_or_error(), to search for a ROW variable
   fields by the variable offset and its field name.

   Changing Item_splocal_row_field_by_name::fix_fields() to do
   use sp_rcontext::find_row_field_by_name_or_error().
   Removing virtual Item::element_index_by_name().

4. Splitting sp_rcontext::set_variable()

   Adding a new virtual method Field::sp_prepare_and_store_item().
   Spliting the two branches of the code in sp_rcontext::set_variable()
   into two virtual implementations of Field::sp_prepare_and_store_item(),
   (for Field and for Field_row).

   Moving the former part of sp_rcontext::set_variable() with the loop
   doing set_null() for all ROW fields into a new method
   Virtual_tmp_table::set_all_fields_to_null() and using it in
   Field_row::sp_prepare_and_store_item().

   Moving the former part of sp_rcontext::set_variable() with the loop
   doing set_variable_row_field() into a new method
   Virtual_tmp_table::set_all_fields_from_item() and using it in
   Field_row::sp_prepare_and_store_item().
   The loop in the new method now uses sp_prepare_and_store_item()
   instead of set_variable_row_field(), because saving/restoring
   THD flags is now done on the upper level. No needs to save/restore
   on every iteration.

5. Fixing sp_eval_expr() to simply do two things:
   - backup/restore THD flags
   - call result_field->sp_prepare_and_store_item()
   So now sp_eval_expr() can be used for both scalar and ROW variables.
   Reusing it in sp_rcontext::set_variable*().

6. Moving the loop in sp_rcontext::set_variable_row() into a
   new method Virtual_tmp_table::sp_set_all_fields_from_item_list().

   Changing the loop body to call field->sp_prepare_and_store_item()
   instead of doing set_variable_row_field(). This removes
   saving/restoring of the THD flags from every interation.
   Instead, adding the code to save/restore the flags around
   the entire loop in set_variable_row(), using Sp_eval_expr_state.
   So now saving/restoring is done only once for the entire ROW
   (a slight performance improvement).

7. Removing the code in sp_instr_set::exec_core() that sets
   a variable to NULL if the value evaluation failed.
   sp_rcontext::set_variable() now makes sure to reset
   the variable properly by effectively calling sp_eval_expr(),
   which calls virtual Field::sp_prepare_and_store_item().

   Removing the similar code from sp_instr_set_row_field::exec_core()
   and sp_instr_set_row_field_by_name::exec_core().

   Removing the method sp_rcontext::set_variable_row_field_to_null(),
   as it's not used any more.

8. Removing the call for sp_prepare_func_item() from
   sp_rcontext::set_variable_row_field(), as it was duplicate:
   it was done inside sp_eval_expr(). Now it's done inside
   virtual Field::sp_prepare_and_store_item().

9. Moving the code from sp_instr_set_row_field_by_name::exec_core()
   into sp_rcontext::set_variable_row_field_by_name(), for symmetry
   with other sp_instr_set*::exec_core()/sp_rcontext::set_variable*() pairs.
   Now sp_instr_set_row_field_by_name::exec_core() calls
   sp_rcontext::set_variable_row_field_by_name().

10. Misc:
   - Adding a helper private method sp_rcontext::virtual_tmp_table_for_row(),
     reusing it in a new sp_rcontext methods.
   - Removing Item_field_row::get_row_field(), as it's not used any more.
   - Removing the "Item *result_item" from sp_eval_expr(),
     as it's not needed any more.

The assertion failure was caused by an incorrectly set read_set for
functions in the ORDER BY clause in part of a union, when we are using
a mergeable view and the order by clause can be skipped (removed).

An order by clause can be skipped if it's part of one part of the UNION as
the result set is not meaningful when multiple SELECT queries are UNIONed. The
server is aware of this optimization and tries to remove the order by
clause before JOIN::prepare. The problem is that we need to throw an
error when the ORDER BY clause contains invalid columns. To do this, we
attempt resolving the ORDER BY expressions, then subsequently drop them
if resolution succeeded. However, ORDER BY resolution had the side
effect of adding the expressions to the all_fields list, which is used
to construct temporary tables to store the result. We may be ignoring
the ORDER BY statement, but the tmp table still tried to compute the
values for the expressions, even if the columns are never used.

The assertion only shows itself if the order by clause contains members
which were not previously in the select list, and are part of a
function.

There is an additional question as to why this only manifests when using
VIEWS and not when using a regular table. The difference lies with the
"reset" of the read_set for the temporary table during
SELECT_LEX::update_used_tables() in JOIN::optimize(). The changes
introduced in fdf789a7 cleared the
read_set when a mergeable view is encountered in the TABLE_LIST
defintion.

Upon initial order_list resolution, the table's read_set is updated
correctly. JOIN::optimize() will only reset the read_set if it
encounters a VIEW. Since we no longer have ORDER BY clause in
JOIN::optimize() we never get to correctly update the read_set again.

Other relevant commit by Timour, which first introduced the order
resolution when we "can_skip_sort_order":
883af99e

Solution:
Don't add the resolved ORDER BY elements to all_fields. We only resolve
them to check if an error should be returned for the query. Ignore them
completely otherwise.

splitting technique for equi-joins of materialized derived tables/views/CTEs.
(see mdev-13369 and mdev-13389).

Renamed to SELECT_LEX::vers_setup_conds().
Moved optimized fields check to JOIN::vers_check_items().

This was done to make thing consistent. It gives the additional benefit
that EXPLAIN EXTENDED now treat null_tables like constant's and replaces
columns with NULL, in a similar way that it replaces columns with constants
for constant tables.

- Null tables are tables where all columns are always NULL. The most common
  NULL TABLE is a table used in a LEFT_JOIN that is never true.
- All result changes comes from replacing columns with NULL for null_tables.
- "Impossible where" is now also shows constants for const columns.
- Removed duplicated s->type= JT_CONST
- Reset found_const_table_map when JOIN is created (safety fix)

Most "new" failures fixed in the following files:
- sql_select.cc
- item.cc
- item_func.cc
- opt_subselect.cc

Other things:
- Allocate udf_handler strings in mem_root
  - Required changes in sql_string.h
- Add mem_root as argument to some new [] calls
- Mark udf_handler strings as thread specific
- Removed some comment blocks with code

in joined table + GROUP BY + GROUP_CONCAT + HAVING + ORDER BY
[by field from HAVING] + 1 row expected

The fix is actually a port of the fix for bug #17055185 from
mysql code line (see commit f289aeeef0743508ff87211084453b3b88a6d017
by Mithun C Y into mysql-5.6). The test case for the bug #17055185
was also ported.

- The valgrind warning came from JOIN::optimize() (sql_select.cc:1123)

Changing datatypes for:
- Item_spvar_args::m_table
- sp_rcontext::m_var_table
- return value of create_virtual_tmp_table()
from TABLE* to Virtual_tmp_table*

Advantages:
- Stricter data type control
- Removing the duplicate code (a loop with free_blobs)
  from destructors ~sp_rcontext() and ~Item_spvar_args(),
  using "delete m_(var_)table" in both instead.
- Using Virtual_tmp_table::delete makes the code call Field::delete,
  which calls TRASH() for the freed fields,
  which is good for valgrind test runs.

Currently condition pushdown into materialized views / derived tables
is not implemented yet (see mdev-12387) and grouping views are
optimized early when subqueries are converted to semi-joins in
convert_join_subqueries_to_semijoins(). If a subquery that is converted
to a semi-join uses a grouping view this view is optimized in two phases.
For such a view V only the first phase of optimization is done after
the conversion of subqueries of the outer join into semi-joins.
At the same time the reference of the view V appears in the join
expression of the outer join. In fixed code there was an attempt to push
conditions into this view and to optimize it after this. This triggered
the second phase of the optimization of the view and it was done
prematurely. The second phase of the optimization for the materialized
view is supposed to be called after the splitting condition is pushed
into the view in the call of JOIN::improve_chosen_plan for the outer
join.

The fix blocks the attempt to push conditions into splittable views
if they have been already partly optimized and the following
optimization for them.

The test case of the patch shows that the code for mdev-13369
basically supported the splitting technique for materialized views /
derived tables.

The patch also replaces the name of the state JOIN::OPTIMIZATION_IN_STAGE_2
for JOIN::OPTIMIZATION_PHASE_1_DONE and fixes a bug in
TABLE_LIST::fetch_number_of_rows()