Athena查询使用NOT搭配CONTAINS/LIKE负过滤不生效问题

Athena 完全支持在WHERE子句中使用NOT搭配LIKE、CONTAINS做负向过滤，不存在特殊的专属编写规范，你的查询返回结果不符合预期是写法存在逻辑漏洞，和引擎能力无关。

• 三值逻辑坑：SQL判断存在TRUE/FALSE/NULL三种结果，当subjects数组包含NULL元素，或者聚合前subject字段存在未处理的NULL值时，NOT contains(subjects, 'math')的返回值可能是NULL而非FALSE。WHERE子句只会保留返回TRUE的行，返回NULL的行会被直接过滤，哪怕该行的数组确实包含'english'且没有'math'。
• 旧版引擎兼容问题：Athena V1引擎（基于老版本Presto，未主动升级的存量工作区可能还在使用）中，数组类型的contains函数存在边界bug，如果数组元素存在大小写差异、首尾隐藏空格（比如' math'、'Math'），会出现匹配结果和预期不符的情况，直接导致负向判断失效。
• 保留字冲突：示例代码中直接使用table作为表名，这是SQL标准保留关键字，如果实际查询时没有加反引号转义，会导致解析逻辑异常。
• 聚合后过滤的隐式问题：先全量聚合生成数组再做过滤，不仅性能差，一旦分组内存在脏数据，很容易出现数组内容和预期不符的情况，连带导致过滤结果出错。

优先把过滤逻辑下推到聚合阶段，用HAVING配合条件聚合实现判断，完全绕开数组生成环节的异常，兼容性和性能都更好，还能顺便处理脏数据问题：

SELECT name, array_agg(DISTINCT subject) AS subjects 
FROM 你的实际表名 -- 不要用table当表名，避免保留字冲突
GROUP BY name
HAVING 
  -- 匹配存在english的分组，顺便处理大小写、空格问题
  count_if(trim(lower(subject)) = 'english') > 0
  -- 过滤掉存在math的分组
  AND count_if(trim(lower(subject)) = 'math') = 0

如果你确实需要在聚合后对数组做负向过滤，记得显式处理NULL值，把判断结果转为明确的布尔值，避免三值逻辑的影响：

SELECT * FROM (
    SELECT name, array_agg(DISTINCT subject) AS subjects 
    FROM 你的实际表名
    GROUP BY name
) alias
WHERE 
  contains(subjects, 'english') 
  -- 用coalesce把NULL结果统一转为FALSE，避免被意外过滤
  AND coalesce(NOT contains(subjects, 'math'), FALSE)

• 所有负向判断（NOT LIKE/NOT CONTAINS/!=）都要考虑NULL值的影响，只要参与判断的字段可能为NULL，就要用coalesce显式把NULL结果转为明确的布尔值。
• 做字符串匹配时，建议统一大小写、清除首尾隐藏空格，避免脏数据导致匹配偏差。
• 针对数组、JSON这类复杂类型的过滤，优先在聚合、解析阶段完成条件筛选，不要等复杂类型生成后再做过滤，稳定性和查询性能都更优。

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