Re: Have I found an interval arithmetic bug?

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

bruce@xxxxxxxxxx wrote:

[Thread moved to hackers.] …The best fix I think is…

Bryn wrote: Further… there’s a notable asymmetry. The fractional part of “1.7 years” is 8.4 months. But the fractional part of the months value doesn’t spread further down into days. However, the fractional part of “29.4 months” (12 days) _does_ spread further down into days. What’s the rationale for this asymmetry?

Yes, looking at the code, it seems we only spill down to one unit, not more. I think we need to have a discussion if we want to change that. I think the idea was that if you specify a non-whole number, you probably want to spill down one level, but don't want it spilling all the way to milliseconds, which is certainly possible.

Thanks for the quick response, Bruce. I was half expecting (re the bug) an explanation that showed that I’d (once again) misunderstood a fundamental principle.

I should come clean about the larger context. I work for Yugabyte, Inc. We have a distributed SQL database that uses the Version 11.2 PostgreSQL C code for SQL processing “as is”.

https://blog.yugabyte.com/distributed-postgresql-on-a-google-spanner-architecture-query-layer/

The founders decided to document YugabyteDB’s SQL functionality explicitly rather than just to point to the published PostgreSQL doc. (There are some DDL differences that reflect the storage layer differences.) I’m presently documenting date-time functionality. This is why I’m so focused on understanding the semantics exactly and on understanding the requirements that the functionality was designed to meet. I’m struggling with interval functionality. I read this:

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 appropriate number 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. »

Notice that the doc says that spill-down goes all the way to seconds and not just one unit. This simple test is consistent with the doc (output follows the dash-dash comment):

select ('6.54321 months'::interval)::text as i; --  6 mons 16 days 07:06:40.32

You see similar spill-down with this:

select ('876.54321 days'::interval)::text as i; -- 876 days 13:02:13.344

And so on down through the remaining smaller units. It’s only this test that doesn’t spill down one unit:

select ('6.54321 years'::interval)::text as i; --  6 years 6 mons

This does suggest a straight bug rather than a case for committee debate about what might have been intended. What do you think, Bruce?