Re: BUG #1871: operations with data types

On Sun, Sep 11, 2005 at 12:43:58AM -0400, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)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