Thread: 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

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