Обсуждение: BUG #15075: jsonb_set return [null] if new value is null but not'null'.

The following bug has been logged on the website:

Bug reference:      15075
Logged by:          Sylvain Claudel
Email address:      claudel.sylvain@gmail.com
PostgreSQL version: 10.1
Operating system:   MacOSX
Description:

This behavior is it normal ?

select jsonb_set('{"f1":1,"f2":null,"f3":"bidule"}', '{f3}', null, false);

#> [null]

select jsonb_set('{"f1":1,"f2":null,"f3":"bidule"}', '{f3}', 'null',
false);

#> {"f1":1,"f2":null,"f3":null}

In a update query, it is dangerous.
It may be better that the request crashes rather than executing with
surprising behavior.

Tested in postgresql 9.6.4 and 10.1 but nothing on the changelog concerning
jsonb_set since 9.6.1.

Sorry if this report is not "clean".

On Mon, Feb 19, 2018 at 08:33:49AM +0000, PG Bug reporting form wrote:
> Is this behavior normal ?
>
> select jsonb_set('{"f1":1,"f2":null,"f3":"bidule"}', '{f3}', null, false);
> #> [null]
> select jsonb_set('{"f1":1,"f2":null,"f3":"bidule"}', '{f3}', 'null',
> false);
> #> {"f1":1,"f2":null,"f3":null}
>
> In a update query, it is dangerous.
> It may be better that the request crashes rather than executing with
> surprising behavior.

jsonb_set is a strict function, meaning that any NULL value from input
arguments will make the function return NULL as result, which is what
your first example does.  In the second query, you are using null as
JSON value, which is actually valid as input.  So as far as I can see,
this is normal.

> Sorry if this report is not "clean".

No problem.  I have been able to decrypt it easily :)
--
Michael