BUG #19746: interval with INT64_MIN microseconds prints a value interval_in rejects - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      19746
Logged by:          Ke
Email address:      kehan5800@gmail.com
PostgreSQL version: 18.6
Operating system:   Ubuntu 22.04.2 x86_64
Description:

An interval whose time part is -9223372036854775808 microseconds is a
legal, finite value (it is not -infinity unless months and days are also at
their minimum). It can be entered and is produced by ordinary arithmetic,
but its output cannot be read back:

    SELECT '-9223372036854775808 microseconds'::interval;
             interval
    --------------------------
     -2562047788:00:54.775808

    SELECT '-9223372036854775807 microseconds'::interval
           - '1 microsecond'::interval;
     -2562047788:00:54.775808

    SELECT '-2562047788:00:54.775808'::interval;
    ERROR:  22007: invalid input syntax for type interval:
"-2562047788:00:54.775808"
    LOCATION:  DateTimeParseError, datetime.c:4289

    SELECT '-2562047788:00:54.775807'::interval;     -- one us less: fine
    SELECT '-2562047788 hours -54.775808 secs'::interval;   -- fine

The regression suite already prints this value as expected output
(src/test/regress/expected/interval.out:1745,
"-178956970 years -7 mons -2147483648 days -2562047788:00:54.775808")
but never feeds the text back in.

pg_dump consequence (pg_dump forces IntervalStyle = postgres, which is one
of the affected styles):

    createdb src; createdb dst
    psql -d src -c "CREATE TABLE ivmin (id int PRIMARY KEY, i interval);
      INSERT INTO ivmin VALUES
        (1, '-9223372036854775807 us'::interval - '1 us'::interval),
        (2, '1 day');"
    pg_dump -d src | psql -d dst
    ...
    ERROR:  invalid input syntax for type interval:
"-2562047788:00:54.775808"
    CONTEXT:  COPY ivmin, line 1, column i: "-2562047788:00:54.775808"

    psql -d dst -c "SELECT count(*) FROM ivmin"     -- 0 (both rows lost)

Per IntervalStyle (from the attached transcript):

    value                            postgres  postgres_verbose
sql_standard  iso_8601
    -9223372036854775808 us          FAILS     FAILS             FAILS
ok
    -2147483648 days                 ok        FAILS             ok
ok

Cause: in DecodeInterval() (src/backend/utils/adt/datetime.c), a field such
as "-2562047788:00:54.775808" is tokenized as DTK_TZ, and the code at
datetime.c:3592 decodes the unsigned part first:

    if (strchr(field[i] + 1, ':') != NULL &&
        DecodeTimeForInterval(field[i] + 1, fmask, range,
                              &tmask, itm_in) == 0)
    {
        if (*field[i] == '-')
        {
            /* flip the sign on time field */
            if (itm_in->tm_usec == PG_INT64_MIN)
                return DTERR_FIELD_OVERFLOW;
            itm_in->tm_usec = -itm_in->tm_usec;
        }

DecodeTimeForInterval() (datetime.c:2781) accumulates the magnitude
2562047788 h * 3600000000 + 54775808 = 9223372036854775808 = INT64_MAX + 1
with int64_multiply_add(), which overflows, so the condition is false and
the field falls through to the generic path, which reports "invalid input
syntax". The signed result would have been representable.

The postgres_verbose case has the same shape on the days field:
AddVerboseIntPart() (datetime.c:4700-4709) prints i64abs(value) and appends
"ago", so -2147483648 days prints as "@ 2147483648 days ago", and
2147483648 does not fit the int32 days field on input:

    SELECT '@ 2147483648 days ago'::interval;
    ERROR:  interval field value out of range: "@ 2147483648 days ago"

Expected: interval_out() output is accepted by interval_in() for every
finite interval value (at least in the default IntervalStyle that pg_dump
uses), so dumps restore.

Actual: the minimum time value (and, in postgres_verbose, the minimum days
value) prints as text that interval_in() rejects; a table holding it cannot
be restored from pg_dump, and the whole COPY for that table fails.

Possible fix: let the input side accept a magnitude of exactly
INT64_MAX + 1 when it is going to be negated (e.g. decode the hh:mm:ss
magnitude into a uint64 or negate while accumulating, as the "ago" and
sign handling already does elsewhere). That would also make dumps already
written with this value restorable. Changing EncodeInterval() instead would
only help dumps taken after the fix.