Обсуждение: BUG #6126: CC parameter in to_char() behaves incorrectly

Поиск
Список
Период
Сортировка

BUG #6126: CC parameter in to_char() behaves incorrectly

От
"Artem Andreev"
Дата:
The following bug has been logged online:

Bug reference:      6126
Logged by:          Artem Andreev
Email address:      artem@oktetlabs.ru
PostgreSQL version: 9.0.4
Operating system:   Debian 6.0
Description:        CC parameter in to_char() behaves incorrectly
Details:

CC parameter in to_char() behaves incorrectly with BC dates:

1st century BC:

select to_char('0002-01-01 00:00:00 BC' :: timestamp, 'CC');
====>
 to_char
---------
 01

2nd century BC:

 select to_char('0101-01-01 00:00:00 BC' :: timestamp, 'CC');
=====>
 to_char
---------
 00

3rd century BC:
iliran=> select to_char('0301-01-01 00:00:00 BC' :: timestamp, 'CC');
 to_char
---------
 -2

In all these cases EXTRACT(CENTURY FROM ...) yields the expected result

Re: BUG #6126: CC parameter in to_char() behaves incorrectly

От
Bruce Momjian
Дата:
On Thu, Jul 21, 2011 at 08:40:11AM +0000, Artem Andreev wrote:
>
> The following bug has been logged online:
>
> Bug reference:      6126
> Logged by:          Artem Andreev
> Email address:      artem@oktetlabs.ru
> PostgreSQL version: 9.0.4
> Operating system:   Debian 6.0
> Description:        CC parameter in to_char() behaves incorrectly
> Details:
>
> CC parameter in to_char() behaves incorrectly with BC dates:
>
> 1st century BC:
>
> select to_char('0002-01-01 00:00:00 BC' :: timestamp, 'CC');
> ====>
>  to_char
> ---------
>  01
>
> 2nd century BC:
>
>  select to_char('0101-01-01 00:00:00 BC' :: timestamp, 'CC');
> =====>
>  to_char
> ---------
>  00
>
> 3rd century BC:
> iliran=> select to_char('0301-01-01 00:00:00 BC' :: timestamp, 'CC');
>  to_char
> ---------
>  -2
>
> In all these cases EXTRACT(CENTURY FROM ...) yields the expected result

I have developed the attached patch which fixes this bug:

    test=> select to_char('0002-01-01 00:00:00 BC' :: timestamp, 'CC');
     to_char
    ---------
     -1
    (1 row)

    test=> select to_char('0101-01-01 00:00:00 BC' :: timestamp, 'CC');
     to_char
    ---------
     -2
    (1 row)

    test=> select to_char('0301-01-01 00:00:00 BC' :: timestamp, 'CC');
     to_char
    ---------
     -4
    (1 row)

I also tested boundry values, e.g. 6th Century BC is 600-501:

    test=> select to_char('0600-01-01 00:00:00 BC' :: timestamp, 'CC');
     to_char
    ---------
     -6
    (1 row)

    test=> select to_char('0599-01-01 00:00:00 BC' :: timestamp, 'CC');
     to_char
    ---------
     -6
    (1 row)

    test=> select to_char('0501-01-01 00:00:00 BC' :: timestamp, 'CC');
     to_char
    ---------
     -6
    (1 row)

    test=> select to_char('0500-01-01 00:00:00 BC' :: timestamp, 'CC');
     to_char
    ---------
     -5
    (1 row)

I am thinking it is too late to apply this for 9.2 because users might
have already tested their applications, though I doubt many are using BC
dates.  Feedback?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
Вложения

Re: BUG #6126: CC parameter in to_char() behaves incorrectly

