[
https://issues.apache.org/jira/browse/CALCITE-7122?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18011940#comment-18011940
]
Julian Hyde edited comment on CALCITE-7122 at 8/4/25 9:24 PM:
--------------------------------------------------------------
What SQL functions are idempotent? In addition to {{{}LOWER{}}}, {{{}UPPER{}}},
{{ABS}} in this case, and {{TRIM}} in CALCITE-7042, I can think of variants of
{{{}LTRIM{}}}, {{RTRIM}} (variants of {{{}TRIM{}}}); {{INITCAP}} (in the same
family as {{{}LOWER{}}}, {{{}UPPER{}}}); logical functions {{IS_TRUE}} and
{{{}IS_NOT_FALSE{}}}; arithmetic functions {{{}FLOOR{}}}, {{CEIL}} (including
variants that work on date-time values.)
A [Twitter thread|https://x.com/julianhyde/status/1952444338279162191] adds
{{{}COALESCE{}}}, {{GREATEST}} and {{LEAST}} (left- and right-idempotent);
{{{}LPAD{}}}, {{{}RPAD{}}}, {{{}MOD{}}}, {{ARRAY_SORT}} (right-idempotent),
{{SUBSTR(x, 1, n)}} (idempotent on the 3rd argument as long as the second
argument is 1).
{{CAST}} (if the source and target datatypes are the same) and unary plus are
trivial.
was (Author: julianhyde):
What SQL functions are idempotent? In addition to {{LOWER}}, {{UPPER}}, {{ABS}}
in this case, and {{TRIM}} in CALCITE-7042, I can think of variants of
{{LTRIM}}, {{RTRIM}} (variants of {{TRIM}}); {{INITCAP}} (in the same family as
{{LOWER}}, {{UPPER}}); logical functions {{IS_TRUE}} and {{IS_NOT_FALSE}};
arithmetic functions {{FLOOR}}, {{CEIL}} (including variants that work on
date-time values.)
> Eliminate nested calls for idempotent unary functions upper/lower/abs
> ---------------------------------------------------------------------
>
> Key: CALCITE-7122
> URL: https://issues.apache.org/jira/browse/CALCITE-7122
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.40.0
> Reporter: Yu Xu
> Assignee: Yu Xu
> Priority: Minor
> Labels: pull-request-available
> Fix For: 1.41.0
>
>
> Unary functions like upper/lower/abs are idempotent( f(f(...fx))—> fx, but
> currently had not eliminate idempotency for them.
> eg:
>
> {code:java}
> SELECT LOWER(LOWER('AAA')) {code}
> would convert to:
>
> {code:java}
> SELECT LOWER(LOWER('AAA')){code}
> should would better simplify to
>
> {code:java}
> SELECT LOWER('AAA') {code}
>
>
> Because the idempotency elimination of unary functions is relatively simple,
> we can design a mechanism to simplify.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
