BUG #16022: to_json on arrays with unusual lower bound is not intuitive

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: ab(at)principiamentis(dot)com
Subject: BUG #16022: to_json on arrays with unusual lower bound is not intuitive
Date: 2019-09-25 12:23:11
Message-ID: 16022-5c864f8a572d5b5b@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-docs

The following bug has been logged on the website:

Bug reference: 16022
Logged by: A Bergmaier
Email address: ab(at)principiamentis(dot)com
PostgreSQL version: 11.5
Operating system: Ubuntu
Description:

Hi!
I would expect the array indices of a JSON array to match with the
subscripts of a postgres array when converting them back and forth.
However, Postgres lets the JSON array begin at the `lower_bound` of the
array, not at subscript 1 (= index 0).
For example, `to_json('[-2:5]={-2,-1,0,1,2,3,4,5}'::int[])` results in
`[-2,-1,0,1,2,3,4,5]` where I would have expected `[1,2,3,4,5]` (or an
error, since JSON arrays must not have negative indices).
And more annoyingly, `to_json('[3:8]={3,4,5,6,7,8}'::int[])` results in
`[3,4,5,6,7,8]` where I would have needed `[null,null,3,4,5,6,7,8]`.
(See also some demo
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=2d359608815f7059f3120fa17da1bdef)

Just for reference, I'm on PostgreSQL 11.4 (Debian 11.4-1.pgdg90+1) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0
20170516, 64-bit, but I doubt it matters.

I can circumvent this behaviour by using a function like
CREATE FUNCTION array_fillup(arr anyarray) RETURNS anyarray
AS $$
BEGIN
IF array_lower(arr, 1) > 1 THEN
arr[1] = NULL;
END IF;
RETURN arr;
END
$$ LANGUAGE plpgsql STRICT;
before passing my sparse array into `to_json`, but I would like to know
whether there's a better solution or whether the issue could be fixed at the
core. (Presumable, breaking backwards-compatibility is a problem?)

Kind regards,
Andreas Bergmaier

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2019-09-25 14:34:16 Re: BUG #16022: to_json on arrays with unusual lower bound is not intuitive
Previous Message movead.li@highgo.ca 2019-09-25 08:09:46 Re: BUG #15929: logical decoding can not write down the analyse result when the output file is touched.

Browse pgsql-docs by date

  From Date Subject
Next Message Tom Lane 2019-09-25 14:34:16 Re: BUG #16022: to_json on arrays with unusual lower bound is not intuitive
Previous Message Peter Eisentraut 2019-09-25 08:25:59 Re: I'm surprised to see the word master here