[
https://issues.apache.org/jira/browse/CAY-2895?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18051465#comment-18051465
]
Nikita Timofeev commented on CAY-2895:
--------------------------------------
Linked a new issue: CAY-2910
> Incorrect Lazy Pagination Comparison for BigInteger PK
> ------------------------------------------------------
>
> Key: CAY-2895
> URL: https://issues.apache.org/jira/browse/CAY-2895
> Project: Cayenne
> Issue Type: Bug
> Components: Core Library
> Affects Versions: 4.2.2
> Reporter: lallemand
> Assignee: Nikita Timofeev
> Priority: Critical
> Fix For: 4.2.3, 5.0-M2
>
> Attachments: image-2025-09-01-14-24-18-956.png,
> image-2025-09-01-14-24-41-442.png, image-2025-09-01-14-26-02-296.png,
> image-2025-09-01-14-26-35-313.png, image-2025-09-01-14-26-45-722.png,
> image-2025-09-01-14-27-06-177.png, image-2025-09-01-14-27-12-756.png,
> image-2025-09-01-14-27-27-432.png, image-2025-09-15-14-00-38-120.png,
> image-2025-09-15-14-02-55-828.png, image-2025-09-15-14-22-47-825.png,
> image-2025-12-05-14-13-00-036.png, image-2025-12-05-14-13-14-697.png,
> image-2025-12-05-14-13-22-488.png, image-2025-12-05-14-13-32-527.png
>
>
> Dears,
>
> In the latest stable version ({*}4.2.2{*}), we are encountering an issue with
> the *Lazy implementation* based on {{ObjectSelect<T>.pageSize()}} when the
> table’s *primary key* is of type {*}BIGINT{*}.
>
> ----
> *Steps to Reproduce:*
> # Use {{ObjectSelect<T>}} with pagination ({{{}pageSize{}}}) on a table
> where the primary key column is of type {*}BIGINT{*}.
> # Allow the {{IncrementalFaultList}} in *cayenne-server* to populate
> elements.
> # Insert a new item into the dataset.
> ----
> *Technical Details:*
> * The issue occurs in {*}{{IncrementalFaultList.class}}{*}, specifically
> within the {{fillIn}} method, which initializes {{{}protected final List
> elements{}}}.
> * When processing a *BIGINT* database PK mapping, the type defaults to
> {*}{{Long}}{*}.
> * This works fine until a new item is added. At that point, the method
> {{updateWithResolvedObjectInRange}} is called, which in turn calls
> {{replacesObject}} (for one of six implementations).
> In our case, the class *{{SimpleIdIncrementalFaultList}}* is used.
> Example:
> * {{objectInTheList}} → value: {{{}137{}}}, type: *Long*
> * {{idSnapshot.get(pk.getName())}} → value: {{{}137{}}}, type: *BigInteger*
> *!image-2025-09-01-14-24-18-956.png!*
> Because of the type mismatch, the comparison fails, leading to the following
> exception:
> !image-2025-09-01-14-24-41-442.png!
> As a result, the list contains a *Long* object instead of the expected
> *ObjEntity* inside our lazy implementation{*}.{*}
> ----
> *Proposed Fix / Workaround:*
> I modified the {{replacesObject}} method to handle *BigInteger* correctly.
> !image-2025-09-01-14-26-02-296.png!
> * *First modification (blue section in my test code):*
> _Not recommended._ This forces replacement regardless of match. I added it
> only to bypass an unrelated bug I observed occasionally. This may indicate a
> deeper issue in the pagination implementation that requires further
> investigation.
> * *Second modification (red section in my test code):*
> _Required for correctness._ This ensures that *BigInteger* primary keys are
> compared properly with Long values.
> While this fix works, I believe a more elegant solution should exist within
> Cayenne’s type handling logic.
> ----
> *Test Configuration:*
> * *Databases:*
> Oracle
> !image-2025-09-01-14-26-35-313.png!
> SQL Server
> !image-2025-09-01-14-26-45-722.png!
> * *Entities:*
> {{DbEntity}}
> !image-2025-09-01-14-27-12-756.png!
> ObjEntity !image-2025-09-01-14-27-27-432.png!
> ----
> *Impact:*
> This is a {*}critical issue{*}: pagination becomes completely unusable when
> the primary key is of type {*}BIGINT{*}.
> ----
> *Edit 1:*
> I’ve discovered that the result of this function can sometimes be
> inconsistent if not enough time is allowed for it to complete. This behavior
> seems to be the root cause of the issue highlighted in blue in my previous
> review.
> !image-2025-09-15-14-00-38-120.png!
> Called by
> !image-2025-09-15-14-02-55-828.png!
> When I introduce a slight slowdown in the code execution, the behavior
> appears more stable during the first load. This is related to the issue
> “First modification (highlighted in blue in my test code)" section.
> My environment:
> * Arch Linux
> * Containerized databases (Oracle / SQL Server)
> The problem seems to be that the execution is sometimes “too fast,” which
> leads to certain items not being matched during the initial load. I’ve spent
> hours debugging but haven’t been able to identify the exact culprit. The only
> reliable observation so far is that slowing execution slightly improves
> stability (though this is clearly not a recommended fix).
> This suggests there may be an underlying timing, synchronization, async or
> readiness issue. Further investigation into the root cause would be helpful.
>
> ----
> *NB:*
> *I had to disable this test when using blue change, so I do understand that
> my fixe cannot be used as it, because it's seem to be breaking your logic
> greatly. But without the blue fix, I'm not able to have a stable environement
> on my side when using lazy.*
> !image-2025-09-15-14-22-47-825.png!
>
> ----
> *Edit 2:*
>
> Sorry for the delayed reply. I tested right after your fix and still
> experienced the same issue. I already suspected the problem originated on my
> side, so I continued my investigations this week, and here are the results.
> First of all, let me explain why this issue started. One year ago, we had a
> data-length incident with a customer. To be safe, I switched several Java
> types to *BigInteger* to “cover future growth.” At the same time, I updated
> the database schema:
> * SQL Server column changed from {{integer}} → {{bigint}}
> * Oracle column changed from {{integer}} → {{NUMBER(10,0)}}
> I then updated the Cayenne Modeler to reflect the {{bigint}} definition.
> This change is actually what caused the mismatch with Cayenne’s type-mapping
> logic. Cayenne correctly assumes that {{bigint}} should map to {{{}Long{}}},
> because {{bigint}} is conceptually a {{{}Long{}}}, not a {{{}BigInteger{}}}.
> So the issue originates entirely from my side: your code is consistent and
> database-agnostic, but my model/configuration was not. The confusion came
> from naming inside the modeler. As noted, DB {{bigint}} should map to Java
> {{{}Long{}}}, not {{{}BigInteger{}}}.
> ----
> h2. Solution 1 (works but undesirable)
> In the ObjEntity, explicitly map the attribute to the {{id}} db-attribute
> path (or rename):
> {code:java}
> <obj-attribute name="idNumber" type="java.math.BigInteger"
> db-attribute-path="id"/>{code}
> This forces the mapping to use the DB {{id}} field and works correctly.
> However, I do not like this solution because it requires declaring the {{id}}
> inside the ObjEntity, which is not acceptable for our model rules.
> !image-2025-12-05-14-13-00-036.png!
> ----
> h2. Solution 2 (recommended like explained above)
> Use \{{Long }}in the Java model where appropriate, because:
> * SQL Server {{bigint}} and Oracle {{NUMBER(10,0)}} never exceed {{Long}}
> range in our use-case.
> * This is cleaner and aligns with Cayenne’s default type resolution.
> I recommend switching to {{Long}} for every field where {{precision ≤ 19}}
> and {{{}scale == 0{}}}.
> The only drawback: this implies a large refactoring effort and testing, as
> our codebase is around {*}1.2 million lines{*}.
> ----
>
> While checking everything (and going a bit nuts), I found the following trail:
> Cayenne’s {{getJavaBySqlType()}} function implies that to support
> {{BigInteger}} directly on an ObjEntity attribute {*}without adding the ID
> field to the ObjEntity{*}, the underlying DbAttribute must be:
> * {{DECIMAL}}
> * {{maxLength ≥ 19}}
> * {{scale = 0}}
> {code:java}
> public static String getJavaBySqlType(DbAttribute attribute) {
> if(attribute.getType() == DECIMAL) {
> if(attribute.getScale() == 0) {
> // integer value, could fold into a smaller type
> if (attribute.getMaxLength() < 10) {
> return JAVA_INTEGER;
> } else if(attribute.getMaxLength() < 19) {
> return JAVA_LONG;
> } else {
> return JAVA_BIGINTEGER;
> }
> } else {
> // decimal, no optimizations here
> return JAVA_BIGDECIMAL;
> }
> }
> return SQL_ENUM_JAVA.get(attribute.getType());
> }{code}
>
> However, there is a problem:
> *The Cayenne Modeler currently prevents setting* {{{}scale = 0{}}}{*}.{*}
> This makes it impossible to reach the code path that selects
> {{{}BigInteger{}}}.
> This appears to be an unintended limitation, and led me into an additional
> rabbit hole.
> ----
> h2. Local fixes I applied (so the BigInteger path works)
> To validate and test the full logic, I applied three local modifications that
> allow BigInteger support without forcing the ID into the ObjEntity.
> h3. 1. *DbAttribute*
> * Allowed/preserved {{{}scale = 0{}}}.
> * Ensured {{maxLength}} (precision) is properly read/stored so
> {{getJavaBySqlType()}} can correctly determine when to use {{{}BigInteger{}}}.
> *Purpose:*
> Ensure DbAttribute correctly reflects precision and scale, and allow
> persisting \{{{}scale
> = 0{}}}.
> !image-2025-12-05-14-13-14-697.png!
> ----
> h3. 2. *XMLEncoder*
> * Ensured the encoder writes {{scale="0"}} into the Cayenne XML file.
> Previously, {{scale=0}} was omitted or filtered out.
> *Purpose:*
> Allow the model files to contain an explicit scale of 0 so Modeler and
> runtime behave consistently.
> !image-2025-12-05-14-13-22-488.png!
> ----
> h3. 3. *ProcedureParameter*
> * Added the same scale/precision preservation for stored-procedure
> parameters.
> *Purpose:*
> Align procedure parameter handling with DbAttribute logic so BigInteger
> mapping is possible for stored procedures as well.
> !image-2025-12-05-14-13-32-527.png!
> ----
> h2. Request
> Please review the three local fixes (DbAttribute, XMLEncoder,
> ProcedureParameter) and confirm whether:
> * They are acceptable for inclusion upstream,
> ----
> h2. Final summary
> The entire issue was caused by my decision to use BigInteger everywhere as a
> safety measure. In practice, the cleanest solution is to use {{Long}} when
> the DB uses {{bigint}} / {{{}NUMBER(precision <= 19, scale = 0){}}}. And only
> use {{bigint}} in Java side when set to {{decimal }}{{{}(length >= 19, scale
> = 0){}}}{{{{}}{}}}
> If Cayenne is expected to fully support {{bigint}} mapping through the
> modeler, then:
> * the modeler must allow saving {*}scale = 0{*}, and
> * the XMLEncoder must persist that value, which is precisely what my three
> local fixes restore.
>
> *Best regards,*
> Anton L.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
