Обсуждение: BUG #15696: year field of interval type is not rounded to nearest integer
BUG #15696: year field of interval type is not rounded to nearest integer
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 15696 Logged by: zhou xiaowei Email address: 110876189@qq.com PostgreSQL version: 11.2 Operating system: Linux x86_64 Description: all fields of interval type use rule of rounding to nearest integer(by rint() function), except for year field. details: postgres=# select '0.9999999999999999 year'::interval month; interval ---------- 11 mons (1 row) postgres=# select '0.9999999999999999 dec'::interval year; interval ---------- 9 years (1 row) postgres=# select '0.9999999999999999 cent'::interval year; interval ---------- 99 years (1 row) postgres=# select '0.9999999999999999 mil'::interval year; interval ----------- 999 years (1 row) the reason is that assign double value to int value in function DecodeInterval() : int DecodeInterval(char **field, int *ftype, int nf, int range, int *dtype, struct pg_tm *tm, fsec_t *fsec) { double fval; ....... switch (ftype[i]) case DTK_YEAR: tm->tm_year += val; if (fval != 0) tm->tm_mon += fval * MONTHS_PER_YEAR; tmask = DTK_M(YEAR); break; case DTK_DECADE: tm->tm_year += val * 10; if (fval != 0) tm->tm_mon += fval * MONTHS_PER_YEAR * 10; tmask = DTK_M(DECADE); break; case DTK_CENTURY: tm->tm_year += val * 100; if (fval != 0) tm->tm_mon += fval * MONTHS_PER_YEAR * 100; tmask = DTK_M(CENTURY); break; case DTK_MILLENNIUM: tm->tm_year += val * 1000; if (fval != 0) tm->tm_mon += fval * MONTHS_PER_YEAR * 1000; tmask = DTK_M(MILLENNIUM); break; ...... } The code 'tm->tm_mon' is int type.I think this is a bug,need use rint() to adjust it’s result,like: tm->tm_mon += rint(fval * MONTHS_PER_YEAR * 1000);
PG Bug reporting form <noreply@postgresql.org> writes: > all fields of interval type use rule of rounding to nearest integer(by > rint() function), except for year field. I'm not really convinced this is wrong, or at least that year is particularly inconsistent with anything else. Yes, "0.9999... year" is never rounded up to 1 year, but neither is 0.9999... month rounded to 1 mon: regression=# select '0.9999999999999999 mon'::interval; interval ------------------ 29 days 24:00:00 (1 row) nor 0.9999... day rounded to 1 day: regression=# select '0.9999999999999999 day'::interval; interval ---------- 24:00:00 (1 row) In all these cases the fraction is only allowed to propagate to lower-order fields of the interval result. A possibly more interesting point is that 0.9999... year only affects the months field and not any lower fields; by analogy to the 0.9999... month case, you might expect to get "11 mons 30 days" or perhaps "11 mons 29 days 24:00:00", but you don't. Probably more interesting for practical use is that today you get regression=# select '0.3 year'::interval; interval ---------- 3 mons (1 row) but (if I did the math correctly) that would change to "4 mons" if we rounded where you suggest doing so. I don't find that terribly defensible --- in a green field, it wouldn't have mattered which we picked perhaps, but I doubt we can get away with changing that behavior now. We could imagine some other universe where all this is done differently, but why exactly is that so much better that we should take risks of backwards-compatibility complaints? None of this seems like an expected use-case for intervals. The way you really ought to do scaling-type calculations is more like regression=# select 0.3 * '1 year'::interval; ?column? ---------------- 3 mons 18 days (1 row) regression=# select 0.33 * '1 year'::interval; ?column? ------------------------- 3 mons 28 days 19:12:00 (1 row) regression=# select 0.9999999999999999 * '1 year'::interval; ?column? ----------------- 11 mons 30 days (1 row) regards, tom lane
sorry,I lose the expected result. Such as "select '0.9999999999999999 year'::interval month;" expected result is "12 mons",not "11 mons".I think this rule of rounding to nearest int like oracle function numtoyminterval(),maybe a more better idea.
PG Bug reporting form <noreply@postgresql.org> writes:
> all fields of interval type use rule of rounding to nearest integer(by
> rint() function), except for year field.
I'm not really convinced this is wrong, or at least that year is
particularly inconsistent with anything else. Yes, "0.9999... year"
is never rounded up to 1 year, but neither is 0.9999... month rounded
to 1 mon:
regression=# select '0.9999999999999999 mon'::interval;
interval
------------------
29 days 24:00:00
(1 row)
nor 0.9999... day rounded to 1 day:
regression=# select '0.9999999999999999 day'::interval;
interval
----------
24:00:00
(1 row)
In all these cases the fraction is only allowed to propagate to
lower-order fields of the interval result.
A possibly more interesting point is that 0.9999... year only
affects the months field and not any lower fields; by analogy to
the 0.9999... month case, you might expect to get "11 mons 30 days"
or perhaps "11 mons 29 days 24:00:00", but you don't.
Probably more interesting for practical use is that today you get
regression=# select '0.3 year'::interval;
interval
----------
3 mons
(1 row)
but (if I did the math correctly) that would change to "4 mons"
if we rounded where you suggest doing so. I don't find that
terribly defensible --- in a green field, it wouldn't have mattered
which we picked perhaps, but I doubt we can get away with changing
that behavior now.
We could imagine some other universe where all this is done differently,
but why exactly is that so much better that we should take risks of
backwards-compatibility complaints? None of this seems like an
expected use-case for intervals. The way you really ought to do
scaling-type calculations is more like
regression=# select 0.3 * '1 year'::interval;
?column?
----------------
3 mons 18 days
(1 row)
regression=# select 0.33 * '1 year'::interval;
?column?
-------------------------
3 mons 28 days 19:12:00
(1 row)
regression=# select 0.9999999999999999 * '1 year'::interval;
?column?
-----------------
11 mons 30 days
(1 row)
regards, tom lane
---Original---
From: "Tom Lane"<tgl@sss.pgh.pa.us>
Date: Sat, Mar 16, 2019 22:46 PM
To: "110876189"<110876189@qq.com>;
Cc: "pgsql-bugs"<pgsql-bugs@lists.postgresql.org>;
Subject: Re: BUG #15696: year field of interval type is not rounded to nearest integer
PG Bug reporting form <noreply@postgresql.org> writes:
> all fields of interval type use rule of rounding to nearest integer(by
> rint() function), except for year field.
I'm not really convinced this is wrong, or at least that year is
particularly inconsistent with anything else. Yes, "0.9999... year"
is never rounded up to 1 year, but neither is 0.9999... month rounded
to 1 mon:
regression=# select '0.9999999999999999 mon'::interval;
interval
------------------
29 days 24:00:00
(1 row)
nor 0.9999... day rounded to 1 day:
regression=# select '0.9999999999999999 day'::interval;
interval
----------
24:00:00
(1 row)
In all these cases the fraction is only allowed to propagate to
lower-order fields of the interval result.
A possibly more interesting point is that 0.9999... year only
affects the months field and not any lower fields; by analogy to
the 0.9999... month case, you might expect to get "11 mons 30 days"
or perhaps "11 mons 29 days 24:00:00", but you don't.
Probably more interesting for practical use is that today you get
regression=# select '0.3 year'::interval;
interval
----------
3 mons
(1 row)
but (if I did the math correctly) that would change to "4 mons"
if we rounded where you suggest doing so. I don't find that
terribly defensible --- in a green field, it wouldn't have mattered
which we picked perhaps, but I doubt we can get away with changing
that behavior now.
We could imagine some other universe where all this is done differently,
but why exactly is that so much better that we should take risks of
backwards-compatibility complaints? None of this seems like an
expected use-case for intervals. The way you really ought to do
scaling-type calculations is more like
regression=# select 0.3 * '1 year'::interval;
?column?
----------------
3 mons 18 days
(1 row)
regression=# select 0.33 * '1 year'::interval;
?column?
-------------------------
3 mons 28 days 19:12:00
(1 row)
regression=# select 0.9999999999999999 * '1 year'::interval;
?column?
-----------------
11 mons 30 days
(1 row)
regards, tom lane