On Sun, Dec 11, 2022 at 10:29 AM Joseph Koshakow <kosh...@gmail.com> wrote:
>
> Hi all,
>
> Attached is a patch to fix a parsing error for date-time types that
> allow dangling units in the input. For example,
> `date '1995-08-06 m y d'` was considered a valid date and the dangling
> units were ignored.
>
> Intervals also suffer from a similar issue, but the attached patch
> doesn't fix that issue. For example,
> `interval '1 day second month 6 hours days years ago'` is parsed as a
> valid interval with -1 days and -6 hours. I'm hoping to fix that in a
> later patch, but it will likely be more complicated than the other
> date-time fixes.
>
> - Joe Koshakow
I think I sent that to the wrong email address.
From fbcf39211fc7a379ea021160298604694383d56c Mon Sep 17 00:00:00 2001
From: Joseph Koshakow <kosh...@gmail.com>
Date: Sat, 10 Dec 2022 18:59:26 -0500
Subject: [PATCH] Handle dangling units in date-time input
DecodeDateTime and DecodeTimeOnly allowed dangling unit types on input
without returning an error. For example, `date '1995-08-06 m y d'` was
considered a valid date and the dangling units were ignored. This
commit fixes this issue so an error is returned instead.
---
src/backend/utils/adt/datetime.c | 8 ++++++++
src/test/regress/expected/date.out | 5 +++++
src/test/regress/expected/time.out | 5 +++++
src/test/regress/expected/timestamp.out | 5 +++++
src/test/regress/expected/timestamptz.out | 5 +++++
src/test/regress/expected/timetz.out | 5 +++++
src/test/regress/sql/date.sql | 3 +++
src/test/regress/sql/time.sql | 3 +++
src/test/regress/sql/timestamp.sql | 3 +++
src/test/regress/sql/timestamptz.sql | 3 +++
src/test/regress/sql/timetz.sql | 3 +++
11 files changed, 48 insertions(+)
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index b5b117a8ca..a985d1b6ea 100644
--- a/src/backend/utils/adt/datetime.c
+++ b/src/backend/utils/adt/datetime.c
@@ -1566,6 +1566,10 @@ DecodeDateTime(char **field, int *ftype, int nf,
fmask |= tmask;
} /* end loop over fields */
+ /* prefix type was dangling and never handled */
+ if (ptype != 0)
+ return DTERR_BAD_FORMAT;
+
/* do final checking/adjustment of Y/M/D fields */
dterr = ValidateDate(fmask, isjulian, is2digits, bc, tm);
if (dterr)
@@ -2415,6 +2419,10 @@ DecodeTimeOnly(char **field, int *ftype, int nf,
fmask |= tmask;
} /* end loop over fields */
+ /* prefix type was dangling and never handled */
+ if (ptype != 0)
+ return DTERR_BAD_FORMAT;
+
/* do final checking/adjustment of Y/M/D fields */
dterr = ValidateDate(fmask, isjulian, is2digits, bc, tm);
if (dterr)
diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out
index f8f83e40e9..fec466a594 100644
--- a/src/test/regress/expected/date.out
+++ b/src/test/regress/expected/date.out
@@ -1526,3 +1526,8 @@ 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 error on dangling units
+SELECT date '1995-08-06 m';
+ERROR: invalid input syntax for type date: "1995-08-06 m"
+LINE 1: SELECT date '1995-08-06 m';
+ ^
diff --git a/src/test/regress/expected/time.out b/src/test/regress/expected/time.out
index a44caededd..5f7058eca8 100644
--- a/src/test/regress/expected/time.out
+++ b/src/test/regress/expected/time.out
@@ -229,3 +229,8 @@ SELECT date_part('epoch', TIME '2020-05-26 13:30:25.575401');
48625.575401
(1 row)
+-- test error on dangling units
+SELECT time '12:30:15 d';
+ERROR: invalid input syntax for type time: "12:30:15 d"
+LINE 1: SELECT time '12:30:15 d';
+ ^
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index be66274738..6fce7319eb 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -2110,3 +2110,8 @@ 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 error on dangling units
+SELECT timestamp '1995-08-06 12:30:15 y';
+ERROR: invalid input syntax for type timestamp: "1995-08-06 12:30:15 y"
+LINE 1: SELECT timestamp '1995-08-06 12:30:15 y';
+ ^
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index fb06acbccc..565c5595ea 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -3085,3 +3085,8 @@ 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 error on dangling units
+SELECT timestamptz '1995-08-06 12:30:15 m';
+ERROR: invalid input syntax for type timestamp with time zone: "1995-08-06 12:30:15 m"
+LINE 1: SELECT timestamptz '1995-08-06 12:30:15 m';
+ ^
diff --git a/src/test/regress/expected/timetz.out b/src/test/regress/expected/timetz.out
index 984285663b..2ddf5e43ba 100644
--- a/src/test/regress/expected/timetz.out
+++ b/src/test/regress/expected/timetz.out
@@ -262,3 +262,8 @@ SELECT date_part('epoch', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-
63025.575401
(1 row)
+-- test error on dangling units
+SELECT timetz '12:30:15 d';
+ERROR: invalid input syntax for type time with time zone: "12:30:15 d"
+LINE 1: SELECT timetz '12:30:15 d';
+ ^
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index 9fd15be5f9..9eb85dfc18 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -371,3 +371,6 @@ 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 error on dangling units
+SELECT date '1995-08-06 m';
diff --git a/src/test/regress/sql/time.sql b/src/test/regress/sql/time.sql
index b439cd6b41..4f1c14226c 100644
--- a/src/test/regress/sql/time.sql
+++ b/src/test/regress/sql/time.sql
@@ -77,3 +77,6 @@ SELECT date_part('microsecond', TIME '2020-05-26 13:30:25.575401');
SELECT date_part('millisecond', TIME '2020-05-26 13:30:25.575401');
SELECT date_part('second', TIME '2020-05-26 13:30:25.575401');
SELECT date_part('epoch', TIME '2020-05-26 13:30:25.575401');
+
+-- test error on dangling units
+SELECT time '12:30:15 d';
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index e1175b12ce..031025bd85 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -391,3 +391,6 @@ 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 error on dangling units
+SELECT timestamp '1995-08-06 12:30:15 y';
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index 27263b3e0b..3ffef6d8a5 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -594,3 +594,6 @@ 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 error on dangling units
+SELECT timestamptz '1995-08-06 12:30:15 m';
diff --git a/src/test/regress/sql/timetz.sql b/src/test/regress/sql/timetz.sql
index b62aa3fe05..ccbfc31d64 100644
--- a/src/test/regress/sql/timetz.sql
+++ b/src/test/regress/sql/timetz.sql
@@ -84,3 +84,6 @@ SELECT date_part('microsecond', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-
SELECT date_part('millisecond', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
SELECT date_part('second', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
SELECT date_part('epoch', TIME WITH TIME ZONE '2020-05-26 13:30:25.575401-04');
+
+-- test error on dangling units
+SELECT timetz '12:30:15 d';
--
2.34.1
