The problem: consider a query in form:
t1 JOIN (SELECT a FROM t2 GROUP BY a) dt ON dt.a=t1.a
After fix for MDEV-30877, the optimizer computes the number of GROUP BY
groups that we will have in the materialized derived table. It is saved
into TABLE(dt)->used_stat_records and propagates into
TABLE(dt)->opt_range_condition_rows.
On the other hand, the Split-Materialized optimization tries to compute
the number of rows it expects to find in the Split-Materialized derived
table.
These two numbers may come into disagreement: Split-Materialized may
produce a higher number, save it into JOIN_TAB::records and cause this
assertion in apply_selectivity_for_table() to fail:
double table_records= rows2double(s->records);
DBUG_ASSERT(table_records == 0 ||
sel <= s->table->opt_range_condition_rows /
table_records);
We will have sel=1.0 but opt_range_condition_rows < table_records.
Cardinality computations in Split-Materialized code omit a lot, for example
they omit the fact GROUP BY operation will reduce the number or rows.
For now, use a trivial fix: make sure Split-Materialized's estimate of
JOIN_TAB::records doesn't exceed the derived table size.
Fixes unnecessary filesort on derived tables when ordered/grouped by a
field in the key. The data is inherently sorted, wrapping the result set
in a filesort is redundant.
Rename cloning methods of class Item and its descendants
in the following way:
(from) (to)
do_build_clone -> deep_copy
build_clone -> deep_copy_with_checks
do_get_copy -> shallow_copy
get_copy -> shallow_copy_with_checks
to better reflect their functionality.
Also make Item::deep_copy() and shallow_copy() protected.
Outside users should call deep_copy_with_checks()
and shallow_copy_with_checks().
Wrong result is produced when split-materialized optimization is used for
grouping with order by and limit.
The fix is to not let Split-Materialized optimization to happen
when the sub-query has an ORDER BY with LIMIT, by returning FALSE early
in the method opt_split.cc#check_for_splittable_materialized()
However, with just the above change, there is a side-effect of
NOT "using index for group by" in the scenario when
all the following conditions are met: -
1. The query has derived table with GROUP BY and ORDER BY LIMIT
2. joined in a way that would allow Split-Materialized
if ORDER BY LIMIT wasn't present
3. An index suitable for using "index for group-by"
4. No where clause so that, "using for group by" is applicable,
but the index is not included in "possible_keys".
The reason being, join_tab's "keys" field wasn't being set in
sql_select.cc#make_join_select(). So, made this change as well
as part of this PR.
- Removed duplicate words, like "the the" and "to to"
- Removed duplicate lines (one double sort line found in mysql.cc)
- Fixed some typos found while searching for duplicate words.
Command used to find duplicate words:
egrep -rI "\s([a-zA-Z]+)\s+\1\s" | grep -v param
Thanks to Artjoms Rimdjonoks for the command and pointing out the
spelling errors.
add_ext_keyuse_for_splitting assumed the added_key_field->cond was
of type Item_func_eq. In 11.8 there are cases where this is actually
of type Item_func_equal in some cases which is not a class descendant
of Item_func_eq.
The only use of this class is accessing its arguments() method
which is defined all the way up the hierarchy of both Item_func_equal
and Item_func_eq in the class Item_args.
To resolve future mis-castings of this method we assume the topmost
class of Item_args.
This is backported from MDEV-37057 for MariaDB 10.11
When populating the structure spl_opt_info for a TABLE, and evaluating a
key_field for inclusion in spl_opt_info->added_key_fields, the null_rejecting
attribute may be incorrectly set. Originally, this attribute was
assumed to be TRUE, then it was changed
Item *real= key_field->val->real_item();
if ((real->type() == Item::FIELD_ITEM) &&
((Item_field*)real)->field->maybe_null())
added_key_field->null_rejecting= true;
else
added_key_field->null_rejecting= false;
which also incorrectly assumed that the added key field depended on
whether the field was able to set to null.
The correct setting for this attribute is simply to pass it through from
the key being evaluated.
The result of an incorrect value is, in this test case, incorrect
equality conditions being pushed into our (lateral) derived table,
excluding rows that might legitimately contain NULL and thus returning
a wrong result.
Reviewed by Dave Gosselin (dave.gosselin@mariadb.com), PR#4236
main/statistics_json.result is updated for f8ba5ced55 (MDEV-36099)
The test uses 'delete from t1' in many places and then populates
the table again. The natural order of rows in a MyISAM table is well
defined and the test was implicitly relying on that.
before f8ba5ced55 delete was deleting rows one by one, using
ha_myisam::delete_row() because the connection was stuck in rbr mode.
This caused rows to be shown in the reverse insertion order (because of
the delete link list).
MDEV-36099 fixes this bug and the server now correctly uses
ha_myisam::delete_all_rows(). This makes rows to be shown in the
insertion order as expected.
SplM_opt_info::last_refills is never set, so remove it. It was used in
In JOIN_TAB::choose_best_splitting(), use local variable "refills" there,
instead.
The logic in the function is that we compute
startup_cost= refills * spl_plan->cost;
only when we have a splitting query plan which implies "refills" was set
accordingly.
Also
- Added a comment about what "refills" is.
- Updated derived_cond_pushdown.result: the change makes the
Split-Materialized plans more expensive, so the join order changes from
t3, <split-materialized(outer_ref)> to <split-materialized>, t3
Split-Materialized optimization in add_keyuses_for_splitting() pushes
"derived_split_table.field=right_expr" down into the derived_split_table.
This requires that right_expr's attributes are updated. Typically it
has references to tables in the parent select, those become references
to outside in the child select.
This was done with the
right_expr->walk(&Item::set_fields_as_dependent_processor, ...)
call. The function was implemented for Item_field objects. However it
was not implemented for Item_direct_view_ref objects.
If an Item_direct_view_ref object didn't have an Item_field inside
(e.g. view column referred to a constant) this would mean that
used_tables() attribute would not be updated and right_expr will end
up with wrong used_tables(), which could eventually cause a crash
as it looked like a reference to non-existant table.
Fixed by adding Item_direct_view_ref::set_fields_as_dependent_processor()
with the same logic as in Item_field.
During the optimization phase, when determining whether or not to materialize
a derived table all at once or in groups (split materialization), all key
equalities that reference the derived table have their cond Items pushed into
JOIN::spl_opt_info->inj_cond_list.
From there they are filtered (tables in join prefix) and injected into the
JOIN conditions (in JOIN::inject_best_splitting_cond()), where they might
be removed because they are involved ref access and they aren't needed.
These pushed items conditions were always Item_func_eq, whether or or
the key usage specifies null safe equality (<=>) or not.
The fix is to create an Item_func_equal condition when the key equality
is specified using <=>.
approved by Sergei Petrunia (sergey@mariadb.com) PR#4198
When populating the structure spl_opt_info for a TABLE, and evaluating a
key_field for inclusion in spl_opt_info->added_key_fields, the null_rejecting
attribute may be incorrectly set. Originally, this attribute was
assumed to be TRUE, then it was changed
Item *real= key_field->val->real_item();
if ((real->type() == Item::FIELD_ITEM) &&
((Item_field*)real)->field->maybe_null())
added_key_field->null_rejecting= true;
else
added_key_field->null_rejecting= false;
which also incorrectly assumed that the added key field depended on
whether the field was able to set to null.
The correct setting for this attribute is simply to pass it through from
the key being evaluated.
The result of an incorrect value is, in this test case, incorrect
equality conditions being pushed into our (lateral) derived table,
excluding rows that might legitimately contain NULL and thus returning
a wrong result.
Approved by Sergei Petrunia, PR#4140
Support for optimizer hints NO_SPLIT_MATERIALIZED and
SPLIT_MATERIALIZED. These hints allow fine-grained control
of the "lateral derived" optimization within a query.
Introduces new overload of hint_table_state function which
tells both a hint's value as well as whether it is present.
This is useful to disambiguate cases that the other version
of hint_table_state cannot, such as when a hint is forcing
a behavior in the optimizer that it would not normally do
and the corresponding optimizer switch is enabled.
Partial commit of the greater MDEV-34348 scope.
MDEV-34348: MariaDB is violating clang-16 -Wcast-function-type-strict
The functions queue_compare, qsort2_cmp, and qsort_cmp2
all had similar interfaces, and were used interchangable
and unsafely cast to one another.
This patch consolidates the functions all into the
qsort_cmp2 interface.
Reviewed By:
============
Marko Mäkelä <marko.makela@mariadb.com>
create templates
thd->alloc<X>(n) to use instead of (X*)thd->alloc(sizeof(X)*n)
and the same for thd->calloc(). By the default the type is char,
so old usage of thd->alloc(size) works too.
Due to this bug a wrong result might be expected from queries with
an IN subquery predicate in the WHERE clause and a derived table in the
FROM clause to which split optimization could be applied.
The function JOIN::fix_all_splittings_in_plan() used the value of the
bitmap JOIN::sjm_lookup_tables() such as it had been left after the
search for the best plan for the select containing the splittable
derived table. That value could not be guaranteed to be correct. So the
recalculation of this bitmap is needed to exclude the plans with key
accesses from SJM lookup tables.
Approved by Igor Babaev <igor@maridb.com>
This bug could affect queries with IN subqueries in WHERE clause and using
derived tables to which split optimization potentially could be applied.
When looking for the best split of a splittable derived table T any key
access from a semi-join materialized table used for lookups S to table T
must be excluded from consideration because in the current implementation
of such tables as S the values from its records cannot be used to access
other tables.
Approved by Oleksandr Byelkin <sanja@mariadb.com>
