Обсуждение: BUG #13756: jsonb_path_ops gin index produce empty result on nested array
The following bug has been logged on the website: Bug reference: 13756 Logged by: Daniel Cheng Email address: sdiz@sdiz.net PostgreSQL version: 9.4.5 Operating system: Debian (testing) Description: Table with jsonb_path_ops produce wrong result when using the following query. Same query produce different result depends on which query plan is used. db=> select version(); version -------------------------------------------------------------------------------------------------------- PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 5.2.1-21) 5.2.1 20151003, 64-bit (1 row) db=> db=> create table t ( j jsonb); CREATE TABLE db=> insert into t (j) values ('{"a":[ ["b",{"x":1}], ["b",{"x":2}]]}'); INSERT 0 1 db=> create index on t USING gin(j jsonb_path_ops) ; CREATE INDEX db=> select * from t where j @> '{"a":[[{"x":2}]]}'::jsonb; j ------------------------------------------- {"a": [["b", {"x": 1}], ["b", {"x": 2}]]} (1 row) db=> SET enable_seqscan = OFF; SET db=> select * from t where j @> '{"a":[[{"x":2}]]}'::jsonb; j --- (0 rows) db=> explain analyse select * from t where j @> '{"a":[[{"x":2}]]}'::jsonb; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on t (cost=8.00..12.01 rows=1 width=32) (actual time=0.010..0.010 rows=0 loops=1) Recheck Cond: (j @> '{"a": [[{"x": 2}]]}'::jsonb) -> Bitmap Index Scan on t_j_idx1 (cost=0.00..8.00 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1) Index Cond: (j @> '{"a": [[{"x": 2}]]}'::jsonb) Planning time: 0.059 ms Execution time: 0.032 ms (6 rows) db=> drop table t; DROP TABLE Regards, Daniel
Re: BUG #13756: jsonb_path_ops gin index produce empty result on nested array
От
Peter Geoghegan
Дата:
On Thu, Nov 5, 2015 at 9:19 AM, <sdiz@sdiz.net> wrote: > Table with jsonb_path_ops produce wrong result when using the following > query. I can reproduce this. I agree that this does look like a bug in jsonb_path_ops. Investigating. Thanks for the test case. -- Peter Geoghegan
sdiz@sdiz.net writes: > Table with jsonb_path_ops produce wrong result when using the following > query. Fix pushed here: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=788e35ac0bc00489e2b86a930d8c1264100fb94b Thanks for the report! regards, tom lane