Oracle Cloud DB 19c bug with JSON_ARRAYAGG

Question

我尝试使用JSON_ARRAYAGG进行选择，并使用相同的数据获得错误结果

SELECT json_object(
'buy' VALUE JSON_ARRAYAGG(b.buysum),
'total' VALUE JSON_ARRAYAGG(b.totalsum)
)
FROM   (
select *
from   view_count_sum
ORDER BY date_rw DESC
FETCH FIRST 10 ROWS ONLY
) b
ORDER BY b.date_rw;

结果我得到JSON与2个数组，其中有减少的数据顺序在第一个数组和错误的顺序在第二个数组

{"buy":[4168,4145,4130,4101,4068,4042,4008,3940,3900,3858],"total":[7778,7258,7333,7442,7546,7607,7642,7683,7718,7745]}

如果我在select中替换位置JSON_ARRAYAGG，我看到第一个数组的顺序是正确的，第二个数组的顺序是错误的

SELECT json_object(
'total' VALUE JSON_ARRAYAGG(b.totalsum),
'buy' VALUE JSON_ARRAYAGG(b.buysum)
)
FROM   (
select *
from   view_count_sum
ORDER BY date_rw DESC
FETCH FIRST 10 ROWS ONLY
) b
ORDER BY b.date_rw;

看到结果:

{"total":[7778,7745,7718,7683,7642,7607,7546,7442,7333,7258],"buy":[4168,3858,3900,3940,4008,4042,4068,4101,4130,4145]}

第二顺序和任何其他数组是错误的。第一个元素是右的，其他元素都是反的

Answer 1

从一些容易看到预期顺序的数据开始:

CREATE TABLE view_count_sum (date_rw, buysum, totalsum) AS
SELECT 10,  1,  1 FROM DUAL UNION ALL
SELECT  9,  2,  2 FROM DUAL UNION ALL
SELECT  8,  3,  3 FROM DUAL UNION ALL
SELECT  7,  4,  4 FROM DUAL UNION ALL
SELECT  6,  5,  5 FROM DUAL UNION ALL
SELECT  5,  6,  6 FROM DUAL UNION ALL
SELECT  4,  7,  7 FROM DUAL UNION ALL
SELECT  3,  8,  8 FROM DUAL UNION ALL
SELECT  2,  9,  9 FROM DUAL UNION ALL
SELECT  1, 10, 10 FROM DUAL;

那么，如果你这样做了:

SELECT json_object(
'buy'   VALUE JSON_ARRAYAGG(b.buysum),
'total' VALUE JSON_ARRAYAGG(b.totalsum)
) AS json
FROM   (
select *
from   view_count_sum
ORDER BY date_rw DESC
FETCH FIRST 10 ROWS ONLY
) b
ORDER BY b.date_rw;

则输出为:

<表类>JSONtbody><<tr>{"buy"[1, 2, 3, 4, 5, 6, 7, 8, 9, 10),"total":[1、10、9、8、7、6、5、4、3、2]}