BUG #11210: Limit on Query causes error or no JSON labels in output

From: rqmedes(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #11210: Limit on Query causes error or no JSON labels in output
Date: 2014-08-19 10:48:59
Message-ID: 20140819104859.2544.49278@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 11210
Logged by: darryl
Email address: rqmedes(at)gmail(dot)com
PostgreSQL version: 9.3.5
Operating system: Windows
Description:

-- Does not show labels for json. when a limit is used.
-- To replicate

CREATE SCHEMA test32 ;
SET search_path = test32, pg_catalog, public;

CREATE SEQUENCE _id INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807
START 1003480 CACHE 1;

CREATE TABLE item (
id integer NOT NULL DEFAULT nextval('test32._id'::regclass),
code text,
name text NOT NULL,
ttype text
) WITH ( OIDS=FALSE );

CREATE TABLE _inventory (
id integer NOT NULL DEFAULT nextval('test32._id'::regclass),
name text NOT NULL,
ttype text DEFAULT 'inventory'::text
) INHERITS (item) WITH ( OIDS=FALSE );

CREATE TABLE _service (
id integer NOT NULL DEFAULT nextval('test32._id'::regclass),
name text NOT NULL,
ttype text DEFAULT 'service'::text
) INHERITS (item) WITH ( OIDS=FALSE );

INSERT INTO _inventory (id, name) VALUES (1002376,'test32');
INSERT INTO _service (id, name) VALUES (1001498, 'Service1');

/* Labels missing from json output when LIMIT IS added.
In version 9.3.5

-- FAILS WHEN LIMIT IS ADDED --
Select row_to_json(_r) from ( select true as
success,array_agg(row_to_json(result)) as rows from (Select _0."id" as
"[id]" , _0."name" as "[name]" From "item" _0 Limit 10 OFFSET 0) result )
_r ;

OUTPUT =
{"success":true,"rows":[{"":1002376,"":"test32"},{"":1001498,"":"Service1"}]}
EXPECTED OUTPUT =
{"success":true,"rows":[{"[id]":1002376,"[name]":"test32"},{"[id]":1001498,"[name]":"Service1"}]}

-- WORKS WITH NO LIMIT
Select row_to_json(_r) from ( select true as
success,array_agg(row_to_json(result)) as rows from (Select _0."id" as
"[id]" , _0."name" as "[name]" From "item" _0 ) result ) _r ;
OUTPUT =
{"success":true,"rows":[{"[id]":1002376,"[name]":"test32"},{"[id]":1001498,"[name]":"Service1"}]}

In version 9.3.5 the limit causes the query to return json with blank
labels,

IN version 9.3.4 the limit causes the query to fail with an error of.
ERROR: record type has not been registered
********** Error **********

ERROR: record type has not been registered
SQL state: 42809

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message mail 2014-08-19 13:08:01 BUG #11211: regexp_matches acts like a WHERE
Previous Message Kevin Grittner 2014-08-18 22:25:31 Re: BUG #11208: Refresh Materialized View Concurrently bug using user Postgres