Thread: BUG #13687: bug in row_to_json function with '-infinity' timestamp

The following bug has been logged on the website:

Bug reference:      13687
Logged by:          Stepan
Email address:      stepanperlov@gmail.com
PostgreSQL version: 9.4.5
Operating system:   Ubuntu 14.04.1 LTS
Description:

SELECT row_to_json(t)
  FROM (SELECT '-infinity'::timestamptz) t

On Mon, Oct 19, 2015 at 9:00 AM, <stepanperlov@gmail.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13687
> Logged by:          Stepan
> Email address:      stepanperlov@gmail.com
> PostgreSQL version: 9.4.5
> Operating system:   Ubuntu 14.04.1 LTS
> Description:
>
> SELECT row_to_json(t)
>   FROM (SELECT '-infinity'::timestamptz) t
>

=E2=80=8BWhat do you see happening and what do you think should happen (and=
 why)?

David J.
=E2=80=8B

On Mon, Oct 19, 2015 at 10:25:53AM -0400, David G. Johnston wrote:
> On Mon, Oct 19, 2015 at 9:00 AM, <stepanperlov@gmail.com> wrote:
>
>     The following bug has been logged on the website:
>
>     Bug reference:      13687
>     Logged by:          Stepan
>     Email address:      stepanperlov@gmail.com
>     PostgreSQL version: 9.4.5
>     Operating system:   Ubuntu 14.04.1 LTS
>     Description:
>
>     SELECT row_to_json(t)
>       FROM (SELECT '-infinity'::timestamptz) t
>
>
> ​What do you see happening and what do you think should happen (and why)?

Well, the output I see is:

    SELECT row_to_json(t)
    FROM (SELECT '-infinity'::timestamptz) t ;
            row_to_json
    ----------------------------
     {"timestamptz":"infinity"}

I assume the user expects positive infinity.

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

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription                             +

On Mon, Oct 19, 2015 at 11:12 AM, Bruce Momjian <bruce@momjian.us> wrote:

> On Mon, Oct 19, 2015 at 10:25:53AM -0400, David G. Johnston wrote:
> > On Mon, Oct 19, 2015 at 9:00 AM, <stepanperlov@gmail.com> wrote:
> >
> >     The following bug has been logged on the website:
> >
> >     Bug reference:      13687
> >     Logged by:          Stepan
> >     Email address:      stepanperlov@gmail.com
> >     PostgreSQL version: 9.4.5
> >     Operating system:   Ubuntu 14.04.1 LTS
> >     Description:
> >
> >     SELECT row_to_json(t)
> >       FROM (SELECT '-infinity'::timestamptz) t
> >
> >
> > =E2=80=8BWhat do you see happening and what do you think should happen =
(and why)?
>
> Well, the output I see is:
>
>         SELECT row_to_json(t)
>         FROM (SELECT '-infinity'::timestamptz) t ;
>                 row_to_json
>         ----------------------------
>          {"timestamptz":"infinity"}
>
> I assume the user expects positive infinity.
>

=E2=80=8BThe user is expecting negative infinity...which I agree with.

Release notes do indicate this incompatibility:
>>>
When converting values of type date, timestamp or timestamptz to JSON,
render the values in a format compliant with ISO 8601 (Andrew Dunstan)

Previously such values were rendered according to the current DateStyle
setting; but many JSON processors require timestamps to be in ISO 8601
format. If necessary, the previous behavior can be obtained by explicitly
casting the datetime value to text before passing it to the JSON conversion
function.
=E2=80=8B>>>=E2=80=8B

=E2=80=8BBut since ISO 8601 doesn't define infinity our implementation of t=
his
behavior is not externally constrained and probably shouldn't be changed.

David J.

On Mon, Oct 19, 2015 at 11:22:58AM -0400, David G. Johnston wrote:
>     Well, the output I see is:
>
>             SELECT row_to_json(t)
>             FROM (SELECT '-infinity'::timestamptz) t ;
>                     row_to_json
>             ----------------------------
>              {"timestamptz":"infinity"}
>
>     I assume the user expects positive infinity.
>
>
> ​The user is expecting negative infinity...which I agree with.

