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

Push conditions into materialized IN subqueries

    XMLWordPrintable

Details

    • 10.0.34, 10.1.31, 10.2.13, 10.1.32, 10.3.6-1

    Description

      The subqueries with GROUP BY are always materialized in MariaDB/MySQL.
      If such a subquery is an IN subquery

       (expr[1],...) IN (SELECT col[1], ...FROM ... GROUP BY...)
      

      and is a conjunct of the WHERE condition of the main query then for every row in the result set the following is true:

      expr[1]=col[1] AND ...
      

      Let P be a comparison predicate over expr[i]. Then the condition P(expr[i]/col[i]) can be pushed into the subquery.

      This task basically has to repeat what was done for pushing conditions into materialized views/derived tables (see MDEV-9197).

      EDIT
      optimizer_switch flag name is condition_pushdown_for_subquery

      Attachments

        Issue Links

          Activity

            People

              shagalla Galina Shalygina (Inactive)
              igor Igor Babaev
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.