[jira] [Commented] (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:comment-tabpanel=17147094#comment-17147094
]
ASF subversion and git services commented on OFBIZ-11294:
-
Commit f6353957e233777b15d375ce2ada5cb91f234aad in ofbiz-framework's branch
refs/heads/release17.12 from Pawan Verma
[ https://gitbox.apache.org/repos/asf?p=ofbiz-framework.git;h=f635395 ]
Fixed: EntityQuery queryCount is throwing error with distinct method
(OFBIZ-11294)
Added missing support for selectFields in queryCount and all the subsequent
methods.
Thanks: Deepak Dixit for the review.
> 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: Release Branch 16.11, Release Branch 18.12, Release
> Branch 17.12, Trunk
>Reporter: Pawan Verma
>Assignee: 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)
[jira] [Commented] (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:comment-tabpanel=17147091#comment-17147091
]
ASF subversion and git services commented on OFBIZ-11294:
-
Commit 11dedfa79fdab3ee2eb701bc8a43c6f5ccfe116c in ofbiz-framework's branch
refs/heads/release18.12 from Pawan Verma
[ https://gitbox.apache.org/repos/asf?p=ofbiz-framework.git;h=11dedfa ]
Fixed: EntityQuery queryCount is throwing error with distinct method
(OFBIZ-11294)
Added missing support for selectFields in queryCount and all the subsequent
methods.
Thanks: Deepak Dixit for the review.
> 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: Release Branch 16.11, Release Branch 18.12, Release
> Branch 17.12, Trunk
>Reporter: Pawan Verma
>Assignee: 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)
[jira] [Commented] (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:comment-tabpanel=17147090#comment-17147090
]
ASF subversion and git services commented on OFBIZ-11294:
-
Commit 031592cc32b49ebe873515741190f87f4365cb91 in ofbiz-framework's branch
refs/heads/trunk from Pawan Verma
[ https://gitbox.apache.org/repos/asf?p=ofbiz-framework.git;h=031592c ]
Fixed: EntityQuery queryCount is throwing error with distinct method
(OFBIZ-11294)
Added missing support for selectFields in queryCount and all the subsequent
methods.
Thanks: Deepak Dixit for the review.
> 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: Release Branch 16.11, Release Branch 18.12, Release
> Branch 17.12, Trunk
>Reporter: Pawan Verma
>Assignee: 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)
[jira] [Commented] (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:comment-tabpanel=17114708#comment-17114708
]
Amit Gadaley commented on OFBIZ-11294:
--
Hello [~pawan],
I have faced the same issue and this solution looks good to me.
I have tested it and it is working fine for me.
> 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: Release Branch 16.11, Release Branch 18.12, Release
> Branch 17.12, Trunk
>Reporter: Pawan Verma
>Assignee: 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)
[jira] [Commented] (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:comment-tabpanel=17098832#comment-17098832
]
Pawan Verma commented on OFBIZ-11294:
-
If no objection, I would like to commit it :)
> 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: Release Branch 16.11, Release Branch 18.12, Release
> Branch 17.12, Trunk
>Reporter: Pawan Verma
>Assignee: 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)
[jira] [Commented] (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:comment-tabpanel=17092115#comment-17092115
]
Pawan Verma commented on OFBIZ-11294:
-
I've tried to fix it in PR: [https://github.com/apache/ofbiz-framework/pull/76]
Please review and let me your your thoughts. Thanks!
> 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: Release Branch 16.11, Release Branch 18.12, Release
> Branch 17.12, Trunk
>Reporter: Pawan Verma
>Assignee: 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)
