[jira] (CALCITE-6985) Add rule to transform MIN/MAX with ORDER BY and LIMIT 1

["Zhen Chen (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-6985 ]


    Zhen Chen deleted comment on CALCITE-6985:
    ------------------------------------

was (Author: jensen):
[~suibianwanwan33] I don't quite understand. If there is no record, return null directly.

> 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)