You’re right. Cases 2 and 3 are valid. I mis-read the spec. I think that one more fix is required to complete https://issues.apache.org/jira/browse/CALCITE-922.
Julian > On Oct 23, 2015, at 3:14 PM, Hsuan Yi Chu <[email protected]> wrote: > > 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? >>
