Apply subquery filters of the same relation correctly

[yhabteab]

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_update is not 1
• 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.

refs Icinga/icingadb-web#865

[gbin2265]

Hello,

is there any progress in this case because our icinga is based on costumvars on which we make several queries.

We are currently solving this for the variables where we use query's with filtering != to set the default value '0' for all its hosting/services.
This way we can solve the problem that the value NULL never occurs.

The side effect is that number of records in the table of costumvars has now been enormously enlarged.

We currently do not see any immediate performance problems, I just think the reload takes longer when we modify something.

[nilmerg]

The issue with custom variables is caused by another bug, not related to the ORM.