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

"pushdown" of MIN/MAX inside UNION

    XMLWordPrintable

    Details

      Description

      There is min/max optimization in case of single SELECT, i.e:

      SELECT MAX(field) FROM table;
      

      We check only index for max (or min) value.

      But if we have (probably via VIEW)

      SELECT MAX(field) FROM
         (SELECT field1 as field FROM table1
          UNION
          SELECT field2  FROM table2);
      

      we get all tables in temporary table than scan it for max (min).

      If we transform the above query to :

      SELECT MAX(field) FROM
        (SELECT MAX(field1) as field FROM table1
         UNION
         SELECT MAX(field2)  FROM table2);
      

      optimisation will be still possible.

      The idea of optimization is to push MIN/MAX into each part of the UNION if possible (It might not be possible for some SELECTs in the UNION)

      Optimization is not possible in case:

      • underlying SELECT has:
        • GROUP BY
        • HAVING
        • ORDER BY/LIMIT
        • aggregate functions
        • non numeric type of the MAX/MIN field
      • outer SELECT has:
        • WHERE (see MENT-694)
        • GROUP BY (see MENT-694)
        • EXCEPT/INTERSECT in addition to UNIONs
        • Only one "derived" table with the UNION

      Also possible such transformation for COUNT (only for ALL UNION):

      SELECT COUNT(*) FROM
         (SELECT field1 as field FROM table1
          UNION ALL
          SELECT field2  FROM table2);
      

      to

      SELECT SUM(count) FROM
         (SELECT COUNT(*) as count  FROM table1
          UNION ALL
          SELECT COUNT(*)  FROM table2);
      

        Attachments

          Issue Links

            Activity

              People

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