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

Change or document: CAST to interval type doesn't reject non-standard strings



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


      In ISO/IEC 9075-2:2011(E) section 6.13 <cast specification>, General Rule 19 case b says that, in a <cast specification> casting to an interval type, a character string value must be a valid interval <literal> (<interval literal>) or <unquoted interval string>, or else a "data exception — invalid interval format" exception is raised.

      (<interval literal> is the "INTERVAL '1-6' YEAR TO MONTH" syntax; <unquoted interval string> is the "1-6" syntax.)

      However, in Drill, a cast specification casting to an interval type accepts strings not allowed by the SQL standard (ISO-8601 durations, e.g., "P1Y6M").

      Drill should either follow the SQL standard or document the non-standard behavior in the end-user documentation.

      (Note that Drill's current behavior is not quite an extension: Although it does extend the set of strings that can be converted, it lacks the rejection of non-standard strings (for, say, validating strings destined for input into a standard-SQL system).)

      To continue providing the ability to convert ISO-8601-format duration strings into interval values without being non-compliant with the SQL standard, Drill could provide the conversions in conversion functions (perhaps CONVERT_FROM and/or CONVERT_TO).


          Issue Links



              • Assignee:
                dsbos Daniel Barclay
              • Votes:
                0 Vote for this issue
                1 Start watching this issue


                • Created: