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).