[MDEV-25969] Condition pushdown into derived table doesn't work if select list uses SP Created: 2021-06-20  Updated: 2021-10-11  Resolved: 2021-06-30

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.40, 10.3.31, 10.4.21, 10.5.12, 10.6.3

Type: Bug Priority: Critical
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Blocks
PartOf

 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.

 Comments   
Comment by Sergei Petrunia [ 2021-06-22 ]

http://lists.askmonty.org/pipermail/commits/2021-June/014657.html

Comment by Sergei Petrunia [ 2021-06-24 ]

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

Comment by Igor Babaev [ 2021-06-24 ]

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(

Comment by Igor Babaev [ 2021-06-24 ]

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.

Comment by Sergei Petrunia [ 2021-06-25 ]

http://lists.askmonty.org/pipermail/commits/2021-June/014663.html . igor, please review.

Comment by Sergei Petrunia [ 2021-06-29 ]

Next variant, the patch against 10.2: http://lists.askmonty.org/pipermail/commits/2021-June/014667.html . igor, please review.

Comment by Sergei Petrunia [ 2021-06-29 ]

It is also in bb-10.2-mdev25969

Comment by Igor Babaev [ 2021-06-30 ]

Ok to push into 10.2 after applying some minor changes in comments

Generated at Thu Feb 08 09:41:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.