Re: Have I found an interval arithmetic bug? - Mailing list pgsql-hackers
| From | Bryn Llewellyn |
|---|---|
| Subject | Re: Have I found an interval arithmetic bug? |
| Date | |
| Msg-id | 32AF8412-44F2-4861-AFF6-BB8FD5132531@yugabyte.com Whole thread Raw |
| In response to | Re: Have I found an interval arithmetic bug? (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-hackers |
tgl@sss.pgh.pa.us wrote:bruce@momjian.us writes:bryn@yugabyte.com wrote:After all, you've bitten the bullet now and changed the behavior. This means that the semantics of some extant applications will change. So... in for a penny, in for a pound?The docs now say:Field values can have fractional parts; for example, <literal>'1.5
weeks'</literal> or <literal>'01:02:03.45'</literal>. The fractional
--> parts are used to compute appropriate values for the next lower-order
internal fields (months, days, seconds).meaning fractional years flows to the next lower internal unit, months, and no further. Fractional months would flow to days. The idea of not flowing past the next lower-order internal field is that the approximations between units are not precise enough to flow accurately.
Um, what's the argument for down-converting AT ALL? The problem is precisely that any such conversion is mostly fictional.With my patch, the output is now:SELECT INTERVAL '3 years 10.241604 months';
interval
------------------------
3 years 10 mons 7 daysIt used to flow to seconds.
Yeah, that's better than before, but I don't see any principled argument for it not to be "3 years 10 months", full stop.
Tom, I fully support your suggestion to have no flow down at all. Please may this happen! However, the new rule must be described in terms of the three fields of the internal representation: [months, days, seconds]. This representation is already documented.
Don’t forget that '731.42587 hours’ is read back as "731:25:33.132" (or, if you prefer, 731 hours, 25 minutes, and 33.132 seconds if you use "extract" and your own pretty print). But we don’t think of this as “flow down”. Rather, it’s just a conventional representation of the seconds field of the internal representation. I could go on. But you all know what I mean.
By the way, I made a nice little demo (for my doc project). It shows that:
(1) if you pick the right date-time just before a DST change, and do the addition in the right time zone, then adding 24 hours gets a different answer than adding one day.
(2) if you pick the right date-time just before 29-Feb in a leap year, then adding 30 days gets a different answer than adding one month.
You all know why. And though the doc could explain and illustrate this better, it does tell you to expect this. It also says that the difference in semantics that these examples show is the reason for the three-field internal representation.
It seems to me that both the age-old behavior that vanilla 13.2 exhibits, and the behavior in the regime of Bruce’s patch are like adding 2.2 oranges to 1.3 oranges and getting 3 oranges and 21 apples (because 1 orange is conventionally the same as 42 apples). Bruce made a step in the right direction by stopping oranges convert all the way down to bananas. But it would be so much better to get rid of this false equivalence business altogether.
pgsql-hackers by date: