Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

order by of json value is different with MySQL #37506

Closed
Tracked by #36993
YangKeao opened this issue Aug 31, 2022 · 4 comments
Closed
Tracked by #36993

order by of json value is different with MySQL #37506

YangKeao opened this issue Aug 31, 2022 · 4 comments
Assignees
Labels
component/json type/compatibility type/enhancement The issue or PR belongs to an enhancement.

Comments

@YangKeao
Copy link
Member

YangKeao commented Aug 31, 2022

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 YangKeao added type/enhancement The issue or PR belongs to an enhancement. type/compatibility component/json labels Aug 31, 2022
@YangKeao YangKeao self-assigned this Aug 31, 2022
@YangKeao
Copy link
Member Author

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

@xiongjiwei
Copy link
Contributor

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' |
+---------+------+-------------------------------------------------------------------------------+

@xiongjiwei xiongjiwei mentioned this issue Sep 1, 2022
34 tasks
@YangKeao
Copy link
Member Author

YangKeao commented Sep 1, 2022

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
Copy link
Member Author

YangKeao commented Sep 1, 2022

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 😸

@YangKeao YangKeao closed this as completed Sep 1, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
component/json type/compatibility type/enhancement The issue or PR belongs to an enhancement.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants