Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s85VZ-009b6F-6T for pgsql-bugs@arkaria.postgresql.org; Fri, 17 May 2024 21:52:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s85VY-000lIE-Qx for pgsql-bugs@arkaria.postgresql.org; Fri, 17 May 2024 21:52:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s85VY-000lI6-IQ for pgsql-bugs@lists.postgresql.org; Fri, 17 May 2024 21:52:56 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s85VT-000mVo-Tw for pgsql-bugs@lists.postgresql.org; Fri, 17 May 2024 21:52:55 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 44HLqmDH2599240; Fri, 17 May 2024 17:52:48 -0400 From: Tom Lane To: Alvaro Herrera cc: david.perez.ingeniero@gmail.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16 In-reply-to: <2473625.1715968911@sss.pgh.pa.us> References: <202405171346.virffgus6cjg@alvherre.pgsql> <2393945.1715956655@sss.pgh.pa.us> <2473625.1715968911@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Fri, 17 May 2024 14:01:51 -0400" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <2599220.1715982752.0@sss.pgh.pa.us> Date: Fri, 17 May 2024 17:52:48 -0400 Message-ID: <2599239.1715982768@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2599220.1715982752.1@sss.pgh.pa.us> 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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="fix-bug-18470.patch"; charset="us-ascii" Content-ID: <2599220.1715982752.2@sss.pgh.pa.us> Content-Description: fix-bug-18470.patch Content-Transfer-Encoding: quoted-printable diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/date= time.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 !=3D 0) + { + if (ptype !=3D DTK_TIME) + return DTERR_BAD_FORMAT; + ptype =3D 0; + } + dterr =3D 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/exp= ected/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 Ame= rica/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 vagu= e +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 vagu= e +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 =3D '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/horo= logy.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 vagu= e +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 vagu= e +SELECT time with time zone 'T04+08'; SET DateStyle =3D 'Postgres, MDY'; -- Check Julian dates BC SELECT date 'J1520447' AS "Confucius' Birthday"; ------- =_aaaaaaaaaa0--