Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0045922Openbravo ERPA. Platformpublic2021-02-18 18:042022-02-01 08:07
ReporterALopetegui 
Assigned ToTriage Platform Base 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusacknowledgedResolutionopen 
PlatformOS5OS Version
Product VersionPR20Q3.1 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0045922: Extra self-join with main table when using computed columns
DescriptionAdding new hql properties with computed columns, there is an extra join with the main table instead of using the existing one.
Steps To ReproduceAdd a new hql property to master product for example, the query of search product will add an extra join with m_product to use the computed column.
Proposed SolutionIt seems a bug or design defect of hibernate , the way hql makes the query with computed columns.
Additional Information
TagsNOR, Performance
Relationships
related to defect 0045919 closed rqueralta Retail Modules Extra join of product table in master.product 
related to defect 0044911 closed ranjith_qualiantech_com Retail Modules Incremental refresh of Product cannot use index on updated 
related to defect 0045055 closed ranjith_qualiantech_com Retail Modules Incremental refresh of ProductCharacteristic cannot use index on updated 
related to defect 0045144 closed prakashmurugesan88 Retail Modules Fix incremental and full refresh with Multi Tax Category module 
related to defect 0045467 closed prakashmurugesan88 Retail Modules Performance issues on the model CharacteristicValue 
related to feature request 0045056 new Retail Retail Modules Faster insertion of big amounts of data in full refresh in indexedDB 
depends on feature request 0045969 new Triage Platform Base Openbravo ERP upgrade to Hibernate 6.x (prototype) 
Not all the children of this issue are yet resolved or closed.
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2021-02-18 18:04ALopeteguiNew Issue
2021-02-18 18:04ALopeteguiAssigned To => platform
2021-02-18 18:04ALopeteguiModules => Core
2021-02-18 18:04ALopeteguiResolution time => 1617400800
2021-02-18 18:04ALopeteguiTriggers an Emergency Pack => No
2021-02-18 18:05ALopeteguiTag Attached: Performance
2021-02-18 18:16ALopeteguiSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=22136#r22136
2021-02-22 10:44rafarodaTag Attached: NOR
2021-02-22 10:46rafarodaRelationship addedrelated to 0045919
2021-02-22 10:47rafarodaRelationship addedrelated to 0044911
2021-02-22 10:47rafarodaRelationship addedrelated to 0045055
2021-02-22 10:47rafarodaRelationship addedrelated to 0045144
2021-02-22 10:48rafarodaRelationship addedrelated to 0045467
2021-02-22 15:01shuehnerNote Added: 0126272
2021-02-22 15:02shuehnerIssue Monitored: shuehner
2021-02-24 09:53rafarodaResolution time1617400800 => 1614726000
2021-03-01 07:23alostaleNote Added: 0126393
2021-03-01 07:24alostaleResolution time1614726000 =>
2021-03-01 07:24alostaleTypedefect => design defect
2021-03-01 10:12alostaleRelationship addeddepends on 0045968
2021-03-01 10:13alostaleNote Edited: 0126393bug_revision_view_page.php?bugnote_id=0126393#r22155
2021-03-04 12:55rafarodaRelationship addedrelated to 0045056
2021-08-27 15:35shuehnerNote Added: 0131408
2021-08-27 15:45cbernerNote Added: 0131409
2021-09-21 13:30alostaleRelationship addeddepends on 0045969
2021-09-21 13:31alostaleStatusnew => acknowledged
2021-09-21 13:34alostaleNote Added: 0131952
2021-09-21 13:35alostaleRelationship deleteddepends on 0045968
2022-02-01 08:07alostaleAssigned Toplatform => Triage Platform Base

Notes
(0126272)
shuehner   
2021-02-22 15:01   
There is known cases (fixes several times) that this happens if the HQL of the columns is written poorly.
Typical cases:
- Either adding new from clause for main table again
- 2nd referencing to main table pk via "= object" and not ".id = object.id" which avoids the extra self-join.

Adding as a note as unclear if your concrete case if just +1 of that known topic or a seperate unavoidable one.
(0126393)
alostale   
2021-03-01 07:23   
(edited on: 2021-03-01 10:13)
This issue seems to be caused by Hibernate issue HHH-12895, which is fixed in Hibernate 5.4.10.

Moving it to DD as it depends on Hibernate update 0045968

https://hibernate.atlassian.net/browse/HHH-12895 [^]
(0131408)
shuehner   
2021-08-27 15:35   
To reproduce the following module can be used (to add column triggering the behavior):
org.openbravo.france.ecotax (adding columns)
org.openbravo.retail.france.ecotax (adding usage in ProductProperties)
(0131409)
cberner   
2021-08-27 15:45   
This issue is still reproducible after updating hibernate to 5.5.6, so I think the hibernate issue mentioned is not the problem here.

https://hibernate.atlassian.net/browse/HHH-12895 [^]
(0131952)
alostale   
2021-09-21 13:34   
It is reported as a new Hibernate issue [1], but most probably it won't be fixed till 6.x [2].

[1] https://hibernate.atlassian.net/browse/HHH-14830 [^]
[2] https://discourse.hibernate.org/t/extra-cross-join-created-when-using-one-to-one-or-many-to-one-same-table-entity/5723 [^]