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 по дате отправления: