Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0039380Openbravo ERP00. Application dictionarypublic2018-09-28 15:022019-06-26 04:55
ReporterSandrahuguet 
Assigned ToAugustoMauch 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS30OS VersionOpenbravo Appliance 14.04
Product Version 
Target VersionFixed in Version3.0PR19Q3 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0039380: Support using both "-columnName" and "columnName desc" in HQL order by clauses
DescriptionThe AdvancedQueryBuilder does different things to HQL order by clauses built like "-columnName" and to clauses built like "columnName desc", even though they should be equivalent.

There are two places where AdvancedQueryBuilder tries to parse the descendir order by the hql order by clause columns:
- [1], to detect if all columns are in descending order, to add the id column in descending order too
- [2], to parse the hql order by clase part to detect if it is a function

If desc is used instead of -, [1] does not work properly.
If - is used instead of desc, [2] does not work properly.

Given that there are already several hql order by clauses defined with desc and with -, it is better to support both than to try to enforce using one of them

[1] https://code.openbravo.com/erp/devel/pi/file/tip/modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java#l1385 [^]
[2] https://code.openbravo.com/erp/devel/pi/file/tip/modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java#l1415 [^]
Steps To ReproduceUse following expression to find all occurrences in PI:

grep -ri 'Hqlorderbyclause' | grep desc
Proposed SolutionReplace DESC by "-" in hql order by clause.
Additional Information
TagsPerformance
Relationships
related to defect 0039293 closed markmm82 Accounting Transaction Details Query Performance 
has duplicate feature request 0039381 closed platform It would be nice not allow the user add the clause "DESC" in hql order by clause 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2018-09-28 15:02SandrahuguetNew Issue
2018-09-28 15:02SandrahuguetAssigned To => markmm82
2018-09-28 15:02SandrahuguetModules => Core
2018-09-28 15:02SandrahuguetTriggers an Emergency Pack => No
2018-09-28 15:02SandrahuguetSummaryIn tabs the hql order by clause must use "-" instead of "DESC" => In tabs, the hql order by clause must use "-" instead of "DESC"
2018-09-28 15:02SandrahuguetDescription Updatedbug_revision_view_page.php?rev_id=17716#r17716
2018-09-28 15:03SandrahuguetTag Attached: Performance
2018-09-28 15:03SandrahuguetRelationship addedrelated to 0039293
2018-09-28 15:04SandrahuguetNote Added: 0107075
2018-09-28 15:08SandrahuguetRelationship addedrelated to 0039381
2019-01-07 18:05markmm82Statusnew => scheduled
2019-06-13 16:23AugustoMauchAssigned Tomarkmm82 => AugustoMauch
2019-06-13 16:34AugustoMauchTypedefect => feature request
2019-06-13 16:45AugustoMauchRelationship replacedhas duplicate 0039381
2019-06-13 16:55AugustoMauchSummaryIn tabs, the hql order by clause must use "-" instead of "DESC" => Support using both "-columnName" and "columnName desc" in HQL order by clauses
2019-06-13 16:55AugustoMauchDescription Updatedbug_revision_view_page.php?rev_id=18916#r18916
2019-06-18 13:05AugustoMauchReview Assigned To => caristu
2019-06-18 13:06hgbotCheckin
2019-06-18 13:06hgbotNote Added: 0112857
2019-06-18 13:06hgbotStatusscheduled => resolved
2019-06-18 13:06hgbotResolutionopen => fixed
2019-06-18 13:06hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/d8730c85d885fae3cdd402527b10460383672ed3 [^]
2019-06-19 16:02hudsonbotCheckin
2019-06-19 16:02hudsonbotNote Added: 0112894
2019-06-24 11:53hgbotCheckin
2019-06-24 11:53hgbotNote Added: 0112969
2019-06-24 11:54caristuNote Added: 0112970
2019-06-24 11:54caristuStatusresolved => closed
2019-06-24 11:54caristuFixed in Version => 3.0PR19Q3
2019-06-26 04:55hudsonbotCheckin
2019-06-26 04:55hudsonbotNote Added: 0113017

