order by of json value is different with MySQL

[YangKeao]

Enhancement

create table t(i INT, j JSON);

insert into t values (0,'[1,2,3,4]');
insert into t values (0,'[1,2,3]');
insert into t values (0,'[5]');

Then consider the following two query:

select j from t order by j;
select j from t where j > json_array(5);

The first line returns:

+--------------+
| j            |
+--------------+
| [5]          |
| [1, 2, 3]    |
| [1, 2, 3, 4] |
+--------------+

It indicates that [5] is the smallest one. However, the second one returns:

Empty Set

It shows no one is greater than [5]. It's not consistent 😢 .

For TiDB, it follows the json order, and returns:

+--------------+
| j            |
+--------------+
| [1, 2, 3]    |
| [1, 2, 3, 4] |
| [5]          |
+--------------+

For the first selection.

[YangKeao]

Also, submitted a bug for MySQL https://bugs.mysql.com/bug.php?id=108344

[xiongjiwei]

order by and < seems different for json value. You can see the warning when use order by

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------+
| Level   | Code | Message                                                                       |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1235 | This version of MySQL doesn't yet support 'sorting of non-scalar JSON values' |
+---------+------+-------------------------------------------------------------------------------+

[YangKeao]

As described in https://bugs.mysql.com/bug.php?id=108344:

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

But in MySQL documents:

ORDER BY and GROUP BY for JSON values works according to these principles:

1. Ordering of scalar JSON values uses the same rules as in the preceding discussion.
2. For ascending sorts, SQL NULL orders before all JSON values, including the JSON null literal; for descending sorts, SQL NULL orders after all JSON values, including the JSON null literal.
3. Sort keys for JSON values are bound by the value of the max_sort_length system variable, so keys that differ only after the first max_sort_length bytes compare as equal.
4. Sorting of nonscalar values is not currently supported and a warning occurs.

Non-scalar values are not supported in MySQL and give a warning. I think we'd better give a warning too 🤔

[YangKeao]

As suggested by @xiongjiwei, we could regard it as a feature TiDB has implemented, but not implemented by MySQL 🍻, then we can close this issue and the PR (which tried to add warning).

If we'll make a different decision / someone expects a different decision in the future, feel free to reopen the issue and PR 😸