pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16
5+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2024-05-22 019ea7675c a9a7c2c3e1 Fix input of ISO "extended" time format for types time and timetz.
* BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16
@ 2024-05-17 11:04 PG Bug reporting form <noreply@postgresql.org>
2024-05-17 13:46 ` Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16 Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2024-05-17 11:04 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: david.perez.ingeniero@gmail.com
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16
2024-05-17 11:04 BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16 PG Bug reporting form <noreply@postgresql.org>
@ 2024-05-17 13:46 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-05-17 14:37 ` Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Alvaro Herrera @ 2024-05-17 13:46 UTC (permalink / raw)
To: david.perez.ingeniero@gmail.com, pgsql-bugs@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16
2024-05-17 11:04 BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16 PG Bug reporting form <noreply@postgresql.org>
2024-05-17 13:46 ` 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-17 14:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-05-17 18:01 ` Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2024-05-17 14:37 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: david.perez.ingeniero@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16
2024-05-17 11:04 BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16 PG Bug reporting form <noreply@postgresql.org>
2024-05-17 13:46 ` 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-17 14:37 ` 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-17 18:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-05-17 21:52 ` Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2024-05-17 18:01 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: david.perez.ingeniero@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16
2024-05-17 11:04 BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16 PG Bug reporting form <noreply@postgresql.org>
2024-05-17 13:46 ` 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-17 14:37 ` 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-17 18:01 ` 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-17 21:52 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2024-05-17 21:52 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: david.perez.ingeniero@gmail.com; pgsql-bugs@lists.postgresql.org
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
Attachments:
fix-bug-18470.patch (text/x-diff, 5.7K) download
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";
end of thread, other threads:[~2024-05-17 21:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-17 11:04 BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16 PG Bug reporting form <noreply@postgresql.org>
2024-05-17 13:46 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-05-17 14:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-05-17 18:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-05-17 21:52 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox