dolthub / dolthub/dolt

Incorrect results for JSON lookups.

Open
#7,936 0 comments 0 reactions 1 assignee Claimed by @zachmu View on GitHub
correctness json sql
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

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:

```sql
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:

```sql
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:

```sql
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:

```sql
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)
```

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.