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

Поиск
Список
Период
Сортировка
От rqmedes@gmail.com
Тема BUG #11210: Limit on Query causes error or no JSON labels in output
Дата
Msg-id 20140819104859.2544.49278@wrigleys.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #11210: Limit on Query causes error or no JSON labels in output  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      11210
Logged by:          darryl
Email address:      rqmedes@gmail.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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: justin.vanwinkle@gmail.com
Дата:
Сообщение: BUG #11207: empty path will segfault jsonb #>
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #11211: regexp_matches acts like a WHERE