[MDEV-28075] JSON_VALUE returns first value from array not from range Created: 2022-03-16  Updated: 2022-04-12  Resolved: 2022-04-12

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.9
Fix Version/s: 10.9.1

Type: Bug Priority: Major
Reporter: Lena Startseva Assignee: Rucha Deodhar
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-27911 Implement range notation for json path Closed

 Description   

Test:

SET @json1= '[
 
             [{"key1": "value1"}, {"key2": "value2"}],
 
             [{"key3": "value3"}, {"key1": "value4"}],
 
             [{"key1": "value5"}, {"key4": "value6"}, {"key1": "value7"}]
 
            ]';
 
SELECT JSON_VALUE(@json1, '$[2][1 to 2].key1');
 
SELECT JSON_VALUE(@json1, '$[2][1].key1');
 
SELECT JSON_VALUE(@json1, '$[2][2].key1');

Expected result:

SET @json1= '[
 
             [{"key1": "value1"}, {"key2": "value2"}],
 
             [{"key3": "value3"}, {"key1": "value4"}],
 
             [{"key1": "value5"}, {"key4": "value6"}, {"key1": "value7"}]
 
            ]';
 
SELECT JSON_VALUE(@json1, '$[2][1 to 2].key1');
 
JSON_VALUE(@json1, '$[2][1 to 2].key1')
 
value7
 
SELECT JSON_VALUE(@json1, '$[2][1].key1');
 
JSON_VALUE(@json1, '$[2][1].key1')
 
NULL
 
SELECT JSON_VALUE(@json1, '$[2][2].key1');
 
JSON_VALUE(@json1, '$[2][2].key1')
 
value7

Actual result:

SET @json1= '[
 
             [{"key1": "value1"}, {"key2": "value2"}],
 
             [{"key3": "value3"}, {"key1": "value4"}],
 
             [{"key1": "value5"}, {"key4": "value6"}, {"key1": "value7"}]
 
            ]';
 
SELECT JSON_VALUE(@json1, '$[2][1 to 2].key1');
 
JSON_VALUE(@json1, '$[2][1 to 2].key1')
 
value5
 
SELECT JSON_VALUE(@json1, '$[2][1].key1');
 
JSON_VALUE(@json1, '$[2][1].key1')
 
NULL
 
SELECT JSON_VALUE(@json1, '$[2][2].key1');
 
JSON_VALUE(@json1, '$[2][2].key1')
 
value7

The same behavior when json contains only arrays:

SET @json= '[
 
             [1.1, {"key1": "value1"}, 3],
 
             [false, 5, 6],
 
             [7, 8, [9, {"key2": 2}, 11]],
 
             [11, 1.34, [14], ["string1", [16, {"key1":[1,2,3,[4,5,6]]}, 18]]],
 
             [19, 20]
 
            ]';
 
SELECT JSON_VALUE(@json, '$[2 to 3][0]');
 
JSON_VALUE(@json, '$[2 to 3][0]')
 
1.1
 
SELECT JSON_VALUE(@json, '$[2][0]');
 
JSON_VALUE(@json, '$[2][0]')
 
7
 
SELECT JSON_VALUE(@json, '$[3][0]');
 
JSON_VALUE(@json, '$[3][0]')
 
11

"1.1" is the first value in array not first in the specified range.

 Comments   
Comment by Lena Startseva [ 2022-03-16 ]

JSON_QUERY has the same behavior:

SET @json='{
 
            "A": [0,
 
                  [1, 2, 3],
 
                  [4, 5, 6],
 
                  "seven",
 
                   0.8,
 
                   true,
 
                   false,
 
                   "eleven",
 
                  [[17, 18], 12, [13, 14], {"key1":"value1"},[15]],
 
                  true],
 
            "B": {"C": 1},
 
            "D": 2
 
           }';
 
SELECT JSON_QUERY(@json, '$.A[-2][1 to 3]');
 
JSON_QUERY(@json, '$.A[-2][1 to 3]')
 
[17, 18]
 
SELECT JSON_QUERY(@json, '$.A[-2][1]');
 
JSON_QUERY(@json, '$.A[-2][1]')
 
NULL
 
SELECT JSON_QUERY(@json, '$.A[-2][2]');
 
JSON_QUERY(@json, '$.A[-2][2]')
 
[13, 14]
 
SELECT JSON_QUERY(@json, '$.A[-2][3]');
 
JSON_QUERY(@json, '$.A[-2][3]')
 
{"key1":"value1"}

"[17, 18]' is incorrect result it is expected "[13, 14]"

Comment by Rucha Deodhar [ 2022-03-21 ]

Patch: https://github.com/MariaDB/server/commit/a2c85b42079d45b05534526abb2fb52c486176cc

Comment by Alexey Botchkov [ 2022-04-12 ]

ok to push.

Comment by Rucha Deodhar [ 2022-04-12 ]

Pushed to https://github.com/MariaDB/server/tree/bb-10.9-MDEV-27911

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