Daniel Barclay (Drill) created DRILL-3432:
---------------------------------------------
Summary: Change or document: CAST to interval type doesn't reject
non-standard strings
Key: DRILL-3432
URL: https://issues.apache.org/jira/browse/DRILL-3432
Project: Apache Drill
Issue Type: Bug
Reporter: Daniel Barclay (Drill)
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).
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
