Details
Critical Description
A simplified testcase without any customer data:
create function myfunc(a int) returns int DETERMINISTIC return (a+1); |
|
|
create table t1 ( |
pk int primary key, |
a int, |
b int, |
key(a) |
);
|
insert into t1 select seq,seq,seq from seq_1_to_1000; |
create view v1 as |
select
|
t1.a as col1, |
myfunc(t1.b) as col2 |
from
|
t1;
|
|
|
create view v2 as |
select
|
t1.a as col1, |
myfunc(t1.b) as col2 |
from
|
t1;
|
create view v3 as |
select col2, col1 from v1 |
union all |
select col2, col1 from v2; |
I get this both on 10.5.11 and 10.2.14:
explain select * from v3 where col1=123; |
+------+-------------+------------+------+---------------+------+---------+-------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+------------+------+---------------+------+---------+-------+------+-------------+ |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where | |
| 2 | DERIVED | t1 | ref | a | a | 5 | const | 1 | |
|
| 3 | UNION | t1 | ref | a | a | 5 | const | 1 | | |
+------+-------------+------------+------+---------------+------+---------+-------+------+-------------+ |
add col2=321 into the WHERE clause and on 10.5.11 I get this:
explain select * from v3 where col1=123 and col2=321; |
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+ |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2000 | Using where | |
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | | |
| 3 | UNION | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | | |
+------+-------------+------------+------+---------------+------+---------+------+------+-------------+ |
while on 10.2.14 table t1 is still accessed through ref access.
Attachments
Activity
Review input from the Optimizer Call:
We could change the pushdown function to only push the top-level conjuncts that could be cloned.
Decided against using this solution
Instead of check_non_cloneable_processor, can one use check whether item->build_clone function is the original Item::build_clone or overloaded?
A: I don't think C++ allows this. Here's an example attempt https://gist.github.com/spetrunia/39171079b4c0d680d63ae60f1ac274f1
The following patch applied to the current 10.2 fixes the problem:
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
|
index 3ab9384..d8d3716 100644
|
--- a/sql/sql_derived.cc
|
+++ b/sql/sql_derived.cc
|
@@ -1192,6 +1192,40 @@ bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived)
|
}
|
|
|
+static
|
+Item *get_clonable_extracted_cond_for_where(THD *thd,
|
+ Item *cond,
|
+ st_select_lex *sl)
|
+{
|
+ if (cond->type() != Item::COND_ITEM ||
|
+ ((Item_cond*) cond)->functype() != Item_func::COND_AND_FUNC)
|
+ return cond->transform(thd,
|
+ &Item::derived_field_transformer_for_where,
|
+ (uchar*) sl);
|
+ List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
|
+ Item *item;
|
+ while ((item=li++))
|
+ {
|
+ Item *new_item= item->transform(thd,
|
+ &Item::derived_field_transformer_for_where,
|
+ (uchar*) sl);
|
+ if (!new_item)
|
+ li.remove();
|
+ else
|
+ li.replace(new_item);
|
+ }
|
+ switch (((Item_cond*) cond)->argument_list()->elements)
|
+ {
|
+ case 0:
|
+ return 0;
|
+ case 1:
|
+ return ((Item_cond*) cond)->argument_list()->head();
|
+ default:
|
+ return cond;
|
+ }
|
+}
|
+
|
+
|
/**
|
@brief
|
Extract the condition depended on derived table/view and pushed it there
|
@@ -1287,9 +1321,10 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
|
if (!sl->join->group_list && !sl->with_sum_func)
|
{
|
/* extracted_cond_copy is pushed into where of sl */
|
- extracted_cond_copy= extracted_cond_copy->transform(thd,
|
- &Item::derived_field_transformer_for_where,
|
- (uchar*) sl);
|
+ extracted_cond_copy=
|
+ get_clonable_extracted_cond_for_where(thd,
|
+ extracted_cond_copy,
|
+ sl);
|
if (extracted_cond_copy)
|
{
|
extracted_cond_copy->walk(
|
Sergey,
The problem with the check_non_cloneable_processor(void *arg) is that we have a lot of Item classes for which we have to add implementation of this processor. Foe each class for which get_copy() returns 0 we have to add such processor.
http://lists.askmonty.org/pipermail/commits/2021-June/014663.html . igor, please review.
Next variant, the patch against 10.2: http://lists.askmonty.org/pipermail/commits/2021-June/014667.html . igor, please review.
Ok to push into 10.2 after applying some minor changes in comments
http://lists.askmonty.org/pipermail/commits/2021-June/014657.html