[
https://issues.apache.org/jira/browse/CAY-2879?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17922402#comment-17922402
]
Nikita Timofeev commented on CAY-2879:
--------------------------------------
Thank you [~soliax] for reporting this. I could confirm that this is indeed a
regression in 4.2. This is due to a massive refactoring of the query to SQL
translation logic.
It should be fixed in 4.2.2.
> 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
> Assignee: Nikita Timofeev
> 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)
