MS Access 2016添加COUNT子查询后IIF的IsNull逻辑结果异常问题

问题根因 #

这是MS Access 2016查询优化器的典型执行逻辑偏差问题，核心原因是你新增的关联子查询触发了优化器的执行计划改动：

• 你添加的是逐行匹配的关联子查询，需要对client表的每一行kcas_key统计caseDecision表的匹配记录数。Access优化器为了提升子查询执行效率，会隐式提前扫描所有caseDecision表中存在对应kcas_key的记录，覆盖了原本LEFT JOIN无匹配时右表字段全为NULL的逻辑：
  • 当某个client存在任意一条caseDecision记录时，哪怕该记录不符合WHERE子句中decisionNum=1的过滤条件，优化器也会错误地给原本应该全为NULL的caseDecision表字段填充默认值（布尔类型的completeFlag会被填充为默认的False而非NULL），直接导致IsNull(caseDecision.completeFlag)判断失效。
  • 只有完全没有任何caseDecision匹配记录的client，才会保留completeFlag为NULL的状态，这类数据占比通常很低，所以会出现IsNull判断永远不为真的现象。

解决方案 #

可以通过两种方式规避该问题：

1. 替换NULL判断条件：将IsNull(caseDecision.completeFlag)替换为关联键判断IsNull(caseDecision.kcas_key)，关联键是LEFT JOIN是否匹配的核心标识，不会被优化器误填充默认值：

IIf(IsNull(caseDecision.kcas_key), "", IIf(caseDecision.completeFlag=True,"YES","STARTED")) AS completeFlag

2. 把子查询改成预聚合JOIN，避免关联子查询触发优化器逻辑偏差，修改后的完整SQL如下：

SELECT 
  client.*
  , IIf(IsNull(caseDecision.completeFlag),
    "",
    IIf(caseDecision.completeFlag=True,"YES","STARTED")) AS completeFlag
  , caseDecision.decisionNum
  , Nz(cntAgg.cnt, 0) AS cnt
FROM (client LEFT JOIN caseDecision ON client.kcas_key = caseDecision.kcas_key)
LEFT JOIN (
  SELECT kcas_key, COUNT(kcas_key) AS cnt FROM caseDecision GROUP BY kcas_key
) AS cntAgg ON client.kcas_key = cntAgg.kcas_key
WHERE caseDecision.decisionNum = 1
  OR caseDecision.decisionNum IS NULL
ORDER BY client.kcas_key DESC;

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