Bug 975384 - Error displaying calltime data due to SQL error
Error displaying calltime data due to SQL error
Status: CLOSED CURRENTRELEASE
Product: RHQ Project
Classification: Other
Component: CallTime (Show other bugs)
4.8
Unspecified Unspecified
unspecified Severity urgent (vote)
: ---
: RHQ 4.8
Assigned To: Heiko W. Rupp
Mike Foley
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2013-06-18 06:29 EDT by Heiko W. Rupp
Modified: 2013-09-11 05:53 EDT (History)
1 user (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2013-09-11 05:53:59 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Heiko W. Rupp 2013-06-18 06:29:57 EDT
Go to e.g. rhq-server->deployments->rhq.ear->rhq-rest.war->war in the tree and then monitoring tab, calltime subtab.
The red bar shows.

Console log shows

12:27:04,386 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/coregui]] (http-/0.0.0.0:7080-8) Exception while dispatching incoming RPC call: com.google.gwt.user.server.rpc.UnexpectedException: Service method 'public abstract org.rhq.core.domain.util.PageList org.rhq.enterprise.gui.coregui.client.gwt.MeasurementDataGWTService.findCallTimeDataForContext(org.rhq.core.domain.common.EntityContext,org.rhq.core.domain.criteria.CallTimeDataCriteria)' threw an unexpected exception: java.lang.RuntimeException: [1371551224332] javax.ejb.EJBException:javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet -> javax.persistence.PersistenceException:org.hibernate.exception.SQLGrammarException: could not extract ResultSet -> org.hibernate.exception.SQLGrammarException:could not extract ResultSet -> org.postgresql.util.PSQLException:ERROR: column "calltimeda0_.id" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 1092[SQLException=ERROR: column "calltimeda0_.id" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 1092]
Comment 1 Heiko W. Rupp 2013-06-18 07:40:01 EDT
Possible fix for the symptom:

--- modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/measurement/CallTimeDataManagerBean.java	(revision 329ac582bcf65e7c0f66ea10fd2a4faf612be2b8)
+++ modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/measurement/CallTimeDataManagerBean.java	(revision )
@@ -216,6 +216,7 @@
             criteria.addFilterAutoGroupParentResourceId(context.parentResourceId);
             criteria.addFilterAutoGroupResourceTypeId(context.resourceTypeId);
         }
+        criteria.setSupportsAddSortId(false);
 
         CriteriaQueryGenerator generator = new CriteriaQueryGenerator(subject, criteria);
         String replacementSelectList = "" //
Comment 2 Heiko W. Rupp 2013-06-18 08:04:17 EDT
(above fix is from Thomas)


[13:37:44] <pilhuhn>	 tsegismont I am not convinced this is right, even if it solved the issue. We need to find the root cause why it broke

[13:38:55] <pilhuhn>	 perhaps there are more than 4 sorts already and the id got added in one part of the query and not the other
[13:44:24] <tsegismont>	 pilhuhn, it's just that this addFilterId property works well with base entity queries
[13:44:38] <tsegismont>	 not projections with group by
[13:45:47] <tsegismont>	 sorry i meant supportsAddSortId
[13:46:13] <pilhuhn>	 yes. jshaughn has added a default = fallback  sort-by-id if no other specific sorting is given. Question is now if the result is still correct (= no dups on paging) when the sort-by-id is turned off
[13:46:45] <tsegismont>	 for this particular query: yes

SELECT calltimeda1_.CALL_DESTINATION AS col_0_0_,
       min(calltimeda0_.MINIMUM) AS col_1_0_,
       max(calltimeda0_.MAXIMUM) AS col_2_0_,
       sum(calltimeda0_.TOTAL) AS col_3_0_,
       sum(calltimeda0_.COUNT) AS col_4_0_,
       sum(calltimeda0_.TOTAL)/sum(calltimeda0_.COUNT) AS col_5_0_
FROM RHQ_CALLTIME_DATA_VALUE calltimeda0_,
     RHQ_CALLTIME_DATA_KEY calltimeda1_ CROSS
JOIN RHQ_MEASUREMENT_SCHED measuremen3_ CROSS
JOIN RHQ_MEASUREMENT_DEF measuremen4_
WHERE calltimeda0_.KEY_ID=calltimeda1_.id
  AND calltimeda1_.SCHEDULE_ID=measuremen3_.id
  AND measuremen3_.DEFINITION=measuremen4_.ID
  AND measuremen4_.DATA_TYPE= 3
  AND (calltimeda0_.id IN
         (SELECT calltimeda5_.id
          FROM RHQ_CALLTIME_DATA_VALUE calltimeda5_ CROSS
          JOIN RHQ_CALLTIME_DATA_KEY calltimeda6_ CROSS
          JOIN RHQ_MEASUREMENT_SCHED measuremen7_
          WHERE calltimeda5_.KEY_ID=calltimeda6_.id
            AND calltimeda6_.SCHEDULE_ID=measuremen7_.id
            AND measuremen7_.RESOURCE_ID=11063))
  AND calltimeda0_.BEGIN_TIME>1371519368359
  AND calltimeda0_.END_TIME<1371548168359
GROUP BY calltimeda1_.CALL_DESTINATION
ORDER BY sum(calltimeda0_.TOTAL)/sum(calltimeda0_.COUNT) DESC, calltimeda1_.CALL_DESTINATION ASC/*,
                                                               calltimeda0_.id ASC*/ LIMIT 100
Comment 3 Heiko W. Rupp 2013-06-18 08:14:02 EDT
master f3843b8
Comment 4 Heiko W. Rupp 2013-09-11 05:53:59 EDT
Bulk closing of old issues now that HRQ 4.9 is in front of the door.

If you think the issue has not been solved, then please open a new bug and mention this one in the description.

Note You need to log in before you can comment on or make changes to this bug.