Bug #110487 json array field ignored in multi sort.

• Submitted: 24 Mar 2023 6:09
• Modified: 24 Mar 2023 8:46
• Reporter: manyan ouyang
• Status: Verified
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 5.7,8.0.32
• OS: Any
• CPU Architecture: Any
• Tags: array, json, sort

[24 Mar 2023 6:09] manyan ouyang

Description:

when sort json array field with other field ( without using json function),

json field is ignored.

How to repeat:
CREATE TABLE json_test
(
    a  JSON         NULL,
    a1 VARCHAR(128) NULL
);

INSERT INTO oymy_test.json_test (a, a1) VALUES ('[1, 2, 3]', 'a');
INSERT INTO oymy_test.json_test (a, a1) VALUES ('[2, 3, 1]', 'a');
INSERT INTO oymy_test.json_test (a, a1) VALUES ('[3, 3, 1]', 'a');
INSERT INTO oymy_test.json_test (a, a1) VALUES ('[2, 1, 3]', 'b');
INSERT INTO oymy_test.json_test (a, a1) VALUES ('[1, 3, 4]', 'b');
INSERT INTO oymy_test.json_test (a, a1) VALUES ('[1, 3, 2]', 'c');
INSERT INTO oymy_test.json_test (a, a1) VALUES ('[3, 1, 2]', 'c');

select * from json_test order by a, a1;

got results ( didn't order by a first)

a, a1
"[2, 3, 1]",a
"[1, 2, 3]",a
"[3, 3, 1]",a
"[2, 1, 3]",b
"[1, 3, 4]",b
"[1, 3, 2]",c
"[3, 1, 2]",c

Suggested fix:
order by a first then by a1.

[24 Mar 2023 8:46] MySQL Verification Team

Hello manyan ouyang,

Thank you for the report and test case.

regards,
Umesh