Details
-
New Feature
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.39.0
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
- is related to
-
CALCITE-1317 Rewrite "MAX" as "ORDER BY ... LIMIT ..."
-
- Open
-
- links to