You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cayenne.apache.org by "lallemand (Jira)" <ji...@apache.org> on 2025/01/30 13:18:00 UTC
[jira] [Updated] (CAY-2879) Negative number for non parameterized ObjectSelect query not processed correctly
[ 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)