[
https://issues.apache.org/jira/browse/CAY-2879?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
lallemand updated CAY-2879:
---------------------------
Description:
*Description:*
Since upgrading from {*}4.1.1 to 4.2.1{*}, negative numbers are no longer
processed correctly when executing queries. The issue occurs in
{*}performQuery{*}, where negative values are unexpectedly converted into their
positive counterparts.
----
*Affected Code:*
{code:java}
public static RiskScoreTemplate getForName(String pm_name, String pm_zoneId)
{
ObjectSelect<RiskScoreTemplate> query =
ObjectSelect.query(RiskScoreTemplate.class,
ExpressionFactory.exp(NAME.getName() + " = '" + pm_name + "' and " +
DELETED.getName() + " = 0 and " + ZONE.getName() + " = " + pm_zoneId));
//query.setName("RiskScoreTemplateListQuery");
query.setCacheStrategy(QueryCacheStrategy.NO_CACHE);
ObjectContext context = BaseContext.getThreadObjectContext();
List<?> items = context.performQuery(query);
return (!items.isEmpty()) ? (RiskScoreTemplate)items.get(0) : null;
}{code}
*Generated SQL Query:*
{code:java}
exec sp_executesql N'SELECT t0.application_id, t0.deleted, t0.last_update,
t0.name, t0.type, t0.zone_id, t0.id FROM dbo.tRiskScoreTemplate t0 WHERE (
t0.name = @P0 ) AND ( t0.deleted = @P1 ) AND ( t0.zone_id = ( @P2 ) )', N'@P0
nvarchar(4000),@P1 int,@P2 int', N'SYSTEM', 0, 1{code}
*Function Input Values:*
{code:java}
pm_name = SYSTEM
pm_zoneId = -1
{code}
*Issue:*
Since version {*}4.2.1{*}, the negative value *pm_zoneId = -1* is being
converted to a *positive value (1)* when the query is executed. This results in
incorrect query results.
----
*Workaround:*
Using a parameterized query prevents the issue, as shown in the following
corrected code:
{code:java}
public static RiskScoreTemplate getForName(String pm_name, String pm_zoneId)
{
Map<String, Object> parameters = new HashMap<>();
parameters.put("name", pm_name);
parameters.put("deleted", 0);
parameters.put("zone_id", new BigInteger(pm_zoneId));
ObjectSelect<RiskScoreTemplate> query =
ObjectSelect.query(RiskScoreTemplate.class,
ExpressionFactory.exp(NAME.getName() + " = $name and " + DELETED.getName() + "
= $deleted and " + ZONE.getName() + " = $zone_id").params(parameters));
//query.setName("RiskScoreTemplateListQuery");
query.setCacheStrategy(QueryCacheStrategy.NO_CACHE);
ObjectContext context = BaseContext.getThreadObjectContext();
List<?> items = context.performQuery(query);
return (!items.isEmpty()) ? (RiskScoreTemplate)items.get(0) : null;
} {code}
While the solution above correctly handles negative values, our application
contains approximately *2,200 queries* that use the legacy implementation.
Since this method is still supported in the current version, the unexpected
behavior should be investigated and fixed rather than requiring a full
migration.
----
*Expected Behavior:*
* Queries using the legacy approach should continue to handle negative values
correctly, as they did in version {*}4.1.1{*}.
* Negative numbers should not be converted to positive values during
*performQuery* execution.
*Steps to Reproduce:*
# Use the original query method from version {*}4.1.1{*}.
# Pass a negative value for {{pm_zoneId}} (e.g., {{{}-1{}}}).
# Observe that the generated SQL query incorrectly converts {{-1}} to
{{{}1{}}}.
# Compare behavior with {*}4.1.1{*}, where negative values were preserved.
*Impact:*
* The issue affects *2,200+ queries* in our application, leading to incorrect
database results.
* Migration to parameterized queries would require significant refactoring.
* Potential data integrity and filtering issues.
*Suggested Fix:*
* Investigate changes in *4.2.1* related to {{performQuery}} and numeric value
handling.
* Ensure that the legacy query method correctly preserves negative values.
* If necessary, provide a configuration option to enforce legacy behavior
until a full migration is feasible.
* Investigate why ASTNegate node are incorrectly processed although expression
has identified it correctly.
----
*Additional Notes:*
* If a fix cannot be provided in a timely manner, documentation should clearly
state that legacy queries are no longer reliable for handling negative values.
* Any workarounds should be suggested to developers who are unable to migrate
immediately.
was:
*Description:*
Since upgrading from {*}4.1.1 to 4.2.1{*}, negative numbers are no longer
processed correctly when executing queries. The issue occurs in
{*}performQuery{*}, where negative values are unexpectedly converted into their
positive counterparts.
----
*Affected Code:*
{code:java}
public static RiskScoreTemplate getForName(String pm_name, String pm_zoneId)
{
ObjectSelect<RiskScoreTemplate> query =
ObjectSelect.query(RiskScoreTemplate.class,
ExpressionFactory.exp(NAME.getName() + " = '" + pm_name + "' and " +
DELETED.getName() + " = 0 and " + ZONE.getName() + " = " + pm_zoneId));
//query.setName("RiskScoreTemplateListQuery");
query.setCacheStrategy(QueryCacheStrategy.NO_CACHE);
ObjectContext context = BaseContext.getThreadObjectContext();
List<?> items = context.performQuery(query);
return (!items.isEmpty()) ? (RiskScoreTemplate)items.get(0) : null;
}{code}
*Generated SQL Query:*
{code:java}
exec sp_executesql N'SELECT t0.application_id, t0.deleted, t0.last_update,
t0.name, t0.type, t0.zone_id, t0.id FROM dbo.tRiskScoreTemplate t0 WHERE (
t0.name = @P0 ) AND ( t0.deleted = @P1 ) AND ( t0.zone_id = ( @P2 ) )', N'@P0
nvarchar(4000),@P1 int,@P2 int', N'SYSTEM', 0, 1{code}
*Function Input Values:*
{code:java}
pm_name = SYSTEM
pm_zoneId = -1
{code}
*Issue:*
Since version {*}4.2.1{*}, the negative value *pm_zoneId = -1* is being
converted to a *positive value (1)* when the query is executed. This results in
incorrect query results.
----
*Workaround:*
Using a parameterized query prevents the issue, as shown in the following
corrected code:
{code:java}
public static RiskScoreTemplate getForName(String pm_name, String pm_zoneId)
{
Map<String, Object> parameters = new HashMap<>();
parameters.put("name", pm_name);
parameters.put("deleted", 0);
parameters.put("zone_id", new BigInteger(pm_zoneId));
ObjectSelect<RiskScoreTemplate> query =
ObjectSelect.query(RiskScoreTemplate.class,
ExpressionFactory.exp(NAME.getName() + " = $name and " + DELETED.getName() + "
= $deleted and " + ZONE.getName() + " = $zone_id").params(parameters));
//query.setName("RiskScoreTemplateListQuery");
query.setCacheStrategy(QueryCacheStrategy.NO_CACHE);
ObjectContext context = BaseContext.getThreadObjectContext();
List<?> items = context.performQuery(query);
return (!items.isEmpty()) ? (RiskScoreTemplate)items.get(0) : null;
} {code}
While the solution above correctly handles negative values, our application
contains approximately *2,200 queries* that use the legacy implementation.
Since this method is still supported in the current version, the unexpected
behavior should be investigated and fixed rather than requiring a full
migration.
----
*Expected Behavior:*
* Queries using the legacy approach should continue to handle negative values
correctly, as they did in version {*}4.1.1{*}.
* Negative numbers should not be converted to positive values during
*performQuery* execution.
*Steps to Reproduce:*
# Use the original query method from version {*}4.1.1{*}.
# Pass a negative value for {{pm_zoneId}} (e.g., {{{}-1{}}}).
# Observe that the generated SQL query incorrectly converts {{-1}} to
{{{}1{}}}.
# Compare behavior with {*}4.1.1{*}, where negative values were preserved.
*Impact:*
* The issue affects *2,200+ queries* in our application, leading to incorrect
database results.
* Migration to parameterized queries would require significant refactoring.
* Potential data integrity and filtering issues.
*Suggested Fix:*
* Investigate changes in *4.2.1* related to {{performQuery}} and numeric value
handling.
* Ensure that the legacy query method correctly preserves negative values.
* If necessary, provide a configuration option to enforce legacy behavior
until a full migration is feasible.
* Investigate why ASTNegate node are incorrectly processed although expression
has identified it correctly.
----
*Additional Notes:*
* If a fix cannot be provided in a timely manner, documentation should clearly
state that legacy queries are no longer reliable for handling negative values.
* Any workarounds should be suggested to developers who are unable to migrate
immediately.
> Negative number for non parameterized ObjectSelect query not processed
> correctly
> --------------------------------------------------------------------------------
>
> Key: CAY-2879
> URL: https://issues.apache.org/jira/browse/CAY-2879
> Project: Cayenne
> Issue Type: Bug
> Components: Core Library
> Affects Versions: 4.2.1
> Environment: Windows 11 Java 17
> Reporter: lallemand
> Priority: Major
> Fix For: 4.2.2
>
>
> *Description:*
> Since upgrading from {*}4.1.1 to 4.2.1{*}, negative numbers are no longer
> processed correctly when executing queries. The issue occurs in
> {*}performQuery{*}, where negative values are unexpectedly converted into
> their positive counterparts.
> ----
> *Affected Code:*
> {code:java}
> public static RiskScoreTemplate getForName(String pm_name, String pm_zoneId)
> {
> ObjectSelect<RiskScoreTemplate> query =
> ObjectSelect.query(RiskScoreTemplate.class,
> ExpressionFactory.exp(NAME.getName() + " = '" + pm_name + "' and " +
> DELETED.getName() + " = 0 and " + ZONE.getName() + " = " + pm_zoneId));
> //query.setName("RiskScoreTemplateListQuery");
> query.setCacheStrategy(QueryCacheStrategy.NO_CACHE);
> ObjectContext context = BaseContext.getThreadObjectContext();
> List<?> items = context.performQuery(query);
> return (!items.isEmpty()) ? (RiskScoreTemplate)items.get(0) : null;
> }{code}
> *Generated SQL Query:*
> {code:java}
> exec sp_executesql N'SELECT t0.application_id, t0.deleted, t0.last_update,
> t0.name, t0.type, t0.zone_id, t0.id FROM dbo.tRiskScoreTemplate t0 WHERE (
> t0.name = @P0 ) AND ( t0.deleted = @P1 ) AND ( t0.zone_id = ( @P2 ) )', N'@P0
> nvarchar(4000),@P1 int,@P2 int', N'SYSTEM', 0, 1{code}
> *Function Input Values:*
> {code:java}
> pm_name = SYSTEM
> pm_zoneId = -1
> {code}
> *Issue:*
> Since version {*}4.2.1{*}, the negative value *pm_zoneId = -1* is being
> converted to a *positive value (1)* when the query is executed. This results
> in incorrect query results.
> ----
> *Workaround:*
> Using a parameterized query prevents the issue, as shown in the following
> corrected code:
> {code:java}
> public static RiskScoreTemplate getForName(String pm_name, String pm_zoneId)
> {
> Map<String, Object> parameters = new HashMap<>();
> parameters.put("name", pm_name);
> parameters.put("deleted", 0);
> parameters.put("zone_id", new BigInteger(pm_zoneId));
> ObjectSelect<RiskScoreTemplate> query =
> ObjectSelect.query(RiskScoreTemplate.class,
> ExpressionFactory.exp(NAME.getName() + " = $name and " + DELETED.getName() +
> " = $deleted and " + ZONE.getName() + " = $zone_id").params(parameters));
> //query.setName("RiskScoreTemplateListQuery");
> query.setCacheStrategy(QueryCacheStrategy.NO_CACHE);
> ObjectContext context = BaseContext.getThreadObjectContext();
> List<?> items = context.performQuery(query);
> return (!items.isEmpty()) ? (RiskScoreTemplate)items.get(0) : null;
> } {code}
> While the solution above correctly handles negative values, our application
> contains approximately *2,200 queries* that use the legacy implementation.
> Since this method is still supported in the current version, the unexpected
> behavior should be investigated and fixed rather than requiring a full
> migration.
> ----
> *Expected Behavior:*
> * Queries using the legacy approach should continue to handle negative
> values correctly, as they did in version {*}4.1.1{*}.
> * Negative numbers should not be converted to positive values during
> *performQuery* execution.
> *Steps to Reproduce:*
> # Use the original query method from version {*}4.1.1{*}.
> # Pass a negative value for {{pm_zoneId}} (e.g., {{{}-1{}}}).
> # Observe that the generated SQL query incorrectly converts {{-1}} to
> {{{}1{}}}.
> # Compare behavior with {*}4.1.1{*}, where negative values were preserved.
> *Impact:*
> * The issue affects *2,200+ queries* in our application, leading to
> incorrect database results.
> * Migration to parameterized queries would require significant refactoring.
> * Potential data integrity and filtering issues.
> *Suggested Fix:*
> * Investigate changes in *4.2.1* related to {{performQuery}} and numeric
> value handling.
> * Ensure that the legacy query method correctly preserves negative values.
> * If necessary, provide a configuration option to enforce legacy behavior
> until a full migration is feasible.
> * Investigate why ASTNegate node are incorrectly processed although
> expression has identified it correctly.
> ----
> *Additional Notes:*
> * If a fix cannot be provided in a timely manner, documentation should
> clearly state that legacy queries are no longer reliable for handling
> negative values.
> * Any workarounds should be suggested to developers who are unable to
> migrate immediately.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
