Re: BUG #1517: SQL interval syntax is accepted by the parser,

Roy Badami wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1517
> Logged by: Roy Badami
> Email address: roy(at)gnomon(dot)org(dot)uk
> PostgreSQL version: 8.0.1
> Operating system: Solaris 9
> Description: SQL interval syntax is accepted by the parser, but the
> interpretation is bogus
> Details:
>
> The parser accepts SQL interval syntax, but then silently ignores it,
> treating it as a zero interval.
>
> radius=# select date '2005-01-01' + interval '1' month;
> ?column?
> ---------------------
> 2005-01-01 00:00:00
> (1 row)
>
> radius=# select timestamp '2005-01-1 00:00:00' + interval '1' minute;
> ?column?
> ---------------------
> 2005-01-01 00:00:00
> (1 row)

Well, that certainly belongs in the 'bizarre' category. It should not
accept that syntax. It should require the 'month' or 'minute' to be in
single quotes. This is wrong:

test=> select date '2005-01-01' + interval '1' month;
?column?
---------------------
2005-01-01 00:00:00
(1 row)

This is right:

test=> select date '2005-01-01' + interval '1 month';
?column?
---------------------
2005-02-01 00:00:00
(1 row)

In fact when the 'month' is outside the quotes, it modifies the
'interval', like this:

test=> select date '2005-01-01' + interval '1 year' year to month;
?column?
---------------------
2006-01-01 00:00:00
(1 row)

and in fact the '1' is taken to be 1 second:

test=> select date '2005-01-01' + interval '1';
?column?
---------------------
2005-01-01 00:00:01
(1 row)

So, in fact these work just fine:

test=> select date '2005-01-01' + interval '1' second;
?column?
---------------------
2005-01-01 00:00:01
(1 row)

test=> select date '2005-01-01' + interval '1' hour to second;
?column?
---------------------
2005-01-01 00:00:01
(1 row)

Do we need help in this area? Yes. Where? I don't know.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073