From: | sdiz(at)sdiz(dot)net |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #13756: jsonb_path_ops gin index produce empty result on nested array |
Date: | 2015-11-05 17:19:33 |
Message-ID: | 20151105171933.14035.25039@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: 13756
Logged by: Daniel Cheng
Email address: sdiz(at)sdiz(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-11-05 18:54:45 | Re: Memory leak with PL/Python trigger |
Previous Message | Breen Hagan | 2015-11-05 16:00:30 | Re: BUG #13755: pgwin32_is_service not checking if SECURITY_SERVICE_SID is disabled |