Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: jian he <jian(dot)universality(at)gmail(dot)com>, Francisco Olarte <folarte(at)peoplecall(dot)com>, Michael Bondarenko <work(dot)michael(dot)2956(at)gmail(dot)com>, pgsql-bugs(at)lists(dot)postgresql(dot)org, dgrowleyml(at)gmail(dot)com, Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com>
Subject: Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);
Date: 2024-08-16 02:45:58
Message-ID: Zr69ZnXWlS_2_bvI@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

On Mon, Jul 8, 2024 at 01:03:28PM -0400, Tom Lane wrote:
> I took another look at this issue and got annoyed by the fact that the
> proposed coding for "quarter" still doesn't satisfy the rule that
> the output for a negative interval should be the negative of the
> output for the sign-reversed interval. Specifically, if the month
> field is zero, the v2 patch always emits 1:
>
> regression=# select extract(quarter from interval '1 day');
> extract
> ---------
> 1
> (1 row)
>
> regression=# select extract(quarter from interval '-1 day');
> extract
> ---------
> 1
> (1 row)
>
> We could fix that by examining the sign of the lower-order fields
> when month is zero, as in the v3 patch attached. However, I'm not
> at all sure this is really better than v2. Notably, it makes the
> documentation's statement that the result is "the month field
> divided by 3 plus 1" even more incomplete. I still don't really
> want to go into details about the behavior for negative intervals.
> OTOH if we did do that, I'd rather write a blanket statement
> about the result being the negative of the result for a positive
> interval.
>
> Thoughts?

I tested master, patch version 2 and patch version 3 with some sample
extract() queires, attached. I like patch version 2. Patch version 3
bothers me because "-600 days" is ignored if months is non-zero, and
used for its sign for zero month values, which seems odd to me; better
to ignore it.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

Attachment Content-Type Size
extract.sql application/sql 216 bytes
master.out text/plain 336 bytes
patch2.out text/plain 336 bytes
patch3.out text/plain 336 bytes

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Fire Emerald 2024-08-16 07:49:27 Re: TLS session tickets disabled?
Previous Message Tom Lane 2024-08-15 20:32:32 Re: Using current_user as an argument of pl/pgsql function affects collation of other arguments

Browse pgsql-hackers by date

  From Date Subject
Next Message Joseph Koshakow 2024-08-16 02:49:46 Re: Remove dependence on integer wrapping
Previous Message jian he 2024-08-16 02:12:00 Re: SQL:2011 application time