Zhen Chen created CALCITE-6985:
----------------------------------
Summary: Add rule to transform MIN/MAX with ORDER BY and LIMIT 1
Key: CALCITE-6985
URL: https://issues.apache.org/jira/browse/CALCITE-6985
Project: Calcite
Issue Type: New Feature
Components: core
Affects Versions: 1.39.0
Reporter: Zhen Chen
Assignee: Zhen Chen
Fix For: 1.40.0
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 t;
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.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
