Problem:
When I try to find hosts with filter: location = Foo and organization = Bar
Search completed with empty result
Expected outcome:
Hosts with location = Foo and organization = Bar appeared in host list as search result
Foreman and Proxy versions:
3.11
Foreman and Proxy plugin versions:
Distribution and version:
Other relevant data:
I set a debug on and found that when I used search with these combination of filters, the following sql request was generated:
Bad SQL
SELECT DISTINCT
"hosts"."name" AS alias_0,
"hosts"."id"
FROM
"hosts"
LEFT OUTER JOIN
"taxonomies"
ON "taxonomies"."id" = "hosts"."organization_id"
AND "taxonomies"."type" = 'Organization'
LEFT OUTER JOIN
"taxonomies" "locations_hosts"
ON "locations_hosts"."id" = "hosts"."location_id"
AND "locations_hosts"."type" = 'Location'
LEFT OUTER JOIN
"hostgroups"
ON "hostgroups"."id" = "hosts"."hostgroup_id"
LEFT OUTER JOIN
"compute_resources"
ON "compute_resources"."id" = "hosts"."compute_resource_id"
LEFT OUTER JOIN
"operatingsystems"
ON "operatingsystems"."id" = "hosts"."operatingsystem_id"
LEFT OUTER JOIN
"models"
ON "models"."id" = "hosts"."model_id"
LEFT OUTER JOIN
"host_status"
ON "host_status"."type" IS NOT NULL
AND "host_status"."host_id" = "hosts"."id"
LEFT OUTER JOIN
"tokens"
ON "tokens"."host_id" = "hosts"."id"
AND "tokens"."type" = 'Token::Build'
WHERE
"hosts"."type" = 'Host::Managed'
AND
(
(("taxonomies"."title" = 'Bar')
AND
(
"taxonomies"."title" = 'Foo'
)
)
)
ORDER BY
"hosts"."name" ASC LIMIT 20 OFFSET 0;
And get no hosts as result of query.
My guess is that problem is in WHERE clause.
In query there are some joins:
- LEFT OUTER JOIN “taxonomies” to join organizations from “taxonomies” table
- LEFT OUTER JOIN “taxonomies” “locations_hosts” for joining locations from “taxonomies”
By the way in WHERE clause there are next conditions:
(("taxonomies"."title" = 'Bar')
AND
(
"taxonomies"."title" = 'Foo'
)
)
Looks like that locations are selected from organization LEFT OUTER JOIN and not from location join.
I think that correct WHERE clause should contain next conditions:
(("taxonomies"."title" = 'Bar')
AND
(
"locations_hosts"."title" = 'Foo'
)
)
So the correct query is looks like this:
Correct SQL
SELECT DISTINCT
"hosts"."name" AS alias_0,
"hosts"."id"
FROM
"hosts"
LEFT OUTER JOIN
"taxonomies"
ON "taxonomies"."id" = "hosts"."organization_id"
AND "taxonomies"."type" = 'Organization'
LEFT OUTER JOIN
"taxonomies" "locations_hosts"
ON "locations_hosts"."id" = "hosts"."location_id"
AND "locations_hosts"."type" = 'Location'
LEFT OUTER JOIN
"hostgroups"
ON "hostgroups"."id" = "hosts"."hostgroup_id"
LEFT OUTER JOIN
"compute_resources"
ON "compute_resources"."id" = "hosts"."compute_resource_id"
LEFT OUTER JOIN
"operatingsystems"
ON "operatingsystems"."id" = "hosts"."operatingsystem_id"
LEFT OUTER JOIN
"models"
ON "models"."id" = "hosts"."model_id"
LEFT OUTER JOIN
"host_status"
ON "host_status"."type" IS NOT NULL
AND "host_status"."host_id" = "hosts"."id"
LEFT OUTER JOIN
"tokens"
ON "tokens"."host_id" = "hosts"."id"
AND "tokens"."type" = 'Token::Build'
WHERE
"hosts"."type" = 'Host::Managed'
AND
(
(("taxonomies"."title" = 'Bar')
AND
(
"locations_hosts"."title" = 'Foo'
)
)
)
ORDER BY
"hosts"."name" ASC LIMIT 20 OFFSET 0;
If I use that query, I get correct respone with hosts mathing my filters.
Please can someone confirm is this a bug or some misconfiguration in my installation?
What can I do to fix this?