Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0029212Openbravo ERP07. Sales managementpublic2015-03-10 10:032015-03-10 22:50
Reporteralostale 
Assigned ToAugustoMauch 
PriorityimmediateSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR15Q2 
Merge Request Status
Review Assigned Toalostale
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression levelProduction - QA Approved
Regression date2014-12-22
Regression introduced in release3.0PR15Q1
Regression introduced by commithttps://code.openbravo.com/erp/devel/pi/rev/95dd177fbbc7 [^]
Triggers an Emergency PackNo
Summary0029212: can't filter multiple products in return from/to P&Es
DescriptionIn Return from Customer and Return to Vendor P&E it is not possible to filter by more than one product at the same time using product filter drop down list.

A HQL error occurs and no data is shown in the grid.

Even the request from client is correctly sent to backend, there is a problem when generating the HQL query:

1. When there is a single product it looks like:
  select ...
    FROM MaterialMgmtShipmentInOutLine as iol
   where ...
     AND ( iol.product.id = :alias_0 )

2. When filtering by multiple products:
  select ...
    FROM MaterialMgmtShipmentInOutLine as iol
   where ...
     AND ( ( join_0.id = :alias_0 or join_0.id = :alias_1 ) )


2nd case is incorrect (join_0 is not a valid alias)

Steps To Reproduce-Go to Return to Vendor window
-Create new record
-Click Pick/Edit lines
-Clear filters to show several lines with different product
-Open product filter drop down list and select 1 product
   ->OK: grid is filtered correctly by that product
-Open product filter drop down list and select another product keeping previous selection
   -> FAIL:
      -No data is shown in grid
      -Openbravo log shows:
de854ee5 1817163 [http-8080-2] ERROR org.openbravo.service.datasource.DataSourceServlet - Invalid path: 'join_0.id' [select ...]
  at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)


Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
depends on backport 00292143.0PR15Q1.2 closed AugustoMauch can't filter multiple products in return from/to P&Es 
caused by defect 00284323.0PR15Q1 closed AugustoMauch can't filter product FK drop down in return windows P&E 
related to defect 00291733.0PR15Q4 closed egoitz Performance problem when filtering in Return to Vendor pick and execute lines having a big amount of data 
related to defect 0029224 closed AugustoMauch can't filter by Return Reason in Return P&E windows 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2015-03-10 10:03alostaleNew Issue
2015-03-10 10:03alostaleAssigned To => Sandrahuguet
2015-03-10 10:03alostaleModules => Core
2015-03-10 10:03alostaleTriggers an Emergency Pack => No
2015-03-10 10:04alostaleRegression level => Production - QA Approved
2015-03-10 10:04alostaleRegression introduced in release => 3.0PR15Q1
2015-03-10 10:15alostaleRegression date => 2014-11-25
2015-03-10 10:15alostaleRegression introduced by commit => https://code.openbravo.com/erp/devel/pi/rev/059f88b82a8b [^]
2015-03-10 10:16alostaleNote Added: 0075366
2015-03-10 10:18alostaleRelationship addedrelated to 0029173
2015-03-10 10:33alostaleRelationship addedcaused by 0028432
2015-03-10 10:34alostaleRegression introduced by commithttps://code.openbravo.com/erp/devel/pi/rev/059f88b82a8b [^] => https://code.openbravo.com/erp/devel/pi/rev/95dd177fbbc7 [^]
2015-03-10 10:34alostaleAssigned ToSandrahuguet => AugustoMauch
2015-03-10 10:35alostaleRegression date2014-11-25 => 2014-12-22
2015-03-10 11:24AugustoMauchStatusnew => scheduled
2015-03-10 11:25AugustoMauchIssue Monitored: alostale
2015-03-10 11:25AugustoMauchReview Assigned To => alostale
2015-03-10 12:22hgbotCheckin
2015-03-10 12:22hgbotNote Added: 0075373
2015-03-10 12:22hgbotStatusscheduled => resolved
2015-03-10 12:22hgbotResolutionopen => fixed
2015-03-10 12:22hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/3517fc85f514dde8b9f5eb1582d6541d679e816d [^]
2015-03-10 13:36AugustoMauchNote Added: 0075379
2015-03-10 16:10alostaleNote Added: 0075407
2015-03-10 16:10alostaleStatusresolved => closed
2015-03-10 16:10alostaleFixed in Version => 3.0PR15Q2
2015-03-10 16:14alostaleNote Edited: 0075407bug_revision_view_page.php?bugnote_id=0075407#r7918
2015-03-10 16:21alostaleRelationship addedrelated to 0029224
2015-03-10 16:26alostaleRelationship addedrelated to 0029225
2015-03-10 22:50hudsonbotCheckin
2015-03-10 22:50hudsonbotNote Added: 0075424

Notes
(0075366)
alostale   
2015-03-10 10:16   
Regression introduced in PR15Q1 when migrating return P&Es to HQL datasources
(0075373)
hgbot   
2015-03-10 12:22   
Repository: erp/devel/pi
Changeset: 3517fc85f514dde8b9f5eb1582d6541d679e816d
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Tue Mar 10 12:20:08 2015 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/3517fc85f514dde8b9f5eb1582d6541d679e816d [^]

Fixes issue 29212: Filtering multiple products work in Return from/to P&E

The problem was that the HQL WHERE clause built by the AdvancedQueryBuilder was not proper, as it where using join aliases that where not present in the FROM clause. This happened when the AdvancedQueryBuilder was used in the HQLDataSourceService class, because that class uses the WHERE clause returned by the AdvancedQueryBuilder, and the HQL FROM clause defined in the application dictionary. The resulting HQL query was like this:

SELECT iol.id as id, ...
FROM OrderLine AS ol LEFT JOIN ol.salesOrder AS o
WHERE ...
AND (( join_0.id = :alias_0 or join_0.id = :alias_1 ) )

The first two lines are built using the HQL transformers based on the HQL defined in the application dictionary, and the last two lines are built using the AdvancedQueryBuilder. The WHERE clause uses a join alias (join_0) that is not defined in the FROM clause

The new join alias was created in the resolveJoins method, that was invoked when the criteria contained an OR operator. To fix this issue, a new flag, creatingJoinsInWhereClauseIsPrevented, has been added. If this flag is true, instead of creating a new join alias for the where clause, the clause will be built using the entity main alias and the property name. As for now this new flag is only set to true in the HqlDataSourceService class.

---
M modules/org.openbravo.service.datasource/src/org/openbravo/service/datasource/HQLDataSourceService.java
M modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
---
(0075379)
AugustoMauch   
2015-03-10 13:36   
A test has been created in testlink: http://testlink.openbravo.com/testlink/linkto.php?tprojectPrefix=Communit&item=testcase&id=Communit-8064 [^]
(0075407)
alostale   
2015-03-10 16:10   
(edited on: 2015-03-10 16:14)
Code reviewed

Tested:
-Following steps to reproduce
-UOM field in Return window
-No other FK drops downs are present in core P&E
-As per code review this patch only affects P&E windows, so standard windows shouldn't be affected
(0075424)
hudsonbot   
2015-03-10 22:50   
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/b89b825e6235 [^]
Maturity status: Test