Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-6985

Add rule to transform MIN/MAX with ORDER BY and LIMIT 1

    XMLWordPrintableJSON

Details

    • New Feature
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.39.0
    • 1.40.0
    • core

    Description

      This rule transforms the MIN/MAX aggregate functions in SQL queries into subqueries with ORDER BY and LIMIT 1 to improve query performance.

      Example:
      SELECT MIN(c1), MAX(c2) FROM t;
      Can be converted to:
      SELECT
       (SELECT c1 FROM t WHERE c1 IS NOT NULL ORDER BY c1 ASC LIMIT 1) AS min_c1,
       (SELECT c2 FROM t WHERE c2 IS NOT NULL ORDER BY c2 DESC LIMIT 1) AS max_c2
      FROM DUAL;
      When the aggregated columns have appropriate indexes or sorting properties, only a small number of rows need to be scanned to determine MIN/MAX.

      Currently, this rule only handles Aggregates that contain only MIN/MAX aggregate functions and do not include GROUP BY.

      Attachments

        Issue Links

          Activity

            People

              jensen Zhen Chen
              jensen Zhen Chen
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: