Обсуждение: oh dear ...

This worked in 7.3:

regression=# select '1999-jan-08'::date;
ERROR:  date/time field value out of range: "1999-jan-08"
HINT:  Perhaps you need a different "datestyle" setting.

Setting DateStyle to YMD doesn't help, and in any case I'd think that
this ought to be considered an unambiguous input format.

The variantsselect 'jan-08-1999'::date;select '08-jan-1999'::date;
both still work, so I think this is probably some small oversight in the
logic, but I haven't dug into it to find where.

Not sure if this qualifies as a must-fix-for-7.4 or not, but my vote
would be "yes" ...
        regards, tom lane

I said:
> This worked in 7.3:
> regression=# select '1999-jan-08'::date;
> ERROR:  date/time field value out of range: "1999-jan-08"
> HINT:  Perhaps you need a different "datestyle" setting.

> Setting DateStyle to YMD doesn't help, and in any case I'd think that
> this ought to be considered an unambiguous input format.

This appears to be an oversight in the portions of the datetime code
that we recently changed to enforce DateStyle more tightly.
Specifically, DecodeNumber was rewritten without realizing that it was
invoked in a special way when a textual month name appears in the input.
DecodeDate actually makes two passes over the input, noting the textual
month name in the first pass, and then calling DecodeNumber on only the
numeric fields in the second pass.  This means that when DecodeNumber is
called for the first time, the MONTH flag may already be set.  The
rewrite mistakenly assumed that in this case we must be at the second
field of an MM-DD-YY-order input.

I propose the attached patch to fix the problem.  It doesn't break any
regression tests, and it appears to fix the cases noted in its comment.

Opinions on whether to apply this to 7.4?

            regards, tom lane

*** src/backend/utils/adt/datetime.c.orig    Thu Sep 25 10:23:13 2003
--- src/backend/utils/adt/datetime.c    Fri Nov 14 19:22:47 2003
***************
*** 2553,2561 ****
              break;

          case (DTK_M(MONTH)):
!             /* Must be at second field of MM-DD-YY */
!             *tmask = DTK_M(DAY);
!             tm->tm_mday = val;
              break;

          case (DTK_M(YEAR) | DTK_M(MONTH) | DTK_M(DAY)):
--- 2553,2577 ----
              break;

          case (DTK_M(MONTH)):
!             /*
!              * There are two possibilities: we are at second field of
!              * MM-DD-YY (with DateOrder MDY), or we are at the first
!              * numeric field of a date that included a textual month name.
!              * We want to support the variants MON-DD-YYYY, DD-MON-YYYY,
!              * and YYYY-MON-DD as unambiguous inputs.  We will also accept
!              * MON-DD-YY or DD-MON-YY in either DMY or MDY modes, as well
!              * as YY-MON-DD in YMD mode.  Hence:
!              */
!             if (flen >= 3 || DateOrder == DATEORDER_YMD)
!             {
!                 *tmask = DTK_M(YEAR);
!                 tm->tm_year = val;
!             }
!             else
!             {
!                 *tmask = DTK_M(DAY);
!                 tm->tm_mday = val;
!             }
              break;

          case (DTK_M(YEAR) | DTK_M(MONTH) | DTK_M(DAY)):

On Fri, 14 Nov 2003, Tom Lane wrote:

> I said:
> > This worked in 7.3:
> > regression=# select '1999-jan-08'::date;
> > ERROR:  date/time field value out of range: "1999-jan-08"
> > HINT:  Perhaps you need a different "datestyle" setting.
>
> > Setting DateStyle to YMD doesn't help, and in any case I'd think that
> > this ought to be considered an unambiguous input format.
>
> This appears to be an oversight in the portions of the datetime code
> that we recently changed to enforce DateStyle more tightly.
> Specifically, DecodeNumber was rewritten without realizing that it was
> invoked in a special way when a textual month name appears in the input.
> DecodeDate actually makes two passes over the input, noting the textual
> month name in the first pass, and then calling DecodeNumber on only the
> numeric fields in the second pass.  This means that when DecodeNumber is
> called for the first time, the MONTH flag may already be set.  The
> rewrite mistakenly assumed that in this case we must be at the second
> field of an MM-DD-YY-order input.
>
> I propose the attached patch to fix the problem.  It doesn't break any
> regression tests, and it appears to fix the cases noted in its comment.
>
> Opinions on whether to apply this to 7.4?

based on "ought to be considered an unambiguous input format", I'd say
leave it for 7.4.1 ...

Tom Lane wrote:
> I said:
> > This worked in 7.3:
> > regression=# select '1999-jan-08'::date;
> > ERROR:  date/time field value out of range: "1999-jan-08"
> > HINT:  Perhaps you need a different "datestyle" setting.
> 
> > Setting DateStyle to YMD doesn't help, and in any case I'd think that
> > this ought to be considered an unambiguous input format.
> 
> This appears to be an oversight in the portions of the datetime code
> that we recently changed to enforce DateStyle more tightly.
> Specifically, DecodeNumber was rewritten without realizing that it was
> invoked in a special way when a textual month name appears in the input.
> DecodeDate actually makes two passes over the input, noting the textual
> month name in the first pass, and then calling DecodeNumber on only the
> numeric fields in the second pass.  This means that when DecodeNumber is
> called for the first time, the MONTH flag may already be set.  The
> rewrite mistakenly assumed that in this case we must be at the second
> field of an MM-DD-YY-order input.
> 
> I propose the attached patch to fix the problem.  It doesn't break any
> regression tests, and it appears to fix the cases noted in its comment.
> 
> Opinions on whether to apply this to 7.4?

I guess the question is whether we would fix this in a minor release,
and I think the answer it yes, so we can fix it now.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

On Fri, 14 Nov 2003, Bruce Momjian wrote:

> Tom Lane wrote:
> > I said:
> > > This worked in 7.3:
> > > regression=# select '1999-jan-08'::date;
> > > ERROR:  date/time field value out of range: "1999-jan-08"
> > > HINT:  Perhaps you need a different "datestyle" setting.
> >
> > > Setting DateStyle to YMD doesn't help, and in any case I'd think that
> > > this ought to be considered an unambiguous input format.
> >
> > This appears to be an oversight in the portions of the datetime code
> > that we recently changed to enforce DateStyle more tightly.
> > Specifically, DecodeNumber was rewritten without realizing that it was
> > invoked in a special way when a textual month name appears in the input.
> > DecodeDate actually makes two passes over the input, noting the textual
> > month name in the first pass, and then calling DecodeNumber on only the
> > numeric fields in the second pass.  This means that when DecodeNumber is
> > called for the first time, the MONTH flag may already be set.  The
> > rewrite mistakenly assumed that in this case we must be at the second
> > field of an MM-DD-YY-order input.
> >
> > I propose the attached patch to fix the problem.  It doesn't break any
> > regression tests, and it appears to fix the cases noted in its comment.
> >
> > Opinions on whether to apply this to 7.4?
>
> I guess the question is whether we would fix this in a minor release,
> and I think the answer it yes, so we can fix it now.

Ah, so we attempt to fix a bug that affects what appears to be a small %
of configurations with "quick testing" and with the greater possibility of
affecting a larger % of configurations ... instead of releasing what we
has been reported as being stable on the large % of configurations, and
fixing it for that small % of configuratiosn in a minor release?

Sounds to me like a decision design to benefit the few at the risk of the
many ... when documenting the known bug for those few would be safer ...

Marc G. Fournier wrote:

>On Fri, 14 Nov 2003, Bruce Momjian wrote:
>
>  
>
>>Tom Lane wrote:
>>    
>>
>>>I said:
>>>      
>>>
>>>>This worked in 7.3:
>>>>regression=# select '1999-jan-08'::date;
>>>>ERROR:  date/time field value out of range: "1999-jan-08"
>>>>HINT:  Perhaps you need a different "datestyle" setting.
>>>>        
>>>>
>>>>Setting DateStyle to YMD doesn't help, and in any case I'd think that
>>>>this ought to be considered an unambiguous input format.
>>>>        
>>>>
>>>This appears to be an oversight in the portions of the datetime code
>>>that we recently changed to enforce DateStyle more tightly.
>>>Specifically, DecodeNumber was rewritten without realizing that it was
>>>invoked in a special way when a textual month name appears in the input.
>>>DecodeDate actually makes two passes over the input, noting the textual
>>>month name in the first pass, and then calling DecodeNumber on only the
>>>numeric fields in the second pass.  This means that when DecodeNumber is
>>>called for the first time, the MONTH flag may already be set.  The
>>>rewrite mistakenly assumed that in this case we must be at the second
>>>field of an MM-DD-YY-order input.
>>>
>>>I propose the attached patch to fix the problem.  It doesn't break any
>>>regression tests, and it appears to fix the cases noted in its comment.
>>>
>>>Opinions on whether to apply this to 7.4?
>>>      
>>>
>>I guess the question is whether we would fix this in a minor release,
>>and I think the answer it yes, so we can fix it now.
>>    
>>
>
>Ah, so we attempt to fix a bug that affects what appears to be a small %
>of configurations with "quick testing" and with the greater possibility of
>affecting a larger % of configurations ... instead of releasing what we
>has been reported as being stable on the large % of configurations, and
>fixing it for that small % of configuratiosn in a minor release?
>
>Sounds to me like a decision design to benefit the few at the risk of the
>many ... when documenting the known bug for those few would be safer ...
>
>  
>

I'm confused. My understanding from what Tom said is that it affects all 
configurations.

cheers

andrew

On Fri, 14 Nov 2003, Andrew Dunstan wrote:

> I'm confused. My understanding from what Tom said is that it affects all
> configurations.

the stats collector problem, from what I've seen through this list,
affects Solaris, and only some Solaris configuration ..

"Marc G. Fournier" <scrappy@postgresql.org> writes:
> On Fri, 14 Nov 2003, Bruce Momjian wrote:
>> I guess the question is whether we would fix this in a minor release,
>> and I think the answer it yes, so we can fix it now.

> Ah, so we attempt to fix a bug that affects what appears to be a small %
> of configurations with "quick testing" and with the greater possibility of
> affecting a larger % of configurations ... instead of releasing what we
> has been reported as being stable on the large % of configurations, and
> fixing it for that small % of configuratiosn in a minor release?

Huh?  The pgstat bug is a platform dependency, sure, but this datetime
bug is not platform-specific.  I don't see that there's much commonality
in the criteria for whether to patch them.

My vote is to patch both --- I don't like shipping releases with known
bugs in them, when such bugs would have been patched with no discussion
just a week earlier.  For sure we should triple-check the proposed
patches, but once that's done I don't see a reason to hold off.

The pgstat patch has already been checked to my satisfaction, but the
datetime patch needs more eyeballs on it; anyone out there have time to
look at it?
        regards, tom lane

Marc G. Fournier wrote:
> 
> On Fri, 14 Nov 2003, Andrew Dunstan wrote:
>>I'm confused. My understanding from what Tom said is that it affects all
>>configurations.
> 
> the stats collector problem, from what I've seen through this list,
> affects Solaris, and only some Solaris configuration ..
> 

But the issue at hand is this one:

Tom Lane wrote:> This worked in 7.3:>> regression=# select '1999-jan-08'::date;> ERROR:  date/time field value out of
range:"1999-jan-08"> HINT:  Perhaps you need a different "datestyle" setting.
 

Seems like this would affect everyone who uses this style of date in 
their app. If it isn't a must fix for 7.4, we should plan 7.4.1 for a 
fairly quick follow up.

Joe

> I propose the attached patch to fix the problem.  It doesn't break any
> regression tests, and it appears to fix the cases noted in its comment.
> 
> Opinions on whether to apply this to 7.4?

I think it should be fixed, since it could cause applications to break.  Shouldn't you also add a regression test to
catchthis in the future?
 

Chris

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Shouldn't you also add a regression test to catch this in the future?

Yes, I absolutely plan to stick some regression test additions into HEAD.
There's not a need for such changes in the 7.4 branch though.  Right at
the moment what we need is a decision about whether to apply the code
change to 7.4 release ...
        regards, tom lane

Tom Lane wrote:
> The pgstat patch has already been checked to my satisfaction, but the
> datetime patch needs more eyeballs on it; anyone out there have time to
> look at it?

FWIW, it looks good to me, seems to work as intended, and passes all 
existing regression tests.

Joe

My bad, confused two different issues in one thread :(

On Fri, 14 Nov 2003, Joe Conway wrote:

> Marc G. Fournier wrote:
> >
> > On Fri, 14 Nov 2003, Andrew Dunstan wrote:
> >>I'm confused. My understanding from what Tom said is that it affects all
> >>configurations.
> >
> > the stats collector problem, from what I've seen through this list,
> > affects Solaris, and only some Solaris configuration ..
> >
>
> But the issue at hand is this one:
>
> Tom Lane wrote:
>  > This worked in 7.3:
>  >
>  > regression=# select '1999-jan-08'::date;
>  > ERROR:  date/time field value out of range: "1999-jan-08"
>  > HINT:  Perhaps you need a different "datestyle" setting.
>
> Seems like this would affect everyone who uses this style of date in
> their app. If it isn't a must fix for 7.4, we should plan 7.4.1 for a
> fairly quick follow up.
>
> Joe
>
>

On Fri, 14 Nov 2003, Tom Lane wrote:

> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> > Shouldn't you also add a regression test to catch this in the future?
>
> Yes, I absolutely plan to stick some regression test additions into HEAD.
> There's not a need for such changes in the 7.4 branch though.  Right at
> the moment what we need is a decision about whether to apply the code
> change to 7.4 release ...

Go for it ...

Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> The pgstat patch has already been checked to my satisfaction, but the
>> datetime patch needs more eyeballs on it; anyone out there have time to
>> look at it?

> FWIW, it looks good to me, seems to work as intended, and passes all
> existing regression tests.

I made up a more thorough regression test for date input formats, and
found that there were still some cases that were rejected :-(.  Attached
is a more complete patch that handles all month-name cases, and
explicitly can not change the behavior when there's not a textual month
name.  Documentation addition and regression test included.

I'd like some further review of this before I risk applying it to 7.4
though ... anyone have time today?

            regards, tom lane

*** doc/src/sgml/datatype.sgml.orig    Thu Nov  6 17:21:47 2003
--- doc/src/sgml/datatype.sgml    Sat Nov 15 11:53:47 2003
***************
*** 1464,1470 ****
          </row>
          <row>
           <entry>1999-01-08</entry>
!          <entry>ISO 8601, January 8 in any mode
           (recommended format)</entry>
          </row>
          <row>
--- 1464,1470 ----
          </row>
          <row>
           <entry>1999-01-08</entry>
!          <entry>ISO 8601; January 8 in any mode
           (recommended format)</entry>
          </row>
          <row>
***************
*** 1483,1488 ****
--- 1483,1512 ----
            February 1, 2003 in <literal>DMY</> mode;
            February 3, 2001 in <literal>YMD</> mode
           </entry>
+         </row>
+         <row>
+          <entry>1999-Jan-08</entry>
+          <entry>January 8 in any mode</entry>
+         </row>
+         <row>
+          <entry>Jan-08-1999</entry>
+          <entry>January 8 in any mode</entry>
+         </row>
+         <row>
+          <entry>08-Jan-1999</entry>
+          <entry>January 8 in any mode</entry>
+         </row>
+         <row>
+          <entry>99-Jan-08</entry>
+          <entry>January 8 in <literal>YMD</> mode, else error</entry>
+         </row>
+         <row>
+          <entry>08-Jan-99</entry>
+          <entry>January 8, except error in <literal>YMD</> mode</entry>
+         </row>
+         <row>
+          <entry>Jan-08-99</entry>
+          <entry>January 8, except error in <literal>YMD</> mode</entry>
          </row>
          <row>
           <entry>19990108</entry>
*** src/backend/utils/adt/datetime.c.orig    Thu Sep 25 10:23:13 2003
--- src/backend/utils/adt/datetime.c    Sat Nov 15 11:33:50 2003
***************
*** 25,31 ****
  #include "utils/guc.h"


! static int DecodeNumber(int flen, char *field,
               int fmask, int *tmask,
               struct tm * tm, fsec_t *fsec, int *is2digits);
  static int DecodeNumberField(int len, char *str,
--- 25,31 ----
  #include "utils/guc.h"


! static int DecodeNumber(int flen, char *field, bool haveTextMonth,
               int fmask, int *tmask,
               struct tm * tm, fsec_t *fsec, int *is2digits);
  static int DecodeNumberField(int len, char *str,
***************
*** 924,930 ****
      int            val;
      int            dterr;
      int            mer = HR24;
!     int            haveTextMonth = FALSE;
      int            is2digits = FALSE;
      int            bc = FALSE;

--- 924,930 ----
      int            val;
      int            dterr;
      int            mer = HR24;
!     bool        haveTextMonth = FALSE;
      int            is2digits = FALSE;
      int            bc = FALSE;

***************
*** 1281,1287 ****
                      /* otherwise it is a single date/time field... */
                      else
                      {
!                         dterr = DecodeNumber(flen, field[i], fmask,
                                               &tmask, tm,
                                               fsec, &is2digits);
                          if (dterr)
--- 1281,1288 ----
                      /* otherwise it is a single date/time field... */
                      else
                      {
!                         dterr = DecodeNumber(flen, field[i],
!                                              haveTextMonth, fmask,
                                               &tmask, tm,
                                               fsec, &is2digits);
                          if (dterr)
***************
*** 2032,2037 ****
--- 2033,2039 ----
                      else
                      {
                          dterr = DecodeNumber(flen, field[i],
+                                              FALSE,
                                               (fmask | DTK_DATE_M),
                                               &tmask, tm,
                                               fsec, &is2digits);
***************
*** 2229,2234 ****
--- 2231,2237 ----
      int            i,
                  len;
      int            dterr;
+     bool        haveTextMonth = FALSE;
      int            bc = FALSE;
      int            is2digits = FALSE;
      int            type,
***************
*** 2283,2288 ****
--- 2286,2292 ----
              {
                  case MONTH:
                      tm->tm_mon = val;
+                     haveTextMonth = TRUE;
                      break;

                  case ADBC:
***************
*** 2312,2318 ****
          if ((len = strlen(field[i])) <= 0)
              return DTERR_BAD_FORMAT;

!         dterr = DecodeNumber(len, field[i], fmask,
                               &dmask, tm,
                               &fsec, &is2digits);
          if (dterr)
--- 2316,2322 ----
          if ((len = strlen(field[i])) <= 0)
              return DTERR_BAD_FORMAT;

!         dterr = DecodeNumber(len, field[i], haveTextMonth, fmask,
                               &dmask, tm,
                               &fsec, &is2digits);
          if (dterr)
***************
*** 2444,2450 ****
   * Return 0 if okay, a DTERR code if not.
   */
  static int
! DecodeNumber(int flen, char *str, int fmask,
               int *tmask, struct tm * tm, fsec_t *fsec, int *is2digits)
  {
      int            val;
--- 2448,2454 ----
   * Return 0 if okay, a DTERR code if not.
   */
  static int
! DecodeNumber(int flen, char *str, bool haveTextMonth, int fmask,
               int *tmask, struct tm * tm, fsec_t *fsec, int *is2digits)
  {
      int            val;
***************
*** 2534,2543 ****
              tm->tm_mon = val;
              break;

          case (DTK_M(YEAR) | DTK_M(MONTH)):
!             /* Must be at third field of YY-MM-DD */
!             *tmask = DTK_M(DAY);
!             tm->tm_mday = val;
              break;

          case (DTK_M(DAY)):
--- 2538,2596 ----
              tm->tm_mon = val;
              break;

+         case (DTK_M(MONTH)):
+             if (haveTextMonth)
+             {
+                 /*
+                  * We are at the first numeric field of a date that included
+                  * a textual month name.  We want to support the variants
+                  * MON-DD-YYYY, DD-MON-YYYY, and YYYY-MON-DD as unambiguous
+                  * inputs.  We will also accept MON-DD-YY or DD-MON-YY in
+                  * either DMY or MDY modes, as well as YY-MON-DD in YMD mode.
+                  */
+                 if (flen >= 3 || DateOrder == DATEORDER_YMD)
+                 {
+                     *tmask = DTK_M(YEAR);
+                     tm->tm_year = val;
+                 }
+                 else
+                 {
+                     *tmask = DTK_M(DAY);
+                     tm->tm_mday = val;
+                 }
+             }
+             else
+             {
+                 /* Must be at second field of MM-DD-YY */
+                 *tmask = DTK_M(DAY);
+                 tm->tm_mday = val;
+             }
+             break;
+
          case (DTK_M(YEAR) | DTK_M(MONTH)):
!             if (haveTextMonth)
!             {
!                 /* Need to accept DD-MON-YYYY even in YMD mode */
!                 if (flen >= 3 && *is2digits)
!                 {
!                     /* Guess that first numeric field is day was wrong */
!                     *tmask = DTK_M(DAY); /* YEAR is already set */
!                     tm->tm_mday = tm->tm_year;
!                     tm->tm_year = val;
!                     *is2digits = FALSE;
!                 }
!                 else
!                 {
!                     *tmask = DTK_M(DAY);
!                     tm->tm_mday = val;
!                 }
!             }
!             else
!             {
!                 /* Must be at third field of YY-MM-DD */
!                 *tmask = DTK_M(DAY);
!                 tm->tm_mday = val;
!             }
              break;

          case (DTK_M(DAY)):
***************
*** 2552,2563 ****
              tm->tm_year = val;
              break;

-         case (DTK_M(MONTH)):
-             /* Must be at second field of MM-DD-YY */
-             *tmask = DTK_M(DAY);
-             tm->tm_mday = val;
-             break;
-
          case (DTK_M(YEAR) | DTK_M(MONTH) | DTK_M(DAY)):
              /* we have all the date, so it must be a time field */
              dterr = DecodeNumberField(flen, str, fmask,
--- 2605,2610 ----
***************
*** 2574,2583 ****

      /*
       * When processing a year field, mark it for adjustment if it's
!      * exactly two digits.
       */
      if (*tmask == DTK_M(YEAR))
!         *is2digits = (flen == 2);

      return 0;
  }
--- 2621,2630 ----

      /*
       * When processing a year field, mark it for adjustment if it's
!      * only one or two digits.
       */
      if (*tmask == DTK_M(YEAR))
!         *is2digits = (flen <= 2);

      return 0;
  }
*** src/test/regress/expected/date.out.orig    Wed Aug 27 19:13:42 2003
--- src/test/regress/expected/date.out    Sat Nov 15 11:59:09 2003
***************
*** 63,68 ****
--- 63,745 ----
  (3 rows)

  --
+ -- Check all the documented input formats
+ --
+ SET datestyle TO iso;  -- display results in ISO
+ SET datestyle TO ymd;
+ SELECT date 'January 8, 1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1999-01-08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1999-01-18';
+     date
+ ------------
+  1999-01-18
+ (1 row)
+
+ SELECT date '1/8/1999';
+ ERROR:  date/time field value out of range: "1/8/1999"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '1/18/1999';
+ ERROR:  date/time field value out of range: "1/18/1999"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '18/1/1999';
+ ERROR:  date/time field value out of range: "18/1/1999"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '01/02/03';
+     date
+ ------------
+  2001-02-03
+ (1 row)
+
+ SELECT date '19990108';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '990108';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1999.008';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'J2451187';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'January 8, 99 BC';
+ ERROR:  date/time field value out of range: "January 8, 99 BC"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '99-Jan-08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1999-Jan-08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08-Jan-99';
+ ERROR:  date/time field value out of range: "08-Jan-99"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '08-Jan-1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'Jan-08-99';
+ ERROR:  date/time field value out of range: "Jan-08-99"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date 'Jan-08-1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '99-08-Jan';
+ ERROR:  invalid input syntax for type date: "99-08-Jan"
+ SELECT date '1999-08-Jan';
+ ERROR:  invalid input syntax for type date: "1999-08-Jan"
+ SELECT date '99 Jan 08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1999 Jan 08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08 Jan 99';
+ ERROR:  date/time field value out of range: "08 Jan 99"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '08 Jan 1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'Jan 08 99';
+ ERROR:  date/time field value out of range: "Jan 08 99"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date 'Jan 08 1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '99 08 Jan';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1999 08 Jan';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '99-01-08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1999-01-08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08-01-99';
+ ERROR:  date/time field value out of range: "08-01-99"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '08-01-1999';
+ ERROR:  date/time field value out of range: "08-01-1999"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '01-08-99';
+ ERROR:  date/time field value out of range: "01-08-99"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '01-08-1999';
+ ERROR:  date/time field value out of range: "01-08-1999"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '99-08-01';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SELECT date '1999-08-01';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SELECT date '99 01 08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1999 01 08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08 01 99';
+ ERROR:  date/time field value out of range: "08 01 99"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '08 01 1999';
+ ERROR:  date/time field value out of range: "08 01 1999"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '01 08 99';
+ ERROR:  date/time field value out of range: "01 08 99"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '01 08 1999';
+ ERROR:  date/time field value out of range: "01 08 1999"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '99 08 01';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SELECT date '1999 08 01';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SET datestyle TO dmy;
+ SELECT date 'January 8, 1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1999-01-08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1999-01-18';
+     date
+ ------------
+  1999-01-18
+ (1 row)
+
+ SELECT date '1/8/1999';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SELECT date '1/18/1999';
+ ERROR:  date/time field value out of range: "1/18/1999"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '18/1/1999';
+     date
+ ------------
+  1999-01-18
+ (1 row)
+
+ SELECT date '01/02/03';
+     date
+ ------------
+  2003-02-01
+ (1 row)
+
+ SELECT date '19990108';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '990108';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1999.008';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'J2451187';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'January 8, 99 BC';
+      date
+ ---------------
+  0099-01-08 BC
+ (1 row)
+
+ SELECT date '99-Jan-08';
+ ERROR:  date/time field value out of range: "99-Jan-08"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '1999-Jan-08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08-Jan-99';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08-Jan-1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'Jan-08-99';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'Jan-08-1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '99-08-Jan';
+ ERROR:  invalid input syntax for type date: "99-08-Jan"
+ SELECT date '1999-08-Jan';
+ ERROR:  invalid input syntax for type date: "1999-08-Jan"
+ SELECT date '99 Jan 08';
+ ERROR:  date/time field value out of range: "99 Jan 08"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '1999 Jan 08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08 Jan 99';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08 Jan 1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'Jan 08 99';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'Jan 08 1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '99 08 Jan';
+ ERROR:  invalid input syntax for type date: "99 08 Jan"
+ SELECT date '1999 08 Jan';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '99-01-08';
+ ERROR:  date/time field value out of range: "99-01-08"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '1999-01-08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08-01-99';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08-01-1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '01-08-99';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SELECT date '01-08-1999';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SELECT date '99-08-01';
+ ERROR:  date/time field value out of range: "99-08-01"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '1999-08-01';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SELECT date '99 01 08';
+ ERROR:  date/time field value out of range: "99 01 08"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '1999 01 08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08 01 99';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08 01 1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '01 08 99';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SELECT date '01 08 1999';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SELECT date '99 08 01';
+ ERROR:  date/time field value out of range: "99 08 01"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '1999 08 01';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SET datestyle TO mdy;
+ SELECT date 'January 8, 1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1999-01-08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1999-01-18';
+     date
+ ------------
+  1999-01-18
+ (1 row)
+
+ SELECT date '1/8/1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1/18/1999';
+     date
+ ------------
+  1999-01-18
+ (1 row)
+
+ SELECT date '18/1/1999';
+ ERROR:  date/time field value out of range: "18/1/1999"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '01/02/03';
+     date
+ ------------
+  2003-01-02
+ (1 row)
+
+ SELECT date '19990108';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '990108';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '1999.008';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'J2451187';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'January 8, 99 BC';
+      date
+ ---------------
+  0099-01-08 BC
+ (1 row)
+
+ SELECT date '99-Jan-08';
+ ERROR:  date/time field value out of range: "99-Jan-08"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '1999-Jan-08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08-Jan-99';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08-Jan-1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'Jan-08-99';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'Jan-08-1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '99-08-Jan';
+ ERROR:  invalid input syntax for type date: "99-08-Jan"
+ SELECT date '1999-08-Jan';
+ ERROR:  invalid input syntax for type date: "1999-08-Jan"
+ SELECT date '99 Jan 08';
+ ERROR:  invalid input syntax for type date: "99 Jan 08"
+ SELECT date '1999 Jan 08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08 Jan 99';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08 Jan 1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'Jan 08 99';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date 'Jan 08 1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '99 08 Jan';
+ ERROR:  invalid input syntax for type date: "99 08 Jan"
+ SELECT date '1999 08 Jan';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '99-01-08';
+ ERROR:  date/time field value out of range: "99-01-08"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '1999-01-08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08-01-99';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SELECT date '08-01-1999';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SELECT date '01-08-99';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '01-08-1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '99-08-01';
+ ERROR:  date/time field value out of range: "99-08-01"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '1999-08-01';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SELECT date '99 01 08';
+ ERROR:  date/time field value out of range: "99 01 08"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '1999 01 08';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '08 01 99';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SELECT date '08 01 1999';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ SELECT date '01 08 99';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '01 08 1999';
+     date
+ ------------
+  1999-01-08
+ (1 row)
+
+ SELECT date '99 08 01';
+ ERROR:  date/time field value out of range: "99 08 01"
+ HINT:  Perhaps you need a different "datestyle" setting.
+ SELECT date '1999 08 01';
+     date
+ ------------
+  1999-08-01
+ (1 row)
+
+ RESET datestyle;
+ --
  -- Simple math
  -- Leave most of it for the horology tests
  --
*** src/test/regress/sql/date.sql.orig    Tue Sep 12 01:42:19 2000
--- src/test/regress/sql/date.sql    Sat Nov 15 11:58:39 2003
***************
*** 29,34 ****
--- 29,194 ----
    WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01';

  --
+ -- Check all the documented input formats
+ --
+ SET datestyle TO iso;  -- display results in ISO
+
+ SET datestyle TO ymd;
+
+ SELECT date 'January 8, 1999';
+ SELECT date '1999-01-08';
+ SELECT date '1999-01-18';
+ SELECT date '1/8/1999';
+ SELECT date '1/18/1999';
+ SELECT date '18/1/1999';
+ SELECT date '01/02/03';
+ SELECT date '19990108';
+ SELECT date '990108';
+ SELECT date '1999.008';
+ SELECT date 'J2451187';
+ SELECT date 'January 8, 99 BC';
+
+ SELECT date '99-Jan-08';
+ SELECT date '1999-Jan-08';
+ SELECT date '08-Jan-99';
+ SELECT date '08-Jan-1999';
+ SELECT date 'Jan-08-99';
+ SELECT date 'Jan-08-1999';
+ SELECT date '99-08-Jan';
+ SELECT date '1999-08-Jan';
+
+ SELECT date '99 Jan 08';
+ SELECT date '1999 Jan 08';
+ SELECT date '08 Jan 99';
+ SELECT date '08 Jan 1999';
+ SELECT date 'Jan 08 99';
+ SELECT date 'Jan 08 1999';
+ SELECT date '99 08 Jan';
+ SELECT date '1999 08 Jan';
+
+ SELECT date '99-01-08';
+ SELECT date '1999-01-08';
+ SELECT date '08-01-99';
+ SELECT date '08-01-1999';
+ SELECT date '01-08-99';
+ SELECT date '01-08-1999';
+ SELECT date '99-08-01';
+ SELECT date '1999-08-01';
+
+ SELECT date '99 01 08';
+ SELECT date '1999 01 08';
+ SELECT date '08 01 99';
+ SELECT date '08 01 1999';
+ SELECT date '01 08 99';
+ SELECT date '01 08 1999';
+ SELECT date '99 08 01';
+ SELECT date '1999 08 01';
+
+ SET datestyle TO dmy;
+
+ SELECT date 'January 8, 1999';
+ SELECT date '1999-01-08';
+ SELECT date '1999-01-18';
+ SELECT date '1/8/1999';
+ SELECT date '1/18/1999';
+ SELECT date '18/1/1999';
+ SELECT date '01/02/03';
+ SELECT date '19990108';
+ SELECT date '990108';
+ SELECT date '1999.008';
+ SELECT date 'J2451187';
+ SELECT date 'January 8, 99 BC';
+
+ SELECT date '99-Jan-08';
+ SELECT date '1999-Jan-08';
+ SELECT date '08-Jan-99';
+ SELECT date '08-Jan-1999';
+ SELECT date 'Jan-08-99';
+ SELECT date 'Jan-08-1999';
+ SELECT date '99-08-Jan';
+ SELECT date '1999-08-Jan';
+
+ SELECT date '99 Jan 08';
+ SELECT date '1999 Jan 08';
+ SELECT date '08 Jan 99';
+ SELECT date '08 Jan 1999';
+ SELECT date 'Jan 08 99';
+ SELECT date 'Jan 08 1999';
+ SELECT date '99 08 Jan';
+ SELECT date '1999 08 Jan';
+
+ SELECT date '99-01-08';
+ SELECT date '1999-01-08';
+ SELECT date '08-01-99';
+ SELECT date '08-01-1999';
+ SELECT date '01-08-99';
+ SELECT date '01-08-1999';
+ SELECT date '99-08-01';
+ SELECT date '1999-08-01';
+
+ SELECT date '99 01 08';
+ SELECT date '1999 01 08';
+ SELECT date '08 01 99';
+ SELECT date '08 01 1999';
+ SELECT date '01 08 99';
+ SELECT date '01 08 1999';
+ SELECT date '99 08 01';
+ SELECT date '1999 08 01';
+
+ SET datestyle TO mdy;
+
+ SELECT date 'January 8, 1999';
+ SELECT date '1999-01-08';
+ SELECT date '1999-01-18';
+ SELECT date '1/8/1999';
+ SELECT date '1/18/1999';
+ SELECT date '18/1/1999';
+ SELECT date '01/02/03';
+ SELECT date '19990108';
+ SELECT date '990108';
+ SELECT date '1999.008';
+ SELECT date 'J2451187';
+ SELECT date 'January 8, 99 BC';
+
+ SELECT date '99-Jan-08';
+ SELECT date '1999-Jan-08';
+ SELECT date '08-Jan-99';
+ SELECT date '08-Jan-1999';
+ SELECT date 'Jan-08-99';
+ SELECT date 'Jan-08-1999';
+ SELECT date '99-08-Jan';
+ SELECT date '1999-08-Jan';
+
+ SELECT date '99 Jan 08';
+ SELECT date '1999 Jan 08';
+ SELECT date '08 Jan 99';
+ SELECT date '08 Jan 1999';
+ SELECT date 'Jan 08 99';
+ SELECT date 'Jan 08 1999';
+ SELECT date '99 08 Jan';
+ SELECT date '1999 08 Jan';
+
+ SELECT date '99-01-08';
+ SELECT date '1999-01-08';
+ SELECT date '08-01-99';
+ SELECT date '08-01-1999';
+ SELECT date '01-08-99';
+ SELECT date '01-08-1999';
+ SELECT date '99-08-01';
+ SELECT date '1999-08-01';
+
+ SELECT date '99 01 08';
+ SELECT date '1999 01 08';
+ SELECT date '08 01 99';
+ SELECT date '08 01 1999';
+ SELECT date '01 08 99';
+ SELECT date '01 08 1999';
+ SELECT date '99 08 01';
+ SELECT date '1999 08 01';
+
+ RESET datestyle;
+
+ --
  -- Simple math
  -- Leave most of it for the horology tests
  --

> I made up a more thorough regression test for date input formats, and
> found that there were still some cases that were rejected :-(.  Attached
> is a more complete patch that handles all month-name cases, and
> explicitly can not change the behavior when there's not a textual month
> name.  Documentation addition and regression test included.
> 
> I'd like some further review of this before I risk applying it to 7.4
> though ... anyone have time today?

Hi Tom,

Everything passes for me on FreeBSD 4.8, latest CVS, "gmake check", with 
your patch applied.

Chris

Christopher Kings-Lynne wrote:
>> I made up a more thorough regression test for date input formats, and
>> found that there were still some cases that were rejected :-(.  Attached
>> is a more complete patch that handles all month-name cases, and
>> explicitly can not change the behavior when there's not a textual month
>> name.  Documentation addition and regression test included.
>>
>> I'd like some further review of this before I risk applying it to 7.4
>> though ... anyone have time today?
> 
> Everything passes for me on FreeBSD 4.8, latest CVS, "gmake check", with 
> your patch applied.

Same here on RH 9.

Joe