Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19831

Need to clean up temporarry table in select handler. Optimize the way select handler search for tables and rewrites the query.

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.4.6
    • 10.4.8
    • OTHER
    • None

    Description

      There are number of defficiences that should be addressed in 10.4.

      • Pushdown_select instance doesn't clean up the temporary table created for Select_handler
      • The loop in SELECT_LEX::find_select_handler() should iterater of TABLE_LIST from next_global and not next_local. Then subquery tables' handlertons could be searched for select handler capabilities
      • JOIN::optimize_inner() should be called before Select_handler::init() in JOIN::optimize(). If Select_handler::init() is called before JOIN::optimize() then processing of statements like the one is broken since Server doesn't rewrite one of outter JOINs with inner JOIN.

        select j1_key, j6_key, j11_key from j1 right join j6 on j1_key=j6_key right join j11 on j6_key = j11_key where j6_key > 0;
        

      Attachments

        Issue Links

          Activity

            igor Igor Babaev added a comment -

            Review:

            Roman,

            The following chages are ok (more than this the first one fixes a bug)

            1.
            @@ -45,6 +45,8 @@ Pushdown_select::Pushdown_select(SELECT_LEX *sel, select_handler *h)

            Pushdown_select::~Pushdown_select()

            { + if (handler->table) + free_tmp_table(handler->thd, handler->table); delete handler; select->select_h= NULL; }

            2.
            -28551,7 +28553,7 @@ select_handler *SELECT_LEX::find_select_handler(THD *thd)

            • for (TABLE_LIST *tbl= join->tables_list; tbl; tbl= tbl->next_local)
              + for (TABLE_LIST *tbl= join->tables_list; tbl; tbl= tbl->next_global)

            The change concerning replacement of the call of select_lex->pushdown_select->init() is not ok
            You need only rewrites. Which ones? If all of them then the above call should be placed after the last re-write which is performed before this code:
            if (optimize_constant_subqueries())
            DBUG_RETURN(1);
            Yet in this case you have to be able to work with the query tree after all re-writes have been done.
            If you won't be able work with this tree, but you still need some rewrites then situation becomes more complicated.
            In this case the call select_lex->pushdown_select->init() is not moved but it returns in bitmap of re-writes you need (in some member of the select_handler class that has to be added). Then immediately after this call you have to call select_lex->pushdown_select->optimize() that has to take care of all re-writes you needed (now it does not do it of course).
            It's a big change.
            So would suggest first to push in one commit 1&2 that are approved.

            igor Igor Babaev added a comment - Review: Roman, The following chages are ok (more than this the first one fixes a bug) 1. @@ -45,6 +45,8 @@ Pushdown_select::Pushdown_select(SELECT_LEX *sel, select_handler *h) Pushdown_select::~Pushdown_select() { + if (handler->table) + free_tmp_table(handler->thd, handler->table); delete handler; select->select_h= NULL; } 2. -28551,7 +28553,7 @@ select_handler *SELECT_LEX::find_select_handler(THD *thd) for (TABLE_LIST *tbl= join->tables_list; tbl; tbl= tbl->next_local) + for (TABLE_LIST *tbl= join->tables_list; tbl; tbl= tbl->next_global) The change concerning replacement of the call of select_lex->pushdown_select->init() is not ok You need only rewrites. Which ones? If all of them then the above call should be placed after the last re-write which is performed before this code: if (optimize_constant_subqueries()) DBUG_RETURN(1); Yet in this case you have to be able to work with the query tree after all re-writes have been done. If you won't be able work with this tree, but you still need some rewrites then situation becomes more complicated. In this case the call select_lex->pushdown_select->init() is not moved but it returns in bitmap of re-writes you need (in some member of the select_handler class that has to be added). Then immediately after this call you have to call select_lex->pushdown_select->optimize() that has to take care of all re-writes you needed (now it does not do it of course). It's a big change. So would suggest first to push in one commit 1&2 that are approved.
            drrtuy Roman added a comment -

            I removed the questionable change and made a pull request for the patch. Plz merge it.

            drrtuy Roman added a comment - I removed the questionable change and made a pull request for the patch. Plz merge it.
            drrtuy Roman added a comment -

            Review has been done by igor

            drrtuy Roman added a comment - Review has been done by igor
            drrtuy Roman added a comment -

            The patch has been merged into 10.4

            drrtuy Roman added a comment - The patch has been merged into 10.4

            People

              drrtuy Roman
              drrtuy Roman
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.