0033132: OBCriteria (at least sometimes) add client + org filters twice

Description

OBCriteria by default automatically adds 3 typically used filters to the SQL it is building
a.) ad_client (in list of readable clients)
b.) ad_org (in list of readable organizations)
c.) isactive = 'Y'

Reviewing SQL generated at least in some cases it adds all those filters 2 times.

That is bad for at least:
a.) CPU time overhead in processing those double queries in (sql parse + bind stages)
b.) Makes reviewing i.e. postgresql logfile containing all queries for debugging at lot more difficult.

Example of SQL being generated:
select this_.AD_Message_ID as AD1_59_0_, many_other_columns
from AD_Message this_ where lower(this_.Value)=$1 and
this_.AD_Org_ID in ($2, $3, $4, $5, $6, $7, $8, $9) and
this_.AD_Client_ID in ($10, $11) and
this_.IsActive=$12 and

this_.AD_Org_ID in ($13, $14, $15, $16, $17, $18, $19, $20) and
this_.AD_Client_ID in ($21, $22) and
this_.IsActive=$23

limit $24

Steps To Reproduce

Easy why to see the wrong behavior is to run OBMessageUtils.messageBD function for example with a simple junit testcase.

@Test
public void testADMessage() {
log.info(OBMessageUtils.messageBD("OrderDocumentno"));
}

And review all sql triggered by it (i.e. on postgres set log_min_duration_statement=0 in config)

Proposed Solution

Do not double add the filters when building the SQL.

Tags

Performance

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

duplicate of	defect	0033138		closed	alostale	OBCriteria.initialize() is not protected against multiple calls per instance
related to	feature request	0033133		new	Triage Platform Base	OBCriteria skip adding client+org filters if table is known to not required these.

Notes
(0087033) shuehner (administrator) 2016-06-06 15:12	Later checking shows that even just calling uniqueResult() internally falls back to .list() so doing double init already. Example calling sequence: at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347) at org.openbravo.dal.service.OBCriteria.list(OBCriteria.java:90) at org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:369) at org.openbravo.dal.service.OBCriteria.uniqueResult(OBCriteria.java:129) at org.openbravo.dal.service.OBCriteria.count(OBCriteria.java:103) And each of count + uniqueResult + list of the OBCriteria calls initialize()

(0087094) shuehner (administrator) 2016-06-08 13:33	The 33138 has now the reason for the strange behavior found.

Issue History
Date Modified	Username	Field	Change
2016-06-03 17:05	shuehner	New Issue
2016-06-03 17:05	shuehner	Assigned To	=> platform
2016-06-03 17:05	shuehner	OBNetwork customer	=> No
2016-06-03 17:05	shuehner	Modules	=> Core
2016-06-03 17:05	shuehner	Triggers an Emergency Pack	=> No
2016-06-03 17:14	shuehner	Tag Attached: Performance
2016-06-03 17:14	shuehner	Relationship added	related to 0033133
2016-06-05 14:54	shuehner	Relationship added	related to 0033138
2016-06-06 15:12	shuehner	Note Added: 0087033
2016-06-08 13:33	shuehner	Relationship replaced	duplicate of 0033138
2016-06-08 13:33	shuehner	Note Added: 0087094
2016-06-08 13:33	shuehner	Status	new => closed
2016-06-08 13:33	shuehner	Resolution	open => duplicate