MariaDB 10.5 JSON_ARRAYAGG返回类型随关联表索引波动解决方案咨询

该问题是MariaDB 10.5.12及同系列周边版本的已知优化器缺陷：当查询走不同执行计划时（关联命中索引/未命中索引/无索引），JSON_OBJECT返回的JSON类型元数据会意外丢失，被隐式转换为字符串后传入JSON_ARRAYAGG，最终导致输出出现双重转义的字符串数组，而非预期的原生JSON对象数组。

• 方案1：用JSON_EXTRACT包裹JSON_OBJECT，强制保留JSON类型
  该方案无额外性能损耗，兼容性最好，修改后的查询示例如下：

  SELECT 
      JSON_ARRAYAGG(DISTINCT JSON_EXTRACT(
          JSON_OBJECT(
              'comment_id', comment.id, 
              'text', comment.text
          ), '$'
      ) ORDER BY comment.id) AS comments
  FROM post
  LEFT JOIN comment ON comment.post_id = post.id
  LEFT JOIN vote ON vote.user_id = 1 AND vote.post_id = post.id
  GROUP BY post.id
  

  原理：JSON_EXTRACT的返回值固定为JSON类型，不会被优化器隐式转换为字符串，从根源避免类型丢失问题。此前使用CAST不生效是因为优化器会在执行计划生成阶段丢弃CAST的JSON类型标记，该问题不会出现在JSON_EXTRACT上。
• 方案2：强制关联vote表时忽略user_id索引
  适合vote表数据量不大、查询性能可接受的场景，关联语法修改为：

  LEFT JOIN vote IGNORE INDEX (`user_id`) ON vote.user_id = 1 AND vote.post_id = post.id
  

  原理：统一执行计划逻辑，避免因为索引命中与否导致类型传递差异，输出格式会稳定为原生JSON数组。
• 方案3：升级MariaDB稳定版本
  该缺陷已在10.5.18、10.6.11及之后的官方稳定版修复，升级后无需修改业务SQL即可彻底解决该问题，是长期最优方案。

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