[
https://issues.apache.org/jira/browse/CALCITE-6702?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17900505#comment-17900505
]
Julian Hyde commented on CALCITE-6702:
--------------------------------------
You have convinced me that POWER should be ANY. Thanks for doing that research.
Now I am worrying that there are many more functions that have the same problem
- ATAN2, for example - and how we could detect and fix these functions
efficiently.
My question to you is: What evidence did you see that made you realize that
POWER should be ANY, not AS_IS? What is the test case that passes only after
this bug is fixed? I want to apply that test case automatically to all
functions.
I would also like to extend the function definitions so that we do not need to
modify {{Strong.java}} for each function. We almost have enough information in
the function definition. The definition of POWER has
{{ReturnTypes.DOUBLE_NULLABLE}} which says 'the return type is nullable if and
only if any argument is nullable', or equivalently 'POWER returns null only if
at least one argument is null'. We need something a little stronger, namely
'POWER returns null *if and* only if at least one argument is null'.
> Strong Policy for the `SqlStdOperatorTable.POWER` is wrongly assigned `AS_IS`
> when it should be `ANY`
> -----------------------------------------------------------------------------------------------------
>
> Key: CALCITE-6702
> URL: https://issues.apache.org/jira/browse/CALCITE-6702
> Project: Calcite
> Issue Type: Bug
> Reporter: Dhia Eddine Nini
> Assignee: Dhia Eddine Nini
> Priority: Major
> Labels: pull-request-available
> Attachments: image-2024-11-20-17-14-28-261.png,
> image-2024-11-22-12-45-23-068.png
>
>
> +Context:+
> In standard SQL (such as MySQL and PostgreSQL), running the following queries:
> {code:java}
> SELECT POWER(NULL, 2) AS "ALIAS1";
> SELECT POWER(2, NULL) AS "ALIAS2";{code}
> Returns for both queries:
> {code:java}
> NULL
> {code}
> This means that the proper null policy for the expression is `ANY` instead of
> `AS_IS`, since this expression is null {*}if and only if at least one of its
> arguments is null{*}.
> !image-2024-11-20-17-14-28-261.png!
> The `SqlKind` of the the POWER function is `SqlKind.OTHER_FUNCTION`
> The fix should be relatively simple.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
