Some combinations of [Host|Service] queries with custom var filters return wrong results

[RincewindsHat]

Describe the bug

Some queries using the ~ (tilde) operator return wrong (empty) results when search hosts or services.

To Reproduce

Try something like this:

1. /icingaweb2/icingadb/hosts?host.vars.distro=debian&host.vars.transport_mode~icinga_agent shows no results
2. /icingaweb2/icingadb/hosts?host.vars.distro~debian&host.vars.transport_mode~icinga_agent shows the correct results. (Note: = operator in the first condition vs. ~ in the second query)

Your Environment

Include as many relevant details about the environment you experienced the problem in.

• Icinga DB Web version (System - About): 1.1.2
• Icinga Web 2 version (System - About): 2.12.1
• Web browser: Firefox
• PHP version used (php --version): PHP 8.2.18
• Server operating system and version: Debian bookworm (12)

Additional context

When exporting the quries with &format=sql I get this diff between the two queries above (diff query-problem.sql.fmt query-tilde-tilde.sql.fmt):

101,102c101,104
<                        AND ((sub_customvar_flat.flatname = 'transport_mode')
<                             AND (sub_customvar_flat.flatvalue = 'icinga_agent'))))
---
>                        OR ((sub_customvar_flat.flatname = 'transport_mode')
>                            AND (sub_customvar_flat.flatvalue = 'icinga_agent'))
>                      GROUP BY sub_customvar_flat_host.id
>                      HAVING COUNT(DISTINCT sub_customvar_flat.id) >= 2))
167,168c169,172
<                        AND ((sub_customvar_flat.flatname = 'transport_mode')
<                             AND (sub_customvar_flat.flatvalue = 'icinga_agent'))))
---
>                        OR ((sub_customvar_flat.flatname = 'transport_mode')
>                            AND (sub_customvar_flat.flatvalue = 'icinga_agent'))
>                      GROUP BY sub_customvar_flat_host.id
>                      HAVING COUNT(DISTINCT sub_customvar_flat.id) >= 2))

By playing around with some combinations, I noticed, that the GROUP BY and HAVING part seems to be missing from the problematic query.

[nilmerg]

This is a an old limitation now popping up because of the distinction between = and ~. It can be bypassed by using either = or ~ exclusively.

There is no simple fix, I'm afraid. Once we change this, filters such as host.check_interval>=60&host.check_interval<=300 must still return the same results as they do now.