含GROUP_CONCAT的MySQL视图在不同WHERE条件下查询结果不一致问题排查与解决咨询

我最近创建了一个名为cases_keywords2的视图，但在使用不同的WHERE子句查询时，返回的结果出现了莫名其妙的不一致情况，具体如下：

第一次查询 #

执行语句：

select * from cases_keywords2 ck where case_id = 10732 or case_id = 10733 or case_id = 10740

返回结果：

第二次查询 #

执行语句：

select * from cases_keywords2 ck where case_id = 10733 or case_id = 10740

返回结果：

可以看到，case_id=10740的返回值完全变了，这明显不符合预期。

视图定义 #

这个cases_keywords2视图的创建SQL如下：

select `c`.`id` AS `case_id`, coalesce(group_concat(`t`.`id` separator ','), '') AS `keyword_ids`, coalesce(group_concat(`t`.`keyword` separator ','), '') AS `keywords` 
from (`synergy`.`case` `c` left join ( 
    select `ck`.`case_id` AS `case_id`, `k`.`id` AS `id`, `k`.`keyword` AS `keyword` 
    from (`synergy`.`case_has_keyword` `ck` join `synergy`.`keyword` `k` on ((`ck`.`keyword_id` = `k`.`id`)))) `t` on ((`c`.`id` = `t`.`case_id`))) 
group by `c`.`id`;

我的测试尝试 #

我试着把视图替换成它的源SQL并移除GROUP BY子句，执行后得到了预期的正确结果：

select `c`.`id` AS `case_id`,t.id,t.keyword 
from (`synergy`.`case` `c` left join ( 
    select `ck`.`case_id` AS `case_id`, `k`.`id` AS `id`, `k`.`keyword` AS `keyword` 
    from (`synergy`.`case_has_keyword` `ck` join `synergy`.`keyword` `k` on ((`ck`.`keyword_id` = `k`.`id`)))) `t` on ((`c`.`id` = `t`.`case_id`))) 
where c.id = 10732 or c.id = 10733 or c.id = 10740;

返回结果：

但只要重新添加GROUP BY子句，错误的结果就又出现了：

select `c`.`id` AS `case_id`, coalesce(group_concat(`t`.`id` separator ','), '') AS `keyword_ids`, coalesce(group_concat(`t`.`keyword` separator ','), '') AS `keywords` 
from (`synergy`.`case` `c` left join ( 
    select `ck`.`case_id` AS `case_id`, `k`.`id` AS `id`, `k`.`keyword` AS `keyword` 
    from (`synergy`.`case_has_keyword` `ck` join `synergy`.`keyword` `k` on ((`ck`.`keyword_id` = `k`.`id`)))) `t` on ((`c`.`id` = `t`.`case_id`))) 
where c.id = 10732 or c.id = 10733 or c.id = 10740 
group by `c`.`id`;

返回结果：

求助问题 #

请问有没有办法可以避免视图与WHERE子句之间的这种交互影响？或者能否修改GROUP_CONCAT的调用方式，从而得到预期的正确结果？

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