lallemand created CAY-2879:
------------------------------
Summary: 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
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)
