jsonb existence queries are misimplemented by jsonb_ops
От | Tom Lane |
---|---|
Тема | jsonb existence queries are misimplemented by jsonb_ops |
Дата | |
Msg-id | 4392.1399495152@sss.pgh.pa.us обсуждение исходный текст |
Ответы |
Re: jsonb existence queries are misimplemented by jsonb_ops
Re: jsonb existence queries are misimplemented by jsonb_ops |
Список | pgsql-hackers |
I wrote: > The readability of that comment starts to go downhill with its use of > "reset" to refer to what everything else calls a "recheck" flag, and in > any case it's claiming that we *don't* need a recheck for exists (a > statement I suspect to be false, but more later). And, indeed, it's false: regression=# create table j (f1 jsonb); CREATE TABLE regression=# insert into j values ('{"foo": {"bar": "baz"}}'); INSERT 0 1 regression=# insert into j values ('{"foo": {"blah": "baz"}}'); INSERT 0 1 regression=# insert into j values ('{"fool": {"bar": "baz"}}'); INSERT 0 1 regression=# create index on j using gin(f1); CREATE INDEX regression=# select * from j where f1 ? 'bar';f1 ---- (0 rows) regression=# set enable_seqscan to 0; SET regression=# select * from j where f1 ? 'bar'; f1 --------------------------{"foo": {"bar": "baz"}}{"fool": {"bar": "baz"}} (2 rows) The indexscan is incorrectly returning rows where the queried key exists but isn't at top-level. We could fix this either by giving up on no-recheck for existence queries, or by changing the way that non-top-level keys get indexed. However I suspect the latter would break containment queries, or at least make their lives a lot more difficult. Another idea would be to change the definition of the exists operator so that it *does* look into sub-objects. It seems rather random to me that containment looks into sub-objects but exists doesn't. However, possibly there are good reasons for the non-orthogonality. Thoughts? regards, tom lane
В списке pgsql-hackers по дате отправления: