Thanks for doing the legwork, Jinfeng. My reading of the syntax is
that since <years value> cannot be empty and must be an unsigned
integer, a minus sign directly after the quote is not valid.
INTERVAL - '1' YEAR // valid
INTERVAL '-1' YEAR // not valid
INTERVAL - '-1' YEAR // not valid
INTERVAL '0-1' YEAR TO MONTH // valid
INTERVAL - '0-1' MONTH // valid
INTERVAL '1-'
For what it's worth, here's the output from Oracle 11.
SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 23 09:54:28 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> select interval '1' year from dual;
INTERVAL'1'YEAR
---------------------------------------------------------------------------
+01-00
SQL> select interval '-1' year from dual;
INTERVAL'-1'YEAR
---------------------------------------------------------------------------
-01-00
SQL> select interval '-1-1' year from dual;
select interval '-1-1' year from dual
*
ERROR at line 1:
ORA-01867: the interval is invalid
SQL> select interval '-1-1' year to month from dual;
INTERVAL'-1-1'YEARTOMONTH
---------------------------------------------------------------------------
-01-01
SQL> select interval -'1-1' year to month from dual;
select interval -'1-1' year to month from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
Per my reading of the SQL spec, Oracle gets it wrong. Oracle should
disallow query #3 and allow query #4.
Julian
On Fri, Oct 23, 2015 at 8:27 AM, Jinfeng Ni <[email protected]> wrote:
> Here is what SQL standard 2003 say about interval literal:
>
> <interval literal> ::= INTERVAL [ <sign> ] <interval string> <interval
> qualifier>
> <interval string> ::= <quote> <unquoted interval string> <quote>
>
> <unquoted interval string> ::=
> [ <sign> ] { <year-month literal> | <day-time literal> }
>
> <year-month literal> ::=
> <years value> [ <minus sign> <months value> ]
>
> | <months value>
>
> <day-time literal> ::=
> <day-time interval>
>
> | <time interval>
>
> Looks like sign could appear before the quote and inside the quote.
> The minus sign could even appear before months value.
>
>
>
>
> On Thu, Oct 22, 2015 at 10:26 PM, Julian Hyde <[email protected]> wrote:
>> First of all, look up interval laterals in the SQL standard and find out
>> whether it's even valid.
>>
>> Julian
>>
>>> On Oct 22, 2015, at 21:49, Hsuan Yi Chu <[email protected]> wrote:
>>>
>>> Hi All,
>>> I am trying to resolve CALCITE-922, (interval error, coming from attempting
>>> to get value of Interval Type).
>>>
>>> However, I found another issue regarding the "sign (i.e., + or -)" of
>>> Interval type (see CALCITE-928). To resolve it, can somebody tell me what
>>> this means:
>>>
>>> "INTERVAL -'-1' YEAR"
>>>
>>> Do the two negative signs cancel each other?
