Re: Have I found an interval arithmetic bug?

On Wed, Jul 28, 2021 at 1:05 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Fair point, but you decided when you chose to use float that you don't
> care about the differences between numbers that only differ at the
> seventeenth or so decimal place. (Maybe, if you don't understand what
> float is, you didn't make that choice intentionally ... but that's
> a documentation issue not a code shortcoming.) However, it's fairly
> hard to believe that somebody who writes '1.4 years'::interval doesn't
> care about the 0.4 year. The fact that we silently convert that to,
> effectively, 1.33333333... years seems like a bigger roundoff error
> than one would expect.

Yeah, that's definitely a fair point!

> > I am dubious that it's worth the pain of making the input function
> > reject cases involving fractional units. It's true that some people
> > here aren't happy with the current behavior, but they may no happier
> > if we reject those cases with an error, and other people may then be
> > unhappy too.
>
> Maybe. A possible compromise is to accept only exactly-representable
> fractions. Then, for instance, we'd take 1.5 years (resulting in 18
> months) but not 1.4 years. Now, this might fall foul of your point about
> not wanting to mislead people into expecting the system to do things it
> can't; but I'd argue that the existing behavior misleads them much more.

Well, let's see what other people think.

> > I think your previous idea was the best one so far: fix
> > the input function so that 'X years Y months' and 'Y months X years'
> > always produce the same answer, and call it good.
>
> That would clearly be a bug fix. I'm just troubled that there are
> still behaviors that people will see as bugs.

That's a reasonable thing to be troubled about, but the date and time
related datatypes have so many odd and crufty behaviors that I have a
hard time believing that there's another possible outcome. If somebody
showed up today and proposed a new data type and told us that the way
to format values of that data type was to say to_char(my_value,
alphabet_soup) I think they would not be taken very seriously. A lot
of this code, and the associated interfaces, date back to a time when
PostgreSQL was far more primitive than today, and when databases in
general were as well. At least we didn't end up with a datatype called
varchar2 ... or not yet, anyway.

--
Robert Haas
EDB: http://www.enterprisedb.com