This bug is caused by pushdown from HAVING into WHERE.
It appears because condition that is pushed wasn't fixed.
It is also discovered that condition pushdown from HAVING into
WHERE is done wrong. There is no need to build clones for some
conditions that can be pushed. They can be simply moved from HAVING
into WHERE without cloning.
build_pushable_cond_for_having_pushdown(),
remove_pushed_top_conjuncts_for_having() methods are changed.
It is found that there is no transformation made for fields of
pushed condition.
field_transformer_for_having_pushdown transformer is added.
New tests are added. Some comments are changed.
The MDEV-17262 commit 26432e49d3
was skipped. In Galera 4, the implementation would seem to require
changes to the streaming replication.
In the tests archive.rnd_pos main.profiling, disable_ps_protocol
for SHOW STATUS and SHOW PROFILE commands until MDEV-18974
has been fixed.
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.
Condition can be pushed from the HAVING clause into the WHERE clause
if it depends only on the fields that are used in the GROUP BY list
or depends on the fields that are equal to grouping fields.
Aggregate functions can't be pushed down.
How the pushdown is performed on the example:
SELECT t1.a,MAX(t1.b)
FROM t1
GROUP BY t1.a
HAVING (t1.a>2) AND (MAX(c)>12);
=>
SELECT t1.a,MAX(t1.b)
FROM t1
WHERE (t1.a>2)
GROUP BY t1.a
HAVING (MAX(c)>12);
The implementation scheme:
1. Extract the most restrictive condition cond from the HAVING clause of
the select that depends only on the fields that are used in the GROUP BY
list of the select (directly or indirectly through equalities)
2. Save cond as a condition that can be pushed into the WHERE clause
of the select
3. Remove cond from the HAVING clause if it is possible
The optimization is implemented in the function
st_select_lex::pushdown_from_having_into_where().
New test file having_cond_pushdown.test is created.
MDEV-17631 select_handler for a full query pushdown
Interfaces + Proof of Concept for federatedx with test cases.
The interfaces have been developed for integration of ColumnStore engine.
This patch contains a full implementation of the optimization
that allows to use in-memory rowid / primary filters built for range
conditions over indexes. In many cases usage of such filters reduce
the number of disk seeks spent for fetching table rows.
In this implementation the choice of what possible filter to be applied
(if any) is made purely on cost-based considerations.
This implementation re-achitectured the partial implementation of
the feature pushed by Galina Shalygina in the commit
8d5a11122c.
Besides this patch contains a better implementation of the generic
handler function handler::multi_range_read_info_const() that
takes into account gaps between ranges when calculating the cost of
range index scans. It also contains some corrections of the
implementation of the handler function records_in_range() for MyISAM.
This patch supports the feature for InnoDB and MyISAM.
Detailed: changes:
1. Moving Field specific code into new methods on Field:
- Field *Field::create_tmp_field(...)
- virtual void init_for_tmp_table(...)
2. Removing virtual Item::create_tmp_field().
Adding instead a new virtual method Item::create_tmp_field_ex().
Note, a virtual create_tmp_field() still exists, but only for Item_sum.
This resembles 10.0 code structure. Perhaps create_tmp_field() should
be removed from Item_sum and Item_sum descendants should override
create_tmp_field_ex() directly. This can be done in a separate commit.
3. Adding helper classes Tmp_field_src and Tmp_field_param,
to make the API for Item::create_tmp_field_ex() smaller
and easier to extend in the future.
4. Decomposing the public function create_tmp_field() into
virtual implementations for Item and a number of its descendants:
- Item_basic_value
- Item_sp_variable
- Item_name_const
- Item_result_field
- Item_field
- Item_ref
- Item_type_holder
- Item_row
- Item_func_sp
- Item_func_user_var
- Item_sum
- Item_sum_field
- Item_proc
5. Adding DBUG_ASSERT-only virtual implementations for
Item types that should not appear in create_tmp_table_ex(),
for easier debugging:
- Item_nodeset_func
- Item_nodeset_to_const_comparator
- Item_null_result
- Item_copy
- Item_ident_for_show
- Item_user_var_as_out_param
6. Moving public function create_tmp_field_from_field()
as a method to Item_field.
7. Removing Item::set_result_field(). It's not needed any more.
8. Cleanup: Removing the enum value "EXPR_CACHE_ITEM",
as it's not used for a very long time.
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()
The logic and the implementation scheme are similar with the
MDEV-9197 Pushdown conditions into non-mergeable views/derived tables
How the push down is made on the example:
select * from t1
where a>3 and b>10 and
(a,b) in (select x,max(y) from t2 group by x);
-->
select * from t1
where a>3 and b>10 and
(a,b) in (select x,max(y)
from t2
where x>3
group by x
having max(y)>10);
The implementation scheme:
1. Search for the condition cond that depends only on the fields
from the left part of the IN subquery (left_part)
2. Find fields F_group in the select of the right part of the
IN subquery (right_part) that are used in the GROUP BY
3. Extract from the cond condition cond_where that depends only on the
fields from the left_part that stay at the same places in the left_part
(have the same indexes) as the F_group fields in the projection of the
right_part
4. Transform cond_where so it can be pushed into the WHERE clause of the
right_part and delete cond_where from the cond
5. Transform cond so it can be pushed into the HAVING clause of the right_part
The optimization is made in the
Item_in_subselect::pushdown_cond_for_in_subquery() and is controlled by the
variable condition_pushdown_for_subquery.
New test file in_subq_cond_pushdown.test is created.
There are also some changes made for setup_jtbm_semi_joins().
Now it is decomposed into the 2 procedures: setup_degenerate_jtbm_semi_joins()
that is called before optimize_cond() for cond and setup_jtbm_semi_joins()
that is called after optimize_cond().
New setup_jtbm_semi_joins() is made in the way so that the result of its work is
the same as if it was called before optimize_cond().
The code that is common for pushdown into materialized derived and into materialized
IN subqueries is factored out into pushdown_cond_for_derived(),
Item_in_subselect::pushdown_cond_for_in_subquery() and
st_select_lex::pushdown_cond_into_where_clause().
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 "")
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 fdf789a7ea 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":
883af99e7d
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.
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.
As a result of this merge the code for the following tasks appears in 10.3:
- MDEV-12172 Implement tables specified by table value constructors
- MDEV-12176 Transform [NOT] IN predicate with long list of values INTO
[NOT] IN subquery.
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()
with window functions (mdev-10855).
This patch just modified the function pushdown_cond_for_derived()
to support this feature.
Some test cases demonstrating this optimization were added to
derived_cond_pushdown.test.
"Optimization for equi-joins of derived tables with GROUP BY"
should be considered rather as a 'proof of concept'.
The task itself is targeted at an optimization that employs re-writing
equi-joins with grouping derived tables / views into lateral
derived tables. Here's an example of such transformation:
select t1.a,t.max,t.min
from t1 [left] join
(select a, max(t2.b) max, min(t2.b) min from t2
group by t2.a) as t
on t1.a=t.a;
=>
select t1.a,tl.max,tl.min
from t1 [left] join
lateral (select a, max(t2.b) max, min(t2.b) min from t2
where t1.a=t2.a) as t
on 1=1;
The transformation pushes the equi-join condition t1.a=t.a into the
derived table making it dependent on table t1. It means that for
every row from t1 a new derived table must be filled out. However
the size of any of these derived tables is just a fraction of the
original derived table t. One could say that transformation 'splits'
the rows used for the GROUP BY operation into separate groups
performing aggregation for a group only in the case when there is
a match for the current row of t1.
Apparently the transformation may produce a query with a better
performance only in the case when
- the GROUP BY list refers only to fields returned by the derived table
- there is an index I on one of the tables T used in FROM list of
the specification of the derived table whose prefix covers the
the fields from the proper beginning of the GROUP BY list or
fields that are equal to those fields.
Whether the result of the re-writing can be executed faster depends
on many factors:
- the size of the original derived table
- the size of the table T
- whether the index I is clustering for table T
- whether the index I fully covers the GROUP BY list.
This patch only tries to improve the chosen execution plan using
this transformation. It tries to do it only when the chosen
plan reaches the derived table by a key whose prefix covers
all the fields of the derived table produced by the fields of
the table T from the GROUP BY list.
The code of the patch does not evaluates the cost of the improved
plan. If certain conditions are met the transformation is applied.
If the optimizer chose an execution plan where
a semi-join nest were materialized and the
result of materialization was scanned to access
other tables by ref access it could build a key
over columns of the tables from the nest that
were actually inaccessible.
The patch performs a proper check whether a key
that uses columns of the tables from a materialized
semi-join nest can be employed to access outer tables.
The usage of windows functions when all tables were optimized away
by min/max optimization were not supported. As result a result,
the queries that used window functions with min/max aggregation
over the whole table returned wrong result sets.
The patch fixed this problem.
Benefits of this patch:
- Removed a lot of calls to strlen(), especially for field_string
- Strings generated by parser are now const strings, less chance of
accidently changing a string
- Removed a lot of calls with LEX_STRING as parameter (changed to pointer)
- More uniform code
- Item::name_length was not kept up to date. Now fixed
- Several bugs found and fixed (Access to null pointers,
access of freed memory, wrong arguments to printf like functions)
- Removed a lot of casts from (const char*) to (char*)
Changes:
- This caused some ABI changes
- lex_string_set now uses LEX_CSTRING
- Some fucntions are now taking const char* instead of char*
- Create_field::change and after changed to LEX_CSTRING
- handler::connect_string, comment and engine_name() changed to LEX_CSTRING
- Checked printf() related calls to find bugs. Found and fixed several
errors in old code.
- A lot of changes from LEX_STRING to LEX_CSTRING, especially related to
parsing and events.
- Some changes from LEX_STRING and LEX_STRING & to LEX_CSTRING*
- Some changes for char* to const char*
- Added printf argument checking for my_snprintf()
- Introduced null_clex_str, star_clex_string, temp_lex_str to simplify
code
- Added item_empty_name and item_used_name to be able to distingush between
items that was given an empty name and items that was not given a name
This is used in sql_yacc.yy to know when to give an item a name.
- select table_name."*' is not anymore same as table_name.*
- removed not used function Item::rename()
- Added comparision of item->name_length before some calls to
my_strcasecmp() to speed up comparison
- Moved Item_sp_variable::make_field() from item.h to item.cc
- Some minimal code changes to avoid copying to const char *
- Fixed wrong error message in wsrep_mysql_parse()
- Fixed wrong code in find_field_in_natural_join() where real_item() was
set when it shouldn't
- ER_ERROR_ON_RENAME was used with extra arguments.
- Removed some (wrong) ER_OUTOFMEMORY, as alloc_root will already
give the error.
TODO:
- Check possible unsafe casts in plugin/auth_examples/qa_auth_interface.c
- Change code to not modify LEX_CSTRING for database name
(as part of lower_case_table_names)
Define my_thread_id as an unsigned type, to avoid mismatch with
ulonglong. Change some parameters to this type.
Use size_t in a few more places.
Declare many flag constants as unsigned to avoid sign mismatch
when shifting bits or applying the unary ~ operator.
When applying the unary ~ operator to enum constants, explictly
cast the result to an unsigned type, because enum constants can
be treated as signed.
In InnoDB, change the source code line number parameters from
ulint to unsigned type. Also, make some InnoDB functions return
a narrower type (unsigned or uint32_t instead of ulint;
bool instead of ibool).
The issue was that JOIN::rollup_write_data() used
JOIN::tmp_table_param::[start_]recinfo, which had uninitialized data.
These fields have uninitialized data, because JOIN::tmp_table_param
currently only stores some grouping-related data fields. The data about
the work (temporary) tables themselves is stored in
join->join_tab[...].tmp_table_param.
The fix is to make JOIN::rollup_write_data follow this convention
and look at the right TMP_TABLE_PARAM object
JOIN_CACHE's were initialized in check_join_cache_usage()
from make_join_readinfo(). After that make_join_readinfo() was looking
whether it's possible to use keyread. Later, after make_join_readinfo(),
optimizer decided whether to use filesort. And even later, at the
execution time, from join_read_first(), keyread was actually enabled.
The problem is, that if a query uses a vcol, base columns that it
depends on are automatically added to the read_set - because they're
needed to calculate the vcol. But if we're doing keyread, vcol is taken
from the index, not calculated, and base columns do not need to be
in the read set (even should not be - as they aren't getting values).
The bug was that JOIN_CACHE used read_set with base columns,
they were not read because of keyread, so it was caching garbage.
So read_set is only known after the keyread was decided. And after the
filesort was decided, as filesort doesn't use keyread. But
check_join_cache_usage() needs to be done in make_join_readinfo(),
as the code below depends on these checks,
Fix: keep JOIN_CACHE checks where they were, but move initialization
down to the very end of JOIN::optimize_inner. If keyread was enabled,
update the read_set to include only columns that are part of the index.
Copy the keyread logic from join_read_first() to happen at optimize time.
- Tabular EXPLAIN now prints "RECURSIVE UNION".
- There is a basic implementation of EXPLAIN FORMAT=JSON.
- it produces "recursive_union" JSON struct
- No other details or ANALYZE support, yet.
Temporary tables created for recursive CTE
were instantiated at the prepare phase. As
a result these temporary tables missed
indexes for look-ups and optimizer could not
use them.
Variant #4 of the fix.
Make ORDER BY optimization functions take into account multiple
equalities. This is done in several places:
- remove_const() checks whether we can sort the first table in the
join, or we need to put rows into temp.table and then sort.
- test_if_order_by_key() checks whether there are indexes that
can be used to produce the required ordering
- make_unireg_sortorder() constructs sort criteria for filesort.
This bug revealed a serious problem: if the same partition list
was used in two window specifications then the temporary table created
to calculate window functions contained fields for two identical
partitions. This problem was fixed as well.
- Rename Window_funcs_computation to Window_funcs_computation_step
- Introduce Window_func_sort which invokes filesort and then
invokes computation of all window functions that use this ordering.
- Expose Window functions' sort operations in EXPLAIN|ANALYZE FORMAT=JSON
Added class Window_funcs_computation, with setup() method to setup
execution, and exec() to run window function computation.
setup() is currently trivial. In the future, it is expected to optimize
the number of sorting operations and passes that are done over the temp.
table.
filesort and init_read_record() for the same table.
This will simplify code for WINDOW FUNCTIONS (MDEV-6115)
- Filesort_info renamed to SORT_INFO and moved to filesort.h
- filesort now returns SORT_INFO
- init_read_record() now takes a SORT_INFO parameter.
- unique declaration is moved to uniques.h
- subselect caching of buffers is now more explicit than before
- filesort_buffer is now reusable even if rec_length has changed.
- filsort_free_buffers() and free_io_cache() calls are removed
- Remove one malloc() when using get_addon_fields()
Other things:
- Added --debug-assert-on-not-freed-memory option to make it easier to
debug some not-freed-memory issues.
These do not have any meaning after MDEV-8646. Their only valid values are
- table_access_tabs= join_tab;
- top_table_access_tabs_count= top_join_tab_count;
Disable the code that attempts to group window functions together
by their PARTITION BY / ORDER BY clauses, because
1. It doesn't work: when I issue a query with just one window function,
and no indexes on the table, filesort is not invoked at all.
2. It is not possible to check that it works currently.
Add my own code that does invoke filesort() for each window function.
- Hopefully the sort criteria is right
- Debugging shows that filesort operates on {sort_key, rowid} pairs (OK)
- We can read the filesort rowid result in order.
"Re-factor the code for post-join operations".
The patch mainly contains the code ported from mysql-5.6 and
created for two essential architectural changes:
1. WL#5558: Resolve ORDER BY execution method at the optimization stage
2. WL#6071: Inline tmp tables into the nested loops algorithm
The first task was implemented for mysql-5.6 by Ole John Aske.
It allows to make all decisions on ORDER BY operation at the optimization
stage.
The second task implemented for mysql-5.6 by Evgeny Potemkin adds JOIN_TAB
nodes for post-join operations that require temporary tables. It allows
to execute these operations within the nested loops algorithm that used to
be used before this task only for join queries. Besides these task moves
all planning on the execution of these operations from the execution phase
to the optimization phase.
Some other re-factoring changes of mysql-5.6 were pulled in, mainly because
it was easier to pull them in than roll them back. In particular all
changes concerning Ref_ptr_array were incorporated.
The port required some changes in the MariaDB code that concerned the
functionality of EXPLAIN and ANALYZE. This was done mainly by Sergey
Petrunia.
This task is to allow storage engines that can execute GROUP BY or
summary queries efficiently to intercept a full query or sub query from
MariaDB and deliver the result either to the client or to a temporary
table for further processing.
- Added code in sql_select.cc to intercept GROUP BY queries.
Creation of group_by_handler is done after all optimizations to allow
storage engine to benefit of an optimized WHERE clause and suggested
indexes to use.
- Added group by handler to sequence engine and a group_by test suite as
a way to test the new interface.
- Intercept EXPLAIN with a message "Storage engine handles GROUP BY"
libmysqld/CMakeLists.txt:
Added new group_by_handler files
sql/CMakeLists.txt:
Added new group_by_handler files
sql/group_by_handler.cc:
Implementation of group_by_handler functions
sql/group_by_handler.h:
Definition of group_by_handler class
sql/handler.h:
Added handlerton function to create a group_by_handler, if the storage
engine can intercept the query.
sql/item_cmpfunc.cc:
Allow one to evaluate item_equal any time.
sql/sql_select.cc:
Added code to intercept GROUP BY queries
- If all tables are from the same storage engine and the query is
using sum functions, call create_group_by() to check if the storage
engine can intercept the query.
- If yes:
- create a temporary table to hold a GROUP_BY row or result
- In do_select() intercept normal query execution by instead
calling the group_by_handler to get the result
- Intercept EXPLAIN
sql/sql_select.h:
Added handling of group_by_handler
Added caching of the original join tab (needed for cleanup after
group_by handler)
storage/sequence/mysql-test/sequence/group_by.result:
Test group_by_handler interface
storage/sequence/mysql-test/sequence/group_by.test:
Test group_by_handler interface
storage/sequence/sequence.cc:
Added simple group_by_engine for handling COUNT(*) and
SUM(primary_key). This was done as a test of the group_by_handler
interface
- Added mem_root to all calls to new Item
- Added private method operator new(size_t size) to Item to ensure that
we always use a mem_root when creating an item.
This saves use once call to current_thd per Item creation
Added mandatory thd parameter to Item (and all derivative classes) constructor.
Added thd parameter to all routines that may create items.
Also removed "current_thd" from Item::Item. This reduced number of
pthread_getspecific() calls from 290 to 177 per OLTP RO transaction.
- Make semi-join optimizer not to choose LooseScan
when 1) the index is not covered and 2) full index
scan will be required.
- Make sure that the code in make_join_select() that may change
full index scan into a range scan is not invoked when the table
uses full scan.
- Changed ER(ER_...) to ER_THD(thd, ER_...) when thd was known or if there was many calls to current_thd in the same function.
- Changed ER(ER_..) to ER_THD_OR_DEFAULT(current_thd, ER...) in some places where current_thd is not necessary defined.
- Removing calls to current_thd when we have access to thd
Part of this is optimization (not calling current_thd when not needed),
but part is bug fixing for error condition when current_thd is not defined
(For example on startup and end of mysqld)
Notable renames done as otherwise a lot of functions would have to be changed:
- In JOIN structure renamed:
examined_rows -> join_examined_rows
record_count -> join_record_count
- In Field, renamed new_field() to make_new_field()
Other things:
- Added DBUG_ASSERT(thd == tmp_thd) in Item_singlerow_subselect() just to be safe.
- Removed old 'tab' prefix in JOIN_TAB::save_explain_data() and use members directly
- Added 'thd' as argument to a few functions to avoid calling current_thd.
Fixed several optimizer issues relatied to GROUP BY:
a) Refering to a SELECT column in HAVING sometimes calculated it twice, which caused problems with non determinstic functions
b) Removing duplicate fields and constants from GROUP BY was done too late for "using index for group by" optimization to work
c) EXPLAIN SELECT ... GROUP BY did wrongly show 'Using filesort' in some cases involving "Using index for group-by"
a) was fixed by:
- Changed last argument to Item::split_sum_func2() from bool to int to allow more flags
- Added flag argument to Item::split_sum_func() to allow on to specify if the item was in the SELECT part
- Mark all split_sum_func() calls from SELECT with SPLIT_SUM_SELECT
- Changed split_sum_func2() to do nothing if called with an argument that is not a sum function and doesn't include sum functions, if we are not an argument to SELECT.
This ensures that in a case like
select a*sum(b) as f1 from t1 where a=1 group by c having f1 <= 10;
That 'a' in the SELECT part is stored as a reference in the temporary table togeher with sum(b) while the 'a' in having isn't (not needed as 'a' is already a reference to a column in the result)
b) was fixed by:
- Added an extra remove_const() pass for GROUP BY arguments before make_join_statistics() in case of one table SELECT.
This allowes get_best_group_min_max() to optimize things better.
c) was fixed by:
- Added test for group by optimization in JOIN::exec_inner for
select->quick->get_type() == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX
item.cc:
- Simplifed Item::split_sum_func2()
- Split test to make them faster and easier to read
- Changed last argument to Item::split_sum_func2() from bool to int to allow more flags
- Added flag argument to Item::split_sum_func() to allow on to specify if the item was in the SELECT part
- Changed split_sum_func2() to do nothing if called with an argument that is not a sum function and doesn't include sum functions, if we are not an argument to SELECT.
opt_range.cc:
- Simplified get_best_group_min_max() by calcuating first how many group_by elements.
- Use join->group instead of join->group_list to test if group by, as join->group_list may be NULL if everything was optimized away.
sql_select.cc:
- Added an extra remove_const() pass for GROUP BY arguments before make_join_statistics() in case of one table SELECT.
- Use group instead of group_list to test if group by, as group_list may be NULL if everything was optimized away.
- Moved printing of "Error in remove_const" to remove_const() instead of having it in caller.
- Simplified some if tests by re-ordering code.
- update_depend_map_for_order() and remove_const() fixed to handle the case where make_join_statistics() has not yet been called (join->join_tab is 0 in this case)
This is MDEV-7601, including it's sub tasks MDEV-7594, MDEV-7555, MDEV-7590, MDEV-7581, MDEV-7589
The problem was that select_lex->non_agg_fields was not properly reset for re-execution and this caused an overwrite of a random memory position.
The fix was move non_agg_fields from select_lext to JOIN, which is properly reset.
Split first_breadth_first_tab() into
JOIN::first_breadth_first_optimization_tab() and
JOIN::first_breadth_first_execution_tab().
This allows to eliminate function call and one condition. Adjusted callers
accordingly.
Overhead change:
first_breadth_first_tab() 0.07% -> out of radar
next_breadth_first_tab() 0.04% -> 0.04%
JOIN::cleanup() 0.15% -> 0.11%
JOIN::save_explain_data_intern() 0.28% -> 0.24%
Step#5: changing the function remove_eq_conds() into a virtual method in Item.
It removes 6 virtual calls for Item_func::type(), and adds only 2
virtual calls for Item***::remove_eq_conds().
sql_alloc() has additional costs compared to direct mem_root allocation:
- function call: it is defined in a separate translation unit and can't be
inlined
- it needs to call pthread_getspecific() to get THD::mem_root
It is called dozens of times implicitely at least by:
- List<>::push_back()
- List<>::push_front()
- new (for Sql_alloc derived classes)
- sql_memdup()
Replaced lots of implicit sql_alloc() calls with direct mem_root allocation,
passing through THD pointer whenever it is needed.
Number of sql_alloc() calls reduced 345 -> 41 per OLTP RO transaction.
pthread_getspecific() overhead dropped 0.76 -> 0.59
sql_alloc() overhed dropped 0.25 -> 0.06
