On Sat, Mar 4, 2023 at 2:48 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > > Right. So really we ought to move the ValidateDate call as > well as the next half-dozen lines about "mer" down into > the subsequent "do additional checking" stanza. It's all > only relevant to normal date specs. > > BTW, looking at the set of RESERV tokens in datetktbl[], > it looks to me like this change renders the final "default:" > case unreachable, so probably we could just make that an error.
Please see the attached patch with these changes. - Joe Koshakow
From 64a71ed287aa9611c22eaa6e2cbb7e080d93be79 Mon Sep 17 00:00:00 2001 From: Joseph Koshakow <kosh...@gmail.com> Date: Sun, 11 Dec 2022 16:08:43 -0500 Subject: [PATCH] Handle extraneous fields in date-time input DecodeDateTime sometimest allowed extraneous fields to be included with reserved keywords. For example `date '1995-08-06 epoch'` would be parsed successfully, but the date was ignored. This commit fixes the issue so an error is returned instead. --- src/backend/utils/adt/datetime.c | 35 ++++++++++++++--------- src/test/regress/expected/date.out | 33 +++++++++++++++++++++ src/test/regress/expected/timestamp.out | 33 +++++++++++++++++++++ src/test/regress/expected/timestamptz.out | 33 +++++++++++++++++++++ src/test/regress/sql/date.sql | 10 +++++++ src/test/regress/sql/timestamp.sql | 10 +++++++ src/test/regress/sql/timestamptz.sql | 10 +++++++ 7 files changed, 150 insertions(+), 14 deletions(-) diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c index 01660637a2..0c1207223c 100644 --- a/src/backend/utils/adt/datetime.c +++ b/src/backend/utils/adt/datetime.c @@ -1431,8 +1431,15 @@ DecodeDateTime(char **field, int *ftype, int nf, *tzp = 0; break; - default: + case DTK_EPOCH: + case DTK_LATE: + case DTK_EARLY: + tmask = (DTK_DATE_M | DTK_TIME_M | DTK_M(TZ)); *dtype = val; + break; + + default: + return DTERR_BAD_FORMAT; } break; @@ -1567,22 +1574,22 @@ DecodeDateTime(char **field, int *ftype, int nf, fmask |= tmask; } /* end loop over fields */ - /* do final checking/adjustment of Y/M/D fields */ - dterr = ValidateDate(fmask, isjulian, is2digits, bc, tm); - if (dterr) - return dterr; - - /* handle AM/PM */ - if (mer != HR24 && tm->tm_hour > HOURS_PER_DAY / 2) - return DTERR_FIELD_OVERFLOW; - if (mer == AM && tm->tm_hour == HOURS_PER_DAY / 2) - tm->tm_hour = 0; - else if (mer == PM && tm->tm_hour != HOURS_PER_DAY / 2) - tm->tm_hour += HOURS_PER_DAY / 2; - /* do additional checking for full date specs... */ if (*dtype == DTK_DATE) { + /* do final checking/adjustment of Y/M/D fields */ + dterr = ValidateDate(fmask, isjulian, is2digits, bc, tm); + if (dterr) + return dterr; + + /* handle AM/PM */ + if (mer != HR24 && tm->tm_hour > HOURS_PER_DAY / 2) + return DTERR_FIELD_OVERFLOW; + if (mer == AM && tm->tm_hour == HOURS_PER_DAY / 2) + tm->tm_hour = 0; + else if (mer == PM && tm->tm_hour != HOURS_PER_DAY / 2) + tm->tm_hour += HOURS_PER_DAY / 2; + if ((fmask & DTK_DATE_M) != DTK_DATE_M) { if ((fmask & DTK_TIME_M) == DTK_TIME_M) diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out index f5949f3d17..c874f06546 100644 --- a/src/test/regress/expected/date.out +++ b/src/test/regress/expected/date.out @@ -1532,3 +1532,36 @@ select make_time(10, 55, 100.1); ERROR: time field value out of range: 10:55:100.1 select make_time(24, 0, 2.1); ERROR: time field value out of range: 24:00:2.1 +-- test errors with reserved keywords +SELECT date '1995-08-06 epoch'; +ERROR: invalid input syntax for type date: "1995-08-06 epoch" +LINE 1: SELECT date '1995-08-06 epoch'; + ^ +SELECT date '1995-08-06 infinity'; +ERROR: invalid input syntax for type date: "1995-08-06 infinity" +LINE 1: SELECT date '1995-08-06 infinity'; + ^ +SELECT date '1995-08-06 -infinity'; +ERROR: invalid input syntax for type date: "1995-08-06 -infinity" +LINE 1: SELECT date '1995-08-06 -infinity'; + ^ +SELECT date 'epoch 1995-08-06'; +ERROR: invalid input syntax for type date: "epoch 1995-08-06" +LINE 1: SELECT date 'epoch 1995-08-06'; + ^ +SELECT date 'infinity 1995-08-06'; +ERROR: invalid input syntax for type date: "infinity 1995-08-06" +LINE 1: SELECT date 'infinity 1995-08-06'; + ^ +SELECT date '-infinity 1995-08-06'; +ERROR: invalid input syntax for type date: "-infinity 1995-08-06" +LINE 1: SELECT date '-infinity 1995-08-06'; + ^ +SELECT date 'now infinity'; +ERROR: invalid input syntax for type date: "now infinity" +LINE 1: SELECT date 'now infinity'; + ^ +SELECT date '-infinity infinity'; +ERROR: invalid input syntax for type date: "-infinity infinity" +LINE 1: SELECT date '-infinity infinity'; + ^ diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out index c64bcb7c12..c2159c2cec 100644 --- a/src/test/regress/expected/timestamp.out +++ b/src/test/regress/expected/timestamp.out @@ -2125,3 +2125,36 @@ select * from generate_series('2020-01-01 00:00'::timestamp, '2020-01-02 03:00'::timestamp, '0 hour'::interval); ERROR: step size cannot equal zero +-- test errors with reserved keywords +SELECT timestamp '1995-08-06 01:01:01 epoch'; +ERROR: invalid input syntax for type timestamp: "1995-08-06 01:01:01 epoch" +LINE 1: SELECT timestamp '1995-08-06 01:01:01 epoch'; + ^ +SELECT timestamp '1995-08-06 01:01:01 infinity'; +ERROR: invalid input syntax for type timestamp: "1995-08-06 01:01:01 infinity" +LINE 1: SELECT timestamp '1995-08-06 01:01:01 infinity'; + ^ +SELECT timestamp '1995-08-06 01:01:01 -infinity'; +ERROR: invalid input syntax for type timestamp: "1995-08-06 01:01:01 -infinity" +LINE 1: SELECT timestamp '1995-08-06 01:01:01 -infinity'; + ^ +SELECT timestamp 'epoch 1995-08-06 01:01:01'; +ERROR: invalid input syntax for type timestamp: "epoch 1995-08-06 01:01:01" +LINE 1: SELECT timestamp 'epoch 1995-08-06 01:01:01'; + ^ +SELECT timestamp 'infinity 1995-08-06 01:01:01'; +ERROR: invalid input syntax for type timestamp: "infinity 1995-08-06 01:01:01" +LINE 1: SELECT timestamp 'infinity 1995-08-06 01:01:01'; + ^ +SELECT timestamp '-infinity 1995-08-06 01:01:01'; +ERROR: invalid input syntax for type timestamp: "-infinity 1995-08-06 01:01:01" +LINE 1: SELECT timestamp '-infinity 1995-08-06 01:01:01'; + ^ +SELECT timestamp 'today epoch'; +ERROR: invalid input syntax for type timestamp: "today epoch" +LINE 1: SELECT timestamp 'today epoch'; + ^ +SELECT timestamp '-infinity infinity'; +ERROR: invalid input syntax for type timestamp: "-infinity infinity" +LINE 1: SELECT timestamp '-infinity infinity'; + ^ diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out index 91d7c1f5cc..2f67ba9cc9 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -3100,3 +3100,36 @@ select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00'; Tue Jan 17 16:00:00 2017 PST (1 row) +-- test errors with reserved keywords +SELECT timestamptz '1995-08-06 01:01:01 epoch'; +ERROR: invalid input syntax for type timestamp with time zone: "1995-08-06 01:01:01 epoch" +LINE 1: SELECT timestamptz '1995-08-06 01:01:01 epoch'; + ^ +SELECT timestamptz '1995-08-06 01:01:01 infinity'; +ERROR: invalid input syntax for type timestamp with time zone: "1995-08-06 01:01:01 infinity" +LINE 1: SELECT timestamptz '1995-08-06 01:01:01 infinity'; + ^ +SELECT timestamptz '1995-08-06 01:01:01 -infinity'; +ERROR: invalid input syntax for type timestamp with time zone: "1995-08-06 01:01:01 -infinity" +LINE 1: SELECT timestamptz '1995-08-06 01:01:01 -infinity'; + ^ +SELECT timestamptz 'epoch 1995-08-06 01:01:01'; +ERROR: invalid input syntax for type timestamp with time zone: "epoch 1995-08-06 01:01:01" +LINE 1: SELECT timestamptz 'epoch 1995-08-06 01:01:01'; + ^ +SELECT timestamptz 'infinity 1995-08-06 01:01:01'; +ERROR: invalid input syntax for type timestamp with time zone: "infinity 1995-08-06 01:01:01" +LINE 1: SELECT timestamptz 'infinity 1995-08-06 01:01:01'; + ^ +SELECT timestamptz '-infinity 1995-08-06 01:01:01'; +ERROR: invalid input syntax for type timestamp with time zone: "-infinity 1995-08-06 01:01:01" +LINE 1: SELECT timestamptz '-infinity 1995-08-06 01:01:01'; + ^ +SELECT timestamptz 'today epoch'; +ERROR: invalid input syntax for type timestamp with time zone: "today epoch" +LINE 1: SELECT timestamptz 'today epoch'; + ^ +SELECT timestamptz '-infinity infinity'; +ERROR: invalid input syntax for type timestamp with time zone: "-infinity infinity" +LINE 1: SELECT timestamptz '-infinity infinity'; + ^ diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql index 1c58ff6966..5e7457b155 100644 --- a/src/test/regress/sql/date.sql +++ b/src/test/regress/sql/date.sql @@ -373,3 +373,13 @@ select make_date(2013, 13, 1); select make_date(2013, 11, -1); select make_time(10, 55, 100.1); select make_time(24, 0, 2.1); + +-- test errors with reserved keywords +SELECT date '1995-08-06 epoch'; +SELECT date '1995-08-06 infinity'; +SELECT date '1995-08-06 -infinity'; +SELECT date 'epoch 1995-08-06'; +SELECT date 'infinity 1995-08-06'; +SELECT date '-infinity 1995-08-06'; +SELECT date 'now infinity'; +SELECT date '-infinity infinity'; diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql index b9bcce9cfe..18b51e3b46 100644 --- a/src/test/regress/sql/timestamp.sql +++ b/src/test/regress/sql/timestamp.sql @@ -397,3 +397,13 @@ select generate_series('2022-01-01 00:00'::timestamp, select * from generate_series('2020-01-01 00:00'::timestamp, '2020-01-02 03:00'::timestamp, '0 hour'::interval); + +-- test errors with reserved keywords +SELECT timestamp '1995-08-06 01:01:01 epoch'; +SELECT timestamp '1995-08-06 01:01:01 infinity'; +SELECT timestamp '1995-08-06 01:01:01 -infinity'; +SELECT timestamp 'epoch 1995-08-06 01:01:01'; +SELECT timestamp 'infinity 1995-08-06 01:01:01'; +SELECT timestamp '-infinity 1995-08-06 01:01:01'; +SELECT timestamp 'today epoch'; +SELECT timestamp '-infinity infinity'; diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql index ae9ee4b56a..f91b6418d1 100644 --- a/src/test/regress/sql/timestamptz.sql +++ b/src/test/regress/sql/timestamptz.sql @@ -600,3 +600,13 @@ insert into tmptz values ('2017-01-18 00:00+00'); explain (costs off) select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00'; select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00'; + +-- test errors with reserved keywords +SELECT timestamptz '1995-08-06 01:01:01 epoch'; +SELECT timestamptz '1995-08-06 01:01:01 infinity'; +SELECT timestamptz '1995-08-06 01:01:01 -infinity'; +SELECT timestamptz 'epoch 1995-08-06 01:01:01'; +SELECT timestamptz 'infinity 1995-08-06 01:01:01'; +SELECT timestamptz '-infinity 1995-08-06 01:01:01'; +SELECT timestamptz 'today epoch'; +SELECT timestamptz '-infinity infinity'; -- 2.34.1