От
Bruce Momjian
Дата:
On Fri, Aug  3, 2012 at 06:51:45PM -0400, Bruce Momjian wrote:
> I also tested boundry values, e.g. 6th Century BC is 600-501:
>
>     test=> select to_char('0600-01-01 00:00:00 BC' :: timestamp, 'CC');
>      to_char
>     ---------
>      -6
>     (1 row)
>
>     test=> select to_char('0599-01-01 00:00:00 BC' :: timestamp, 'CC');
>      to_char
>     ---------
>      -6
>     (1 row)
>
>     test=> select to_char('0501-01-01 00:00:00 BC' :: timestamp, 'CC');
>      to_char
>     ---------
>      -6
>     (1 row)
>
>     test=> select to_char('0500-01-01 00:00:00 BC' :: timestamp, 'CC');
>      to_char
>     ---------
>      -5
>     (1 row)
>
> I am thinking it is too late to apply this for 9.2 because users might
> have already tested their applications, though I doubt many are using BC
> dates.  Feedback?

There is never just one bug in formatting.c --- the input side was also
broken for BC/negative centuries.  The attached patch fixes the input
side as well, and shows the old/fixed output for BC centuries.  The test
queries were:

    SELECT to_date('-6  4', 'CC YY');
    SELECT to_date(' 6  4', 'CC YY');
    SELECT to_date('-6 00', 'CC YY');
    SELECT to_date(' 6 00', 'CC YY');
    SELECT to_date('-6', 'CC');
    SELECT to_date(' 6', 'CC');

I believe this is all for 9.3-only.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
Вложения

Re: BUG #6126: CC parameter in to_char() behaves incorrectly

От
Bruce Momjian
Дата:
On Sat, Aug  4, 2012 at 10:34:14AM -0400, Bruce Momjian wrote:
> > I am thinking it is too late to apply this for 9.2 because users might
> > have already tested their applications, though I doubt many are using BC
> > dates.  Feedback?
>
> There is never just one bug in formatting.c --- the input side was also
> broken for BC/negative centuries.  The attached patch fixes the input
> side as well, and shows the old/fixed output for BC centuries.  The test
> queries were:
>
>     SELECT to_date('-6  4', 'CC YY');
>     SELECT to_date(' 6  4', 'CC YY');
>     SELECT to_date('-6 00', 'CC YY');
>     SELECT to_date(' 6 00', 'CC YY');
>     SELECT to_date('-6', 'CC');
>     SELECT to_date(' 6', 'CC');
>
> I believe this is all for 9.3-only.

OK, I found another bug in our code;  the output of:

  SELECT to_date(' 6 BC', 'CC BC');

returned 501BC, instead of the start of the century, 600BC, like SELECT
to_date('-6', 'CC') does.

I also allowed negative BC dates to map to AD dates, just like negative
AD dates map to BC dates.

Attached is an updated patch and output diff.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
Вложения

Re: BUG #6126: CC parameter in to_char() behaves incorrectly

От
Bruce Momjian
Дата:
On Mon, Aug  6, 2012 at 03:20:18PM -0400, Bruce Momjian wrote:
> On Sat, Aug  4, 2012 at 10:34:14AM -0400, Bruce Momjian wrote:
> > > I am thinking it is too late to apply this for 9.2 because users might
> > > have already tested their applications, though I doubt many are using BC
> > > dates.  Feedback?
> >
> > There is never just one bug in formatting.c --- the input side was also
> > broken for BC/negative centuries.  The attached patch fixes the input
> > side as well, and shows the old/fixed output for BC centuries.  The test
> > queries were:
> >
> >     SELECT to_date('-6  4', 'CC YY');
> >     SELECT to_date(' 6  4', 'CC YY');
> >     SELECT to_date('-6 00', 'CC YY');
> >     SELECT to_date(' 6 00', 'CC YY');
> >     SELECT to_date('-6', 'CC');
> >     SELECT to_date(' 6', 'CC');
> >
> > I believe this is all for 9.3-only.
>
> OK, I found another bug in our code;  the output of:
>
>   SELECT to_date(' 6 BC', 'CC BC');
>
> returned 501BC, instead of the start of the century, 600BC, like SELECT
> to_date('-6', 'CC') does.
>
> I also allowed negative BC dates to map to AD dates, just like negative
> AD dates map to BC dates.
>
> Attached is an updated patch and output diff.

Applied.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +