maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

On Fri, May 11, 2018 at 03:08:29AM +0530, Varun wrote:
> revision-id: 108af298032cb4cea316a08c304c306268a2badf (mariadb-5.5.56-213-g108af298032)
> parent(s): 318097bb8f6e12c546b5dcd287416158209dbb39
> author: Varun Gupta
> committer: Varun Gupta
> timestamp: 2018-05-11 02:56:33 +0530
> message:
> 
> MDEV-15454: Nested SELECT IN returns wrong results
> 
> In this case we are setting the field Item_func_eq::in_eqaulity_no for the semi-join equalities.
> This helps us to remove these equalites as the inner tables are not available during parent select execution
> while the outer tables are not available during materialization phase.
> We only have it set for the equalites for the fields involved with the IN subquery
> and reset it for the equalities which do not belong to the IN subquery.
> 
> For example in case of nested IN subqueries:
> 
>     SELECT t1.a FROM t1 WHERE t1.a IN
>       (SELECT t2.a FROM t2 where t2.b IN
>           (select t3.b from t3 where t3.c=27 ))
> 
> there are two equalites involving the fields of the IN subquery
> 
> 1) t2.b = t3.b :  the field Item_func_eq::in_eqaulity_no is set when we merge the grandchild select into the child select
> 2) t1.a = t2.a :  the field Item_func_eq::in_eqaulity_no is set when we merge the child select into the parent select
> 
> But when we perform case 2) we should ensure that we reset the equalities in the child's WHERE clause.
> 
> ---
>  mysql-test/r/subselect_sj_mat.result | 46 +++++++++++++++++++++++++
>  mysql-test/t/subselect_sj_mat.test   | 49 ++++++++++++++++++++++++++
>  sql/opt_subselect.cc                 | 66 ++++++++++++++++++++++++++++++++++++
>  3 files changed, 161 insertions(+)
> 
...

> diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
> index a7edd64e68b..31213a5cf1d 100644
> --- a/sql/opt_subselect.cc
> +++ b/sql/opt_subselect.cc
> @@ -1481,6 +1543,7 @@ static int subq_sj_candidate_cmp(Item_in_subselect* el1, Item_in_subselect* el2,
>      TRUE   Out of memory error
>  */
>  
> +
>  static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
>  {
>    SELECT_LEX *parent_lex= parent_join->select_lex;
> @@ -1713,6 +1776,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
>  
>    if (subq_pred->left_expr->cols() == 1)
>    {
> +    reset_equality_number_for_subq_conds(sj_nest->sj_on_expr);
>      /* add left = select_list_element */
>      nested_join->sj_outer_expr_list.push_back(&subq_pred->left_expr);
>      /*
> @@ -1735,6 +1799,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
>    }
>    else if (subq_pred->left_expr->type() == Item::ROW_ITEM)
>    {
> +    reset_equality_number_for_subq_conds(sj_nest->sj_on_expr);
>      /*
>        disassemple left expression and add
>        left1 = select_list_element1 and left2 = select_list_element2 ...
> @@ -1759,6 +1824,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
>    }
>    else
>    {
> +    reset_equality_number_for_subq_conds(sj_nest->sj_on_expr);
>      /*
>        add row operation
>        left = (select_list_element1, select_list_element2, ...)

So, the code here is 

  if (...) 
  { 
    variant1; 
  } 
  else if (...) 
  {
    variant2;
  }
  else 
  {
    variant3;
  }

and there is the same call added to all three:

> +    reset_equality_number_for_subq_conds(sj_nest->sj_on_expr);

Can you move it out of the "if" and make it one call instead of three?

Otherwise, the patch is ok, ok to push after the above is addressed.

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog