MDEV-22224: Support JSON Path negative index

This patch can be viewed as combination of two parts:
1) Enabling '-' in the path so that the parser does not give out a warning.
2) Setting the negative index to a correct value and returning the
   appropriate value.

1) To enable using the negative index in the path:
To make the parser not return warning when negative index is used in path
'-' needs to be allowed in json path characters. P_NEG is added
to enable this and is made recognizable by setting the 45th index of
json_path_chr_map[] to P_NEG (instead of previous P_ETC)
because 45 corresponds to '-' in unicode.
When the path is being parsed and '-' is encountered, the parser should
recognize it as parsing '-' sign, so a new json state PS_NEG is required.
When the state is PS_NEG, it means that a negative integer is
going to be parsed so set is_negative_index of current step to 1 and
n_item is set accordingly when integer is encountered after '-'.
Next proceed with parsing rest of the path and get the correct path.
Next thing is parsing the json and returning correct value.

2) Setting the negative index to a correct value and returning the value:
While parsing json if we encounter array and the path step for the array
is a negative index (n_item < 0), then we can count the number of elements
in the array and set n_item to correct corresponding value. This is done in
json_skip_array_and_count.

2 files changed, 483 insertions, 1 deletions

diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result
index 206547c7d7a..5bcd90f3410 100644
--- a/mysql-test/main/func_json.result
+++ b/mysql-test/main/func_json.result
@@ -1753,3 +1753,265 @@ Warning	4037	Unexpected end of JSON text in argument 2 to function 'json_overlap
 #
 # End of 10.9 test
 #
+#
+# Beginning of 10.9 Test
+#
+# MDEV-22224: Support JSON Path negative index
+#
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_ARRAY_APPEND(@json, '$.A[-2][-1]', 5);
+JSON_ARRAY_APPEND(@json, '$.A[-2][-1]', 5)
+{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, 13, {"key1": "value1"}, [15, 5]], true], "B": {"C": 1}, "D": 2}
+SELECT JSON_ARRAY_APPEND(@json, '$.A[last-1][last]', 5);
+JSON_ARRAY_APPEND(@json, '$.A[last-1][last]', 5)
+{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, 13, {"key1": "value1"}, [15, 5]], true], "B": {"C": 1}, "D": 2}
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_ARRAY_INSERT(@json, '$.A[-2][-2]', 5);
+JSON_ARRAY_INSERT(@json, '$.A[-2][-2]', 5)
+{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, 13, {"key1": "value1"}, 5, [15]], true], "B": {"C": 1}, "D": 2}
+SELECT JSON_ARRAY_INSERT(@json, '$.A[last-1][last-1]', 5);
+JSON_ARRAY_INSERT(@json, '$.A[last-1][last-1]', 5)
+{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, 13, {"key1": "value1"}, 5, [15]], true], "B": {"C": 1}, "D": 2}
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_CONTAINS(@json, '15', '$.A[-2][-1]');
+JSON_CONTAINS(@json, '15', '$.A[-2][-1]')
+1
+SELECT JSON_CONTAINS(@json, '15', '$.A[last-1][last]');
+JSON_CONTAINS(@json, '15', '$.A[last-1][last]')
+1
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_CONTAINS_PATH(@json, 'one', '$.A[-2]');
+JSON_CONTAINS_PATH(@json, 'one', '$.A[-2]')
+1
+SELECT JSON_CONTAINS_PATH(@json, 'one', '$.A[last-1]');
+JSON_CONTAINS_PATH(@json, 'one', '$.A[last-1]')
+1
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_EXISTS(@json, '$.A[-2][-1]');
+JSON_EXISTS(@json, '$.A[-2][-1]')
+1
+SELECT JSON_EXISTS(@json, '$.A[last-1][last]');
+JSON_EXISTS(@json, '$.A[last-1][last]')
+1
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_EXTRACT(@json, '$.A[-8][1]');
+JSON_EXTRACT(@json, '$.A[-8][1]')
+5
+SELECT JSON_EXTRACT(@json, '$.A[last-7][1]');
+JSON_EXTRACT(@json, '$.A[last-7][1]')
+5
+SET @json= '[{"A": 1, "B": 2, "C": {"D": 3}},{"A": 1, "B": 2, "C": {"D": 3}}]';
+SELECT JSON_KEYS(@json, '$[-1].C');
+JSON_KEYS(@json, '$[-1].C')
+["D"]
+SELECT JSON_KEYS(@json, '$[last].C');
+JSON_KEYS(@json, '$[last].C')
+["D"]
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_LENGTH(@json, '$.A[-2][-3]');
+JSON_LENGTH(@json, '$.A[-2][-3]')
+2
+SELECT JSON_LENGTH(@json, '$.A[last-1][last-2]');
+JSON_LENGTH(@json, '$.A[last-1][last-2]')
+2
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_QUERY(@json, '$.A[-8]');
+JSON_QUERY(@json, '$.A[-8]')
+[4, 5, 6]
+SELECT JSON_QUERY(@json, '$.A[last-7]');
+JSON_QUERY(@json, '$.A[last-7]')
+[4, 5, 6]
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_REMOVE(@json, '$.A[-10]');
+JSON_REMOVE(@json, '$.A[-10]')
+{"A": [[1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], true], "B": {"C": 1}, "D": 2}
+SELECT JSON_REMOVE(@json, '$.A[last-9]');
+JSON_REMOVE(@json, '$.A[last-9]')
+{"A": [[1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], true], "B": {"C": 1}, "D": 2}
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_REPLACE(@json, '$.A[-1]', 4);
+JSON_REPLACE(@json, '$.A[-1]', 4)
+{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], 4], "B": {"C": 1}, "D": 2}
+SELECT JSON_REPLACE(@json, '$.A[last]', 4);
+JSON_REPLACE(@json, '$.A[last]', 4)
+{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, false, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], 4], "B": {"C": 1}, "D": 2}
+SET @json = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
+SELECT JSON_SEARCH(@json, 'all', 'abc', NULL, '$[-2]');
+JSON_SEARCH(@json, 'all', 'abc', NULL, '$[-2]')
+"$[2].x"
+SELECT JSON_SEARCH(@json, 'all', 'abc', NULL, '$[last-1]');
+JSON_SEARCH(@json, 'all', 'abc', NULL, '$[last-1]')
+"$[2].x"
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_SET(@json, '$.A[-4]', 100);
+JSON_SET(@json, '$.A[-4]', 100)
+{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, 100, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], true], "B": {"C": 1}, "D": 2}
+SELECT JSON_SET(@json, '$.A[last-3]', 100);
+JSON_SET(@json, '$.A[last-3]', 100)
+{"A": [0, [1, 2, 3], [4, 5, 6], "seven", 0.8, true, 100, "eleven", [12, [13, 14], {"key1": "value1"}, [15]], true], "B": {"C": 1}, "D": 2}
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":123},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_VALUE(@json, '$.A[-2][-2].key1');
+JSON_VALUE(@json, '$.A[-2][-2].key1')
+123
+SELECT JSON_VALUE(@json, '$.A[last-1][last-1].key1');
+JSON_VALUE(@json, '$.A[last-1][last-1].key1')
+123
+#
+# End of 10.9 Test
+#
diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test
index 1e7d29dd350..ea7358f4563 100644
--- a/mysql-test/main/func_json.test
+++ b/mysql-test/main/func_json.test
@@ -916,7 +916,6 @@ DROP TABLE t;
 --echo #
 --echo # End of 10.5 tests
 --echo #
