Hi, The mix of interval and comparison with float is not easy to interpret. See the following (I got 0.0833 since the result for interval '0.3 years' + interval '0.4 years' - ... query was 1 month and 1/12 ~= 0.0833).
yugabyte=# select 0.3 * '1 year'::interval + 0.4 * '1 year'::interval - 0.7 * '1 year'::interval = '0.0833 year'::interval; ?column? ---------- f As long as Bruce's patch makes improvements over the current behavior, I think that's fine. Cheers On Fri, Apr 2, 2021 at 6:24 PM Isaac Morland <isaac.morl...@gmail.com> wrote: > On Fri, 2 Apr 2021 at 21:08, Zhihong Yu <z...@yugabyte.com> wrote: > >> Hi, >> I got a local build with second patch where: >> >> yugabyte=# SELECT interval '0.3 years' + interval '0.4 years' - >> interval '0.7 years'; >> ?column? >> ---------- >> 1 mon >> >> I think the outcome is a bit unintuitive (I would expect result close to >> 0). >> > > That's not fundamentally different from this: > > odyssey=> select 12 * 3/10 + 12 * 4/10 - 12 * 7/10; > ?column? > ---------- > -1 > (1 row) > > odyssey=> > > And actually the result is pretty close to 0. I mean it’s less than 0.1 > year. > > I wonder if it might have been better if only integers had been accepted > for the components? If you want 0.3 years write 0.3 * '1 year'::interval. > But changing it now would be a pretty significant backwards compatibility > break. > > There's really no avoiding counterintuitive behaviour though. Look at this: > > odyssey=> select 0.3 * '1 year'::interval + 0.4 * '1 year'::interval - 0.7 > * '1 year'::interval; > ?column? > ------------------ > -1 mons +30 days > (1 row) > > odyssey=> select 0.3 * '1 year'::interval + 0.4 * '1 year'::interval - 0.7 > * '1 year'::interval = '0'; > ?column? > ---------- > t > (1 row) > > odyssey=> > > In other words, doing the “same” calculation but with multiplying 1 year > intervals by floats to get the values to add, you end up with an interval > that while not identical to 0 does compare equal to 0. So very close to 0; > in fact, as close to 0 as you can get without actually being identically 0. >
