OrderByAggregate on GlideAggregate leaves warnings when executes as background script!! - Known Error

OrderByAggregate on GlideAggregate leaves warnings when executes as background script!! Description OrderByAggregate() method is creating " JoinQuery invalid field name: count_of_480013622 " error message in a Background Script. Steps to Reproduce

Run the following code in a background script: var gaTask = new GlideAggregate('task'); gaTask.addAggregate('COUNT', 'business_service'); gaTask.orderByAggregate('count', 'business_service'); gaTask.addNotNullQuery('business_service'); gaTask.setLimit(10); gaTask.query(); while(gaTask.next()) { gs.info('Business Service: ' + gaTask.business_service.name + ' ' + gaTask.getAggregate('COUNT', 'business_service')); }

Output:

JoinQuery invalid field name: count_of_480013622 Time: 0:00:00.443 id: haufedev_1[glide.18] (connpid=793579) for: SELECT task0.`business_service` AS `business_service`, count(*) AS count_of_480013622 FROM (task task0 LEFT JOIN cmdb cmdb1 ON task0.`business_service` = cmdb1.`sys_id` ) WHERE task0.`business_service` IS NOT NULL GROUP BY task0.`business_service`,cmdb1.`name` ORDER BY count_of_480013622 DESC ,cmdb1.`name` /* gs:0AF832801B3F58103E88DCEACD4BCBE5, tx:1b0c8f4c1bbf58103e88dceacd4bcb63 */ Time: 0:00:00.160 id: haufedev_1[glide.24] (connpid=793585) for: SELECT count(distinct(cmdb1.`name`)) as count_of_1607073472 FROM (task task0 LEFT JOIN cmdb cmdb1 ON task0.`business_service` = cmdb1.`sys_id` ) WHERE task0.`business_service` IS NOT NULL /* gs:0AF832801B3F58103E88DCEACD4BCBE5, tx:1b0c8f4c1bbf58103e88dceacd4bcb63 */ *** Script: Business Service: Client Support - Hardware 49314 *** Script: Business Service: Client Support - Software 10240 *** Script: Business Service: Facility Management 9787 *** Script: Business Service: SAP ERP 8399 *** Script: Business Service: SharePoint 7251 *** Script: Business Service: Mail Services 6249

Workaround There is no workaround for the issue, below are the comments provided by development team: Earlier in older versions there was no such issue and no warning message thrown for 'orderByAggregate' because, orderByAggregate used to return early when ce object from below code is not valid. Therefore it won't hit the Log warning statement. See below code for Old versions. public void addOrderByField(String s) { if (StringUtil.nil(s)) return;

CompositeElement ce = new CompositeElement(s, fDrivingTable); if (!ce.isValid()) return; <--- code return here

addJoinsForField(ce.getDisplayName()); }

There were some code changes done in new versions where the early return functionality for "orderByAggregate" was removed. Therefore, it will hit log warning. Code path now looks like below. JoinQuery.addJoinsForField(CompositeElement, String, boolean, String) line: 235 JoinQuery.addJoinsForField(CompositeElement, String) line: 231 DBAggregateQuery(DBQuery).addJoinsForDisplayField(CompositeElement) line: 1210 DBAggregateQuery(DBQuery).addJoinsForDisplayFields(List ) line: 1205 DBAggregateQuery(DBQuery).genJoinsForQuery(StorageTableDescriptor) line: 1166

Latest code just return in the later code path which contains log warning statement. Points to be noted importantly The orderByAggregate functionality works properly. The current behavior can not be considered as something that has to be fixed since the warning log was helpful in certain cases. In ServiceNow platform, we usually see this type of situation in which some warnings are produced when running background script. It is a benign warning. No customer is affected by this. Related Problem: PRB1379064