MariaDB多字段GROUP BY关联LEFT JOIN SUM返回NULL异常问题

问题结论 #

该问题不是索引设置错误，属于MariaDB已知内核bug MDEV-26337。触发条件是GROUP BY的多个列都存在独立单列索引时，优化器会选择松散索引扫描策略加速分组运算，该策略在处理分组键前缀连续重复的场景时存在逻辑缺陷，会错误跳过部分分组结果，因此你会遇到相邻行sales_order_id相同时匹配不到结果返回NULL的情况。你删除sales_order_id索引、用concat拼接分组键的操作本质都是破坏了松散索引扫描的触发条件，所以结果恢复正常。

可落地解决方案 #

• 方案1：临时关闭当前会话的松散索引扫描优化，对性能影响极小，适合不想改表结构、不想升级的场景，执行查询前先运行：

SET SESSION optimizer_switch='loose_index_scan=off';

再运行你的原始查询即可得到正确结果。

• 方案2：创建联合索引优化查询性能，同时绕过bug，适合长期使用的业务查询：
  给tr_sales_order_detail表创建分组字段的联合索引，优化器会优先选择联合索引执行分组，不会触发松散索引扫描bug，同时分组不需要回表，查询性能比原有单列索引更高：

CREATE INDEX idx_so_detail_group ON tr_sales_order_detail(item_id, row_id, sales_order_id);

• 方案3：升级MariaDB到修复版本，适合可以做版本升级的场景
  该bug已经在以下版本完成修复：10.3.36、10.4.26、10.5.17、10.6.9、10.7.5及更高迭代版本，升级后无需修改任何查询语句或索引即可正常运行。
• 方案4：修改查询语句绕过优化器选择，适合临时调整不想改配置的场景
  在子查询的GROUP BY语句后添加ORDER BY NULL即可破坏松散索引扫描的触发条件，修改后的完整查询如下：

SELECT
tr_sales_delivery_detail.sales_order_id,
tr_sales_delivery_detail.row_id,
tr_sales_delivery_detail.qty_delivery,
so.qty AS so_qty
FROM tr_sales_delivery_detail
LEFT JOIN (
    SELECT tr_sales_order_detail.item_id, 
             tr_sales_order_detail.row_id,  
             tr_sales_order_detail.sales_order_id,
             SUM(tr_sales_order_detail.qty) AS qty FROM tr_sales_order_detail
    GROUP BY tr_sales_order_detail.item_id, tr_sales_order_detail.row_id, tr_sales_order_detail.sales_order_id
    ORDER BY NULL
) so
ON so.item_id = tr_sales_delivery_detail.item_id
AND so.row_id = tr_sales_delivery_detail.row_id
AND so.sales_order_id = tr_sales_delivery_detail.sales_order_id
WHERE tr_sales_delivery_detail.sales_delivery_id = '10923';

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