Re: Strange results from to_timestamp - Mailing list pgsql-hackers

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: