Bug #108344 `order by` of json value is inconsistent with json order

• Submitted: 31 Aug 2022 7:26
• Modified: 31 Aug 2022 12:42
• Reporter: Yang Keao
• Status: Not a Bug
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Linux
• CPU Architecture: x86

[31 Aug 2022 7:26] Yang Keao

Description:
The order of json comparing with `>`, `=`, `<`, is inconsistent with the order returned with `order by`.

How to repeat:
mysql> create table t(i INT, j JSON);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values (0,'[1,2,3,4]');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (0,'[1,2,3]');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (0,'[5]');
Query OK, 1 row affected (0.00 sec)

mysql> select j from t order by j;
+--------------+
| j            |
+--------------+
| [5]          |
| [1, 2, 3]    |
| [1, 2, 3, 4] |
+--------------+
3 rows in set, 1 warning (0.00 sec)

This result indicates that the `[5]` is the smallest value

mysql> select j from t where j > json_array(5);
Empty set (0.00 sec)

This result indicates that no value is greater than `[5]`

[31 Aug 2022 12:42] MySQL Verification Team

Hi Mr. Keao,

Thank you for your bug report.

However, this is not a bug.

JSON values are not ordered by their contents, but by their size. 

Comparisons are completely different topic.

This can all be found in the literature on JSON.

Not a bug.