You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@ofbiz.apache.org by "Pawan Verma (Jira)" <ji...@apache.org> on 2019/11/23 12:15:00 UTC
[jira] [Updated] (OFBIZ-11294) EntityQuery queryCount is throwing
error with distinct method
[ https://issues.apache.org/jira/browse/OFBIZ-11294?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Pawan Verma updated OFBIZ-11294:
--------------------------------
Description:
We have a bug/missing support for distinct when used with queryCount method of EntityQuery.
Below is the more detail
{code:java}
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);{code}
*Case 1:* queryList().size() with distinct
{code:java}
int productAtLocation = EntityQuery.use(delegator).select("productId").from("InventoryItem")
.where(cond).maxRows(2).distinct().queryList().size();{code}
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
{code:java}
Long testCount = EntityQuery.use(delegator).select("productId").from("InventoryItem")
.where(cond).maxRows(2).queryCount();{code}
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
{code:java}
Long testCount = EntityQuery.use(delegator).select("productId").from("InventoryItem")
.where(cond).maxRows(2).queryCount();{code}
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:
{code:java}
testCount = select("productId").from("InventoryItem").distinct().queryCount()
{code}
was:
We have a bug/missing support for distinct when used with queryCount method of EntityQuery.
Below is the more detail
{code:java}
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);{code}
*Case 1:* queryList().size() with distinct
{code:java}
int productAtLocation = EntityQuery.use(delegator).select("productId").from("InventoryItem")
.where(cond).maxRows(2).distinct().queryList().size();{code}
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
{code:java}
Long testCount = EntityQuery.use(delegator).select("productId").from("InventoryItem")
.where(cond).maxRows(2).queryCount();{code}
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
{code:java}
Long testCount = EntityQuery.use(delegator).select("productId").from("InventoryItem")
.where(cond).maxRows(2).queryCount();{code}
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:
{code:java}
testCount = select("productId").from("InventoryItem").distinct().queryCount()
{code}
> EntityQuery queryCount is throwing error with distinct method
> -------------------------------------------------------------
>
> Key: OFBIZ-11294
> URL: https://issues.apache.org/jira/browse/OFBIZ-11294
> Project: OFBiz
> Issue Type: Bug
> Components: framework
> Affects Versions: Trunk, Release Branch 16.11, Release Branch 17.12, Release Branch 18.12
> Reporter: Pawan Verma
> Priority: Major
>
> We have a bug/missing support for distinct when used with queryCount method of EntityQuery.
> Below is the more detail
> {code:java}
> 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);{code}
> *Case 1:* queryList().size() with distinct
> {code:java}
> int productAtLocation = EntityQuery.use(delegator).select("productId").from("InventoryItem")
> .where(cond).maxRows(2).distinct().queryList().size();{code}
> 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
> {code:java}
> Long testCount = EntityQuery.use(delegator).select("productId").from("InventoryItem")
> .where(cond).maxRows(2).queryCount();{code}
> 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
> {code:java}
> Long testCount = EntityQuery.use(delegator).select("productId").from("InventoryItem")
> .where(cond).maxRows(2).queryCount();{code}
> 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:
> {code:java}
> testCount = select("productId").from("InventoryItem").distinct().queryCount()
> {code}
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)