Incorrect results for JSON lookups.

[nicktobey]

According to the MySQL documentation for JSON paths (https://dev.mysql.com/doc/refman/8.4/en/json.html):

• [N] appended to a path that selects an array names the value at position N within the array. Array positions are integers beginning with zero. If path does not select an array value, path[0] evaluates to the same value as path.

The following example queries demonstrate this:

mysql> select JSON_VALUE('{"a": 1}', "$[0].a");
+----------------------------------+
| JSON_VALUE('{"a": 1}', "$[0].a") |
+----------------------------------+
| 1                                |
+----------------------------------+
1 row in set (0.01 sec)

mysql> select JSON_VALUE('{"a": 1}', "$[0].a[0]");
+-------------------------------------+
| JSON_VALUE('{"a": 1}', "$[0].a[0]") |
+-------------------------------------+
| 1                                   |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select JSON_VALUE('{"a": 1}', "$.a[0]");
+----------------------------------+
| JSON_VALUE('{"a": 1}', "$.a[0]") |
+----------------------------------+
| 1                                |
+----------------------------------+
1 row in set (0.00 sec)

However, all of these return errors in Dolt:

jtest/main*> select JSON_VALUE('{"a": 1}', "$[0].a");
object is not Slice
jtest/main*> select JSON_VALUE('{"a": 1}', "$.a[0]");
object is not Slice
jtest/main*> select JSON_VALUE('{"a": 1}', "$[0].a[0]");
object is not Slice

Functions the modify JSON documents have similar behavior. This is the MySQL behavior:

mysql> select JSON_SET('{"a": 1}', "$.a[0]", 2);
+-----------------------------------+
| JSON_SET('{"a": 1}', "$.a[0]", 2) |
+-----------------------------------+
| {"a": 2}                          |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select JSON_SET('{"a": 1}', "$[0].a[0]", 2);
+--------------------------------------+
| JSON_SET('{"a": 1}', "$[0].a[0]", 2) |
+--------------------------------------+
| {"a": 2}                             |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select JSON_SET('{"a": 1}', "$[0].a", 2);
+-----------------------------------+
| JSON_SET('{"a": 1}', "$[0].a", 2) |
+-----------------------------------+
| {"a": 2}                          |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select JSON_SET('{"a": 1}', "$.a[0][1]", 2);
+--------------------------------------+
| JSON_SET('{"a": 1}', "$.a[0][1]", 2) |
+--------------------------------------+
| {"a": [1, 2]}                        |
+--------------------------------------+
1 row in set (0.00 sec)

But in Dolt, all but the first of these return incorrect results:

jtest/main*> select JSON_SET('{"a": 1}', "$.a[0]", 2);
+-----------------------------------+
| JSON_SET('{"a": 1}', "$.a[0]", 2) |
+-----------------------------------+
| {"a": 2}                          |
+-----------------------------------+
1 row in set (0.00 sec)

jtest/main*> select JSON_SET('{"a": 1}', "$[0].a[0]", 2);
+--------------------------------------+
| JSON_SET('{"a": 1}', "$[0].a[0]", 2) |
+--------------------------------------+
| 2                                    |
+--------------------------------------+
1 row in set (0.00 sec)

jtest/main*> select JSON_SET('{"a": 1}', "$[0].a", 2);
+-----------------------------------+
| JSON_SET('{"a": 1}', "$[0].a", 2) |
+-----------------------------------+
| 2                                 |
+-----------------------------------+
1 row in set (0.00 sec)
jtest/main*> select JSON_SET('{"a": 1}', "$.a[0][1]", 2);
+--------------------------------------+
| JSON_SET('{"a": 1}', "$.a[0][1]", 2) |
+--------------------------------------+
| {"a": 2}                             |
+--------------------------------------+
1 row in set (0.00 sec)