Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0032234Openbravo ERPA. Platformpublic2016-02-15 14:522022-02-01 08:05
Reporteraferraz 
Assigned ToTriage Platform Base 
PrioritynormalSeverityminorReproducibilityalways
StatusacknowledgedResolutionopen 
PlatformOS30OS VersionProfessional Appliance
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0032234: isNull filter is not working correctly in property fields
DescriptionisNull filter (Advance Filtering) is not working correctly in property fields.
Steps To ReproduceAs F&B Admin:

- Create a Sales Order for "Alimentos y Supermercados, S.A" business partner and invoice terms "Immediate".
  Add a line for "Agua sin Gas 1L" product and quantity=2.
  Book it.
  Add a payment.

- Create a Sales Invoice for "Alimentos y Supermercados, S.A" business partner.
  Create lines from previous order.
  Update line for "Agua sin Gas 1L" product and set quantity=1.
  Complete it.

- Navigate to previous created Payment In.
  Move to Lines tab and realize there are two records, one with non empty Invoice No. and the other one with empty Invoice No.
  Set "!#" filter in Invoice No. column and realize row with non empty Invoice No. appears.
  Set "#" filter in Invoice No. column and realize no row is shown while row with empty Invoice No. should appear.
Proposed SolutionTry to resolve joins in AdvancedQueryBuilder in order to avoid the adding of the cross joins due to the usage of the navigation properties (See notes).

resolveJoins() method can be used to replace the property path with the corresponding alias
Additional Information
TagsNo tags attached.
Relationships
related to defect 0033923 acknowledged Triage Platform Base Advanced filter expressions do not work as expected in some type of fields 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2016-02-15 14:52aferrazNew Issue
2016-02-15 14:52aferrazAssigned To => platform
2016-02-15 14:52aferrazModules => Core
2016-02-15 14:52aferrazTriggers an Emergency Pack => No
2016-02-15 14:54aferrazSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=11136#r11136
2016-02-15 15:15aferrazRegression level => Production - Confirmed Stable
2016-02-16 17:23caristuNote Added: 0084268
2016-02-16 17:26caristuAssigned Toplatform => caristu
2016-02-16 17:26caristuStatusnew => acknowledged
2016-02-16 17:32caristuRegression levelProduction - Confirmed Stable =>
2016-02-16 17:32caristuStatusacknowledged => scheduled
2016-02-16 18:44caristuProposed Solution updated
2016-02-17 08:59alostaleAssigned Tocaristu => platform
2016-02-17 08:59alostaleStatusscheduled => acknowledged
2016-02-17 08:59alostaleTarget Version3.0PR16Q2 =>
2016-02-17 09:41caristuSummaryAdvance Filtering is not working correctly in some columns => isNull filter is not working correctly in property fields
2016-02-17 09:41caristuDescription Updatedbug_revision_view_page.php?rev_id=11149#r11149
2016-02-17 09:41caristuProposed Solution updated
2016-02-17 09:42caristuProposed Solution updated
2016-04-08 10:59hgbotCheckin
2016-04-08 10:59hgbotNote Added: 0085516
2016-09-07 19:44caristuRelationship addedrelated to 0033923
2022-02-01 08:05alostaleAssigned Toplatform => Triage Platform Base

Notes
(0084268)
caristu   
2016-02-16 17:23   
The Invoice No. is a property field. The generated query tries to access it: e.invoicePaymentSchedule.invoice.documentNo

As it is a derived property hibernate adds automatically some cross joins in order to retrieve the derived information.

Using the following HQL query:

select e.id from FIN_Payment_ScheduleDetail as e
left join e.invoicePaymentSchedule as join_2
left join join_2.invoice as join_3
where e.invoicePaymentSchedule.invoice.documentNo is null
order by e.creationDate desc

The resulting SQL is:

select fin_paymen0_.FIN_Payment_Scheduledetail_ID as col_0_0_
from FIN_Payment_ScheduleDetail fin_paymen0_
left outer join FIN_Payment_Schedule fin_paymen1_ on fin_paymen0_.FIN_Payment_Schedule_Invoice=fin_paymen1_.Fin_Payment_Schedule_ID
left outer join C_Invoice invoice2_ on fin_paymen1_.C_Invoice_ID=invoice2_.C_Invoice_ID
cross join FIN_Payment_Schedule fin_paymen3_
cross join C_Invoice invoice4_
where fin_paymen0_.FIN_Payment_Schedule_Invoice=fin_paymen3_.Fin_Payment_Schedule_ID and fin_paymen3_.C_Invoice_ID=invoice4_.C_Invoice_ID and (invoice4_.DocumentNo is null) order by fin_paymen0_.Created desc

with those cross joins the rows which does not have an invoice related to the schedule are not returned. This is why the filtering is not working.

If we do not use the derived property in the HQL:

select e.id from FIN_Payment_ScheduleDetail as e
left join e.invoicePaymentSchedule as join_2
left join join_2.invoice as join_3
order by e.creationDate desc

The resulting SQL does not use those cross joins:

select fin_paymen0_.FIN_Payment_Scheduledetail_ID as col_0_0_
from FIN_Payment_ScheduleDetail fin_paymen0_
left outer join FIN_Payment_Schedule fin_paymen1_ on fin_paymen0_.FIN_Payment_Schedule_Invoice=fin_paymen1_.Fin_Payment_Schedule_ID
left outer join C_Invoice invoice2_ on fin_paymen1_.C_Invoice_ID=invoice2_.C_Invoice_ID
order by fin_paymen0_.Created desc
(0085516)
hgbot   
2016-04-08 10:59   
Repository: tools/automation/int
Changeset: e19396add870a6d4288aa6d6921d5fd684c10553
Author: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com>
Date: Wed Feb 17 19:07:43 2016 +0100
URL: http://code.openbravo.com/tools/automation/int/rev/e19396add870a6d4288aa6d6921d5fd684c10553 [^]

Fix PRORegression31873Out1 & SALRegression31873In1 until issue 32234 is fixed

---
M src-test/com/openbravo/test/integration/erp/modules/functionalteam/procurement/testsuites/PRO_Regressions/PRORegression31873Out1.java
M src-test/com/openbravo/test/integration/erp/modules/functionalteam/sales/testsuites/SAL_Regressions/SALRegression31873In1.java
---