[
https://issues.apache.org/jira/browse/CAY-2895?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
lallemand updated CAY-2895:
---------------------------
Description:
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.
was:
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}
{code}
*{{<obj-attribute name="idNumber" type="java.math.BigInteger"
db-attribute-path="id"/>}}*
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}
{code}
{{/**
* @param attribute to get java type for
* @return Fully qualified Java type name or null if not found.
* @see #getJavaBySqlType(int)
*
* @since 4.2
*/
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());
}}}
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.
> 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)