Notes
(0107075)
Sandrahuguet   
2018-09-28 15:04   
modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TAB.xml:<!--1EA4A3F31A27483A8C6E93176AE912D3--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TAB.xml:<!--2A9BA5FDA0F8470D881F8EA9DEDCC598--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TAB.xml:<!--3FD616A223E44621806A8573068B6C62--> <HQLORDERBYCLAUSE><![CDATA[obSelected desc, dueDate, amount, documentno]]></HQLORDERBYCLAUSE>
modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_TAB.xml:<!--81644A22CC4D4B879AA71C33C30E00A4--> <HQLORDERBYCLAUSE><![CDATA[obSelected desc, paymentPriority, expectedDate, coalesce(orderDocumentno, invoiceorderDocumentno)]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--0B6731F4F17A4A7AB00960148051E969--> <HQLORDERBYCLAUSE><![CDATA[sh.documentNo desc, e.lineNo asc]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--1B49A74CF8314D02B7F41B6595A169D4--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--21587C5E538E48758ED0C59B621DA91B--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--21790166FE1A47A284F9EA6F68695ACE--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--275327A580BF446DAA6502BFBCCDA20C--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--3595EBAC842D45FC95B96135C7799F0A--> <HQLORDERBYCLAUSE><![CDATA[validFromDate desc]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--38A56E62067242B39D3815E434D5BC48--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--3ACD18ADFBA8406086852B071250C481--> <HQLORDERBYCLAUSE><![CDATA[documentNo desc]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--52B21E690E024445A9F3B9F0A880AE8F--> <HQLORDERBYCLAUSE><![CDATA[o.documentNo desc, e.lineNo, e.product]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--5734406CB06D492CA9C5E31960CA7628--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--6EE02C9681A74406A129F1D96D022BA4--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--7209E9DB66CC47CF933AAEC8E3F873B6--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--7309F57E84264F6BB8F2A5E4A976ED93--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--89F074E609E841A7ACD0D4A19B6AF476--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--93787F9E92BD433EA7FD0E61227BC126--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--A399D7976DD74832A0D56586E46D149A--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--A55C6E2F874F4CD0B7AEDE4362701EA2--> <HQLORDERBYCLAUSE><![CDATA[creationDate desc]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--B01BFDF1E6B24CF4941807CA7F77A073--> <HQLORDERBYCLAUSE><![CDATA[obSelected desc, io.movementDate desc, io.documentNo desc, iol.lineNo]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--B1A51802BAF64D34BC59B91EA36D5064--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--B67386FF3EA94579BA9BA22353B5B897--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--C68533256F40484C89006BF00F669CD5--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--D7B93F8414E643DD8C1103D6FEF0B568--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--DA845735FBB34548B04F1908E2089CEC--> <HQLORDERBYCLAUSE><![CDATA[creationDate desc]]></HQLORDERBYCLAUSE>
src-db/database/sourcedata/AD_TAB.xml:<!--EE01CF71A3D741E8B2B7204ADCBBF7A8--> <HQLORDERBYCLAUSE><![CDATA[accountingSchema.name, accountingDate, abs(debit) desc, credit desc, value]]></HQLORDERBYCLAUSE>
34804:-<!--242--> <HQLORDERBYCLAUSE><![CDATA[accountingDate desc, id desc]]></HQLORDERBYCLAUSE>
(0112857)
hgbot   
2019-06-18 13:06   
Repository: erp/devel/pi
Changeset: d8730c85d885fae3cdd402527b10460383672ed3
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Mon Jun 17 16:02:45 2019 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/d8730c85d885fae3cdd402527b10460383672ed3 [^]

Fixes issue 39380: Both desc and - can be used in HQL Where Clauses

The AdvancedQueryBuilder class did not behave the same when an HQL where clause used - instead of
desc in descending order columns (i.e. -quantity vs quantity desc).

There were two places that behaved differently:
- The code that checked if all columns were ordered descending.
- The pattern used to parse if a where clause part was a function.

---
M modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
---
(0112894)
hudsonbot   
2019-06-19 16:02   
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/5a31264dd0f8 [^]
Maturity status: Test
(0112969)
hgbot   
2019-06-24 11:53   
Repository: erp/devel/pi
Changeset: d2cdc9ad4da2f94feaabec4e724015443fe06cbc
Author: Carlos Aristu <carlos.aristu <at> openbravo.com>
Date: Mon Jun 24 11:51:40 2019 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/d2cdc9ad4da2f94feaabec4e724015443fe06cbc [^]

related to bug 39380: handle special case

  Take also into account when order by expression ends with "desc" or "DESC".

---
M modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
---
(0112970)
caristu   
2019-06-24 11:54   
Reviewed + tested OK.
(0113017)
hudsonbot   
2019-06-26 04:55   
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/0c724fa9726e [^]
Maturity status: Test