[jira] [Created] (DRILL-7265) Cast/To_type with on error handling

["benj (JIRA)" <ji...@apache.org>]

benj created DRILL-7265:
---------------------------

             Summary: Cast/To_type with on error handling
                 Key: DRILL-7265
                 URL: https://issues.apache.org/jira/browse/DRILL-7265
             Project: Apache Drill
          Issue Type: Improvement
          Components: Functions - Drill
    Affects Versions: 1.16.0
            Reporter: benj


It will be usefull to have the possibility to catch error in _CAST_ and in function _TO_*_ (ex _TO_DATE_)

Examples:
{code:java}
/* currently */
SELECT CAST('5' AS int);
=> 5
SELECT CAST('foo' AS int);
=> Error: SYSTEM ERROR: NumberFormatException: foo

/* Expected */
SELECT CAST('foo' AS int DEFAULT NULL ON ERROR);
=> NULL
SELECT CAST('foo' AS int DEFAULT -6 ON ERROR);
=> -6
SELECT CAST('foo' AS int);
=> Error: SYSTEM ERROR: NumberFormatException: foo
{code}
As mentioned in the relatively close ticket DRILL-3727, currently the situation is not homogeneous:
 # Some system has the _DEFAULT NULL_ comportment as default, example: hive, spark sql
 # Some other propose this options, example: oracle ([https://oracle-base.com/articles/12c/conversion-function-enhancements-12cr2)]
 # Some other propose special operator to do that: vertica ("::!"), SQLServer (TRY_CAST, TRY_CONVERT), BigQuery (SAFE_CAST)
 # Some systems unfortunately do not have this possibility, example : PostgreSQL

Solutions 2 and 3 will be great but a simple option (ALTER SESSION) to get the behavior of the solution 1 would already be interesting.

 

 



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)