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

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)?

David J.
​

On Mon, Oct 19, 2015 at 5:25 PM, David G. Johnston <
david.g.johnston@gmail.com> 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)?

David J.
​

Sorry, forget add output:

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

OUT: '{"time":"infinity"}'
MUST: '{"time":"-infinity"}'

In postgresql 9.3 it returns '-infinity'.
Problem with cast timestamp to string in row_to_json function.

SELECT row_to_json(t2) FROM (SELECT time::text FROM (SELECT
'-infinity'::timestamptz AS time) t) t2
returns '{"time":"-infinity"}'

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                             +

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

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

​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.

​The 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.
​>>>​

​But since ISO 8601 doesn't define infinity our implementation of this
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                             +

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

=?UTF-8?B?0KHRgtC10L/QsNC9INCf0LXRgNC70L7Qsg==?= <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

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

On 2015-10-19 17:31, Tom Lane wrote:

=?UTF-8?B?0KHRgtC10L/QsNC9INCf0LXRgNC70L7Qsg==?= <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

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

"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

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

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:

... ​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.

​That 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

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

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

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