[MDEV-28075] JSON_VALUE returns first value from array not from range - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.9(EOL)
Fix Version/s: 10.9.1
Component/s: JSON
Labels:
None

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]')

SELECT JSON_VALUE(@json, '$[3][0]');

JSON_VALUE(@json, '$[3][0]')

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

Attachments

Issue Links

relates to

MDEV-27911 Implement range notation for json path

Closed

Activity

Ascending order - Click to sort in descending order

Lena Startseva added a comment - 2022-03-16 05:43

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]"

Lena Startseva added a comment - 2022-03-16 05:43 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] "

Rucha Deodhar added a comment - 2022-03-21 18:33

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

Rucha Deodhar added a comment - 2022-03-21 18:33 Patch: https://github.com/MariaDB/server/commit/a2c85b42079d45b05534526abb2fb52c486176cc

Alexey Botchkov added a comment - 2022-04-12 12:16

ok to push.

Alexey Botchkov added a comment - 2022-04-12 12:16 ok to push.

Rucha Deodhar added a comment - 2022-04-12 17:03

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

Rucha Deodhar added a comment - 2022-04-12 17:03 Pushed to https://github.com/MariaDB/server/tree/bb-10.9-MDEV-27911

People

Assignee:: Rucha Deodhar

Reporter:: Lena Startseva

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2022-03-16 05:07

Updated:: 2022-04-12 17:03

Resolved:: 2022-04-12 17:03

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server