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

Pushdown conditions into non-mergeable views/derived tables

Details

    Description

      It is possible to do condition pushdown into non-mergable VIEWs or derived tables.

      Example:

      select ...
      		 
      from 
        (select col1, max(col2) as max_val 
         from t2 
         group by t1 ) TBL
      		 
      where 
         col1 !='foo' and max_val > 100

      here, both parts of top-level select's WHERE clause can be pushed into derived table's HAVING clause.

      Implementation challenges

      Moving condition from one select into another changes the context which condition is used in.
      Table condition pushdown produces strictest-possible conditions, but has a property that certain part of condition can be attached to multiple tables.
      Doing the same thing here will cause trouble: if the same part of the condition is both in subquery's HAVING and in upper select's WHERE, it should e.g. automagically changes its used_tables() depending on what context we're looking at it from.
      The solution to this is to do limited pushdown: only push down expressions that we can completely remove from upper query's WHERE.

      Pushdown may also require adjustments to ref_pointer_array. Sanja and Igor seem to understand the details of this.

      Attachments

        Issue Links

          is part of

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-10137 Providing compatibility to other databases

          • Major - Major loss of function.
          • Open
          relates to

          Task - A task that needs to be done. MDEV-7486 Condition pushdown from HAVING into WHERE

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10884 Wrong query result with condition pushdown into derived table with ORDER BY ... LIMIT

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21388 Wrong result of DAYNAME()=xxx in combination with condition_pushdown_for_derived=on

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-4517 Upper query conditions push down to (aggregated) VIEWs

          • Major - Major loss of function.
          • Closed
          links to

          Web Link GitHub Pull Request #216

          Activity

            People

              igor Igor Babaev (Inactive)
              psergei Sergei Petrunia
              Votes:
              8 Vote for this issue
              Watchers:
              9 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.