MariaDB 10.8中嵌套JSON查询的多字段排序异常问题

生成嵌套JSON时需要满足两个排序规则：

• 外层数据按datetime字段降序排列
• 每个条目内的readings数组元素按u.id升序排列，以此固定[温度、湿度、气压]的展示顺序

仅按datetime排序的查询可正常运行，但添加u.id ASC排序条件后，部分readings的顺序不符合预期，使用的数据库版本为MariaDB 10.8。

有效查询（仅按datetime排序） #

select json_arrayagg(json_object("station_id", t.station_id, 
       "datetime", t.datetime, "readings", t.js)) 
from (select r.station_id, r.datetime, json_arrayagg(json_object("dimension", ud.dimension, 
      "value", r.value, 
      "representation", u.representation, 
      "unit", u.unit, "unit_id", u.id)) js 
      from readings r join units u on r.unit_id = u.id 
      join units_dimension ud on ud.id = u.dimension_id
group by r.station_id, r.datetime order by r.datetime desc) t

添加u.id排序后的查询（排序异常） #

select json_arrayagg(json_object("station_id", t.station_id, 
   "datetime", t.datetime, "readings", t.js)) 
from (select r.station_id, r.datetime, json_arrayagg(json_object("dimension", ud.dimension, 
      "value", r.value, 
      "representation", u.representation, 
      "unit", u.unit, "unit_id", u.id)) js 
      from readings r join units u on r.unit_id = u.id 
      join units_dimension ud on ud.id = u.dimension_id
group by r.station_id, r.datetime order by r.datetime desc, u.id ASC) t

返回的结果集 #

[
  {
    "station_id": "ESP0001",
    "datetime": "2022-11-25 07:43:06",
    "readings": [
      {
        "dimension": "temperature",
        "value": 15.33,
        "representation": "°C",
        "unit": "Celsius",
        "unit_id": 1
      },
      {
        "dimension": "humidity",
        "value": 92,
        "representation": "%",
        "unit": "Percentage",
        "unit_id": 4
      },
      {
        "dimension": "pressure",
        "value": 1016,
        "representation": "hPa",
        "unit": "HectoPascal",
        "unit_id": 5
      }
    ]
  },
  {
    "station_id": "ESP0001",
    "datetime": "2022-11-25 07:33:06",
    "readings": [
      {
        "dimension": "temperature",
        "value": 15.3,
        "representation": "°C",
        "unit": "Celsius",
        "unit_id": 1
      },
      {
        "dimension": "humidity",
        "value": 92,
        "representation": "%",
        "unit": "Percentage",
        "unit_id": 4
      },
      {
        "dimension": "pressure",
        "value": 1016,
        "representation": "hPa",
        "unit": "HectoPascal",
        "unit_id": 5
      }
    ]
  },
  {
    "station_id": "ESP0001",
    "datetime": "2022-11-25 07:23:06",
    "readings": [
      {
        "dimension": "humidity",
        "value": 91,
        "representation": "%",
        "unit": "Percentage",
        "unit_id": 4
      },
      {
        "dimension": "pressure",
        "value": 1016,
        "representation": "hPa",
        "unit": "HectoPascal",
        "unit_id": 5
      },
      {
        "dimension": "temperature",
        "value": 15.43,
        "representation": "°C",
        "unit": "Celsius",
        "unit_id": 1
      }
    ]
  },
  {
    "station_id": "ESP0001",
    "datetime": "2022-11-25 07:13:06",
    "readings": [
      {
        "dimension": "pressure",
        "value": 1016,
        "representation": "hPa",
        "unit": "HectoPascal",
        "unit_id": 5
      },
      {
        "dimension": "temperature",
        "value": 15.46,
        "representation": "°C",
        "unit": "Celsius",
        "unit_id": 1
      },
      {
        "dimension": "humidity",
        "value": 91,
        "representation": "%",
        "unit": "Percentage",
        "unit_id": 4
      }
    ]
  },
  {
    "station_id": "ESP0001",
    "datetime": "2022-11-25 07:03:05",
    "readings": [
      {
        "dimension": "temperature",
        "value": 14.78,
        "representation": "°C",
        "unit": "Celsius",
        "unit_id": 1
      },
      {
        "dimension": "humidity",
        "value": 98,
        "representation": "%",
        "unit": "Percentage",
        "unit_id": 4
      },
      {
        "dimension": "pressure",
        "value": 1017,
        "representation": "hPa",
        "unit": "HectoPascal",
        "unit_id": 5
      }
    ]
  }
]

内容的提问来源于stack exchange，提问作者am4rtinez