From d2f59497a3781a4146b06415177cb7545930beab Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 7 Dec 2024 13:12:32 -0500 Subject: [PATCH] Fix is_digit labeling of to_timestamp's FFn format codes. These format codes produce or consume strings of digits, so they should be labeled with is_digit = true, but they were not. This has effect in only one place, where is_next_separator() is checked to see if the preceding format code should slurp up all the available digits. Thus, with a format such as '...SSFF3' with remaining input '12345', the 'SS' code would consume all five digits (and then complain about seconds being out of range) when it should eat only two digits. Per report from Nick Davies. This bug goes back to d589f9446 where the FFn codes were introduced, so back-patch to v13. Discussion: https://postgr.es/m/AM8PR08MB6356AC979252CFEA78B56678B6312@AM8PR08MB6356.eurprd08.prod.outlook.com --- src/backend/utils/adt/formatting.c | 26 +++++++++++++------------- src/test/regress/expected/horology.out | 11 +++++++++++ src/test/regress/sql/horology.sql | 1 + 3 files changed, 25 insertions(+), 13 deletions(-) diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c index 7b1f080f80d..e301cf70f16 100644 --- a/src/backend/utils/adt/formatting.c +++ b/src/backend/utils/adt/formatting.c @@ -653,7 +653,7 @@ typedef enum DCH_Day, DCH_Dy, DCH_D, - DCH_FF1, + DCH_FF1, /* FFn codes must be consecutive */ DCH_FF2, DCH_FF3, DCH_FF4, @@ -819,12 +819,12 @@ static const KeyWord DCH_keywords[] = { {"Day", 3, DCH_Day, false, FROM_CHAR_DATE_NONE}, {"Dy", 2, DCH_Dy, false, FROM_CHAR_DATE_NONE}, {"D", 1, DCH_D, true, FROM_CHAR_DATE_GREGORIAN}, - {"FF1", 3, DCH_FF1, false, FROM_CHAR_DATE_NONE}, /* F */ - {"FF2", 3, DCH_FF2, false, FROM_CHAR_DATE_NONE}, - {"FF3", 3, DCH_FF3, false, FROM_CHAR_DATE_NONE}, - {"FF4", 3, DCH_FF4, false, FROM_CHAR_DATE_NONE}, - {"FF5", 3, DCH_FF5, false, FROM_CHAR_DATE_NONE}, - {"FF6", 3, DCH_FF6, false, FROM_CHAR_DATE_NONE}, + {"FF1", 3, DCH_FF1, true, FROM_CHAR_DATE_NONE}, /* F */ + {"FF2", 3, DCH_FF2, true, FROM_CHAR_DATE_NONE}, + {"FF3", 3, DCH_FF3, true, FROM_CHAR_DATE_NONE}, + {"FF4", 3, DCH_FF4, true, FROM_CHAR_DATE_NONE}, + {"FF5", 3, DCH_FF5, true, FROM_CHAR_DATE_NONE}, + {"FF6", 3, DCH_FF6, true, FROM_CHAR_DATE_NONE}, {"FX", 2, DCH_FX, false, FROM_CHAR_DATE_NONE}, {"HH24", 4, DCH_HH24, true, FROM_CHAR_DATE_NONE}, /* H */ {"HH12", 4, DCH_HH12, true, FROM_CHAR_DATE_NONE}, @@ -875,12 +875,12 @@ static const KeyWord DCH_keywords[] = { {"dd", 2, DCH_DD, true, FROM_CHAR_DATE_GREGORIAN}, {"dy", 2, DCH_dy, false, FROM_CHAR_DATE_NONE}, {"d", 1, DCH_D, true, FROM_CHAR_DATE_GREGORIAN}, - {"ff1", 3, DCH_FF1, false, FROM_CHAR_DATE_NONE}, /* f */ - {"ff2", 3, DCH_FF2, false, FROM_CHAR_DATE_NONE}, - {"ff3", 3, DCH_FF3, false, FROM_CHAR_DATE_NONE}, - {"ff4", 3, DCH_FF4, false, FROM_CHAR_DATE_NONE}, - {"ff5", 3, DCH_FF5, false, FROM_CHAR_DATE_NONE}, - {"ff6", 3, DCH_FF6, false, FROM_CHAR_DATE_NONE}, + {"ff1", 3, DCH_FF1, true, FROM_CHAR_DATE_NONE}, /* f */ + {"ff2", 3, DCH_FF2, true, FROM_CHAR_DATE_NONE}, + {"ff3", 3, DCH_FF3, true, FROM_CHAR_DATE_NONE}, + {"ff4", 3, DCH_FF4, true, FROM_CHAR_DATE_NONE}, + {"ff5", 3, DCH_FF5, true, FROM_CHAR_DATE_NONE}, + {"ff6", 3, DCH_FF6, true, FROM_CHAR_DATE_NONE}, {"fx", 2, DCH_FX, false, FROM_CHAR_DATE_NONE}, {"hh24", 4, DCH_HH24, true, FROM_CHAR_DATE_NONE}, /* h */ {"hh12", 4, DCH_HH12, true, FROM_CHAR_DATE_NONE}, diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out index e41a71a6723..9caae4ff429 100644 --- a/src/test/regress/expected/horology.out +++ b/src/test/regress/expected/horology.out @@ -3054,6 +3054,17 @@ SELECT i, to_timestamp('2018-11-02 12:34:56.123456', 'YYYY-MM-DD HH24:MI:SS.FF' SELECT i, to_timestamp('2018-11-02 12:34:56.123456789', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; ERROR: date/time field value out of range: "2018-11-02 12:34:56.123456789" +SELECT i, to_timestamp('20181102123456123456', 'YYYYMMDDHH24MISSFF' || i) FROM generate_series(1, 6) i; + i | to_timestamp +---+------------------------------------- + 1 | Fri Nov 02 12:34:56.1 2018 PDT + 2 | Fri Nov 02 12:34:56.12 2018 PDT + 3 | Fri Nov 02 12:34:56.123 2018 PDT + 4 | Fri Nov 02 12:34:56.1235 2018 PDT + 5 | Fri Nov 02 12:34:56.12346 2018 PDT + 6 | Fri Nov 02 12:34:56.123456 2018 PDT +(6 rows) + SELECT to_date('1 4 1902', 'Q MM YYYY'); -- Q is ignored to_date ------------ diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql index d3a68264d7c..e2e151dd691 100644 --- a/src/test/regress/sql/horology.sql +++ b/src/test/regress/sql/horology.sql @@ -480,6 +480,7 @@ SELECT i, to_timestamp('2018-11-02 12:34:56.1234', 'YYYY-MM-DD HH24:MI:SS.FF' || SELECT i, to_timestamp('2018-11-02 12:34:56.12345', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; SELECT i, to_timestamp('2018-11-02 12:34:56.123456', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; SELECT i, to_timestamp('2018-11-02 12:34:56.123456789', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; +SELECT i, to_timestamp('20181102123456123456', 'YYYYMMDDHH24MISSFF' || i) FROM generate_series(1, 6) i; SELECT to_date('1 4 1902', 'Q MM YYYY'); -- Q is ignored SELECT to_date('3 4 21 01', 'W MM CC YY'); -- 2.39.5