Apply subquery filters of the same relation correctly #119
Description
Trying to filter for hosts based on multiple service state columns results in two separate subquery filters not being generated correctly. Similarly, filtering for hosts/services that don't have a customvar foo set yields unexpected results.
Example:
I have a single host that defines the customvar example and many other hosts which doesn't define this variable.
object Host "Test-server" {
address = "localhost"
check_command = "dummy"
vars.example = true
}Now if you filter for hosts that do not set the example variable to anything, it will yield null results: /icingaweb2/icingadb/hosts?host.vars.example!~%2A
The resulted subquery filter looks as follows:
...
WHERE ((host.id NOT IN ((SELECT sub_customvar_flat_host.id AS sub_customvar_flat_host_id
FROM customvar_flat sub_customvar_flat
INNER JOIN host_customvar sub_customvar_flat_host_customvar
ON sub_customvar_flat_host_customvar.customvar_id =
sub_customvar_flat.customvar_id
INNER JOIN host sub_customvar_flat_host
ON sub_customvar_flat_host.id = sub_customvar_flat_host_customvar.host_id
WHERE (sub_customvar_flat.flatvalue IS NOT NULL)
AND (sub_customvar_flat.flatname = 'example')
GROUP BY sub_customvar_flat_host.id
HAVING COUNT(DISTINCT sub_customvar_flat.id) >= 1)) OR host.id IS NULL))
AND (host.id IN ((SELECT sub_customvar_flat_host.id AS sub_customvar_flat_host_id
FROM customvar_flat sub_customvar_flat
INNER JOIN host_customvar sub_customvar_flat_host_customvar
ON sub_customvar_flat_host_customvar.customvar_id =
sub_customvar_flat.customvar_id
INNER JOIN host sub_customvar_flat_host
ON sub_customvar_flat_host.id = sub_customvar_flat_host_customvar.host_id
WHERE sub_customvar_flat.flatname = 'example')))
...When you filter for hosts as follows, it doesn't yield the expected results as well.
- All services their
last_updateis not1 - And all services that have a performance data (the performance data is not null)
icingaweb2/icingadb/hosts?service.state.last_update!=1&service.state.performance_data!~%2A
...
WHERE ((host.id NOT IN ((SELECT sub_service_state_service_host.id AS sub_service_state_service_host_id
FROM service_state sub_service_state
INNER JOIN service sub_service_state_service
ON sub_service_state_service.id = sub_service_state.service_id
LEFT JOIN host sub_service_state_service_host
ON sub_service_state_service_host.id = sub_service_state_service.host_id
WHERE sub_service_state.last_update = 1000000
GROUP BY sub_service_state_service_host.id
HAVING COUNT(DISTINCT sub_service_state.service_id) >= 1)) OR host.id IS NULL))
AND ((host.id NOT IN ((SELECT sub_service_state_service_host.id AS sub_service_state_service_host_id
FROM service_state sub_service_state
INNER JOIN service sub_service_state_service
ON sub_service_state_service.id = sub_service_state.service_id
LEFT JOIN host sub_service_state_service_host
ON sub_service_state_service_host.id = sub_service_state_service.host_id
WHERE sub_service_state.performance_data IS NOT NULL
GROUP BY sub_service_state_service_host.id
HAVING COUNT(DISTINCT sub_service_state.service_id) >= 1)) OR host.id IS NULL))
...The above subqueries look good at first glance, but the second one is causing the trouble as it omits an important additional where clause. It currently only matches hosts that doesn't have services with no performance data, but this should also additionally filter for sub_service_state.last_update = 1000000, which would give the expected result.
As discussed this offline with @nilmerg, the problem occurs for all Models with HasMany relations of the filter subjects.
Changing these lines of code produces at least the same query as the service state filter but doesn't fully resolve the issue.
https://github.com/Icinga/icingadb-web/blob/master/library/Icingadb/Model/Behavior/FlattenedObjectVars.php#L35-L37
$class = get_class($condition);
$nameFilter = new $class($relation . 'flatname', $column);
$valueFilter = new $class($relation . 'flatvalue', $condition->getValue());Possible Solutions
I don't really have anything in mind to solve such a major problem that affects many use cases, but we should consider re-evaluating the entire subquery processing path. But with the above examples, even a single subquery filter would achieve the desired result. IMHO it is completely unnecessary, to build two three independent subqueries that affect the same relation.