Thread: Have I found an interval arithmetic bug?
Or am I misunderstanding something?
Try this. The result of each “select” is shown as the trailing comment on the same line. I added whitespace by hand to line up the fields.
select interval '-1.7 years'; -- -1 years -8 mons
select interval '29.4 months'; -- 2 years 5 mons 12 days
select interval '-1.7 years 29.4 months'; -- 8 mons 12 days << wrong
select interval '29.4 months -1.7 years'; -- 9 mons 12 days
select interval '-1.7 years' + interval '29.4 months'; -- 9 mons 12 days
select interval '29.4 months' + interval '-1.7 years'; -- 9 mons 12 days
As I reason it, the last four “select” statements are all semantically the same. They’re just different syntaxes to add the two intervals the the first two “select” statements use separately. There’s one odd man out. And I reason this one to be wrong. Is there a flaw in my reasoning?
Try this. The result of each “select” is shown as the trailing comment on the same line. I added whitespace by hand to line up the fields.
select interval '-1.7 years'; -- -1 years -8 mons
select interval '29.4 months'; -- 2 years 5 mons 12 days
select interval '-1.7 years 29.4 months'; -- 8 mons 12 days << wrong
select interval '29.4 months -1.7 years'; -- 9 mons 12 days
select interval '-1.7 years' + interval '29.4 months'; -- 9 mons 12 days
select interval '29.4 months' + interval '-1.7 years'; -- 9 mons 12 days
As I reason it, the last four “select” statements are all semantically the same. They’re just different syntaxes to add the two intervals the the first two “select” statements use separately. There’s one odd man out. And I reason this one to be wrong. Is there a flaw in my reasoning?
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?
I can’t see that my observations here can be explained by the difference between calendar time and clock time. Here I’m just working with non-metric units like feet and inches. One year is just defined as 12 months. And one month is just defined as 30 days. All that stuff about adding a month to 3-Feb-2020 taking you to 3-Mar-2020 (same for leap years an non-leap years) , and that other stuff about adding one day to 23:00 on the day before the “spring forward” moment taking you to 23:00 on the next day (i.w. when intervals are added to timestamps) is downstream of simply adding two intervals.
Thread moved to hackers, with a patch. --------------------------------------------------------------------------- On Thu, Apr 1, 2021 at 09:46:58PM -0700, Bryn Llewellyn wrote: > Or am I misunderstanding something? > > Try this. The result of each “select” is shown as the trailing comment on the > same line. I added whitespace by hand to line up the fields. > > select interval '-1.7 years'; -- -1 years -8 mons > > select interval '29.4 months'; -- 2 years 5 mons 12 > days > > select interval '-1.7 years 29.4 months'; -- 8 mons 12 > days << wrong > select interval '29.4 months -1.7 years'; -- 9 mons 12 > days > > select interval '-1.7 years' + interval '29.4 months'; -- 9 mons 12 > days > select interval '29.4 months' + interval '-1.7 years'; -- 9 mons 12 > days > > As I reason it, the last four “select” statements are all semantically the > same. They’re just different syntaxes to add the two intervals the the first > two “select” statements use separately. There’s one odd man out. And I reason > this one to be wrong. Is there a flaw in my reasoning? > > 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? > > I can’t see that my observations here can be explained by the difference > between calendar time and clock time. Here I’m just working with non-metric > units like feet and inches. One year is just defined as 12 months. And one > month is just defined as 30 days. All that stuff about adding a month to > 3-Feb-2020 taking you to 3-Mar-2020 (same for leap years an non-leap years) , > and that other stuff about adding one day to 23:00 on the day before the > “spring forward” moment taking you to 23:00 on the next day (i.w. when > intervals are added to timestamps) is downstream of simply adding two > intervals. > -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
bruce@momjian.us 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”.
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:
« …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?
On Fri, Apr 2, 2021 at 11:06 AM Bruce Momjian <bruce@momjian.us> wrote:
Thread moved to hackers, with a patch.
---------------------------------------------------------------------------
Here is a link to that thread, for others who might be curious about it as I was:
I get why it can make sense to move a thread. But if when doing so you post a link to the new thread, that would be appreciated. Thanks!
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Fri, Apr 2, 2021 at 01:20:26PM -0700, Ken Tanzer wrote: > On Fri, Apr 2, 2021 at 11:06 AM Bruce Momjian <bruce@momjian.us> wrote: > > > Thread moved to hackers, with a patch. > --------------------------------------------------------------------------- > > > > Here is a link to that thread, for others who might be curious about it as I > was: > https://www.postgresql.org/message-id/flat/20210402180549.GF9270%40momjian.us# > b3bdafbfeacab0dd8967ff2a3ebf7844 > > I get why it can make sense to move a thread. But if when doing so you post a > link to the new thread, that would be appreciated. Thanks! I didn't think anyone but the original poster, who was copied in the new thread, would really care about this thread, but it seems I was wrong. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.