0001-to-timestamp-format-checking-v7.patch
text/x-patch
Filename: 0001-to-timestamp-format-checking-v7.patch
Type: text/x-patch
Part: 0
Message:
Re: Bug in to_timestamp().
Patch
Format: unified
Series: patch v7-0001
| File | + | − |
|---|---|---|
| doc/src/sgml/func.sgml | 15 | 1 |
| src/backend/utils/adt/formatting.c | 152 | 59 |
| src/test/regress/expected/horology.out | 67 | 7 |
| src/test/regress/sql/horology.sql | 22 | 2 |
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2e64cc4..5a4e248 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6159,7 +6159,8 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
<function>to_timestamp</function> and <function>to_date</function>
skip multiple blank spaces in the input string unless the
<literal>FX</literal> option is used. For example,
- <literal>to_timestamp('2000 JUN', 'YYYY MON')</literal> works, but
+ <literal>to_timestamp('2000 JUN', 'YYYY MON')</literal> and
+ <literal>to_timestamp('2000 JUN', 'YYYY MON')</literal> work, but
<literal>to_timestamp('2000 JUN', 'FXYYYY MON')</literal> returns an error
because <function>to_timestamp</function> expects one space only.
<literal>FX</literal> must be specified as the first item in
@@ -6169,6 +6170,19 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
<listitem>
<para>
+ <function>to_timestamp</function> and <function>to_date</function> don't
+ skip multiple printable non letter and non digit characters in the input
+ string, but skip them in the formatting string. For example,
+ <literal>to_timestamp('2000-JUN', 'YYYY/MON')</literal> and
+ <literal>to_timestamp('2000/JUN', 'YYYY//MON')</literal> work, but
+ <literal>to_timestamp('2000//JUN', 'YYYY/MON')</literal>
+ returns an error because count of the "/" character in the input string
+ doesn't match count of it in the formatting string.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
Ordinary text is allowed in <function>to_char</function>
templates and will be output literally. You can put a substring
in double quotes to force it to be interpreted as literal text
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index d4eaa50..d28ceec 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -169,6 +169,8 @@ struct FormatNode
#define NODE_TYPE_END 1
#define NODE_TYPE_ACTION 2
#define NODE_TYPE_CHAR 3
+#define NODE_TYPE_SEPARATOR 4
+#define NODE_TYPE_SPACE 5
#define SUFFTYPE_PREFIX 1
#define SUFFTYPE_POSTFIX 2
@@ -951,6 +953,7 @@ typedef struct NUMProc
static const KeyWord *index_seq_search(const char *str, const KeyWord *kw,
const int *index);
static const KeySuffix *suff_search(const char *str, const KeySuffix *suf, int type);
+static bool is_separator_char(const char *str);
static void NUMDesc_prepare(NUMDesc *num, FormatNode *n);
static void parse_format(FormatNode *node, const char *str, const KeyWord *kw,
const KeySuffix *suf, const int *index, int ver, NUMDesc *Num);
@@ -967,7 +970,6 @@ static void dump_node(FormatNode *node, int max);
static const char *get_th(char *num, int type);
static char *str_numth(char *dest, char *num, int type);
static int adjust_partial_year_to_2020(int year);
-static int strspace_len(char *str);
static void from_char_set_mode(TmFromChar *tmfc, const FromCharDateMode mode);
static void from_char_set_int(int *dest, const int value, const FormatNode *node);
static int from_char_parse_int_len(int *dest, char **src, const int len, FormatNode *node);
@@ -1040,6 +1042,16 @@ suff_search(const char *str, const KeySuffix *suf, int type)
return NULL;
}
+static bool
+is_separator_char(const char *str)
+{
+ /* ASCII printable character, but not letter or digit */
+ return (*str > 0x20 && *str < 0x7F &&
+ !(*str >= 'A' && *str <= 'Z') &&
+ !(*str >= 'a' && *str <= 'z') &&
+ !(*str >= '0' && *str <= '9'));
+}
+
/* ----------
* Prepare NUMDesc (number description struct) via FormatNode struct
* ----------
@@ -1225,9 +1237,10 @@ parse_format(FormatNode *node, const char *str, const KeyWord *kw,
{
const KeySuffix *s;
FormatNode *n;
+ bool in_text = false,
+ in_backslash = false;
int node_set = 0,
- suffix,
- last = 0;
+ suffix;
#ifdef DEBUG_TO_FROM_CHAR
elog(DEBUG_elog_output, "to_char/number(): run parser");
@@ -1239,6 +1252,50 @@ parse_format(FormatNode *node, const char *str, const KeyWord *kw,
{
suffix = 0;
+ /* Previous character was a backslash */
+ if (in_backslash)
+ {
+ in_backslash = false;
+
+ n->type = NODE_TYPE_CHAR;
+ n->character = *str;
+ n->key = NULL;
+ n->suffix = 0;
+ n++;
+ str++;
+ continue;
+ }
+ /* Previous character was a quote */
+ else if (in_text)
+ {
+ if (*str == '"')
+ {
+ str++;
+ in_text = false;
+ }
+ else if (*str == '\\')
+ {
+ str++;
+ in_backslash = true;
+ }
+ else
+ {
+ if (ver == DCH_TYPE && is_separator_char(str))
+ n->type = NODE_TYPE_SEPARATOR;
+ else if (isspace((unsigned char) *str))
+ n->type = NODE_TYPE_SPACE;
+ else
+ n->type = NODE_TYPE_CHAR;
+
+ n->character = *str;
+ n->key = NULL;
+ n->suffix = 0;
+ n++;
+ str++;
+ }
+ continue;
+ }
+
/*
* Prefix
*/
@@ -1278,48 +1335,30 @@ parse_format(FormatNode *node, const char *str, const KeyWord *kw,
}
else if (*str)
{
- /*
- * Special characters '\' and '"'
- */
- if (*str == '"' && last != '\\')
+ if (*str == '"')
{
- int x = 0;
-
- while (*(++str))
- {
- if (*str == '"' && x != '\\')
- {
- str++;
- break;
- }
- else if (*str == '\\' && x != '\\')
- {
- x = '\\';
- continue;
- }
- n->type = NODE_TYPE_CHAR;
- n->character = *str;
- n->key = NULL;
- n->suffix = 0;
- ++n;
- x = *str;
- }
+ in_text = true;
node_set = 0;
- suffix = 0;
- last = 0;
+ str++;
}
- else if (*str && *str == '\\' && last != '\\' && *(str + 1) == '"')
+ else if (*str == '\\')
{
- last = *str;
+ in_backslash = true;
+ node_set = 0;
str++;
}
- else if (*str)
+ else
{
- n->type = NODE_TYPE_CHAR;
+ if (ver == DCH_TYPE && is_separator_char(str))
+ n->type = NODE_TYPE_SEPARATOR;
+ else if (isspace((unsigned char) *str))
+ n->type = NODE_TYPE_SPACE;
+ else
+ n->type = NODE_TYPE_CHAR;
+
n->character = *str;
n->key = NULL;
node_set = 1;
- last = 0;
str++;
}
}
@@ -1336,6 +1375,17 @@ parse_format(FormatNode *node, const char *str, const KeyWord *kw,
}
}
+ if (in_backslash)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid escape sequence")));
+
+ /* If we didn't meet closing quotes */
+ if (in_text)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unexpected end of format string, expected '\"' character")));
+
n->type = NODE_TYPE_END;
n->suffix = 0;
}
@@ -2068,20 +2118,6 @@ adjust_partial_year_to_2020(int year)
return year;
}
-
-static int
-strspace_len(char *str)
-{
- int len = 0;
-
- while (*str && isspace((unsigned char) *str))
- {
- str++;
- len++;
- }
- return len;
-}
-
/*
* Set the date mode of a from-char conversion.
*
@@ -2151,11 +2187,6 @@ from_char_parse_int_len(int *dest, char **src, const int len, FormatNode *node)
char *init = *src;
int used;
- /*
- * Skip any whitespace before parsing the integer.
- */
- *src += strspace_len(*src);
-
Assert(len <= DCH_MAX_ITEM_SIZ);
used = (int) strlcpy(copy, *src, len + 1);
@@ -2934,19 +2965,62 @@ DCH_from_char(FormatNode *node, char *in, TmFromChar *out)
FormatNode *n;
char *s;
int len,
- value;
+ value,
+ prev_type = NODE_TYPE_SPACE;
bool fx_mode = false;
for (n = node, s = in; n->type != NODE_TYPE_END && *s != '\0'; n++)
{
- if (n->type != NODE_TYPE_ACTION)
+ /* Ignore all spaces after previous field */
+ if (prev_type == NODE_TYPE_ACTION)
+ while (*s != '\0' && isspace((unsigned char) *s))
+ s++;
+
+ if (n->type == NODE_TYPE_SPACE || n->type == NODE_TYPE_SEPARATOR)
+ {
+ /*
+ * In non FX (fixed format) mode we skip spaces and separator
+ * characters.
+ */
+ if (!fx_mode)
+ {
+ if (isspace((unsigned char) *s) || is_separator_char(s))
+ s++;
+
+ prev_type = n->type;
+ continue;
+ }
+
+ /* Checks for FX mode */
+ if (n->type == NODE_TYPE_SPACE && !isspace((unsigned char) *s))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+ errmsg("unexpected character \"%c\", expected \"%c\"",
+ *s, n->character),
+ errdetail("The given value did not match any of the allowed "
+ "values for this field.")));
+ else if (n->type == NODE_TYPE_SEPARATOR && n->character != *s)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+ errmsg("unexpected character \"%c\", expected \"%c\"",
+ *s, n->character),
+ errdetail("The given value did not match any of the allowed "
+ "values for this field.")));
+
+ s++;
+ prev_type = n->type;
+ continue;
+ }
+ else if (n->type == NODE_TYPE_CHAR)
{
/*
- * Separator, so consume one character from input string. Notice
- * we don't insist that the consumed character match the format's
- * character.
+ * Text character, so consume one character from input string.
+ * Notice we don't insist that the consumed character match the
+ * format's character.
+ * Text field ignores FX mode.
*/
s++;
+ prev_type = n->type;
continue;
}
@@ -2955,7 +3029,25 @@ DCH_from_char(FormatNode *node, char *in, TmFromChar *out)
{
while (*s != '\0' && isspace((unsigned char) *s))
s++;
+
+ /*
+ * Lost separator character from the input string. Separator
+ * character should match at least one space or separator character
+ * from the format string.
+ */
+ if (is_separator_char(s))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+ errmsg("unexpected character \"%c\"", *s),
+ errdetail("The given value did not match any of the allowed "
+ "values for this field.")));
}
+ else if (isspace((unsigned char) *s) || is_separator_char(s))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+ errmsg("unexpected character \"%c\"", *s),
+ errdetail("The given value did not match any of the allowed "
+ "values for this field.")));
from_char_set_mode(out, n->key->date_mode);
@@ -3194,6 +3286,7 @@ DCH_from_char(FormatNode *node, char *in, TmFromChar *out)
SKIP_THth(s, n->suffix);
break;
}
+ prev_type = n->type;
}
}
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index f9d12e0..96773fa 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -2769,14 +2769,32 @@ SELECT to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS');
Sat Feb 16 08:14:30 0097 PST
(1 row)
+SELECT TO_TIMESTAMP('2011$03!18 23_38_15', 'YYYY-MM-DD HH24:MI:SS');
+ to_timestamp
+------------------------------
+ Fri Mar 18 23:38:15 2011 PDT
+(1 row)
+
SELECT to_timestamp('1985 January 12', 'YYYY FMMonth DD');
to_timestamp
------------------------------
Sat Jan 12 00:00:00 1985 PST
(1 row)
+SELECT to_timestamp('1985 FMMonth 12', 'YYYY "FMMonth" DD');
+ to_timestamp
+------------------------------
+ Sat Jan 12 00:00:00 1985 PST
+(1 row)
+
+SELECT to_timestamp('1985 \ 12', 'YYYY \\ DD');
+ to_timestamp
+------------------------------
+ Sat Jan 12 00:00:00 1985 PST
+(1 row)
+
SELECT to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16',
- '"My birthday-> Year" YYYY, "Month:" FMMonth, "Day:" DD');
+ '"My birthday-> Year:" YYYY, "Month:" FMMonth, "Day:" DD');
to_timestamp
------------------------------
Sun May 16 00:00:00 1976 PDT
@@ -2789,12 +2807,18 @@ SELECT to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
(1 row)
SELECT to_timestamp('15 "text between quote marks" 98 54 45',
- E'HH24 "\\text between quote marks\\"" YY MI SS');
+ E'HH24 "\\"text between quote marks\\"" YY MI SS');
to_timestamp
------------------------------
Thu Jan 01 15:54:45 1998 PST
(1 row)
+SELECT to_timestamp('Year: 1976, Month: May, Day: 16', '" Year:" YYYY, "Month:" FMMonth, "Day:" DD');
+ to_timestamp
+------------------------------
+ Sun May 16 00:00:00 1976 PDT
+(1 row)
+
SELECT to_timestamp('05121445482000', 'MMDDHH24MISSYYYY');
to_timestamp
------------------------------
@@ -2808,8 +2832,23 @@ SELECT to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay');
(1 row)
SELECT to_timestamp('97/Feb/16', 'YYMonDD');
-ERROR: invalid value "/Fe" for "Mon"
+ERROR: unexpected character "/"
DETAIL: The given value did not match any of the allowed values for this field.
+SELECT to_timestamp('97/Feb/16', 'YY:Mon:DD');
+ to_timestamp
+------------------------------
+ Sun Feb 16 00:00:00 1997 PST
+(1 row)
+
+SELECT to_timestamp('97/Feb/16', 'FXYY:Mon:DD');
+ERROR: unexpected character "/", expected ":"
+DETAIL: The given value did not match any of the allowed values for this field.
+SELECT to_timestamp('97/Feb/16', 'FXYY/Mon/DD');
+ to_timestamp
+------------------------------
+ Sun Feb 16 00:00:00 1997 PST
+(1 row)
+
SELECT to_timestamp('19971116', 'YYYYMMDD');
to_timestamp
------------------------------
@@ -2936,7 +2975,7 @@ SELECT to_timestamp('2011-12-18 11:38 PM', 'YYYY-MM-DD HH12:MI PM');
SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS');
to_timestamp
------------------------------
- Sun Dec 18 03:38:15 2011 PST
+ Sun Dec 18 23:38:15 2011 PST
(1 row)
SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS');
@@ -2966,9 +3005,30 @@ SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS');
SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS');
to_timestamp
------------------------------
- Sun Dec 18 03:38:15 2011 PST
+ Sun Dec 18 23:38:15 2011 PST
(1 row)
+SELECT to_timestamp('2000----JUN', 'YYYY MON');
+ERROR: unexpected character "-"
+DETAIL: The given value did not match any of the allowed values for this field.
+SELECT to_timestamp('2000 + JUN', 'YYYY MON');
+ to_timestamp
+------------------------------
+ Thu Jun 01 00:00:00 2000 PDT
+(1 row)
+
+SELECT to_timestamp('2000 + + JUN', 'YYYY MON');
+ERROR: unexpected character "+"
+DETAIL: The given value did not match any of the allowed values for this field.
+SELECT to_timestamp('2000 + + JUN', 'YYYY MON');
+ to_timestamp
+------------------------------
+ Thu Jun 01 00:00:00 2000 PDT
+(1 row)
+
+SELECT to_timestamp('2000--JUN', 'YYYY/MON');
+ERROR: unexpected character "-"
+DETAIL: The given value did not match any of the allowed values for this field.
SELECT to_date('2011 12 18', 'YYYY MM DD');
to_date
------------
@@ -2984,13 +3044,13 @@ SELECT to_date('2011 12 18', 'YYYY MM DD');
SELECT to_date('2011 12 18', 'YYYY MM DD');
to_date
------------
- 12-08-2011
+ 12-18-2011
(1 row)
SELECT to_date('2011 12 18', 'YYYY MM DD');
to_date
------------
- 02-18-2011
+ 12-18-2011
(1 row)
SELECT to_date('2011 12 18', 'YYYY MM DD');
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index a7bc9dc..2563980 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -392,15 +392,23 @@ SELECT to_timestamp('0097/Feb/16 --> 08:14:30', 'YYYY/Mon/DD --> HH:MI:SS');
SELECT to_timestamp('97/2/16 8:14:30', 'FMYYYY/FMMM/FMDD FMHH:FMMI:FMSS');
+SELECT TO_TIMESTAMP('2011$03!18 23_38_15', 'YYYY-MM-DD HH24:MI:SS');
+
SELECT to_timestamp('1985 January 12', 'YYYY FMMonth DD');
+SELECT to_timestamp('1985 FMMonth 12', 'YYYY "FMMonth" DD');
+
+SELECT to_timestamp('1985 \ 12', 'YYYY \\ DD');
+
SELECT to_timestamp('My birthday-> Year: 1976, Month: May, Day: 16',
- '"My birthday-> Year" YYYY, "Month:" FMMonth, "Day:" DD');
+ '"My birthday-> Year:" YYYY, "Month:" FMMonth, "Day:" DD');
SELECT to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
SELECT to_timestamp('15 "text between quote marks" 98 54 45',
- E'HH24 "\\text between quote marks\\"" YY MI SS');
+ E'HH24 "\\"text between quote marks\\"" YY MI SS');
+
+SELECT to_timestamp('Year: 1976, Month: May, Day: 16', '" Year:" YYYY, "Month:" FMMonth, "Day:" DD');
SELECT to_timestamp('05121445482000', 'MMDDHH24MISSYYYY');
@@ -408,6 +416,12 @@ SELECT to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay');
SELECT to_timestamp('97/Feb/16', 'YYMonDD');
+SELECT to_timestamp('97/Feb/16', 'YY:Mon:DD');
+
+SELECT to_timestamp('97/Feb/16', 'FXYY:Mon:DD');
+
+SELECT to_timestamp('97/Feb/16', 'FXYY/Mon/DD');
+
SELECT to_timestamp('19971116', 'YYYYMMDD');
SELECT to_timestamp('20000-1116', 'YYYY-MMDD');
@@ -458,6 +472,12 @@ SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS');
SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS');
SELECT to_timestamp('2011-12-18 23:38:15', 'YYYY-MM-DD HH24:MI:SS');
+SELECT to_timestamp('2000----JUN', 'YYYY MON');
+SELECT to_timestamp('2000 + JUN', 'YYYY MON');
+SELECT to_timestamp('2000 + + JUN', 'YYYY MON');
+SELECT to_timestamp('2000 + + JUN', 'YYYY MON');
+SELECT to_timestamp('2000--JUN', 'YYYY/MON');
+
SELECT to_date('2011 12 18', 'YYYY MM DD');
SELECT to_date('2011 12 18', 'YYYY MM DD');
SELECT to_date('2011 12 18', 'YYYY MM DD');