Обсуждение: BUG #14446: make_date with negative year

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDQ0NgpMb2dnZWQgYnk6ICAg
ICAgICAgIEFuZHkgQWJlbGlzdG8KRW1haWwgYWRkcmVzczogICAgICBhYmVs
aXN0b0BnbWFpbC5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjUuNQpPcGVy
YXRpbmcgc3lzdGVtOiAgIExpbnV4IE1pbnQKRGVzY3JpcHRpb246ICAgICAg
ICAKCndpdGggdCh4KSBhcyAodmFsdWVzKCcxMTExLTExLTExIEJDJzo6ZGF0
ZSkpIHNlbGVjdCBtYWtlX2RhdGUoZXh0cmFjdCh5ZWFyCmZyb20geCk6Omlu
dCwgZXh0cmFjdChtb250aCBmcm9tIHgpOjppbnQsIGV4dHJhY3QoZGF5IGZy
b20geCk6OmludCkgZnJvbQp0Ow0KRVJST1I6ICBkYXRlIGZpZWxkIHZhbHVl
IG91dCBvZiByYW5nZTogLTExMTEtMTEtMTEKCg==

abelisto@gmail.com wrote:

> Bug reference:      14446
> Logged by:          Andy Abelisto
> Email address:      abelisto@gmail.com
> PostgreSQL version: 9.5.5
> Operating system:   Linux Mint
> Description:
>
> with t(x) as (values('1111-11-11 BC'::date)) select make_date(extract(year
> from x)::int, extract(month from x)::int, extract(day from x)::int) from
> t;
> ERROR:  date field value out of range: -1111-11-11

make_date() has:

    /*
     * Note: we'll reject zero or negative year values.  Perhaps negatives
     * should be allowed to represent BC years?
     */
    dterr = ValidateDate(DTK_DATE_M, false, false, false, &tm);

With the attached patch it becomes (needs docs, tests):

with t(x) as (values('1111-11-11 BC'::date),
        ('00001-11-11 bc'),
        ('00001-11-11'),
        ('1111-11-11')
)
select make_date(extract(year
from x)::int, extract(month from x)::int, extract(day from x)::int) from
t;
   make_date
───────────────
 1111-11-11 BC
 0001-11-11 BC
 0001-11-11
 1111-11-11
(4 filas)

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

2016-12-04 14:40 GMT+01:00 Alvaro Herrera <alvherre@2ndquadrant.com>:

> abelisto@gmail.com wrote:
>
> > Bug reference:      14446
> > Logged by:          Andy Abelisto
> > Email address:      abelisto@gmail.com
> > PostgreSQL version: 9.5.5
> > Operating system:   Linux Mint
> > Description:
> >
> > with t(x) as (values('1111-11-11 BC'::date)) select
> make_date(extract(year
> > from x)::int, extract(month from x)::int, extract(day from x)::int) fro=
m
> > t;
> > ERROR:  date field value out of range: -1111-11-11
>
> make_date() has:
>
>         /*
>          * Note: we'll reject zero or negative year values.  Perhaps
> negatives
>          * should be allowed to represent BC years?
>          */
>         dterr =3D ValidateDate(DTK_DATE_M, false, false, false, &tm);
>
> With the attached patch it becomes (needs docs, tests):
>
> with t(x) as (values('1111-11-11 BC'::date),
>         ('00001-11-11 bc'),
>         ('00001-11-11'),
>         ('1111-11-11')
> )
> select make_date(extract(year
> from x)::int, extract(month from x)::int, extract(day from x)::int) from
> t;
>    make_date
> =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80
>  1111-11-11 BC
>  0001-11-11 BC
>  0001-11-11
>  1111-11-11
> (4 filas)
>
>
+1

Regards

Pavel


> --
> =C3=81lvaro Herrera                https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>

Pavel Stehule wrote:
> 2016-12-04 14:40 GMT+01:00 Alvaro Herrera <alvherre@2ndquadrant.com>:

> > abelisto@gmail.com wrote:
> > >
> > > with t(x) as (values('1111-11-11 BC'::date)) select
> > make_date(extract(year
> > > from x)::int, extract(month from x)::int, extract(day from x)::int) from
> > > t;
> > > ERROR:  date field value out of range: -1111-11-11

So make_date was introduced in 9.4 by commit f901bb50e; this report is
based on 9.5.  Do we want to backpatch this change?  Since the fix only
changes behavior that currently errors out anyway, we would not be
changing anything that people are relying on.  I lean towards
backpatching all the way back to 9.4 myself.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

2016-12-05 19:38 GMT+01:00 Alvaro Herrera <alvherre@2ndquadrant.com>:

> Pavel Stehule wrote:
> > 2016-12-04 14:40 GMT+01:00 Alvaro Herrera <alvherre@2ndquadrant.com>:
>
> > > abelisto@gmail.com wrote:
> > > >
> > > > with t(x) as (values('1111-11-11 BC'::date)) select
> > > make_date(extract(year
> > > > from x)::int, extract(month from x)::int, extract(day from x)::int)
> from
> > > > t;
> > > > ERROR:  date field value out of range: -1111-11-11
>
> So make_date was introduced in 9.4 by commit f901bb50e; this report is
> based on 9.5.  Do we want to backpatch this change?  Since the fix only
> changes behavior that currently errors out anyway, we would not be
> changing anything that people are relying on.  I lean towards
> backpatching all the way back to 9.4 myself.
>

I have not a problem with backpatch - there are a user who see current
behave as bug.

Regards

Pavel

>
> --
> =C3=81lvaro Herrera                https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Pavel Stehule wrote:
> 2016-12-05 19:38 GMT+01:00 Alvaro Herrera <alvherre@2ndquadrant.com>:

> > So make_date was introduced in 9.4 by commit f901bb50e; this report is
> > based on 9.5.  Do we want to backpatch this change?  Since the fix only
> > changes behavior that currently errors out anyway, we would not be
> > changing anything that people are relying on.  I lean towards
> > backpatching all the way back to 9.4 myself.
> 
> I have not a problem with backpatch - there are a user who see current
> behave as bug.

In the end, I decided against backpatching.  It is not entirely out of
the question that somebody *is* depending on this erroring out if
negative years are passed.  But I am acting on my own opinion only; if
there are more votes for a backpatch, I am open to changing it.

If not, we're done here and I'd like to move along.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs