On Fri, Jun 5, 2015 at 8:32 , Alvaro Herrera <alvherre@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