I attached new patch "0001-to-timestamp-format-checking-v2.patch". It
fixes behaviour for Amul's scenarious:
Following are few scenarios where we break existing behaviour:
SELECT TO_TIMESTAMP('2015-12-31 13:43:36', 'YYYY MM DD HH24 MI SS');
SELECT TO_TIMESTAMP('2011$03!18 23_38_15', 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_TIMESTAMP('2011*03*18 23^38&15', 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_TIMESTAMP('2011*03!18 #%23^38$15', 'YYYY-MM-DD$$$HH24:MI:SS');
But current patch behaviour is not that much bad either at least we have
errors, but I am not sure about community acceptance.
I would like to divert communities' attention on following case:
SELECT TO_TIMESTAMP('2013--10-01', 'YYYY-MM-DD');
For queries above the patch gives an output without any error.
Another is, shouldn’t we have error in following cases?
SELECT TO_TIMESTAMP('2016-06-13 99:99:99', 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_TIMESTAMP('2016-02-30 15:43:36', 'YYYY-MM-DD HH24:MI:SS');
I attached second patch "0002-to-timestamp-validation-v2.patch". With it
PostgreSQL perform additional checks for date and time. But as I wrote
there is another patch in the thread "to_date_valid()" wich differs from
this patch.
Sincerely,
--
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 7830334..559c55f 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6083,9 +6083,12 @@ SELECT regexp_matches('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..b14678d 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,49 @@ 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
+ {
+ n->type = NODE_TYPE_CHAR;
+ n->character = *str;
+ n->key = NULL;
+ n->suffix = 0;
+ n++;
+ str++;
+ }
+ continue;
+ }
+
/*
* Prefix
*/
@@ -1290,48 +1347,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 +1387,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 +2131,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 +2200,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);
@@ -2952,23 +2983,62 @@ DCH_from_char(FormatNode *node, char *in, TmFromChar *out)
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++;
+ 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++;
+ 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++;
continue;
}
- /* Ignore spaces before fields when not in FX (fixed width) mode */
+ /* Ignore spaces before fields when not in FX (fixed * width) mode */
if (!fx_mode && n->key->id != DCH_FX)
{
- while (*s != '\0' && isspace((unsigned char) *s))
+ while (*s != '\0' && (isspace((unsigned char) *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);
@@ -3340,15 +3410,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 +3676,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 +4055,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..0bf4287 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,7 +2807,7 @@ 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
@@ -2810,6 +2828,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 +2945,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 +2975,7 @@ 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_date('2011 12 18', 'YYYY MM DD');
@@ -2960,13 +2993,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..514c124 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -392,15 +392,21 @@ 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('05121445482000', 'MMDDHH24MISSYYYY');
@@ -408,6 +414,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');
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
