Sorry. I did not get last two mails from Amul. Don't know why. So I
reply to another mail.
Documented as working case, but unfortunatly it does not :
postgres=# SELECT to_timestamp('2000----JUN', 'YYYY MON');
ERROR: invalid value "---" for "MON"
DETAIL: The given value did not match any of the allowed values for this field.
Indeed! Fixed it. Now this query executes without error. Added this case
to tests.
NODE_TYPE_CHAR assumption in else block seems incorrect. What if we have space
after double quote? It will again have incorrect output without any error, see
below:
postgres=# SELECT to_timestamp('Year: 1976, Month: May, Day: 16',
postgres(# '" Year:" YYYY, "Month:" FMMonth, "Day:" DD');
to_timestamp
------------------------------
0006-05-16 00:00:00-07:52:58
(1 row)
I guess, we might need NODE_TYPE_SEPARATOR and NODE_TYPE_SPACE check as well?
Agree. Fixed and added to tests.
Unnecessary hunk?
We should not touch any code unless it necessary to implement proposed feature,
otherwise it will add unnecessary diff to the patch and eventually extra burden
to review the code. Similarly hunk in the patch @ line # 313 - 410, nothing to
do with to_timestamp behaviour improvement, IIUC.
If you think this changes need to be in, please submit separate cleanup-patch.
Fixed it. It was a typo.
About lines # 313 - 410. It is necessary to avoid bugs. I wrote aboud it
in
https://www.postgresql.org/message-id/b2a39359-3282-b402-f4a3-057aae500ee7%40postgrespro.ru
:
- now DCH_cache_getnew() is called after parse_format(). Because now
parse_format() can raise an error and in the next attempt
DCH_cache_search() could return broken cache entry.
For example, you can test incorrect inputs for to_timestamp(). Try to
execute such query several times.
--
Artur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6355300..0fe50e1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6146,9 +6146,12 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
<listitem>
<para>
<function>to_timestamp</function> and <function>to_date</function>
- skip multiple blank spaces in the input string unless the
+ skip multiple blank spaces and printable non letter and non digit
+ characters in the input string and in the formatting 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>,
+ <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
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index bbd97dc..a3dbcaf 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
@@ -947,6 +949,7 @@ typedef struct NUMProc
static const KeyWord *index_seq_search(char *str, const KeyWord *kw,
const int *index);
static const KeySuffix *suff_search(char *str, const KeySuffix *suf, int type);
+static bool is_char_separator(char *str);
static void NUMDesc_prepare(NUMDesc *num, FormatNode *n);
static void parse_format(FormatNode *node, char *str, const KeyWord *kw,
const KeySuffix *suf, const int *index, int ver, NUMDesc *Num);
@@ -963,7 +966,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);
@@ -1036,6 +1038,17 @@ suff_search(char *str, const KeySuffix *suf, int type)
return NULL;
}
+static bool
+is_char_separator(char *str)
+{
+ return ((pg_mblen(str) == 1) &&
+ /* printable character, but not letter and digit */
+ ((*str >= '!' && *str <= '/') ||
+ (*str >= ':' && *str <= '@') ||
+ (*str >= '[' && *str <= '`') ||
+ (*str >= '{' && *str <= '~')));
+}
+
/* ----------
* Prepare NUMDesc (number description struct) via FormatNode struct
* ----------
@@ -1237,9 +1250,10 @@ parse_format(FormatNode *node, 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");
@@ -1251,6 +1265,55 @@ parse_format(FormatNode *node, char *str, const KeyWord *kw,
{
suffix = 0;
+ /* Previous character was a backslash */
+ if (in_backslash)
+ {
+ /* After backslash should go non-space character */
+ if (isspace(*str))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid escape sequence")));
+ 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_char_separator(str))
+ n->type = NODE_TYPE_SEPARATOR;
+ else if (isspace(*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
*/
@@ -1290,48 +1353,30 @@ parse_format(FormatNode *node, 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_char_separator(str))
+ n->type = NODE_TYPE_SEPARATOR;
+ else if (isspace(*str))
+ n->type = NODE_TYPE_SPACE;
+ else
+ n->type = NODE_TYPE_CHAR;
+
n->character = *str;
n->key = NULL;
node_set = 1;
- last = 0;
str++;
}
}
@@ -1348,6 +1393,17 @@ parse_format(FormatNode *node, 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;
return;
@@ -2081,20 +2137,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.
*
@@ -2164,11 +2206,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);
@@ -2947,19 +2984,56 @@ DCH_from_char(FormatNode *node, char *in, TmFromChar *out)
FormatNode *n;
char *s;
int len,
- value;
+ value,
+ prev_type;
bool fx_mode = false;
for (n = node, s = in; n->type != NODE_TYPE_END && *s != '\0'; n++)
{
- if (n->type != NODE_TYPE_ACTION)
+ if (n->type == NODE_TYPE_SPACE || n->type == NODE_TYPE_SEPARATOR)
{
/*
- * Separator, so consume one character from input string. Notice
- * we don't insist that the consumed character match the format's
- * character.
+ * In non FX (fixed format) mode we skip spaces and separator
+ * characters.
*/
+ if (!fx_mode)
+ {
+ if (isspace(*s) || is_char_separator(s))
+ s++;
+ prev_type = n->type;
+ continue;
+ }
+
+ /* Checks for FX mode */
+ if (n->type == NODE_TYPE_SPACE && !isspace(*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)
+ {
+ /*
+ * 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;
}
@@ -2968,7 +3042,20 @@ DCH_from_char(FormatNode *node, char *in, TmFromChar *out)
{
while (*s != '\0' && isspace((unsigned char) *s))
s++;
+ /*
+ * Ignore separator characters if previous node was space or
+ * separator and current is not.
+ */
+ if (prev_type == NODE_TYPE_SPACE || prev_type == NODE_TYPE_SEPARATOR)
+ while (*s != '\0' && is_char_separator(s))
+ s++;
}
+ else if (isspace(*s) || is_char_separator(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);
@@ -3207,6 +3294,7 @@ DCH_from_char(FormatNode *node, char *in, TmFromChar *out)
SKIP_THth(s, n->suffix);
break;
}
+ prev_type = n->type;
}
}
@@ -3340,15 +3428,17 @@ datetime_to_char_body(TmToChar *tmtc, text *fmt, bool is_interval, Oid collid)
if ((ent = DCH_cache_search(fmt_str)) == NULL)
{
- ent = DCH_cache_getnew(fmt_str);
+ FormatNode buf[DCH_CACHE_SIZE + 1];
/*
* Not in the cache, must run parser and save a new format-picture
* to the cache.
*/
- parse_format(ent->format, fmt_str, DCH_keywords,
+ parse_format(buf, fmt_str, DCH_keywords,
DCH_suff, DCH_index, DCH_TYPE, NULL);
+ ent = DCH_cache_getnew(fmt_str);
+ memcpy(ent->format, buf, sizeof(buf));
(ent->format + fmt_len)->type = NODE_TYPE_END; /* Paranoia? */
#ifdef DEBUG_TO_FROM_CHAR
@@ -3604,15 +3694,17 @@ do_to_timestamp(text *date_txt, text *fmt,
if ((ent = DCH_cache_search(fmt_str)) == NULL)
{
- ent = DCH_cache_getnew(fmt_str);
+ FormatNode buf[DCH_CACHE_SIZE + 1];
/*
* Not in the cache, must run parser and save a new
* format-picture to the cache.
*/
- parse_format(ent->format, fmt_str, DCH_keywords,
+ parse_format(buf, fmt_str, DCH_keywords,
DCH_suff, DCH_index, DCH_TYPE, NULL);
+ ent = DCH_cache_getnew(fmt_str);
+ memcpy(ent->format, buf, sizeof(buf));
(ent->format + fmt_len)->type = NODE_TYPE_END; /* Paranoia? */
#ifdef DEBUG_TO_FROM_CHAR
/* dump_node(ent->format, fmt_len); */
@@ -3981,32 +4073,41 @@ NUM_cache(int len, NUMDesc *Num, text *pars_str, bool *shouldFree)
if ((ent = NUM_cache_search(str)) == NULL)
{
- ent = NUM_cache_getnew(str);
+ FormatNode buf[NUM_CACHE_SIZE + 1];
+
+ zeroize_NUM(Num);
/*
* Not in the cache, must run parser and save a new format-picture
* to the cache.
*/
- parse_format(ent->format, str, NUM_keywords,
- NULL, NUM_index, NUM_TYPE, &ent->Num);
+ parse_format(buf, str, NUM_keywords,
+ NULL, NUM_index, NUM_TYPE, Num);
+
+ ent = NUM_cache_getnew(str);
+
+ memcpy(ent->format, buf, sizeof(buf));
+ memcpy(&ent->Num, Num, sizeof(NUMDesc));
(ent->format + len)->type = NODE_TYPE_END; /* Paranoia? */
}
+ else
+ {
+ /*
+ * Copy cache to used struct
+ */
+ Num->flag = ent->Num.flag;
+ Num->lsign = ent->Num.lsign;
+ Num->pre = ent->Num.pre;
+ Num->post = ent->Num.post;
+ Num->pre_lsign_num = ent->Num.pre_lsign_num;
+ Num->need_locale = ent->Num.need_locale;
+ Num->multi = ent->Num.multi;
+ Num->zero_start = ent->Num.zero_start;
+ Num->zero_end = ent->Num.zero_end;
+ }
format = ent->format;
-
- /*
- * Copy cache to used struct
- */
- Num->flag = ent->Num.flag;
- Num->lsign = ent->Num.lsign;
- Num->pre = ent->Num.pre;
- Num->post = ent->Num.post;
- Num->pre_lsign_num = ent->Num.pre_lsign_num;
- Num->need_locale = ent->Num.need_locale;
- Num->multi = ent->Num.multi;
- Num->zero_start = ent->Num.zero_start;
- Num->zero_end = ent->Num.zero_end;
}
#ifdef DEBUG_TO_FROM_CHAR
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 1fe02be..46d4c7b 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
------------------------------
@@ -2810,6 +2834,21 @@ SELECT to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay');
SELECT to_timestamp('97/Feb/16', 'YYMonDD');
ERROR: invalid value "/Fe" for "Mon"
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
------------------------------
@@ -2912,7 +2951,7 @@ SELECT to_timestamp(' 20050302', 'YYYYMMDD');
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');
@@ -2942,7 +2981,13 @@ 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');
+ to_timestamp
+------------------------------
+ Thu Jun 01 00:00:00 2000 PDT
(1 row)
SELECT to_date('2011 12 18', 'YYYY MM DD');
@@ -2960,13 +3005,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 c81437b..966cde1 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');
@@ -452,6 +466,8 @@ 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_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');
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 559c55f..e86dd0c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6100,13 +6100,9 @@ SELECT regexp_matches('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
<para>
<function>to_timestamp</function> and <function>to_date</function>
exist to handle input formats that cannot be converted by
- simple casting. These functions interpret input liberally,
- with minimal error checking. While they produce valid output,
- the conversion can yield unexpected results. For example,
- input to these functions is not restricted by normal ranges,
- thus <literal>to_date('20096040','YYYYMMDD')</literal> returns
- <literal>2014-01-17</literal> rather than causing an error.
- Casting does not have this behavior.
+ simple casting. These functions have error checking as well as casting.
+ For example, <literal>to_date('20096040','YYYYMMDD')</literal> returns
+ an error.
</para>
</listitem>
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index b14678d..98d9847 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -3635,7 +3635,10 @@ do_to_timestamp(text *date_txt, text *fmt,
{
FormatNode *format;
TmFromChar tmfc;
- int fmt_len;
+ int fmt_len,
+ fmask = 0, /* Bit mask for ValidateDate() */
+ dterr;
+ char *date_str = NULL;
ZERO_tmfc(&tmfc);
ZERO_tm(tm);
@@ -3646,7 +3649,6 @@ do_to_timestamp(text *date_txt, text *fmt,
if (fmt_len)
{
char *fmt_str;
- char *date_str;
bool incache;
fmt_str = text_to_cstring(fmt);
@@ -3704,7 +3706,6 @@ do_to_timestamp(text *date_txt, text *fmt,
DCH_from_char(format, date_str, &tmfc);
- pfree(date_str);
pfree(fmt_str);
if (!incache)
pfree(format);
@@ -3780,6 +3781,7 @@ do_to_timestamp(text *date_txt, text *fmt,
if (tmfc.bc && tm->tm_year > 0)
tm->tm_year = -(tm->tm_year - 1);
}
+ fmask |= DTK_M(YEAR);
}
else if (tmfc.cc) /* use first year of century */
{
@@ -3791,10 +3793,14 @@ do_to_timestamp(text *date_txt, text *fmt,
else
/* +1 because year == 599 is 600 BC */
tm->tm_year = tmfc.cc * 100 + 1;
+ fmask |= DTK_M(YEAR);
}
if (tmfc.j)
+ {
j2date(tmfc.j, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+ fmask |= DTK_DATE_M;
+ }
if (tmfc.ww)
{
@@ -3808,6 +3814,7 @@ do_to_timestamp(text *date_txt, text *fmt,
isoweekdate2date(tmfc.ww, tmfc.d, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
else
isoweek2date(tmfc.ww, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+ fmask |= DTK_DATE_M;
}
else
tmfc.ddd = (tmfc.ww - 1) * 7 + 1;
@@ -3818,11 +3825,17 @@ do_to_timestamp(text *date_txt, text *fmt,
if (tmfc.d)
tm->tm_wday = tmfc.d - 1; /* convert to native numbering */
if (tmfc.dd)
+ {
tm->tm_mday = tmfc.dd;
+ fmask |= DTK_M(DAY);
+ }
if (tmfc.ddd)
tm->tm_yday = tmfc.ddd;
if (tmfc.mm)
+ {
tm->tm_mon = tmfc.mm;
+ fmask |= DTK_M(MONTH);
+ }
if (tmfc.ddd && (tm->tm_mon <= 1 || tm->tm_mday <= 1))
{
@@ -3845,6 +3858,7 @@ do_to_timestamp(text *date_txt, text *fmt,
j0 = isoweek2j(tm->tm_year, 1) - 1;
j2date(j0 + tmfc.ddd, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+ fmask |= DTK_DATE_M;
}
else
{
@@ -3867,9 +3881,36 @@ do_to_timestamp(text *date_txt, text *fmt,
if (tm->tm_mday <= 1)
tm->tm_mday = tmfc.ddd - y[i - 1];
+
+ fmask |= DTK_M(MONTH) | DTK_M(DAY);
}
}
+ /* Validate date with bit mask received above */
+ if (fmask != 0 && date_str)
+ {
+ dterr = ValidateDate(fmask, false, false, false, tm);
+ if (dterr != 0)
+ DateTimeParseError(dterr, date_str, "");
+ }
+
+ /* Do checks for time part */
+#ifdef HAVE_INT64_TIMESTAMP
+ if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > MINS_PER_HOUR - 1 ||
+ tm->tm_sec < 0 || tm->tm_sec > SECS_PER_MINUTE ||
+ *fsec < INT64CONST(0) ||
+ *fsec > USECS_PER_SEC)
+ DateTimeParseError(DTERR_FIELD_OVERFLOW, date_str, "");
+#else
+ if (tm->tm_hour < 0 || tm->tm_min < 0 || tm->tm_min > MINS_PER_HOUR - 1 ||
+ tm->tm_sec < 0 || tm->tm_sec > SECS_PER_MINUTE ||
+ *fsec < 0 || *fsec > 1)
+ DateTimeParseError(DTERR_FIELD_OVERFLOW, date_str, "");
+#endif
+
+ if (date_str)
+ pfree(date_str);
+
#ifdef HAVE_INT64_TIMESTAMP
if (tmfc.ms)
*fsec += tmfc.ms * 1000;
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 0bf4287..8ee6569 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -3015,7 +3015,7 @@ SELECT to_date('2011 12 18', 'YYYY MM DD');
(1 row)
--
--- Check errors for some incorrect usages of to_timestamp()
+-- Check errors for some incorrect usages of to_timestamp() and to_date()
--
-- Mixture of date conventions (ISO week and Gregorian):
SELECT to_timestamp('2005527', 'YYYYIWID');
@@ -3043,6 +3043,12 @@ DETAIL: Value must be an integer.
SELECT to_timestamp('10000000000', 'FMYYYY');
ERROR: value for "YYYY" in source string is out of range
DETAIL: Value must be in the range -2147483648 to 2147483647.
+-- Time overflow:
+SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYY-MM-DD HH24:MI:SS');
+ERROR: date/time field value out of range: "2016-06-13 99:99:99"
+-- Date overflow:
+SELECT to_date('2016-02-30', 'YYYY-MM-DD');
+ERROR: date/time field value out of range: "2016-02-30"
--
-- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)
--
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 514c124..327dbfd 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -473,7 +473,7 @@ SELECT to_date('2011 12 18', 'YYYY MM DD');
SELECT to_date('2011 12 18', 'YYYY MM DD');
--
--- Check errors for some incorrect usages of to_timestamp()
+-- Check errors for some incorrect usages of to_timestamp() and to_date()
--
-- Mixture of date conventions (ISO week and Gregorian):
@@ -494,6 +494,12 @@ SELECT to_timestamp('199711xy', 'YYYYMMDD');
-- Input that doesn't fit in an int:
SELECT to_timestamp('10000000000', 'FMYYYY');
+-- Time overflow:
+SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYY-MM-DD HH24:MI:SS');
+
+-- Date overflow:
+SELECT to_date('2016-02-30', 'YYYY-MM-DD');
+
--
-- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)
--
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
