Hi Varun, On Thu, May 24, 2018 at 03:17:02AM +0530, Varun wrote:

revision-id: de722585c1b2ad2af99bd1749e8566ea77838764 (mariadb-5.5.56-219-gde722585c1b) parent(s): 1ada4afb0a51f7283b6187a95019ec2cb80c8a0b author: Varun Gupta committer: Varun Gupta timestamp: 2018-05-24 03:09:42 +0530 message:

MDEV-16225: wrong resultset from query with semijoin=on

For non-semi-join subquery optimization we do a cost based decision between Materialisation and IN -> EXIST transformation. The issue in this case is that for IN->EXIST transformation we run JOIN::reoptimize with the IN->EXISt conditions and we come up with a new query plan. But when we compare the cost with Materialization, we make the decision to chose Materialization so we need to restore the query plan for Materilization. The restoring of query plan is not handled correctly.

diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d6d269a700f..b8d43b9797e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -23805,6 +23805,22 @@ void JOIN::restore_query_plan(Join_plan_state *restore_from) }

} + else + { + /* + After running JOIN::reoptimize for IN->EXISTS + transformation we might add elements to the keyuse array, + The original query plan was that the keyuse array had no elements, so we + should restore that state here. + */ + while(keyuse.elements) + delete_dynamic_element(&keyuse, 0);

I'm wondering, why do we need the above loop and not just delete everything with delete_dynamic(). But if I don't miss anything, this is not significant. Ok to push either the above variant or one with delete_dynamic().

+ for (uint i= 0; i < table_count; i++) + { + join_tab[i].keyuse= NULL; + join_tab[i].checked_keys.clear_all(); + } + } memcpy((uchar*) best_positions, (uchar*) restore_from->best_positions, sizeof(POSITION) * (table_count + 1)); /* Restore SJM nests */ _______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

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