Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-7265

Cast/To_type with on error handling

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.16.0
    • Fix Version/s: None
    • Component/s: Functions - Drill
    • Labels:
      None

      Description

      It will be usefull to have the possibility to catch error in CAST and in function TO*_ (ex TO_DATE)

      Examples:

      /* 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
      

      As mentioned in the relatively close ticket DRILL-3727, currently the situation is not homogeneous:

      1. Some system has the DEFAULT NULL comportment as default, example: hive, spark sql
      2. Some other propose this options, example: oracle (https://oracle-base.com/articles/12c/conversion-function-enhancements-12cr2)
      3. Some other propose special operator to do that: vertica ("::!"), SQLServer (TRY_CAST, TRY_CONVERT), BigQuery (SAFE_CAST)
      4. 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.

       

       

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              benj641 benj
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: