MariaDB中派生表内外重复使用常量时结果集缺失行问题

我当前运行的查询语句如下：

select t.term_id, t.name, t.slug, a.c, a.term_order, a.menu_order, ttparent.taxonomy from 
(
    SELECT p.term_id, count(distinct p.ID) c, p.term_order, p.menu_order
    FROM pz_fww_object_ancestors p
    WHERE p.taxonomy = 'product_cat' and p.term_id IN (1445,9561) group by term_id 
) a inner join pz_terms t
ON a.term_id = t.term_id inner join pz_term_taxonomy ttparent on ttparent.term_id = t.term_id 
and t.term_id IN (1445, 9561)
;

该查询预期应返回2行结果，但实际仅返回term_id为1445的1行数据。

但term_id为9561的行明确符合返回条件：当我将查询最后一行的条件修改为term_id in (9561)时，可正常返回该行数据。

我目前的一个可行变通方案是删除外层针对term_id的额外常量校验，但我了解到MySQL优化器无法从派生表中推断或传递常量，如果仅在派生表中配置过滤条件，部分场景下可能导致查询执行速度变慢。
删除外层查询的常量校验条件后，可正常返回2行结果，结果集如下：

我查看了两个版本查询的EXPLAIN执行计划，增加外层常量校验时查询的执行顺序发生了变化，我推测该问题是此处的优化器bug导致的。

以下为删除外层常量校验的正常查询的执行计划：

补充说明：当前使用的数据库版本为10.3.31-MariaDB-0ubuntu0.20.04.1。

问题解答 #

根因说明 #

你遇到的是MariaDB 10.3分支的已知优化器缺陷，在处理多表连接+派生表+常量IN条件的场景下，优化器会错误剪除掉部分符合条件的执行路径，导致IN列表中只有第一个值被正确匹配，后续值的匹配逻辑被跳过。
你的观察完全符合问题特征：当你在外层连接条件中额外添加term_id IN (1445, 9561)时，优化器错误调整了表连接顺序，提前把派生表的过滤逻辑和外层常量条件做了错误的关联映射，导致9561的匹配结果被丢弃。

可靠解决方案 #

除了你已经尝试的删除外层常量校验的方案外，还有两个不影响查询性能的修复方案可选：

1. 添加优化器提示禁用条件下推
   在查询开头添加/*+ NO_CONDITION_PUSHDOWN */提示，强制优化器不把外层常量条件下推到派生表逻辑中，既保留了两层过滤条件提升性能的效果，也不会出现结果丢失的问题，修改后的查询头部如下：

select /*+ NO_CONDITION_PUSHDOWN */ t.term_id, t.name, t.slug, a.c, a.term_order, a.menu_order, ttparent.taxonomy from 
-- 后续原有逻辑保持不变

2. 将外层IN条件移到WHERE子句而非JOIN ON子句
   原来的IN条件写在pz_term_taxonomy的JOIN ON子句中，调整到全局WHERE子句即可避免优化器的错误映射：

select t.term_id, t.name, t.slug, a.c, a.term_order, a.menu_order, ttparent.taxonomy from 
(
    SELECT p.term_id, count(distinct p.ID) c, p.term_order, p.menu_order
    FROM pz_fww_object_ancestors p
    WHERE p.taxonomy = 'product_cat' and p.term_id IN (1445,9561) group by term_id 
) a inner join pz_terms t
ON a.term_id = t.term_id 
inner join pz_term_taxonomy ttparent on ttparent.term_id = t.term_id 
WHERE t.term_id IN (1445, 9561);

长期修复建议 #

该缺陷在MariaDB 10.4.12及后续版本已经被修复，如果业务允许升级数据库版本，升级到更高稳定版本可以彻底解决这类优化器错误问题。

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