Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16 - Mailing list pgsql-bugs

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";