maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Varun,

On Tue, Jan 23, 2018 at 10:49:37AM +0530, Varun wrote:
> revision-id: b960f68b102ec72cf75252b357760e99c5dffc18 (mariadb-5.5.56-144-gb960f68b102)
> parent(s): a7a4519a40c58947796c6d9b2e4e58acc18aeef8
> author: Varun Gupta
> committer: Varun Gupta
> timestamp: 2018-01-23 10:31:02 +0530
> message:
> 
> MDEV-11274: Executing EXPLAIN of complex query over join limit causes server to crash
> 
> For this case we have a view that is mergeable but we are not able to merge it in the
> parent select because that would exceed the maximum tables allowed in the join list, so we
> materialise this view
> TABLE_LIST::dervied is NULL for such views, it is only set for views which have ALGORITHM=TEMPTABLE
> Fixed by using SELECT_LEX_UNIT representing the result of the query within the view definition
...

> diff --git a/sql/table.cc b/sql/table.cc
> index 9cade76cb78..8f377d5bda8 100644
> --- a/sql/table.cc
> +++ b/sql/table.cc
> @@ -6817,9 +6817,8 @@ int TABLE_LIST::fetch_number_of_rows()
>    if (jtbm_subselect)
>      return 0;
>    if (is_materialized_derived() && !fill_me)
> -
>    {
> -    table->file->stats.records= ((select_union*)derived->result)->records;
> +    table->file->stats.records= ((select_union*)get_unit()->result)->records;
>      set_if_bigger(table->file->stats.records, 2);

So, regular VIEWs with algorithm=TEMPTABLE have 

  derived == view->unit

This is set in mysql_make_view():
    ...
    table->derived_type= VIEW_ALGORITHM_TMPTABLE;
    DBUG_PRINT("info", ("algorithm: TEMPORARY TABLE"));
    view_select->linkage= DERIVED_TABLE_TYPE;
    ...

However, the view in the example has algorithm=MERGE so the execution doesn't
reach that point.
The decision to materialized is made in mysql_derived_merge. It calls 

  derived->set_materialized_derived();

which calls 
   
   set_check_materialized();

I have discussed this with Sanja, and the outcome is that a better solution
would be to set TABLE_LIST::derived when we decide that this view is going to
be handled with temp.table strategy.

set_check_materialized() seems to be the right place to do it (although its
name looks confusing. What does one "check"? Probably the name is a leftover 
from some feature backport from MySQL).


Could you change the patch to set TABLE_LIST::derived and see if that works?

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