[jira] [Comment Edited] (CALCITE-4771) Add TRY_CAST (MSSQL compatibility)

["Benchao Li (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4771?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17713040#comment-17713040 ] 

Benchao Li edited comment on CALCITE-4771 at 4/17/23 11:46 AM:
---------------------------------------------------------------

Fixed via https://github.com/apache/calcite/commit/83f136167d3637319eb9b6fbbdca0eb9dd74cc85

[~FrankZou] Thanks for your contribution! And thanks [~zhenw], [~oliverlee] for the review!


was (Author: libenchao):
Fixed via https://github.com/apache/calcite/commit/83f136167d3637319eb9b6fbbdca0eb9dd74cc85

[~FrankZou] Thanks for your contribution! And thanks [~zhenw][~oliverlee] for the review!

> Add TRY_CAST (MSSQL compatibility)
> ----------------------------------
>
>                 Key: CALCITE-4771
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4771
>             Project: Calcite
>          Issue Type: Improvement
>            Reporter: xuyang
>            Assignee: Dan Zou
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 1.35.0
>
>         Attachments: image-2021-09-16-11-43-55-743.png
>
>          Time Spent: 2h 10m
>  Remaining Estimate: 0h
>
> 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)