[
https://issues.apache.org/jira/browse/CALCITE-6985?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17947295#comment-17947295
]
suibianwanwan commented on CALCITE-6985:
----------------------------------------
By the way, you should also consider `NULLS FIRST/LAST`. This can yield
different results with `MIN/MAX`. Similar discussion exists in
[PR-4181|https://github.com/apache/calcite/pull/4181].
> 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)
