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((unsigned char) *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 == '.')
================================================================================
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
