[ https://issues.apache.org/jira/browse/CALCITE-5557?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17696343#comment-17696343 ]
Oliver Lee edited comment on CALCITE-5557 at 3/3/23 11:52 PM: -------------------------------------------------------------- I'm currently investigating this task. At a very high level, what I'm seeing for CAST("a" to INT64) is that it takes the SqlFunction and does these series of steps: * Parses {{CAST()}} in {{Parser.jj and makes call to SqlStdOperatorTable Cast function.}} * Some validation around number of operands but not the actual types, checks on the nullability of the return (set to non-nullable for types like Integer), * Converts to RexNodes, SqlToRelConverter.java , {{convertSelectImpl}} * Attempts to simplify the expression in RexSimplify.java {{simplify()}} -> {{{}simplifyCast{}}}() * In the {{reduce}} steps it will try and execute the RexExecutable which is where {{toInt(String s)}} is called and throws Ideally we can reuse most of the code for CAST, and add in a flag that makes it so it returns {{null}} if it throws, but since it is so nested I'm not sure where that should go yet. was (Author: JIRAUSER297744): I'm currently investigating this task. At a very high level, what I'm seeing for CAST("a" to INT64) is that it takes the SqlFunction and does these series of steps: * Parses {{CAST()}} in {{Parser.jj and makes call to SqlStdOperatorTable Cast function.}} * Some validation around number of operands but not the actual types, checks on the nullability of the return (set to non-nullable for types like Integer), * Converts to RexNodes, SqlToRelConverter.java \{{convertSelectImpl}} * Attempts to simplify the expression in RexSimplify.java {{simplify()}} -> {{{}simplifyCast{}}}() * In the {{reduce}} steps it will try and execute the RexExecutable which is where {{toInt(String s)}} is called and throws Ideally we can reuse most of the code for CAST, and add in a flag that makes it so it returns {{null}} if it throws, but since it is so nested I'm not sure where that should go yet. > Add SAFE_CAST (big BigQuery compatibility) > ------------------------------------------ > > Key: CALCITE-5557 > URL: https://issues.apache.org/jira/browse/CALCITE-5557 > Project: Calcite > Issue Type: New Feature > Reporter: Oliver Lee > Assignee: Oliver Lee > Priority: Minor > > Implement SAFE_CAST per BigQuery specifications > [https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#safe_casting] > Equivalent to CAST(), except it will return {{null}} instead of throwing an > exception if the casting fails. > {quote}SAFE_CAST('1' as INT) -> 1 > SAFE_CAST('a' as INT) -> NULL > SAFE_CAST("2022-12-12" as DATE) -> DATE('2022-12-12') > SAFE_CAST(1 AS BOOLEAN) -> true > SAFE_CAST('A' AS BOOLEAN) -> null > {quote} > -- This message was sent by Atlassian Jira (v8.20.10#820010)