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

Upper query conditions push down to (aggregated) VIEWs

    XMLWordPrintable

    Details

      Description

      create table employee (dept int, emp_no int, name varchar(64));
      create table salary (emp_no int, year int, month int, month_salary int);
      create view v (emp_no, year, income) as 
      select 
        emp_no, year, sum(month_salary) as income_per_year 
      from
         salary 
      group by 
        emp_no, year;
       
      select 
        name 
      from 
        employee t, v 
      where 
        v.emp_no between 1 and 1000 and 
        v.year=2012 and 
        v.emp_no=t.emp_no and income>100000;
      

      Now above query first materialize whole view an then start joining, when the size of materialized table could be reduced by pushing conditions related to the view it its HAVING clause.

      Problems:
      1. 'strip' view field references when pushed into lower SELECT (SELECT of the view)
      2. ref_pointer_array predefined size

      Implementation:
      1. Clone() method should be done for most (all) items to be able to construct expression for pusing down to the VIEW
      2. If we will construct whole item tree then fields for view should be substituted by its translation table entry (also cloned?).
      3. It is better to avoid pushing subqueries down to the VIEW because it requre resorting SELECT_LEX tree.
      4. for UNION based VIEW condition can be pushed to fake select HAVING.
      5. All transformation shouldbe rolled back before next execution.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              sanja Oleksandr Byelkin
              Votes:
              4 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.