[
https://issues.apache.org/jira/browse/CALCITE-6985?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhen Chen updated CALCITE-6985:
-------------------------------
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.
was:
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.
> 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
> Priority: Major
> 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 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.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
