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

"pushdown" of MIN/MAX inside UNION with WHERE and GROUP BY

    XMLWordPrintable

Details

    Description

      Even if we will not have MIN/MAX optimisation (as in MENT-693) we can get gail from early filtering by MIN/MAX pushdown.

      Transformation:

      SELECT MAX(field) FROM
         (SELECT field1 as field, field3 as group_field FROM table1
          UNION
          SELECT field2, firld4  FROM table2)
        WHERE cond(field,group_field) and other_cond()
        GROUP by group_field
      

      to

      SELECT MAX(field) FROM
         (SELECT MAX(field1) as field, field3 as group_field FROM table1 WHERE cond(field1,filed3) GROUP BY group_field
          UNION
          SELECT MAX(field2), firld4  FROM table2 WHERE  cond(field2,filed4) GROUP BY field4)
        WHERE cond(field) and othercond()
        GROUP by group_field
      

      The idea of this task is to lift WHERE/GROUP BY limitations of MENT-693.

      We can clone WHERE condition portion which depend on field under MAX/MIN and GROUP BY (if possible to clone and to separate this portion from other condition (could be side effect, random, outer references mixed with the fields)), clone GROUP BY list (also if possible), in both (WHERE & GROUP BY) change fields of UNION result by references (direct reference?) on SELECT LIST of each underlying SELECT. Cloned WHERE will be add (AND) to the WHERE of each underlying SELECT and GROUP BY just add to each such SELECT (the select should not have own GROUP BY).

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              sanja Oleksandr Byelkin
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.