0042235: WebPOS Product query has 2 in-efficient sub-selects related to attributes

Description

Main 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 Reproduce

Check 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_

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0115855) hgbot (developer) 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 ---

Issue History
Date Modified	Username	Field	Change
2019-11-08 12:35	shuehner	New Issue
2019-11-08 12:35	shuehner	Assigned To	=> Retail
2019-11-08 12:35	shuehner	Triggers an Emergency Pack	=> No
2019-11-14 12:40	marvintm	Resolution time	=> 1576364400
2019-11-14 12:40	marvintm	Severity	minor => major
2019-11-25 10:23	ranjith_qualiantech_com	Assigned To	Retail => ranjith_qualiantech_com
2019-11-25 10:38	ranjith_qualiantech_com	Status	new => scheduled
2019-11-27 06:45	hgbot	Checkin
2019-11-27 06:45	hgbot	Note Added: 0115855
2019-11-27 06:45	hgbot	Status	scheduled => resolved
2019-11-27 06:45	hgbot	Resolution	open => fixed
2019-11-27 06:45	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/0c72ce05a8f275908f0a769a8022f082ddfa465e [^]
2019-11-29 12:09	guilleaer	Review Assigned To	=> guilleaer
2019-11-29 12:09	guilleaer	Status	resolved => closed
2019-11-29 12:09	guilleaer	Fixed in Version	=> RR20Q1