Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0034669Retail ModulesWeb POSpublic2016-12-01 16:372016-12-22 16:38
Reportermtaal 
Assigned Tomigueldejuana 
PriorityimmediateSeveritycriticalReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionRR17Q1Fixed in VersionRR17Q1 
Merge Request Status
Review Assigned Tomarvintm
OBNetwork customer
Support ticket
Regression levelProduction - Confirmed Stable
Regression date2015-10-28
Regression introduced in releaseRR16Q1
Regression introduced by commithttps://code.openbravo.com/erp/pmods/org.openbravo.mobile.core/rev/7f56ee2e32ec [^]
Triggers an Emergency PackNo
Summary0034669: ProcessHQLQuery returns duplicates causing errors when inserting data and really slow login with many products
DescriptionThe 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 ReproduceCreate 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 SolutionThe 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
TagsNo tags attached.
Relationships
depends on backport 0034810RR16Q4.1 closed marvintm ProcessHQLQuery returns duplicates causing errors when inserting data and really slow login with many products 
caused by feature request 0031212RR16Q1 closed migueldejuana Add a preference to control how many products/bp/address/tickets are loaded when searching 
caused by defect 0033519 closed ranjith_qualiantech_com Masterdata loading fails when pagination is used 
has duplicate defect 0034672RR17Q1 closed Retail Default page size (35000) of initial data load is too high for high volume cases 
Attached Filespng Selection_032.png (648,311) 2016-12-01 16:37
https://issues.openbravo.com/file_download.php?file_id=10142&type=bug
diff issue34669MobileCore16Q4.diff (6,917) 2016-12-22 15:45
https://issues.openbravo.com/file_download.php?file_id=10213&type=bug
Issue History
Date ModifiedUsernameFieldChange
2016-12-01 16:37mtaalNew Issue
2016-12-01 16:37mtaalAssigned To => Retail
2016-12-01 16:37mtaalFile Added: Selection_032.png
2016-12-01 16:37mtaalRegression level => Production - Confirmed Stable
2016-12-01 16:37mtaalRegression date => 2015-10-28
2016-12-01 16:37mtaalRegression introduced in release => RR16Q1
2016-12-01 16:37mtaalRegression introduced by commit => https://code.openbravo.com/erp/pmods/org.openbravo.mobile.core/rev/7f56ee2e32ec [^]
2016-12-01 16:37mtaalTriggers an Emergency Pack => No
2016-12-01 16:38mtaalRelationship addedcaused by 0031212
2016-12-01 16:38mtaalRelationship addedcaused by 0033519
2016-12-01 16:43mtaalDescription Updatedbug_revision_view_page.php?rev_id=13932#r13932
2016-12-01 16:43mtaalSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=13934#r13934
2016-12-01 16:43mtaalProposed Solution updated
2016-12-08 16:14ranjith_qualiantech_comAssigned ToRetail => ranjith_qualiantech_com
2016-12-09 06:21ranjith_qualiantech_comAssigned Toranjith_qualiantech_com => Retail
2016-12-16 13:41hgbotCheckin
2016-12-16 13:41hgbotNote Added: 0092496
2016-12-16 13:41hgbotCheckin
2016-12-16 13:41hgbotNote Added: 0092497
2016-12-16 13:41hgbotStatusnew => resolved
2016-12-16 13:41hgbotResolutionopen => fixed
2016-12-16 13:41hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.mobile.core/rev/b08a151d8926837f4b917e77bad5f1be5c755565 [^]
2016-12-16 13:42migueldejuanaReview Assigned To => marvintm
2016-12-16 14:05marvintmAssigned ToRetail => migueldejuana
2016-12-22 12:18marvintmStatusresolved => new
2016-12-22 12:18marvintmResolutionfixed => open
2016-12-22 12:18marvintmStatusnew => scheduled
2016-12-22 12:18marvintmStatusscheduled => resolved
2016-12-22 12:18marvintmFixed in Version => RR17Q1
2016-12-22 12:18marvintmResolutionopen => fixed
2016-12-22 15:45migueldejuanaFile Added: issue34669MobileCore16Q4.diff
2016-12-22 16:38marvintmStatusresolved => closed
2016-12-23 14:26marvintmRelationship addedhas duplicate 0034672

Notes
(0092496)
hgbot   
2016-12-16 13:41   
Repository: erp/pmods/org.openbravo.mobile.core
Changeset: 6151c54f767b65b8c90ea1cdc324a15a40f27055
Author: Miguel de Juana <miguel.dejuana <at> openbravo.com>
Date: Thu Dec 15 13:55:25 2016 +0100
URL: http://code.openbravo.com/erp/pmods/org.openbravo.mobile.core/rev/6151c54f767b65b8c90ea1cdc324a15a40f27055 [^]

Related to issue 0033519 and related to issue 0034669

Backed out changeset c8dfc96d4a92

- This changeset is not needed, offset is not needed to update in non-masterdata queries. In fact it breaks recalculation of limit using totalRows instead of queryRows

---
M src/org/openbravo/mobile/core/process/ProcessHQLQuery.java
---
(0092497)
hgbot   
2016-12-16 13:41   
Repository: erp/pmods/org.openbravo.mobile.core
Changeset: b08a151d8926837f4b917e77bad5f1be5c755565
Author: Miguel de Juana <miguel.dejuana <at> openbravo.com>
Date: Fri Dec 16 10:15:37 2016 +0100
URL: http://code.openbravo.com/erp/pmods/org.openbravo.mobile.core/rev/b08a151d8926837f4b917e77bad5f1be5c755565 [^]

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

- Change masterdata batch size from 35000 to 10000
- For masterdata requests send isMasterdata = true
- Request to backend, Parameters starting with '_' are not query params so we will set them in data object
- Define a reference to be able to change masterdata batch size with a preference
- In models with more than one query, do not recalculate limit for next queries when the request is a masterdata request, just recalculate it for remote requests

---
M src-db/database/sourcedata/AD_REF_LIST.xml
M src/org/openbravo/mobile/core/process/ProcessHQLQuery.java
M web/org.openbravo.mobile.core/source/data/ob-datasource.js
---