Re: Have I found an interval arithmetic bug? - Mailing list pgsql-hackers

On Mon, Apr  5, 2021 at 11:33:10AM -0500, Justin Pryzby wrote:
> On Fri, Apr 02, 2021 at 10:21:26PM -0400, Bruce Momjian wrote:
> > I wish I could figure out how to improve it any futher.  What is odd is
> > that I have never seen this reported as a problem before.  I plan to
> > apply this early next week for PG 14.
> 
> On Fri, Apr 02, 2021 at 01:05:42PM -0700, Bryn Llewellyn wrote:
> > bruce@momjian.us wrote:
> > > Yes, looking at the code, it seems we only spill down to one unit, not more. I think we need to have a discussion
ifwe want to change that. 
 
> 
> If this is a bug, then there's no deadline - and if you're proposing a behavior
> change, then I don't think it's a good time to begin the discussion.

Well, bug or not, we are not going to change back branches for this, and
if you want a larger discussion, it will have to wait for PG 15.

> > https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
> > « …field values can have fractional parts; for example '1.5 week' or '01:02:03.45'. Such input is converted to the
appropriatenumber of months, days, and seconds for storage. When this would result in a fractional number of months or
days,the fraction is added to the lower-order fields using the conversion factors 1 month = 30 days and 1 day = 24
hours.For example, '1.5 month' becomes 1 month and 15 days. Only seconds will ever be shown as fractional on output. »
 

I see that.  What is not clear here is how far we flow down.  I was
looking at adding documentation or regression tests for that, but was
unsure.  I adjusted the docs slightly in the attached patch.

> Your patch changes what seems to be the intended behavior, with the test case
> added by:
> 
> |commit 57bfb27e60055c10e42b87e68a894720c2f78e70
> |Author: Tom Lane <tgl@sss.pgh.pa.us>
> |Date:   Mon Sep 4 01:26:28 2006 +0000
> |
> |    Fix interval input parser so that fractional weeks and months are
> |    cascaded first to days and only what is leftover into seconds.  This
> 
> And documented by:
> 
> |commit dbf57d31f8d7bf5c058a9fab2a1ccb4a336864ce
> |Author: Tom Lane <tgl@sss.pgh.pa.us>
> |Date:   Sun Nov 9 17:09:48 2008 +0000
> |
> |    Add some documentation about handling of fractions in interval input.
> |    (It's always worked like this, but we never documented it before.)
> 
> If you were to change the behavior, I think you'd have to update the
> documentation, too - but I think that's not a desirable change.

> I *am* curious why the YEAR, DECADE, CENTURY, AND MILLENIUM cases only handle
> fractional intervals down to the next smaller unit, and not down to
> seconds/milliseconds.  I wrote a patch to handle that by adding
> AdjustFractMons(), if we agree that it's desirable to change.

The interaction of months/days/seconds is so imprecise that passing it
futher down doesn't make much sense, and suggests a precision that
doesn't exist, but if people prefer that we can do it.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.