On Fri, Jun 5, 2015 at 8:32 , Alvaro Herrera <alvhe...@2ndquadrant.com>
wrote:
Andrew Dunstan wrote:
'some jsonb value' - '{foo,bar}' is already ambiguous - the RH
operand
could be a single text datum or a text array.
Hmm, but that's not in 9.4, so we can still tweak it if necessary.
Consider this jsonb datum. Nobody in their right mind would have a
key
that looks like a path, I hear you say; yet I'm sure this is going to
happen.
alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}, "{c,a}":
"uh"}' ;
jsonb
------------------------------------------------------
{"a": "1", "b": "2", "c": {"a": "2"}, "{c,a}": "uh"}
(1 fila)
This seems pretty surprising to me:
-- here, the -(jsonb,text) operator is silently chosen, even though
the
-- right operand looks like an array. And we do the wrong thing.
alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -
'{c,a}';
?column?
---------------------------------------
{"a": "1", "b": "2", "c": {"a": "2"}}
(1 fila)
-- here, the -(jsonb,text[]) operator is chosen
alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -
_text '{c,a}';
?column?
-------------------------------
{"a": "1", "b": "2", "c": {}}
(1 fila)
But this seems worse to me, because we silently do nothing:
alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -
'{c,a}';
?column?
---------------------------------------
{"a": "1", "b": "2", "c": {"a": "2"}}
(1 fila)
I think the first operator can be qualified as dangerous. If you
delete
that one, then it's fine because you can't do that query anymore
because
of the conflict with -(jsonb, int).
alvherre=# select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -
'{c,a}';
ERROR: operator is not unique: jsonb - unknown
LÍNEA 1: ...elect jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' -
'{c,a}'...
^
SUGERENCIA: Could not choose a best candidate operator. You might
need to add explicit type casts.
That's a good point, and it won't get any better if/when we add the
json point support in 9.6 since the syntax would be something like
select jsonb '{"a":"1", "b":"2", "c": {"a": "2"}}' - '/c/a'; and we
will again silently do nothing. That's going to cause bugs in
applications using this.
--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
