0034810: ProcessHQLQuery returns duplicates causing errors when inserting data and really slow login with many products

Openbravo Issue Tracking System - Retail Modules

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0034810

Retail Modules

Web POS

public

2016-12-01 16:37

2017-01-17 18:43

Reporter

mtaal

Assigned To

marvintm

Priority

immediate

Severity

critical

Reproducibility

have not tried

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

RR16Q4.1

Fixed in Version

RR16Q4.1

Merge Request Status

Review Assigned To

marvintm

OBNetwork customer

Support ticket

Regression level

Production - Confirmed Stable

Regression date

2015-10-28

Regression introduced in release

RR16Q1

Regression introduced by commit

https://code.openbravo.com/erp/pmods/org.openbravo.mobile.core/rev/7f56ee2e32ec [^]

Triggers an Emergency Pack

Summary

0034810: ProcessHQLQuery returns duplicates causing errors when inserting data and really slow login with many products

Description

The ProcessHQLQuery class uses the offset and limit parameters to support paginated data [1]. This is needed in case of large datasets so that the client side is not flooded with too much data.

The ProcessHQLQuery class can execute multiple queries depending on the subclass implementation. It will change the offset/limit after each query [2]. For 2 examples see the Product [3] and Business Partner [4].

The problem is that if there are multiple queries then these queries will use different offset/limit parameters within one request. This means that the next request for the next page will get overlapping data with previous requests or can skip data.

Let's take an example:

Subclass of ProcessHQLQuery adds 2 queries. The first query would return 15000 records (unpaginated), the second query would return 8000 records (unpaginated).

Master data load:
Request 1:
offset: 0, limit: 10000
With the current code this would return only 10000 records of the first query, the second query won't be executed anymore
Request 2:
offset: 10000, limit: 10000
This would return 5000 records of the first query and the first 5000 records of the second query (the offset is set to zero if a query returns less than the limit).
Request 3:
offset: 20000, limit: 10000
This would return the first 8000 records of the second query, overlapping with request 2

[1]
https://code.openbravo.com/erp/pmods/org.openbravo.mobile.core/file/0d7bcfc60eb8/src/org/openbravo/mobile/core/process/ProcessHQLQuery.java#l165 [^]

[2]
https://code.openbravo.com/erp/pmods/org.openbravo.mobile.core/file/0d7bcfc60eb8/src/org/openbravo/mobile/core/process/ProcessHQLQuery.java#l229 [^]

[3]
https://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/file/5754a065bc58/src/org/openbravo/retail/posterminal/master/Product.java#l247 [^]
https://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/file/5754a065bc58/src/org/openbravo/retail/posterminal/master/Product.java#l271 [^]
https://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/file/5754a065bc58/src/org/openbravo/retail/posterminal/master/Product.java#l301 [^]

[4]
https://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/file/5754a065bc58/src/org/openbravo/retail/posterminal/master/BusinessPartner.java#l57 [^]

Steps To Reproduce

Create a database which would return products for all product queries in the product master

Set the limit in the request for products to something small
- Find this line:
handleIncrementalRequest(35000, 0, params, incremental);
and change it to:
handleIncrementalRequest(20, 0, params, incremental);

clear cache and log in --> duplicate errors

Proposed Solution

The ProcessHQLQuery should not reset the limit or offset, they should be the same for each query to prevent duplicates and to prevent skipping data.

In BP/Product search you will get more results than the preference/default page size (each query will return max of that), but this is not too bad.

Additional Information