-
Type:
Incident report
-
Resolution: Won't fix
-
Priority:
Trivial
-
None
-
Affects Version/s: None
-
Component/s: API (A)
-
None
The Call:
{'params': {}, 'jsonrpc': '2.0', 'method': 'template.get', 'auth': 'db318f658837af2d76ccd06802961e31', 'id': 45}'
Results in the following SQL query:
mysql> SELECT h.* FROM hosts h WHERE h.status=3 AND EXISTS (SELECT NULL FROM hosts_groups hgg JOIN rights r ON r.id=hgg.groupid AND r.groupid='18' WHERE h.hostid=hgg.hostid GROUP BY hgg.hostid HAVING MIN(r.permission)>0 AND MAX(r.permission)>='2');
Which is incorrect (the subquery) doesn't have the "h" alias.
The correct query should be:
mysql> SELECT h.* FROM hosts h WHERE h.status=3 AND EXISTS (SELECT NULL FROM hosts_groups hgg JOIN rights r ON r.id=hgg.groupid AND r.groupid='18' WHERE hostid=hgg.hostid GROUP BY hgg.hostid HAVING MIN(r.permission)>0 AND MAX(r.permission)>='2');
The corresponding code is in line 121 in frontends/php/include/classes/api/services/CTemplate.php:
WHERE h.hostid=hgg.hostid
... was changed to ...
WHERE hostid=hgg.hostid
... and tested sucessfully.
Patch attached