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