Patch for SQL-standard negative valued year-month literals - Mailing list pgsql-hackers

Tom Lane wrote:
> ...  SQL-spec interval literals.  I decided to go look at exactly
> how unfinished it was, and it turns out that it's actually pretty close.
> Hence the attached proposed patch ;-)

Short summary:
 I think this patch fixes a bug with sql-spec negative interval literals.

Longer.

I believe this short (4 lines of code & 1 line of comment) patch (below)
fixes the way we handle SQL-standard negative-valued year-month interval
strings.

In particular, if I read the spec right (relevant excerpts below), the
string '-1-1' is a vaild SQL-200N "year-month" interval meaning a
negative year and a month - because the spec only allows a <sign>
in the beginning of the unquoted interval string:   <unquoted interval string> ::=     [ <sign> ] { <year-month
literal>| <day-time literal> }
 
Current HEAD interprets '-1-1' as "-1 days -1 hours".  8.3 doesn't
recognize it at all.

Assuming I read the spec right, are there any problems with this,
and if not, could I ask that the patch at the end of this email
be applied?
   Ron



===============================================================================
== with this patch
===============================================================================

regression=# select interval '-1-1';     interval
------------------ -1 years -1 mons
(1 row)

===============================================================================
== without this patch
===============================================================================

regression=# select interval '-1-1';     interval
------------------- -1 days -01:00:00
(1 row)

===============================================================================
== 8.3
===============================================================================

regression=# select interval '-1-1';
ERROR:  invalid input syntax for type interval: "-1-1"

===============================================================================
== I think the relevant part of SQL 200N
===============================================================================

<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>

<years value> ::=  <datetime value>
<months value> ::=  <datetime value>

<datetime value> ::=  <unsigned integer>

... If SV is a negative interval, then <sign> shall be specified
within <unquoted interval string> in the literal Y.

===============================================================================
== The patch
===============================================================================

*** a/src/backend/utils/adt/datetime.c
--- b/src/backend/utils/adt/datetime.c
***************
*** 609,621 **** ParseDateTime(const char *timestr, char *workbuf, size_t buflen,              /* soak up leading
whitespace*/              while (isspace((unsigned char) *cp))                  cp++;
 
!             /* numeric timezone? */              if (isdigit((unsigned char) *cp))              {
ftype[nf]= DTK_TZ;                  APPEND_CHAR(bufp, bufend, *cp++);                  while (isdigit((unsigned char)
*cp)||
 
!                        *cp == ':' || *cp == '.')                      APPEND_CHAR(bufp, bufend, *cp++);
}             /* special? */
 
--- 609,621 ----              /* soak up leading whitespace */              while (isspace((unsigned char) *cp))
         cp++;
 
!             /* numeric timezone?  or sql year-month interval?*/              if (isdigit((unsigned char) *cp))
     {                  ftype[nf] = DTK_TZ;                  APPEND_CHAR(bufp, bufend, *cp++);                  while
(isdigit((unsignedchar) *cp) ||
 
!                        *cp == ':' || *cp == '.' || *cp == '-')                      APPEND_CHAR(bufp, bufend, *cp++);
            }              /* special? */
 
***************
*** 2876,2889 **** DecodeInterval(char **field, int *ftype, int nf, int range,                  {
/*SQL "years-months" syntax */                      int        val2;
 
!                      val2 = strtoi(cp + 1, &cp, 10);                      if (errno == ERANGE || val2 < 0 || val2 >=
MONTHS_PER_YEAR)                         return DTERR_FIELD_OVERFLOW;                      if (*cp != '\0')
            return DTERR_BAD_FORMAT;                      type = DTK_MONTH;
 
!                     val = val * MONTHS_PER_YEAR + val2;                      fval = 0;                  }
    else if (*cp == '.')
 
--- 2876,2890 ----                  {                      /* SQL "years-months" syntax */                      int
  val2;
 
!                     int     sign;
!                     sign = val < 0 ? -1 : 1;                      val2 = strtoi(cp + 1, &cp, 10);
if (errno == ERANGE || val2 < 0 || val2 >= MONTHS_PER_YEAR)                          return DTERR_FIELD_OVERFLOW;
              if (*cp != '\0')                          return DTERR_BAD_FORMAT;                      type =
DTK_MONTH;
!                     val = val * MONTHS_PER_YEAR + val2*sign;                      fval = 0;                  }
         else if (*cp == '.')
 
================================================================================