-
 --echo #
 --echo # Beginning of 10.9 tests
 --echo #
@@ -1089,3 +1088,224 @@ SELECT JSON_OVERLAPS('true','tr');
 --echo #
 --echo # End of 10.9 test
 --echo #
+--echo #
+--echo # Beginning of 10.9 Test
+--echo #
+--echo # MDEV-22224: Support JSON Path negative index
+--echo #
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_ARRAY_APPEND(@json, '$.A[-2][-1]', 5);
+SELECT JSON_ARRAY_APPEND(@json, '$.A[last-1][last]', 5);
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_ARRAY_INSERT(@json, '$.A[-2][-2]', 5);
+SELECT JSON_ARRAY_INSERT(@json, '$.A[last-1][last-1]', 5);
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_CONTAINS(@json, '15', '$.A[-2][-1]');
+SELECT JSON_CONTAINS(@json, '15', '$.A[last-1][last]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_CONTAINS_PATH(@json, 'one', '$.A[-2]');
+SELECT JSON_CONTAINS_PATH(@json, 'one', '$.A[last-1]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, 13, {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_EXISTS(@json, '$.A[-2][-1]');
+SELECT JSON_EXISTS(@json, '$.A[last-1][last]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_EXTRACT(@json, '$.A[-8][1]');
+SELECT JSON_EXTRACT(@json, '$.A[last-7][1]');
+
+SET @json= '[{"A": 1, "B": 2, "C": {"D": 3}},{"A": 1, "B": 2, "C": {"D": 3}}]';
+SELECT JSON_KEYS(@json, '$[-1].C');
+SELECT JSON_KEYS(@json, '$[last].C');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_LENGTH(@json, '$.A[-2][-3]');
+SELECT JSON_LENGTH(@json, '$.A[last-1][last-2]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_QUERY(@json, '$.A[-8]');
+SELECT JSON_QUERY(@json, '$.A[last-7]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_REMOVE(@json, '$.A[-10]');
+SELECT JSON_REMOVE(@json, '$.A[last-9]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_REPLACE(@json, '$.A[-1]', 4);
+SELECT JSON_REPLACE(@json, '$.A[last]', 4);
+
+SET @json = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
+SELECT JSON_SEARCH(@json, 'all', 'abc', NULL, '$[-2]');
+SELECT JSON_SEARCH(@json, 'all', 'abc', NULL, '$[last-1]');
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":"value1"},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_SET(@json, '$.A[-4]', 100);
+SELECT JSON_SET(@json, '$.A[last-3]', 100);
+
+SET @json='{
+            "A": [0,
+                  [1, 2, 3],
+                  [4, 5, 6],
+                  "seven",
+                   0.8,
+                   true,
+                   false,
+                   "eleven",
+                  [12, [13, 14], {"key1":123},[15]],
+                  true],
+            "B": {"C": 1},
+            "D": 2
+           }';
+SELECT JSON_VALUE(@json, '$.A[-2][-2].key1');
+SELECT JSON_VALUE(@json, '$.A[last-1][last-1].key1');
+
+--echo #
+--echo # End of 10.9 Test
+--echo #