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

CAST to interval type doesn't accept standard-format strings

    XMLWordPrintableJSON

    Details

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

      Description

      Cast specification evaluation is not compliant with the SQL standard. Mainly, it yields errors for standard-format strings that are specified to successfully yield interval values.

      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 that is a valid interval <literal> (<interval literal>) or <unquoted interval string> yields an interval value.

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

      Drill currently rejects both of those syntaxes. Note the casts to type INTERVAL HOUR and the resulting error messages in the following:

      0: jdbc:drill:zk=local> SELECT CAST( CAST( 'INTERVAL ''1'' HOUR' AS VARCHAR(100) ) AS INTERVAL HOUR) FROM INFORMATION_SCHEMA.CATALOGS;
      Error: SYSTEM ERROR: IllegalArgumentException: Invalid format: "INTERVAL '1' HOUR"
      
      Fragment 0:0
      
      [Error Id: b4bed61a-1efe-4e06-86d4-fff8f9829d50 on dev-linux2:31010] (state=,code=0)
      0: jdbc:drill:zk=local> SELECT CAST( CAST( '1' AS VARCHAR(100) ) AS INTERVAL HOUR) FROM INFORMATION_SCHEMA.CATALOGS;
      Error: SYSTEM ERROR: IllegalArgumentException: Invalid format: "1"
      
      Fragment 0:0
      
      [Error Id: 91dec1ed-5cac-4235-93d7-49a2a0f03a1a on dev-linux2:31010] (state=,code=0)
      0: jdbc:drill:zk=local> 
      

      (The extra cast to VARCHAR is a workaround for a CHAR-vs.-VARCHAR bug.)

      Drill should accept the standard formats or at least document the non-compliance for users.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated: