pgsql-4702
Version:
8.3.6
Links:
bug report: http://archives.postgresql.org/pgsql-bugs/2009-03/msg00069.php
Patch: http://archives.postgresql.org/pgsql-bugs/2009-03/msg00070.php
Symptom(Failure):
Refuse valid input: postgres doesn’t handle ‘BC’ in year format.
test=# select date_trunc('week','0001-12-09 BC'::timestamp);
ERROR: cannot calculate week number without year information
How it is diagnosed:
Reproduced.
Root Cause:
Postgres process ‘BC’ in time stamp inconsistently. When parsing the input and calculating the numeric ‘year’, postgres handled ‘BC’, thus when the input is ‘0001-12-09 BC’, postgres simply calculated year as 0 (which is correct). However, later when processing this information to calculate which week the date was, postgres simply forgot to handle ‘BC’, and instead, aggressively report an error when seeing the ‘year’ as zero. In fact, if the ‘year’ was some other BC years (such as ‘0002 BC’), postgres would simply return a wrong result without reporting error.
----- Where the year is read in:
field = (arg == DCH_YYYY) ? &tmfc->year : &tmfc->iyear;
if (S_FM(suf) || is_next_separator(node))
{
sscanf(inout, "%d", field);
----- Later this input year is calculated and stored in tm->tm_year
----- Finally, logic to compute ‘week of the year’:
*** src/backend/utils/adt/formatting.c 12 Mar 2009 00:53:25 -0000 1.155
--- src/backend/utils/adt/formatting.c 13 Mar 2009 23:40:45 -0000
*************** do_to_timestamp
*** 3281,3308 ****
* be interpreted as a Gregorian day-of-year, or an ISO week date
* day-of-year.
*/
+
+ if (!tm->tm_year && !tmfc.bc)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+ errmsg("cannot calculate day of year without year information")));
+
… ...
j0 = isoweek2j(tmfc.year, 1) - 1;
*** src/backend/utils/adt/timestamp.c 1 Jan 2009 17:23:50 -0000 1.196
--- src/backend/utils/adt/timestamp.c 13 Mar 2009 23:40:46 -0000
*************** isoweek2j
*** 3668,3678 ****
- if (!year)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
/* This is where the original error message was printed. */
- errmsg("cannot calculate week number without year information")));
-
/* fourth day of current year */
day4 = date2j(year, 1, 4);
The fix is to move the ‘year’ check from callee (isoweek2j) to caller (do_to_timestamp).
So the pattern is input check: the input year should not be zero!
Is there any log message?
Yes.
How can we automatically insert a log message?
To automatically put a message, invariant checking might be good (value of tm->tm_year is not zero)!
Details:
#0 errmsg (fmt=0x84bdc8 "cannot calculate week number without year information") at elog.c:658
#1 0x00000000006e118d in isoweek2j (year=0, week=49) at timestamp.c:3677
#2 0x00000000006e1210 in isoweek2date (woy=49, year=0x7fff9a9848f4, mon=0x7fff9a9848f0, mday=0x7fff9a9848ec)
at timestamp.c:3698
#3 0x00000000006e0315 in timestamp_trunc (fcinfo=0x7fff9a9849f0) at timestamp.c:3327
#4 0x000000000058323d in ExecMakeFunctionResult (fcache=0x1e8b7740, econtext=0x1e8b7610,
isNull=0x1e8b7de8 "\177~\177\177\177\177\177\177\300\035\212\036", isDone=0x1e8b7ea0) at execQual.c:1351
#5 0x0000000000583b3b in ExecEvalFunc (fcache=0x1e8b7740, econtext=0x1e8b7610,
isNull=0x1e8b7de8 "\177~\177\177\177\177\177\177\300\035\212\036", isDone=0x1e8b7ea0) at execQual.c:1753
#6 0x000000000058a4b2 in ExecTargetList (targetlist=0x1e8b7c78, econtext=0x1e8b7610, values=0x1e8b7dc8,
isnull=0x1e8b7de8 "\177~\177\177\177\177\177\177\300\035\212\036", itemIsDone=0x1e8b7ea0, isDone=0x7fff9a984f14)
at execQual.c:4610
3281 Datum
3282 timestamp_trunc(PG_FUNCTION_ARGS)
3283 {
… ...
3303 if (type == UNITS)
3304 {
3305 if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
3306 ereport(ERROR,
3307 (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
3308 errmsg("timestamp out of range")));
3309
3310 switch (val)
3311 {
3312 case DTK_WEEK:
int timestamp2tm(Timestamp dt, int *tzp, struct pg_tm * tm, fsec_t *fsec, char **tzn, pg_tz *attimezone) {
… ...
j2date((int) date, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
dt2time(time, &tm->tm_hour, &tm->tm_min, &tm->tm_sec, fsec);
AFter j2date, tm->tm_year == 0, which is the same in both 8.3.6 and 8.4.0 (correct version).