Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0042235Retail ModulesWeb POSpublic2019-11-08 12:352019-11-29 12:09
Reportershuehner 
Assigned Toranjith_qualiantech_com 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionRR20Q1 
Merge Request Status
Review Assigned Toguilleaer
OBNetwork customerGold
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0042235: WebPOS Product query has 2 in-efficient sub-selects related to attributes
DescriptionMain product query from WebPOS with its HQLProperties defined in org.openbravo.retail.posterminal.master.ProductProperties has 2 sub-selects related to attributes:

https://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/file/tip/src/org/openbravo/retail/posterminal/master/ProductProperties.java#l81 [^]

Both add self-join to m_product table while the main outer query is about product anyway.

Those 2 sub-selects should be fixed to avoid that same as done in any other sub-select of the query.
 
Steps To ReproduceCheck SQL query executed focusing on that part and notice the 2 extra m_product accesses.

 (SELECT CASE
              WHEN attributes21_.M_AttributeSet_ID IS NOT NULL THEN TRUE
              ELSE FALSE
          END
   FROM M_Product product20_
   LEFT OUTER JOIN M_AttributeSet attributes21_ ON product20_.M_AttributeSet_ID=attributes21_.M_AttributeSet_ID
   WHERE product20_.M_Product_ID=product1_.M_Product_ID) AS col_82_0_,

  (SELECT CASE
              WHEN attributes23_.IsSerNo='Y' THEN TRUE
              ELSE FALSE
          END
   FROM M_Product product22_
   LEFT OUTER JOIN M_AttributeSet attributes23_ ON product22_.M_AttributeSet_ID=attributes23_.M_AttributeSet_ID
   WHERE product22_.M_Product_ID=product1_.M_Product_ID) AS col_83_0_

Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2019-11-08 12:35shuehnerNew Issue
2019-11-08 12:35shuehnerAssigned To => Retail
2019-11-08 12:35shuehnerOBNetwork customer => Gold
2019-11-08 12:35shuehnerTriggers an Emergency Pack => No
2019-11-14 12:40marvintmResolution time => 1576364400
2019-11-14 12:40marvintmSeverityminor => major
2019-11-25 10:23ranjith_qualiantech_comAssigned ToRetail => ranjith_qualiantech_com
2019-11-25 10:38ranjith_qualiantech_comStatusnew => scheduled
2019-11-27 06:45hgbotCheckin
2019-11-27 06:45hgbotNote Added: 0115855
2019-11-27 06:45hgbotStatusscheduled => resolved
2019-11-27 06:45hgbotResolutionopen => fixed
2019-11-27 06:45hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/0c72ce05a8f275908f0a769a8022f082ddfa465e [^]
2019-11-29 12:09guilleaerReview Assigned To => guilleaer
2019-11-29 12:09guilleaerStatusresolved => closed
2019-11-29 12:09guilleaerFixed in Version => RR20Q1

Notes
(0115855)
hgbot   
2019-11-27 06:45   
Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: 0c72ce05a8f275908f0a769a8022f082ddfa465e
Author: Ranjith S R <ranjith <at> qualiantech.com>
Date: Wed Nov 27 11:15:21 2019 +0530
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/0c72ce05a8f275908f0a769a8022f082ddfa465e [^]

Fixed issue 42235 : Removed Subquery in Product Properties

* AttributeSet values are feteched by joining with product instead of subquery

---
M src/org/openbravo/retail/posterminal/master/Product.java
M src/org/openbravo/retail/posterminal/master/ProductProperties.java
---