Uploaded image for project: 'OFBiz'
  1. OFBiz
  2. OFBIZ-11294

EntityQuery queryCount is throwing error with distinct method

    XMLWordPrintableJSON

    Details

    • 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: 18.12.01, 17.12.04
    • Component/s: framework
    • Labels:
      None

      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()
      

       

       

        Attachments

          Activity

            People

            • Assignee:
              pawan Pawan Verma
              Reporter:
              pawan Pawan Verma
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: