Re: BUG #1871: operations with data types - Mailing list pgsql-bugs

On Fri, Sep 09, 2005 at 01:00:31PM +0100, anris@polynet.lviv.ua wrote:
> select '2005-08-31'::date + '1 month'::interval-'1 month'::interval
>
> from the mathematical me the resulting value should be '2005-08-31'

You didn't show any output; this is what I get:

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

Apparently the two intervals don't cancel each other out (i.e.,
they're not optimized to zero), so effectively we get this:

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

test=> select '2005-09-30 00:00:00'::timestamp - '1 month'::interval;
      ?column?
---------------------
 2005-08-30 00:00:00
(1 row)

I'm wondering if the first expression ('2005-08-31' + '1 month')
should raise an exception.  Date & Darwen say it should in _A Guide
to the SQL Standard_, Fourth Edition, p. 276:

  ....thus, for example, the expression

      DATE '1998-08-31' + INTERVAL '1' MONTH

  ("August 31st, 1998 plus one month") apparently evaluates to

      DATE '1998-09-31'

  ("September 31st, 1998"), and thus fails (an "invalid date"
  exception is raised.  It does _not_ evaluate (as might perhaps
  have been expected) to

      DATE '1998-10-01'

  ("October 1st, 1998"), because such a result would require an
  adjustment to the DAY field after the MONTH addition had been
  performed.  In other words, if interval _i_ is added to date _d_,
  and _i_ is of type year-month, then the DAY value in the result
  is the same as the DAY value in _d_ (i.e., the DAY value does
  not change).

SQL:2003 (draft) Foundation, 6.30 <datetime value expression>,
General Rule 4 says

  If the <interval value expression> or <interval term> is a
  year-month interval, then the DAY field of the result is the
  same as the DAY field of the <datetime term> or <datetime value
  expression>.

and General Rule 6b says

  If, after the preceding step, any <primary datetime field> of the
  result is outside the permissible range of values for the field
  or the result is invalid based on the natural rules for dates and
  times, then an exception condition is raised: data exception --
  datetime field overflow.

Based on these rules, I'd expect '2005-08-31' + '1 month' to evaluate
to '2005-09-31' and thus raise an exception; instead, PostgreSQL
returns '2005-09-30'.

Any standards lawyers out there?  Have I misunderstood anything?

--
Michael Fuhr