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