Thread: BUG #1871: operations with data types

The following bug has been logged online:

Bug reference:      1871
Logged by:
Email address:      anris@polynet.lviv.ua
PostgreSQL version: 7-8
Operating system:   Linux
Description:        operations with data types
Details:

May be it is not bug, but anywhere:

here you are sample query
select '2005-08-31'::date + '1 month'::interval-'1 month'::interval

from the mathematical me the resulting value should be '2005-08-31', but
......

can you explain this

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

Michael Fuhr <mike@fuhr.org> writes:
> Apparently the two intervals don't cancel each other out (i.e.,
> they're not optimized to zero),

Well, no, because + and - associate left-to-right.

> so effectively we get this:

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

This seems to be the crux of the issue: is the above expression valid
and if so what should it yield?

I think you are right that the SQL spec wants it to raise an error,
but the spec's rules about datetime behavior are uselessly narrow
minded (last I checked, they still had not heard of daylight savings
time ... so they're obviously not trying very hard in this area).

The behavior that's in our code now is to round back to the last real
day of the month.  This might not be the best choice, but raising an
error doesn't seem better to me offhand.  Date and Darwen seem to
think rounding forward to the first day of the next month would be
more natural.  I'm not sure why; it certainly wouldn't fix the
complainant's issue, only surprise him in a different way.  Also,
even if you like round-forward for the above case, what about
subtraction --- what should '2005-10-31' - '1 month' give?  Rounding
down definitely feels more natural in that case, at least to me.

Any comments out there?

            regards, tom lane

On Sun, Sep 11, 2005 at 12:43:58AM -0400, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > Apparently the two intervals don't cancel each other out (i.e.,
> > they're not optimized to zero),
>
> Well, no, because + and - associate left-to-right.

Sure, I wasn't expecting any different -- I was just mentioning it
for the mathematically inclined who might think +1 and -1 should
cancel each other out.

> > test=> select '2005-08-31'::date + '1 month'::interval;
> >       ?column?
> > ---------------------
> >  2005-09-30 00:00:00
> > (1 row)
>
> This seems to be the crux of the issue: is the above expression valid
> and if so what should it yield?

Dunno.  It does seem inconsistent that these expressions give the
same answer:

select '2005-08-30'::date + '1 month'::interval,
       '2005-08-30'::date + '1 day'::interval + '1 month'::interval;
      ?column?       |      ?column?
---------------------+---------------------
 2005-09-30 00:00:00 | 2005-09-30 00:00:00
(1 row)

and these give different answers:

select '2005-08-30'::date + '1 day'::interval + '1 month'::interval,
       '2005-08-30'::date + '1 month'::interval + '1 day'::interval;
      ?column?       |      ?column?
---------------------+---------------------
 2005-09-30 00:00:00 | 2005-10-01 00:00:00
(1 row)

but I doubt I could make an argument for an alternative that was
any better than its counterargument.

Wait a minute, here's proof that it *must* be wrong ;-)

mysql> select date_add('2005-08-31', interval 1 month);
+------------------------------------------+
| date_add('2005-08-31', interval 1 month) |
+------------------------------------------+
| 2005-09-30                               |
+------------------------------------------+
1 row in set (0.11 sec)

What do other DBMSs do?

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:
> [ Mike's unhappy that ] these give different answers:

> select '2005-08-30'::date + '1 day'::interval + '1 month'::interval,
>        '2005-08-30'::date + '1 month'::interval + '1 day'::interval;

This is certainly an arena in which you can't expect the commutative
or associative laws to hold.  For instance:

    ('2005-02-28' + '1 day') + '1 month'

You can't possibly argue that that produces anything but
'2005-03-01' + '1 month' and thence '2005-04-01'.  On the other
hand, you can't possibly argue that

    ('2005-02-28' + '1 month') + '1 day'

produces anything but '2005-03-28' + '1 day' and thence '2005-03-29'.
So the original complaint can at best be described as lacking thought.

> Wait a minute, here's proof that it *must* be wrong ;-)
> mysql> select date_add('2005-08-31', interval 1 month);
> | 2005-09-30                               |

rotfl ;-)

> What do other DBMSs do?

This is a fair question.  Given that the SQL committee hasn't provided
any useful leadership, what are other groups doing?

            regards, tom lane

On Sun, Sep 11, 2005 at 03:03:57AM -0400, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > What do other DBMSs do?
>
> This is a fair question.  Given that the SQL committee hasn't provided
> any useful leadership, what are other groups doing?

I don't have access to an Oracle system, but the following page has
examples:

  http://philip.greenspun.com/sql/dates

It looks like Oracle has "old" and "new" (version 9 and later?)
ways of doing the query and they give different results:

  -- old
  select add_months(to_date('2003-07-31','YYYY-MM-DD'),-1) from dual;

    ADD_MONTHS
  ----------
  2003-06-30

  -- new
  select to_timestamp('2003-07-31','YYYY-MM-DD') - interval '1' month from dual;

  ERROR at line 1:
  ORA-01839: date not valid for month specified

I just noticed something in PostgreSQL that might be considered
surprising (although I do see "Add ISO INTERVAL handling" in the
TODO list):

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

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

  test=> select interval '1' month;
   interval
  ----------
   00:00:00
  (1 row)

What's the parser doing here?

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:
> I just noticed something in PostgreSQL that might be considered
> surprising (although I do see "Add ISO INTERVAL handling" in the
> TODO list):

>   test=> select interval '1' month;
>    interval
>   ----------
>    00:00:00
>   (1 row)

> What's the parser doing here?

Not getting it right ;-).  Trying this in historical versions is
amusing:

7.0:
regression=# select interval '1' month;
ERROR:  parser: parse error at or near "month"

7.1:
regression=# select interval '1' month;
ERROR:  Bad interval external representation '1'

7.2:
regression=# select interval '1' month;
 interval
----------
 00:00
(1 row)

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

7.4 and up:
regression=# select interval '1' month;
 interval
----------
 00:00:00
(1 row)

What is happening in the current versions is that coerce_type thinks
it can coerce the literal string to interval without supplying the
modifier, and then use interval_scale() to apply the typmod.  This
works OK for most of the data types, but not for interval it seems...

Basically the support for these weird syntaxes is something that Tom
Lockhart never finished, and no one has bothered to pick up the work
since he left the project.

            regards, tom lane

Added to TODO:

    o Fix SELECT INTERVAL '1' MONTH;

---------------------------------------------------------------------------

Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > I just noticed something in PostgreSQL that might be considered
> > surprising (although I do see "Add ISO INTERVAL handling" in the
> > TODO list):
>
> >   test=> select interval '1' month;
> >    interval
> >   ----------
> >    00:00:00
> >   (1 row)
>
> > What's the parser doing here?
>
> Not getting it right ;-).  Trying this in historical versions is
> amusing:
>
> 7.0:
> regression=# select interval '1' month;
> ERROR:  parser: parse error at or near "month"
>
> 7.1:
> regression=# select interval '1' month;
> ERROR:  Bad interval external representation '1'
>
> 7.2:
> regression=# select interval '1' month;
>  interval
> ----------
>  00:00
> (1 row)
>
> 7.3:
> regression=# select interval '1' month;
>  interval
> ----------
>  00:00:01
> (1 row)
>
> 7.4 and up:
> regression=# select interval '1' month;
>  interval
> ----------
>  00:00:00
> (1 row)
>
> What is happening in the current versions is that coerce_type thinks
> it can coerce the literal string to interval without supplying the
> modifier, and then use interval_scale() to apply the typmod.  This
> works OK for most of the data types, but not for interval it seems...
>
> Basically the support for these weird syntaxes is something that Tom
> Lockhart never finished, and no one has bothered to pick up the work
> since he left the project.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

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