Horton

Thread

Commits

  1. Fix input of ISO "extended" time format for types time and timetz.

  2. Tighten error checks in datetime input, and remove bogus "ISO" format.

  1. BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16

    The Post Office <noreply@postgresql.org> — 2024-05-17T11:04:53Z

    The following bug has been logged on the website:

Bug reference:      18470
Logged by:          david perez
Email address:      david.perez.ingeniero@gmail.com
PostgreSQL version: 16.3
Operating system:   Linux
Description:        

This is the result with Postgres 16:

postgres=# select VERSION();
                                                       version              
                                        
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

postgres=# select 'T14:00:00'::time;
ERROR:  invalid input syntax for type time: "T14:00:00"
LINE 1: select 'T14:00:00'::time;

And this is the result with Postgres 15:

postgres=# select VERSION();
                                                       version              
                                        
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.7 (Debian 15.7-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

postgres=# select 'T14:00:00'::time;
   time   
----------
 14:00:00
(1 row)

A time literal that begins with the letter "T" isn't accepted anymore.

I've read carefully this documentation:
https://www.postgresql.org/docs/current/datatype-datetime.html

It states that:

ISO 8601 specifies the use of uppercase letter T to separate the date and
time. PostgreSQL accepts that format on input, but on output it uses a space
rather than T, as shown above. This is for readability and for consistency
with RFC 3339 as well as some other database systems

  2. Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-05-17T13:46:45Z

    On 2024-May-17, PG Bug reporting form wrote:

> I've read carefully this documentation:
> https://www.postgresql.org/docs/current/datatype-datetime.html
> 
> It states that:
> 
> ISO 8601 specifies the use of uppercase letter T to separate the date and
> time. PostgreSQL accepts that format on input, but on output it uses a space
> rather than T, as shown above. This is for readability and for consistency
> with RFC 3339 as well as some other database systems

Hmm, at least the Wikipedia page claims that the leading T should be
accepted.
https://en.wikipedia.org/wiki/ISO_8601#Times


> postgres=# select 'T14:00:00'::time;
> ERROR:  invalid input syntax for type time: "T14:00:00"

This changed with commit 5b3c5953553b, "Tighten error checks in datetime
input, and remove bogus "ISO" format."

https://git.postgresql.org/cgit/postgresql.git/commit/?id=5b3c5953553bb9fb0b171abc6041e7c7e9ca5b4d

There's no test case modified in this commit that specified only a time
with no date, so assume this particular change was unintentional.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Most hackers will be perfectly comfortable conceptualizing users as entropy
 sources, so let's move on."                               (Nathaniel Smith)
      https://mail.gnu.org/archive/html/monotone-devel/2007-01/msg00080.html

  3. Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-05-17T14:37:35Z

    Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Hmm, at least the Wikipedia page claims that the leading T should be
> accepted.
> https://en.wikipedia.org/wiki/ISO_8601#Times

>> postgres=# select 'T14:00:00'::time;
>> ERROR:  invalid input syntax for type time: "T14:00:00"

> This changed with commit 5b3c5953553b, "Tighten error checks in datetime
> input, and remove bogus "ISO" format."

Thanks, I was just about to go bisect that for myself.

My recollection is that that commit meant to remove some formats
that we couldn't see any justification for in the ISO 8601 spec.
I'm not sure whether that wikipedia page should be taken
as authoritative, but if we can convince ourselves that
'T14:00:00' really is a thing per 8601, then yeah we should
undo that part.  If it's not a thing per the spec, then I'd
tend to say "sorry, but this was intentional to reduce the
code's propensity to accept garbage".

			regards, tom lane

  4. Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-05-17T18:01:51Z

    I wrote:
> I'm not sure whether that wikipedia page should be taken
> as authoritative, but if we can convince ourselves that
> 'T14:00:00' really is a thing per 8601, then yeah we should
> undo that part.

Given the lack of ready availability of 8601 itself, perhaps
we should assume that everyone's going to believe wikipedia.
In any case, what it says is pretty straightforward:

    As of ISO 8601-1:2019, the basic format is T[hh][mm][ss] and the
    extended format is T[hh]:[mm]:[ss]. Earlier versions omitted the T
    (representing time) in both formats.

    Either the seconds, or the minutes and seconds, may be omitted
    from the basic or extended time formats for greater brevity but
    decreased precision; the resulting reduced precision time formats
    are:

    T[hh][mm] in basic format or T[hh]:[mm] in extended format, when
    seconds are omitted.

    T[hh], when both seconds and minutes are omitted.

We previously accepted all of these except T[hh].  I'm inclined
to continue to reject that, even if it's nominally standards
compliant, because it seems way too likely to be a mistake.

Interestingly, the code does still take the "basic" (colon-free)
forms:

regression=# select time 'T1347';
   time   
----------
 13:47:00
(1 row)

regression=# select time 'T134712';
   time   
----------
 13:47:12
(1 row)

regression=# select time 'T134712.4';
    time    
------------
 13:47:12.4
(1 row)

So at this point, yeah that's a bug we should fix.
I'll look into it later if nobody beats me to it.

			regards, tom lane

  5. Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-05-17T21:52:48Z

    I wrote:
> So at this point, yeah that's a bug we should fix.
> I'll look into it later if nobody beats me to it.

It is failing because it sees an "unhandled prefix type",
that is ptype still nonzero after the parsing loop
in DecodeTimeOnly.  We need to reset ptype, as is done
in the comparable case in DecodeDateTime.

			regards, tom lane