[MDEV-29586] JSON_VALUE and JSON_EXTRACT doesn't handle special characters Created: 2022-09-21  Updated: 2023-08-24  Resolved: 2023-01-06

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.9.3, 10.9, 10.10
Fix Version/s: 10.9.5

Type: Bug Priority: Major
Reporter: Andy Heathershaw Assignee: Rucha Deodhar
Resolution: Duplicate Votes: 2
Labels: regression
Environment:

Ubuntu Server 22.04.1 LTS, MariaDB 10.9 installed from official MariaDB repositories.

Issue Links:
Duplicate
duplicates MDEV-29381 JSON paths containing dashes are repo... Closed
is duplicated by MDEV-30060 JSON path expression with dashes does... Closed
Problem/Incident
is caused by MDEV-22224 Support JSON Path negative index Closed
Relates
relates to MDEV-32007 JSON_VALUE and JSON_EXTRACT doesn't h... Closed

 Description   

10.9 doesn't seem to be handling special characters in JSON keys in JSON_VALUE and JSON_EXTRACT functions - it returns NULL even when the key is quoted according to the documentation:

."memberName" - the same as above but allows one to select a member with a name that's not a valid identifier (that is, has space, dot, and/or other characters)

This has changed in 10.9 - 10.8 works as expected (I don't have any earlier version to test against.)

In 10.8.4:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Server version: 10.8.4-MariaDB-1:10.8.4+maria~ubu2004-log mariadb.org binary distribution
 
 
 
MariaDB [(none)]> SET @test = '{"NetworkManager":"1:1.36.0-7.el8_6","NetworkManager-initscripts-updown":"1:1.36.0-7.el8_6"}';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
 
 
MariaDB [(none)]> SELECT JSON_VALUE(@test, '$."NetworkManager"');
 
+-----------------------------------------+
 
| JSON_VALUE(@test, '$."NetworkManager"') |
 
+-----------------------------------------+
 
| 1:1.36.0-7.el8_6                        |
 
+-----------------------------------------+
 
1 row in set (0.003 sec)
 
 
 
 
 
MariaDB [(none)]> SELECT JSON_VALUE(@test, '$."NetworkManager-initscripts-updown"');
 
+------------------------------------------------------------+
 
| JSON_VALUE(@test, '$."NetworkManager-initscripts-updown"') |
 
+------------------------------------------------------------+
 
| 1:1.36.0-7.el8_6                                           |
 
+------------------------------------------------------------+
 
1 row in set (0.000 sec)

In 10.9.2 and 10.9.3, the final query returns NULL:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Server version: 10.9.3-MariaDB-1:10.9.3+maria~ubu2204-log mariadb.org binary distribution
 
 
 
MariaDB [(none)]> SET @test = '{"NetworkManager":"1:1.36.0-7.el8_6","NetworkManager-initscripts-updown":"1:1.36.0-7.el8_6"}';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
 
 
MariaDB [(none)]> SELECT JSON_VALUE(@test, '$."NetworkManager"');
 
+-----------------------------------------+
 
| JSON_VALUE(@test, '$."NetworkManager"') |
 
+-----------------------------------------+
 
| 1:1.36.0-7.el8_6                        |
 
+-----------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
 
 
MariaDB [(none)]> SELECT JSON_VALUE(@test, '$."NetworkManager-initscripts-updown"');
 
+------------------------------------------------------------+
 
| JSON_VALUE(@test, '$."NetworkManager-initscripts-updown"') |
 
+------------------------------------------------------------+
 
| NULL                                                       |
 
+------------------------------------------------------------+
 
1 row in set (0.000 sec)



 Comments   
Comment by Alice Sherepa [ 2022-09-21 ]

Thanks for the report! The problem is with dashes, caused by MDEV-22224 (Support JSON Path negative index)

Comment by Steven Haigh [ 2022-11-30 ]

@Alice - Can you please confirm if this is still the case?

That report was marked as fixed - however, as I added on the ticket you linked:

Given the following query:

SELECT JSON_EXTRACT('{ "temperatures": { "AO-therm": "value" } }', '$.temperatures.AO-therm');

The results for different versions of MariaDB are as follows:

10.11-rc --> null
10.10.4 --> null
10.9.4 --> null
10.8.6 --> "value"
As such, this seems like a regression in behaviours from 10.8.6 onwards.

Comment by Alice Sherepa [ 2022-11-30 ]

CRC, no, the bug is not fixed yet, MDEV-29381(there is a patch from the user) + this bug report.

Comment by Rucha Deodhar [ 2023-01-06 ]

Duplicate of: MDEV-29381
Fixed

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