Sorry, yeah, I meant negative infinity.

> Release notes do indicate this incompatibility:
> >>>
> When converting values of type date, timestamp or timestamptz to JSON, render
> the values in a format compliant with ISO 8601 (Andrew Dunstan)
>
> Previously such values were rendered according to the current DateStyle
> setting; but many JSON processors require timestamps to be in ISO 8601 format.
> If necessary, the previous behavior can be obtained by explicitly casting the
> datetime value to text before passing it to the JSON conversion function.
> ​>>>​
>
> ​But since ISO 8601 doesn't define infinity our implementation of this behavior
> is not externally constrained and probably shouldn't be changed.

Well, if infinity is not defined, we can output whatever seems logical,
and returning +infinity for -infinity seems illogical.

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

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription                             +

Степан Перлов <stepanperlov@gmail.com> writes:
> In postgresql 9.3 it returns '-infinity'.

So I see.  Apparently we broke something during 9.4 devel.
        regards, tom lane

On 2015-10-19 17:31, Tom Lane wrote:
> Степан Перлов <stepanperlov@gmail.com> writes:
>> In postgresql 9.3 it returns '-infinity'.
>
> So I see.  Apparently we broke something during 9.4 devel.
>

Yeah it's the commits c00c3249e and bda76c1c8. First one changed the
output to not use to_char so that we could produce correct ISO timestamp
and disallowed the infinity altogether, the second added custom handling
for infinity, but the code does not differentiate between negative and
positive infinity. I think the json code does not need to have custom
handling of infinity but could just call the EncodeSpecialTimestamp() if
it was exported from the timestamp module.

--  Petr Jelinek                  http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> ... ​But since ISO 8601 doesn't define infinity our implementation of this
> behavior is not externally constrained and probably shouldn't be changed.

But we already did change it, ie, 9.4 is failing to follow the precedent
established by 9.2 and 9.3.  And that was not an intentional thing, nor
is it more reasonable than the previous behavior, so I think it's a bug.

            regards, tom lane

On Mon, Oct 19, 2015 at 12:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > ... =E2=80=8BBut since ISO 8601 doesn't define infinity our implementat=
ion of
> this
> > behavior is not externally constrained and probably shouldn't be change=
d.
>
> But we already did change it, ie, 9.4 is failing to follow the precedent
> established by 9.2 and 9.3.  And that was not an intentional thing, nor
> is it more reasonable than the previous behavior, so I think it's a bug.
>

=E2=80=8BThat was what I was getting it.  It shouldn't be changed from the =
prior
behavior.  Clarified under Bruce's response pointing out the same...

David J.

Petr Jelinek <petr@2ndquadrant.com> writes:
> On 2015-10-19 17:31, Tom Lane wrote:
>> So I see.  Apparently we broke something during 9.4 devel.

> Yeah it's the commits c00c3249e and bda76c1c8. First one changed the
> output to not use to_char so that we could produce correct ISO timestamp
> and disallowed the infinity altogether, the second added custom handling
> for infinity, but the code does not differentiate between negative and
> positive infinity. I think the json code does not need to have custom
> handling of infinity but could just call the EncodeSpecialTimestamp() if
> it was exported from the timestamp module.

Meh.  We'd have to change its API too, because it is expecting to print
into a fixed buffer.  I think probably easier to just duplicate the check.

            regards, tom lane

I wrote:
> Petr Jelinek <petr@2ndquadrant.com> writes:
>> I think the json code does not need to have custom
>> handling of infinity but could just call the EncodeSpecialTimestamp() if
>> it was exported from the timestamp module.

> Meh.  We'd have to change its API too, because it is expecting to print
> into a fixed buffer.  I think probably easier to just duplicate the check.

But on second thought, that would have the json code knowing more about
timestamps than timestamp_out does, which seems like pretty bad design.
I'll change it as you suggest.

            regards, tom lane