Hi Julian,
I think case 2 and 3 are valid. See below plz:
case 2:
INTERVAL '-1' YEAR
=> According to the sql standard. This case satisfies this one:
<unquoted interval string> ::= [ <sign> ] { <year-month literal> |
<day-time literal> }
case 3:
INTERVAL -'-1' YEAR
=> Use above and this rule
<interval literal> ::= INTERVAL [ <sign> ] <interval string> <interval
qualifier>
So I think Calcite does not have issue in this regard.
Not a bad news though.
On Fri, Oct 23, 2015 at 10:09 AM, Julian Hyde <[email protected]> wrote:
> 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?
>
