Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0029214Openbravo ERP07. Sales managementpublic2015-03-10 10:032015-04-01 08:31
Reporteralostale 
Assigned ToAugustoMauch 
PriorityimmediateSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target Version3.0PR15Q1.2Fixed in Version3.0PR15Q1.2 
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
Summary0029214: 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
TagsApproved
Relationships
blocks defect 0029212 closed AugustoMauch can't filter multiple products in return from/to P&Es 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2015-03-10 11:24AugustoMauchTypedefect => backport
2015-03-10 11:24AugustoMauchTarget Version => 3.0PR15Q1.2
2015-03-10 12:50hgbotCheckin
2015-03-10 12:50hgbotNote Added: 0075375
2015-03-10 12:50hgbotStatusscheduled => resolved
2015-03-10 12:50hgbotResolutionopen => fixed
2015-03-10 12:50hgbotFixed in SCM revision => http://code.openbravo.com/erp/backports/3.0PR15Q1.2/rev/721b9a12adeea9332779275d4e290a2db8b603f2 [^]
2015-03-10 12:53AugustoMauchIssue Monitored: alostale
2015-03-10 12:53AugustoMauchReview Assigned To => alostale
2015-03-10 16:10alostaleNote Added: 0075408
2015-03-10 16:10alostaleStatusresolved => closed
2015-03-10 16:10alostaleFixed in Version => 3.0PR15Q1.2
2015-03-10 16:14alostaleNote Edited: 0075408bug_revision_view_page.php?bugnote_id=0075408#r7916
2015-03-13 14:01dmitry_mezentsevTag Attached: Approved
2015-04-01 08:06hgbotCheckin
2015-04-01 08:06hgbotNote Added: 0076205
2015-04-01 08:06hgbotStatusclosed => resolved
2015-04-01 08:06hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/backports/3.0PR15Q1.2/rev/721b9a12adeea9332779275d4e290a2db8b603f2 [^] => http://code.openbravo.com/erp/backports/3.0PR15Q2/rev/721b9a12adeea9332779275d4e290a2db8b603f2 [^]
2015-04-01 08:31priyamStatusresolved => closed
2015-04-01 08:31priyamNote Added: 0076270

Notes
(0075375)
hgbot   
2015-03-10 12:50   
Repository: erp/backports/3.0PR15Q1.2
Changeset: 721b9a12adeea9332779275d4e290a2db8b603f2
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Tue Mar 10 12:20:08 2015 +0100
URL: http://code.openbravo.com/erp/backports/3.0PR15Q1.2/rev/721b9a12adeea9332779275d4e290a2db8b603f2 [^]

Fixes issue 29214: 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
---
(0075408)
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
(0076205)
hgbot   
2015-04-01 08:06   
Repository: erp/backports/3.0PR15Q2
Changeset: 721b9a12adeea9332779275d4e290a2db8b603f2
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Tue Mar 10 12:20:08 2015 +0100
URL: http://code.openbravo.com/erp/backports/3.0PR15Q2/rev/721b9a12adeea9332779275d4e290a2db8b603f2 [^]

Fixes issue 29214: 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
---
(0076270)
priyam   
2015-04-01 08:31   
closed again, as the status changed due the merge tag of 3.0PR15Q1.2 to backports/3.0PR15Q2