[MDEV-14901] Order by JSON element is wrong Created: 2018-01-09  Updated: 2018-01-09  Resolved: 2018-01-09

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Yiftach Kaplan Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

I have a table with a JSON column and I'm trying to sort by a numeric element of the column, but the results order is wrong. For example:

CREATE TABLE test1 (id INT, data JSON);
 
INSERT INTO test1 VALUES (0, '{"k":1}'),(1, '{"k":2}'), (2, '{"k":3}'), (3, '{"k":11}'), (4, '{"k":5}'), (5, '{"k":100}');
 
SELECT data, JSON_EXTRACT(data, '$.k') FROM test1 ORDER BY JSON_EXTRACT(data, '$.k');

Expected results (results in MySQL 5.7.20):

+------------+---------------------------+
 
| data       | JSON_EXTRACT(data, '$.k') |
 
+------------+---------------------------+
 
| {"k": 1}   | 1                         |
 
| {"k": 2}   | 2                         |
 
| {"k": 3}   | 3                         |
 
| {"k": 5}   | 5                         |
 
| {"k": 11}  | 11                        |
 
| {"k": 100} | 100                       |
 
+------------+---------------------------+

Actual results (MariaDb 10.2.12):

+-----------+---------------------------+
 
| data      | JSON_EXTRACT(data, '$.k') |
 
+-----------+---------------------------+
 
| {"k":1}   | 1                         |
 
| {"k":100} | 100                       |
 
| {"k":11}  | 11                        |
 
| {"k":2}   | 2                         |
 
| {"k":3}   | 3                         |
 
| {"k":5}   | 5                         |
 
+-----------+---------------------------+



 Comments   
Comment by Sergei Golubchik [ 2018-01-09 ]

This is expected behavior.

JSON_EXTRACT() is a non-standard function that returns either a JSON value or a JSON object. When it returns a value it behaves as a standard JSON_VALUE() function.

The standard syntax (SQL:2016, part 2, 6.27 <JSON value function>) is

<JSON value function> ::=
 
  JSON_VALUE <left paren>
 
      <JSON API common syntax>
 
      [ <JSON returning clause> ]
 
      [ <JSON value empty behavior> ON EMPTY ]
 
      [ <JSON value error behavior> ON ERROR ]
 
  <right paren>
 
 
 
<JSON returning clause> ::=
 
  RETURNING <data type>

And the Syntax Rules say

1) If <JSON returning clause> is not specified, then an implementation-defined character string type is implicit.

MariaDB does not support <JSON returning clause> yet, so JSON_VALUE() always returns a "character string type", as required by the SQL Standard.

That's why the sorting is done by the string comparison rules. According to the SQL Standard, if you want a numeric sorting, you need to say it explicitly.

Generated at Thu Feb 08 08:17:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.