BUG #13884: array_to_json() works incorrectly for non-0-based arrays

Hi

2016-01-24 11:47 GMT+01:00 <xtracoder@gmail.com>:

The following bug has been logged on the website:

Bug reference: 13884
Logged by: xtracoder
Email address: xtracoder@gmail.com
PostgreSQL version: 9.5.0
Operating system: Windows
Description:

When postgres array has no elements at the beginning, array_to_json()
generates json array starting at the first initialized value

Steps to reproduce
- - - - - - - - - - - - - - - - - - - - - - - -
DO $$
DECLARE
arr real[];
jsarr jsonb;
arr2 real[];
BEGIN
arr[3] = 1.1;
arr[4] = 1.1;
raise notice 'arr = %', arr;

jsarr = array_to_json(arr);
raise notice 'arr = %', jsarr;

arr[0] = 1.1;
raise notice 'arr = %', arr;

jsarr = array_to_json(arr);
raise notice 'arr = %', jsarr;
END $$

Actual result
- - - - - - - - - - - - - - - - - - - - - - - -
1. NOTICE: arr = [3:4]={1.1,1.1}
2. NOTICE: arr = [1.1, 1.1]
3. NOTICE: arr = [0:4]={1.1,NULL,NULL,1.1,1.1}
4. NOTICE: arr = [1.1, null, null, 1.1, 1.1]

Expected result
- - - - - - - - - - - - - - - - - - - - - - - -
2. NOTICE: arr = [null, null, null, 1.1, 1.1]

It doesn't looks like bug - It hard to say, when is a start of PostgreSQL
array and how PostgreSQL arrays should be serialized to JSON. First field
in pg array is a first field in JSON array.

Pavel

Pavel Stehule <pavel.stehule@gmail.com> writes:

2016-01-24 11:47 GMT+01:00 <xtracoder@gmail.com>:

When postgres array has no elements at the beginning, array_to_json()
generates json array starting at the first initialized value

It doesn't looks like bug - It hard to say, when is a start of PostgreSQL
array and how PostgreSQL arrays should be serialized to JSON. First field
in pg array is a first field in JSON array.

The current behavior looks reasonable to me too. Certainly the proposed
change wouldn't be any more consistent --- what would happen when you had
elements with subscripts less than 1?

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, Jan 25, 2016 at 9:57 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Pavel Stehule <pavel.stehule@gmail.com> writes:

2016-01-24 11:47 GMT+01:00 <xtracoder@gmail.com>:

When postgres array has no elements at the beginning, array_to_json()
generates json array starting at the first initialized value

It doesn't looks like bug - It hard to say, when is a start of PostgreSQL
array and how PostgreSQL arrays should be serialized to JSON. First field
in pg array is a first field in JSON array.

The current behavior looks reasonable to me too. Certainly the proposed
change wouldn't be any more consistent --- what would happen when you had
elements with subscripts less than 1
​.

​I'd go with "since json arrays do not have index information attached to
them it is unwise to use them with non-default (i.e., 1-based and present)
PostgreSQL arrays. Its likely to late to actually error in that
situation. If the user really needs to use a non-default array structure
they'd be advised to create their own conversion functions between
json-compatible arrays (1-based with leading nulls) and the PostgreSQL
arrays.

I would say that the current behavior is wrong since there is no way to
round-trip. Any proper solution would at least need a user-specified
option for whether to truncate leading null array positions (which would
suppress the error for using a starting index > 1). Positions <1 should be
outright disallowed - I cannot imagine adding an argument that allows one
to specify a negative starting index.

David J.