[OFBIZ-11294] EntityQuery queryCount is throwing error with distinct method

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: Release Branch 16.11, Release Branch 18.12, Release Branch 17.12, Trunk
• Fix Version/s: 17.12.04, 18.12.01
• Component/s: framework
• Assignee: Pawan Verma
• Reporter: Pawan Verma
• Created: 23/Nov/19 12:14
• Updated: 27/Jun/20 20:14
• Resolved: 27/Jun/20 20:14

Description

We have a bug/missing support for distinct when used with queryCount method of EntityQuery.

Below is the more detail

EntityCondition cond = EntityCondition.makeCondition(UtilMisc.toList(
       EntityCondition.makeCondition("locationSeqId", "00test123"),
       EntityCondition.makeCondition("facilityId", EntityOperator.EQUALS, "10070"),
       EntityCondition.makeCondition("quantityOnHandTotal", EntityOperator.GREATER_THAN, BigDecimal.ZERO)),
       EntityOperator.AND);

Case 1: queryList().size() with distinct

int productAtLocation = EntityQuery.use(delegator).select("productId").from("InventoryItem")
       .where(cond).maxRows(2).distinct().queryList().size();

Result Query: SELECT DISTINCT PRODUCT_ID FROM INVENTORY_ITEM WHERE ((LOCATION_SEQ_ID = ? AND FACILITY_ID = ? AND QUANTITY_ON_HAND_TOTAL > ?))

 Result: This case works well.

Case 2: queryCount without distinct

Long testCount = EntityQuery.use(delegator).select("productId").from("InventoryItem")
       .where(cond).maxRows(2).queryCount();

Result Query: SELECT COUNT(1)  FROM INVENTORY_ITEM WHERE ((LOCATION_SEQ_ID = ? AND FACILITY_ID = ? AND QUANTITY_ON_HAND_TOTAL > ?))

Result: This case also works well

Case 3:  queryCount with distinct

Long testCount = EntityQuery.use(delegator).select("productId").from("InventoryItem")
       .where(cond).maxRows(2).queryCount();

Result Query: SELECT COUNT(DISTINCT *)  FROM INVENTORY_ITEM WHERE ((LOCATION_SEQ_ID = ? AND FACILITY_ID = ? AND QUANTITY_ON_HAND_TOTAL > ?))

Result: This case throw an error
org.apache.ofbiz.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT COUNT(DISTINCT ) FROM OFBIZ.INVENTORY_ITEM (Syntax error: Encountered "" at line 1, column 23.)

 

Below is the research I have done for the issue:

In EntityQuery.queryCount(), return method is delegator.findCountByCondition(). This method doesn't have support for fieldsToSelect.
When we reach till GenericDAO.selectCountByCondition() at line 949, returning method is setting null for selectFields.
And at the implementation of selectCountByCondition, at line 994 we have a check for selectFields, that's why we are having COUNT(DISTINCT *) in our query.

 

To test this, I have used a simple way:
Just include below code at any groovy file and run it:

testCount = select("productId").from("InventoryItem").distinct().queryCount()