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

Merging non-mergeable views into referring selects

    XMLWordPrintable

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Optimizer
    • None

    Description

      Consider the following view:

      create view v1 as select a, sum(b) as s from t1 where a > 0 group by a;
      

      This is a non-mergeable view, because it contains GROUP BY. In the current code this view is never merged. However it's obvious that if this view is used in the select

      select * from v1 where a < 10 and s > 100
      

      the view could be merged into the referring select to produce

      select a, sum(b) from t1 where a > 0 and a < 10 group by a having sum(b) > 100
      

      The select to which a non-mergeable view is merged must refer only to this view in its FROM list. GROUP BY and HAVING clauses, DISTINCT, aggregate functions, window functions cannot be used in this select at the top level.
      Merge for the following non-mergeable views will be supported:

      • selects with GROUP BY
      • selects with DISTINCT
      • selects without GROUP BY with window functions (in this case the select to where to merge must refer only to partition fields in its WHERE condition)
      • unions of selects with only WHERE clauses or above mentioned selects.

      Attachments

        Activity

          People

            igor Igor Babaev
            igor Igor Babaev
            Votes:
            2 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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