Problems with view containing group_contact

Viewed 75

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

1 Answers

The solution is:

use Mysql 8.0.29+

or

change 'group by c.id' to 'group by c.id, t.case_id'

Thanks @wchiquito

Related