[#MDEV-22141] JSON_REMOVE returns NULL on valid arguments.

[MDEV-22141] JSON_REMOVE returns NULL on valid arguments. Created: 2020-04-03 Updated: 2023-11-28
Status:	Confirmed
Project:	MariaDB Server
Component/s:	JSON
Affects Version/s:	10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s:	10.4, 10.5, 10.6

Type:

Bug

Priority:

Major

Reporter:

Alexey Botchkov

Assignee:

Rucha Deodhar

Resolution:

Unresolved

Votes:

Labels:

None

Issue Links:

Duplicate
is duplicated by	MDEV-24542	JSON_REMOVE returns wrong result and ...	Open

Description

MariaDB [(none)]> SELECT JSON_REMOVE('{"A": { "B": 1 }}', '$.A.B.C');

+---------------------------------------------+

| JSON_REMOVE('{"A": { "B": 1 }}', '$.A.B.C') |

+---------------------------------------------+

| {"A": {"B": 1}}                             |

+---------------------------------------------+

1 row in set (0.00 sec)

which is correct.

MariaDB [(none)]> SELECT JSON_REMOVE('{"A": { "B": 1 }}', '$.A.B.C.D');

+-----------------------------------------------+

| JSON_REMOVE('{"A": { "B": 1 }}', '$.A.B.C.D') |

+-----------------------------------------------+

| NULL                                          |

+-----------------------------------------------+

1 row in set, 1 warning (0.00 sec)

MariaDB [(none)]> show warnings;

+---------+------+---------------------------------------------------------------------+

| Level   | Code | Message                                                             |

+---------+------+---------------------------------------------------------------------+

| Warning | 4037 | Unexpected end of JSON text in argument 1 to function 'json_remove' |

+---------+------+---------------------------------------------------------------------+

1 row in set (0.00 sec)

The result should be as in the first query, but we get NULL instead.

Comments

Comment by Jaime Contesse [ 2023-02-06 ]

Another example:

This works great:

SELECT JSON_REMOVE('[{"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}]', '$[1].B')

This returns NULL

SELECT JSON_REMOVE('[{"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}]', '$[*].B')

Comment by Alice Sherepa [ 2023-02-20 ]

jcontesse, KB says that with wildcards error is expected:

MariaDB [test]> SELECT JSON_REMOVE('[{"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}]', '$[*].B');

+---------------------------------------------------------------------------------------------------------------------+

| JSON_REMOVE('[{"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}, {"A": 1, "B": 2}]', '$[*].B') |

+---------------------------------------------------------------------------------------------------------------------+

| NULL                                                                                                                |

+---------------------------------------------------------------------------------------------------------------------+

1 row in set, 1 warning (0,001 sec)

Warning (Code 4044): Wildcards or range in JSON path not allowed in argument 2 to function 'json_remove'

Generated at Thu Feb 08 09:12:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.