[
https://issues.apache.org/jira/browse/CALCITE-4771?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17702944#comment-17702944
]
Oliver Lee commented on CALCITE-4771:
-------------------------------------
[~julianhyde]
I see a few paths we can take re: using conformance or library to configure
this function
- continue to use conformance checking, like how postgres style % is used
- Change {{SqlCastFunction}} to return {{SqlSyntax.FUNCTION}} for {{SAFE_CAST}}
- Change the {{SqlOperator}} lookup in {{SqlFunction deriveType}} to also look
for {{SqlSyntax.SPECIAL}} on top of just {{SqlSyntax.FUNCTION}} (This leads to
some test failures dealing with functions in {{SqlStdOperatorTable}} with
{{SPECIAL}} syntax, however)
- Add a function that will do something similar to
{{CaseSensitiveKey#normalize}} when adding specific {{SqlLibraryOperator}}
functions so that the lookup key can be {{SqlSyntaxSPECIAL}}, but the function
itself has {{FUNCTION}} syntax
> change the value of the CAST function to be nullable
> -----------------------------------------------------
>
> Key: CALCITE-4771
> URL: https://issues.apache.org/jira/browse/CALCITE-4771
> Project: Calcite
> Issue Type: Improvement
> Reporter: xuyang
> Assignee: Zou
> Priority: Major
> Attachments: image-2021-09-16-11-43-55-743.png
>
>
> In the sql "SELECT CAST('haha' AS INT)",the value the function CAST returns
> will be parsed into NOT NULL, because when parsing, the type CAST returns is
> from the INT and the nullable attribute is from the 'haha', which doesn't
> consider the condition that parsing a string to an int could be invalid and
> return NULL values.
> I think there are two ways to improve this question:
> * One is to change the value of the CAST function to be nullable, which
> avoids the invalid parsing.
> * The other way is to introduce a function named TRY_CAST, which is used in
> SQL Server.If the parsing fails, TRY_CAST will return NULL instead of throws
> exception that a NOT NULL field will be set with our unexpected value NULL.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
