Thread: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16
BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16
From
PG Bug reporting form
Date:
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
Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16
From
Alvaro Herrera
Date:
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
Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16
From
Tom Lane
Date:
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
Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16
From
Tom Lane
Date:
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
Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16
From
Tom Lane
Date:
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
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 1b86fff2fb..7abdc62f41 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -1970,6 +1970,17 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
break;
case DTK_TIME:
+
+ /*
+ * This might be an ISO time following a "t" field.
+ */
+ if (ptype != 0)
+ {
+ if (ptype != DTK_TIME)
+ return DTERR_BAD_FORMAT;
+ ptype = 0;
+ }
+
dterr = DecodeTime(field[i], (fmask | DTK_DATE_M),
INTERVAL_FULL_RANGE,
&tmask, tm, fsec);
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 195fe72644..241713cc51 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -274,6 +274,161 @@ SELECT time with time zone 'J2452271 T040506.789 America/Los_Angeles';
04:05:06.789-08
(1 row)
+-- Check time formats required by ISO 8601
+SELECT time without time zone '040506.07';
+ time
+-------------
+ 04:05:06.07
+(1 row)
+
+SELECT time without time zone '04:05:06.07';
+ time
+-------------
+ 04:05:06.07
+(1 row)
+
+SELECT time without time zone '040506';
+ time
+----------
+ 04:05:06
+(1 row)
+
+SELECT time without time zone '04:05:06';
+ time
+----------
+ 04:05:06
+(1 row)
+
+SELECT time without time zone '0405';
+ time
+----------
+ 04:05:00
+(1 row)
+
+SELECT time without time zone '04:05';
+ time
+----------
+ 04:05:00
+(1 row)
+
+SELECT time without time zone 'T040506.07';
+ time
+-------------
+ 04:05:06.07
+(1 row)
+
+SELECT time without time zone 'T04:05:06.07';
+ time
+-------------
+ 04:05:06.07
+(1 row)
+
+SELECT time without time zone 'T040506';
+ time
+----------
+ 04:05:06
+(1 row)
+
+SELECT time without time zone 'T04:05:06';
+ time
+----------
+ 04:05:06
+(1 row)
+
+SELECT time without time zone 'T0405';
+ time
+----------
+ 04:05:00
+(1 row)
+
+SELECT time without time zone 'T04:05';
+ time
+----------
+ 04:05:00
+(1 row)
+
+-- 8601 says "Thh" is allowed, but we intentionally reject it as too vague
+SELECT time without time zone 'T04';
+ERROR: invalid input syntax for type time: "T04"
+LINE 1: SELECT time without time zone 'T04';
+ ^
+SELECT time with time zone '040506.07+08';
+ timetz
+----------------
+ 04:05:06.07+08
+(1 row)
+
+SELECT time with time zone '04:05:06.07+08';
+ timetz
+----------------
+ 04:05:06.07+08
+(1 row)
+
+SELECT time with time zone '040506+08';
+ timetz
+-------------
+ 04:05:06+08
+(1 row)
+
+SELECT time with time zone '04:05:06+08';
+ timetz
+-------------
+ 04:05:06+08
+(1 row)
+
+SELECT time with time zone '0405+08';
+ timetz
+-------------
+ 04:05:00+08
+(1 row)
+
+SELECT time with time zone '04:05+08';
+ timetz
+-------------
+ 04:05:00+08
+(1 row)
+
+SELECT time with time zone 'T040506.07+08';
+ timetz
+----------------
+ 04:05:06.07+08
+(1 row)
+
+SELECT time with time zone 'T04:05:06.07+08';
+ timetz
+----------------
+ 04:05:06.07+08
+(1 row)
+
+SELECT time with time zone 'T040506+08';
+ timetz
+-------------
+ 04:05:06+08
+(1 row)
+
+SELECT time with time zone 'T04:05:06+08';
+ timetz
+-------------
+ 04:05:06+08
+(1 row)
+
+SELECT time with time zone 'T0405+08';
+ timetz
+-------------
+ 04:05:00+08
+(1 row)
+
+SELECT time with time zone 'T04:05+08';
+ timetz
+-------------
+ 04:05:00+08
+(1 row)
+
+-- 8601 says "Thh" is allowed, but we intentionally reject it as too vague
+SELECT time with time zone 'T04+08';
+ERROR: invalid input syntax for type time with time zone: "T04+08"
+LINE 1: SELECT time with time zone 'T04+08';
+ ^
SET DateStyle = 'Postgres, MDY';
-- Check Julian dates BC
SELECT date 'J1520447' AS "Confucius' Birthday";
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 2aa4a49b3f..e5cf12ff63 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -59,6 +59,35 @@ SELECT time with time zone 'T040506.789 -08';
SELECT time with time zone 'T040506.789 America/Los_Angeles';
SELECT time with time zone '2001-12-27 T040506.789 America/Los_Angeles';
SELECT time with time zone 'J2452271 T040506.789 America/Los_Angeles';
+-- Check time formats required by ISO 8601
+SELECT time without time zone '040506.07';
+SELECT time without time zone '04:05:06.07';
+SELECT time without time zone '040506';
+SELECT time without time zone '04:05:06';
+SELECT time without time zone '0405';
+SELECT time without time zone '04:05';
+SELECT time without time zone 'T040506.07';
+SELECT time without time zone 'T04:05:06.07';
+SELECT time without time zone 'T040506';
+SELECT time without time zone 'T04:05:06';
+SELECT time without time zone 'T0405';
+SELECT time without time zone 'T04:05';
+-- 8601 says "Thh" is allowed, but we intentionally reject it as too vague
+SELECT time without time zone 'T04';
+SELECT time with time zone '040506.07+08';
+SELECT time with time zone '04:05:06.07+08';
+SELECT time with time zone '040506+08';
+SELECT time with time zone '04:05:06+08';
+SELECT time with time zone '0405+08';
+SELECT time with time zone '04:05+08';
+SELECT time with time zone 'T040506.07+08';
+SELECT time with time zone 'T04:05:06.07+08';
+SELECT time with time zone 'T040506+08';
+SELECT time with time zone 'T04:05:06+08';
+SELECT time with time zone 'T0405+08';
+SELECT time with time zone 'T04:05+08';
+-- 8601 says "Thh" is allowed, but we intentionally reject it as too vague
+SELECT time with time zone 'T04+08';
SET DateStyle = 'Postgres, MDY';
-- Check Julian dates BC
SELECT date 'J1520447' AS "Confucius' Birthday";