I have a view, but when I use it in select the results differs depending on the where clause:
select * from cases_keywords2 ck where case_id = 10732 or case_id = 10733 or case_id = 10740`
Returns:
| case_id | keywords_ids | keywords |
|---|---|---|
| 10732 | 1,52,118 | sanity,automated,alm |
| 10733 | ||
| 10740 | 1 | sanity |
but
select * from cases_keywords2 ck where case_id = 10733 or case_id = 10740
returns:
| case_id | keywords_ids | keywords |
|---|---|---|
| 10733 | ||
| 10740 | 118 | alm |
Note the changed value for case_id=10740. The cases_keywords2 view is defined by this select:
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`;
I've tried to replace the view by it's source and remove the group by and I've got expected result:
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;
| case_id | id | keyword |
|---|---|---|
| 10732 | 1 | sanity |
| 10732 | 52 | automated |
| 10732 | 118 | alm |
| 10733 | ||
| 10740 | 118 | alm |
But after adding back group by the wrong value is still there:
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`;
| case_id | keywords_ids | keywords |
|---|---|---|
| 10732 | 1,52,118 | sanity,automated,alm |
| 10733 | ||
| 10740 | 1 | sanity |
Is there a way how to prevent interaction between the view and the where clause? Or can group_concat call be modified to get the expected result?
Thanks!
Edited: reproducible with dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=20822073b30d962065bcd7b8294824fe