Re: BRIN minmax multi - incorrect distance for infinite timestamp/date

On Wed, 18 Oct 2023 at 09:16, Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>
> BTW when adding the tests with extreme values, I noticed this:
>
> test=# select '5874897-01-01'::date;
> date
> ---------------
> 5874897-01-01
> (1 row)
>
> test=# select '5874897-01-01'::date + '1 second'::interval;
> ERROR: date out of range for timestamp
>

That's correct because date + interval returns timestamp, and the
value is out of range for a timestamp. This is equivalent to:

select '5874897-01-01'::date::timestamp + '1 second'::interval;
ERROR: date out of range for timestamp

and I think it's good that it gives a different error from this:

select '294276-01-01'::date::timestamp + '1 year'::interval;
ERROR: timestamp out of range

so you can tell that the overflow in the first case happens before the addition.

Of course a side effect of internally casting first is that you can't
do things like this:

select '5874897-01-01'::date - '5872897 years'::interval;
ERROR: date out of range for timestamp

which arguably ought to return '2000-01-01 00:00:00'. In practice
though, I think it would be far more trouble than it's worth trying to
change that.

Regards,
Dean