SQL GROUP BY中CASE搭配exists子查询判断结果异常

问题现象 #

编写SQL统计单条评论下每类表情反应（reaction）的总数量，同时判断指定用户是否提交过对应类型的反应时，出现逻辑错误：只要该用户对这条评论提交过任意一种反应，CASE语句返回的includesMe字段就会对所有反应类型均返回1。
例如用户仅对评论做出🥰反应时，查询会错误判定用户对所有反应类型都有提交，异常效果如下：

出问题的原SQL代码：

SELECT count(*) as "numReacts", 
       reaction, 
       case when exists(
            select *
            from "CommentReaction" as i
            where i."userId" = 'b8b660c9-c416-42b6-9142-19112a9ff811' 
              and i."commentId" = 'c142787b-4422-4128-8357-58d36c177307' 
              and i.reaction = reaction
            ) 
            then 1
            else 0
       end as "includesMe" 
FROM "CommentReaction" 
WHERE "commentId" = 'c142787b-4422-4128-8357-58d36c177307' 
GROUP BY reaction;

错误原因 #

子查询中i.reaction = reaction的判断存在字段名歧义：子查询内部的表i本身就存在reaction字段，数据库解析未加表别名的reaction时，会优先匹配当前子查询作用域内的字段，等价于写了i.reaction = i.reaction，该条件恒为真。只要子查询能查到该用户对当前评论的任意一条反应记录，EXISTS判断就会返回真，最终导致所有反应类型的includesMe字段都被错误置为1。

修复方法 #

给外层查询的表设置别名，子查询关联外层字段时明确指定别名前缀，消除字段歧义，让判断条件正确匹配当前分组对应的反应类型。修复后的SQL：

SELECT count(*) as "numReacts", 
       o.reaction, 
       case when exists(
            select *
            from "CommentReaction" as i
            where i."userId" = 'b8b660c9-c416-42b6-9142-19112a9ff811' 
              and i."commentId" = 'c142787b-4422-4128-8357-58d36c177307' 
              and i.reaction = o.reaction
            ) 
            then 1
            else 0
       end as "includesMe" 
FROM "CommentReaction" as o
WHERE o."commentId" = 'c142787b-4422-4128-8357-58d36c177307' 
GROUP BY o.reaction;

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