Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0029732Openbravo ERPA. Platformpublic2015-04-29 13:222016-03-17 10:54
ReporterSandrahuguet 
Assigned ToAugustoMauch 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS20OS VersionCommunity Appliance
Product Version 
Target VersionFixed in Version3.0PR15Q4 
Merge Request Status
Review Assigned Toalostale
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0029732: In a mandatory field, the query created when filtering shouldn't have the functions 'coalesce' and 'to_char'
DescriptionIn a mandatory field, the query created when filtering shouldn't have the functions 'coalesce' and 'to_char'
Steps To Reproduce-Go to Discounts and Promotions Window
-Navigate to Business partner tab
-Filter by 'A' and pres tab key

The following query is executed:

select pricingadj0_.M_Offer_Bpartner_ID as M1_503_, pricingadj0_.AD_Client_ID as AD2_503_, pricingadj0_.AD_Org_ID as AD3_503_, pricingadj0_.IsActive as IsActive503_, pricingadj0_.Created as Created503_, pricingadj0_.Createdby as Createdby503_, pricingadj0_.Updated as Updated503_, pricingadj0_.Updatedby as Updatedby503_, pricingadj0_.M_Offer_ID as M9_503_, pricingadj0_.C_BPartner_ID as C10_503_ from M_Offer_BPartner pricingadj0_ left outer join C_BPartner businesspa1_ on pricingadj0_.C_BPartner_ID=businesspa1_.C_BPartner_ID where (upper(coalesce(to_char(businesspa1_.Name), '')) like upper(?) escape '|') and pricingadj0_.M_Offer_ID=? and (pricingadj0_.AD_Org_ID in ('D270A5AC50874F8BA67A88EE977F8E3B' , '4399136852B145BD96CC2A6CE0800C68' , '14B1927026BE471E9B85FE699BCA61C2' , '0' , 'B5DE96143D6642228E3B9DEC69886A47' , '3B187EC130A549A7A9388F8060EF156D' , '67839EEFA49E44AC969BD60093FCC899' , '01AD882EFC8545ACA6455E2F6FD51EE9' , 'BF129721D9FB4EB0819509934153E972')) and (pricingadj0_.AD_Client_ID in ('39363B0921BB4293B48383844325E84C' , '0')) order by businesspa1_.Name, pricingadj0_.M_Offer_Bpartner_ID limit ?
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
related to design defect 0029919 acknowledged Triage Platform Base The query done to populate table/tabledir dropdowns in forms when the user enters text can be improved 
blocks feature request 0029612 closed AugustoMauch Extend the grid configuration to improve the performance of the population of foreign key dropdowns 
Attached Files? 29732.export (2,202) 2015-05-11 13:20
https://issues.openbravo.com/file_download.php?file_id=8071&type=bug
Issue History
Date ModifiedUsernameFieldChange
2015-04-29 13:22SandrahuguetNew Issue
2015-04-29 13:22SandrahuguetAssigned To => platform
2015-04-29 13:22SandrahuguetModules => Core
2015-04-29 13:22SandrahuguetTriggers an Emergency Pack => No
2015-04-29 13:23AugustoMauchAssigned Toplatform => AugustoMauch
2015-04-29 13:23AugustoMauchStatusnew => acknowledged
2015-05-11 13:11AugustoMauchRelationship addedblocks 0029612
2015-05-11 13:20AugustoMauchFile Added: 29732.export
2015-05-18 18:36AugustoMauchRelationship addedrelated to 0029919
2015-07-06 09:13AugustoMauchStatusacknowledged => scheduled
2015-07-06 09:23hgbotCheckin
2015-07-06 09:23hgbotNote Added: 0078649
2015-07-06 09:23hgbotStatusscheduled => resolved
2015-07-06 09:23hgbotResolutionopen => fixed
2015-07-06 09:23hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/e684c73bb502f85fb71b82f2e55ad35644dbe505 [^]
2015-07-06 09:24AugustoMauchIssue Monitored: alostale
2015-07-06 09:24AugustoMauchReview Assigned To => alostale
2015-07-07 10:08alostaleNote Added: 0078698
2015-07-07 10:08alostaleStatusresolved => closed
2015-07-07 10:08alostaleFixed in Version => 3.0PR15Q4
2015-08-20 23:15hudsonbotCheckin
2015-08-20 23:15hudsonbotNote Added: 0079458
2016-01-22 11:18hgbotCheckin
2016-01-22 11:18hgbotNote Added: 0083537
2016-01-22 11:18hgbotStatusclosed => resolved
2016-01-22 11:18hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/e684c73bb502f85fb71b82f2e55ad35644dbe505 [^] => http://code.openbravo.com/erp/devel/pi/rev/712d158da36ea648c5ebbb347c22a947387adadb [^]
2016-01-29 08:32alostaleNote Added: 0083700
2016-01-29 08:32alostaleStatusresolved => closed
2016-03-17 10:54hudsonbotCheckin
2016-03-17 10:54hudsonbotNote Added: 0085065

Notes
(0078649)
hgbot   
2015-07-06 09:23   
Repository: erp/devel/pi
Changeset: e684c73bb502f85fb71b82f2e55ad35644dbe505
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Mon May 11 13:18:57 2015 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/e684c73bb502f85fb71b82f2e55ad35644dbe505 [^]

Fixes bug 29732: Get rid of coalesce(to_char()) when possible to improve query

We sometimes use the coalesce(to_char(value),'') functions to prevent passing null values in a where clause, replacing the null value with an empty string. The problem is that if those functions are used, then it will not be possible benefit from the indexes defined in the database.

In this issue we have made a distintion between mandatory and non-mandatory columns. Mandatory columns will no longer use the coalesce and to_char functions in the AdvancedQueryBuilder.createIdentifierLeftClause method. We can do that safely as it is not possible that those columns have null values.

---
M modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
---
(0078698)
alostale   
2015-07-07 10:08   
code reviewed + tested
(0079458)
hudsonbot   
2015-08-20 23:15   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/8c91718397a5 [^]
Maturity status: Test
(0083537)
hgbot   
2016-01-22 11:18   
Repository: erp/devel/pi
Changeset: 712d158da36ea648c5ebbb347c22a947387adadb
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Mon May 11 13:18:57 2015 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/712d158da36ea648c5ebbb347c22a947387adadb [^]

Fixes bug 29732: Get rid of coalesce(to_char()) when possible to improve query

We sometimes use the coalesce(to_char(value),'') functions to prevent passing null values in a where clause, replacing the null value with an empty string. The problem is that if those functions are used, then it will not be possible benefit from the indexes defined in the database.

In this issue we have made a distintion between mandatory and non-mandatory columns. Mandatory columns will no longer use the coalesce and to_char functions in the AdvancedQueryBuilder.createIdentifierLeftClause method. We can do that safely as it is not possible that those columns have null values.

---
M modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
---
(0083700)
alostale   
2016-01-29 08:32   
closed again: was set as resolved incorrectly
(0085065)
hudsonbot   
2016-03-17 10:54   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/b22fb0500156 [^]
Maturity status: Test