Attachment now attached. :-)
---------------------------------------------------------------------------
Bruce Momjian wrote:
>
> Interesting bug report. The problem is that sscanf(buf, "%d", &val)
> eats leading white space, but our functions were not handling that.
>
> I have applied the attached patch that fixes this:
>
> test=> select to_timestamp(' 0300','yyyymmdd hh24mi');
> to_timestamp
> ------------------------
> 0300-01-01 00:00:00-05
> (1 row)
>
> test=> select to_timestamp(' 030004','yyyymmdd hh24mi');
> to_timestamp
> ------------------------
> 0300-04-01 00:00:00-05
> (1 row)
>
> test=> select to_timestamp(' 040004','yyyymmdd hh24mi');
> to_timestamp
> ------------------------
> 0400-04-01 00:00:00-05
> (1 row)
>
> It doesn't throw an error for too mamy format strings, but it does avoid
> the incorrect values. The fix will appear in 8.2.
>
> ---------------------------------------------------------------------------
>
> Mario Weilguni wrote:
> > mydb=# select to_timestamp('00000000 0300','yyyymmdd hh24mi');
> > to_timestamp
> > ---------------------------
> > 0001-01-01 03:00:00+01 BC
> > (1 row)
> >
> > Questionable, but probably valid.
> >
> >
> >
> > mydb=# select to_timestamp(' 0300','yyyymmdd hh24mi');
> > to_timestamp
> > ------------------------
> > 0300-12-25 03:00:00+01
> > (1 row)
> >
> > This puzzles me. Where is the 25th of december coming from?
> >
> >
> >
> > mydb=# select to_timestamp(' 030004','yyyymmdd hh24mi');
> > to_timestamp
> > ------------------------
> > 0382-04-23 03:00:00+01
> > (1 row)
> >
> > Same as above.
> >
> >
> > mydb=# select to_timestamp(' 040004','yyyymmdd hh24mi');
> > to_timestamp
> > ------------------------
> > 0509-10-10 04:00:00+01
> >
> >
> > I think all except the first one should raise a warning, isn't it? Where
> > can I
> > find the source code of this function?
> >
> > Best regards,
> > Mario Weilguni
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> > choose an index scan if your joining column's datatypes do not
> > match
> >
>
> --
> Bruce Momjian http://candle.pha.pa.us
> EnterpriseDB http://www.enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.109
diff -c -c -r1.109 formatting.c
*** src/backend/utils/adt/formatting.c 19 Apr 2006 14:48:06 -0000 1.109
--- src/backend/utils/adt/formatting.c 19 Apr 2006 16:07:35 -0000
***************
*** 917,922 ****
--- 917,923 ----
static char *get_th(char *num, int type);
static char *str_numth(char *dest, char *num, int type);
+ static int strspace_len(char *str);
static int strdigits_len(char *str);
static char *str_toupper(char *buff);
static char *str_tolower(char *buff);
***************
*** 1687,1697 ****
}
static int
strdigits_len(char *str)
{
char *p = str;
! int len = 0;
while (*p && isdigit((unsigned char) *p) && len <= DCH_MAX_ITEM_SIZ)
{
len++;
--- 1688,1714 ----
}
static int
+ strspace_len(char *str)
+ {
+ int len = 0;
+
+ while (*str && isspace((unsigned char) *str))
+ {
+ str++;
+ len++;
+ }
+ return len;
+ }
+
+ static int
strdigits_len(char *str)
{
char *p = str;
! int len;
+ len = strspace_len(str);
+ p += len;
+
while (*p && isdigit((unsigned char) *p) && len <= DCH_MAX_ITEM_SIZ)
{
len++;
***************
*** 1826,1832 ****
else
{
sscanf(inout, "%02d", &tmfc->hh);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 1843,1849 ----
else
{
sscanf(inout, "%02d", &tmfc->hh);
! return strspace_len(inout) + 2 +
SKIP_THth(suf);
}
}
break;
***************
*** 1848,1854 ****
else
{
sscanf(inout, "%02d", &tmfc->hh);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 1865,1871 ----
else
{
sscanf(inout, "%02d", &tmfc->hh);
! return strspace_len(inout) + 2 +
SKIP_THth(suf);
}
}
break;
***************
*** 1870,1876 ****
else
{
sscanf(inout, "%02d", &tmfc->mi);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 1887,1893 ----
else
{
sscanf(inout, "%02d", &tmfc->mi);
! return strspace_len(inout) + 2 +
SKIP_THth(suf);
}
}
break;
***************
*** 1892,1898 ****
else
{
sscanf(inout, "%02d", &tmfc->ss);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 1909,1915 ----
else
{
sscanf(inout, "%02d", &tmfc->ss);
! return strspace_len(inout) + 2 +
SKIP_THth(suf);
}
}
break;
***************
*** 1998,2004 ****
else
{
sscanf(inout, "%05d", &tmfc->ssss);
! return 5 + SKIP_THth(suf);
}
}
break;
--- 2015,2021 ----
else
{
sscanf(inout, "%05d", &tmfc->ssss);
! return strspace_len(inout) + 5 +
SKIP_THth(suf);
}
}
break;
***************
*** 2249,2255 ****
else
{
sscanf(inout, "%02d", &tmfc->mm);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 2266,2272 ----
else
{
sscanf(inout, "%02d", &tmfc->mm);
! return strspace_len(inout) + 2 +
SKIP_THth(suf);
}
}
break;
***************
*** 2323,2329 ****
else
{
sscanf(inout, "%03d", &tmfc->ddd);
! return 3 + SKIP_THth(suf);
}
}
break;
--- 2340,2346 ----
else
{
sscanf(inout, "%03d", &tmfc->ddd);
! return strspace_len(inout) + 3 +
SKIP_THth(suf);
}
}
break;
***************
*** 2345,2351 ****
else
{
sscanf(inout, "%02d", &tmfc->dd);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 2362,2368 ----
else
{
sscanf(inout, "%02d", &tmfc->dd);
! return strspace_len(inout) + 2 +
SKIP_THth(suf);
}
}
break;
***************
*** 2360,2366 ****
else
{
sscanf(inout, "%1d", &tmfc->d);
! return 1 + SKIP_THth(suf);
}
break;
case DCH_WW:
--- 2377,2383 ----
else
{
sscanf(inout, "%1d", &tmfc->d);
! return strspace_len(inout) + 1 + SKIP_THth(suf);
}
break;
case DCH_WW:
***************
*** 2382,2388 ****
else
{
sscanf(inout, "%02d", &tmfc->ww);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 2399,2405 ----
else
{
sscanf(inout, "%02d", &tmfc->ww);
! return strspace_len(inout) + 2 +
SKIP_THth(suf);
}
}
break;
***************
*** 2405,2411 ****
else
{
sscanf(inout, "%02d", &tmfc->iw);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 2422,2428 ----
else
{
sscanf(inout, "%02d", &tmfc->iw);
! return strspace_len(inout) + 2 +
SKIP_THth(suf);
}
}
break;
***************
*** 2422,2428 ****
else
{
sscanf(inout, "%1d", &tmfc->q);
! return 1 + SKIP_THth(suf);
}
break;
case DCH_CC:
--- 2439,2445 ----
else
{
sscanf(inout, "%1d", &tmfc->q);
! return strspace_len(inout) + 1 + SKIP_THth(suf);
}
break;
case DCH_CC:
***************
*** 2447,2453 ****
else
{
sscanf(inout, "%02d", &tmfc->cc);
! return 2 + SKIP_THth(suf);
}
}
break;
--- 2464,2470 ----
else
{
sscanf(inout, "%02d", &tmfc->cc);
! return strspace_len(inout) + 2 +
SKIP_THth(suf);
}
}
break;
***************
*** 2507,2513 ****
{
sscanf(inout, "%04d", &tmfc->year);
tmfc->yysz = 4;
! return 4 + SKIP_THth(suf);
}
}
break;
--- 2524,2530 ----
{
sscanf(inout, "%04d", &tmfc->year);
tmfc->yysz = 4;
! return strspace_len(inout) + 4 +
SKIP_THth(suf);
}
}
break;
***************
*** 2540,2546 ****
else
tmfc->year += 2000;
tmfc->yysz = 3;
! return 3 + SKIP_THth(suf);
}
break;
case DCH_YY:
--- 2557,2563 ----
else
tmfc->year += 2000;
tmfc->yysz = 3;
! return strspace_len(inout) + 3 + SKIP_THth(suf);
}
break;
case DCH_YY:
***************
*** 2572,2578 ****
else
tmfc->year += 1900;
tmfc->yysz = 2;
! return 2 + SKIP_THth(suf);
}
break;
case DCH_Y:
--- 2589,2595 ----
else
tmfc->year += 1900;
tmfc->yysz = 2;
! return strspace_len(inout) + 2 + SKIP_THth(suf);
}
break;
case DCH_Y:
***************
*** 2600,2606 ****
*/
tmfc->year += 2000;
tmfc->yysz = 1;
! return 1 + SKIP_THth(suf);
}
break;
case DCH_RM:
--- 2617,2623 ----
*/
tmfc->year += 2000;
tmfc->yysz = 1;
! return strspace_len(inout) + 1 + SKIP_THth(suf);
}
break;
case DCH_RM:
***************
*** 2652,2658 ****
else
{
sscanf(inout, "%1d", &tmfc->w);
! return 1 + SKIP_THth(suf);
}
break;
case DCH_J:
--- 2669,2675 ----
else
{
sscanf(inout, "%1d", &tmfc->w);
! return strspace_len(inout) + 1 + SKIP_THth(suf);
}
break;
case DCH_J:
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
