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@gnomon.org.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073