Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
None
-
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 |
|
+-----------+---------------------------+
|