Incorrect reparameterization of a Cached prepared SQL statement with IN clause with a different number of elements

[areider <op...@reider.net>]

It appears the JPA2.0.2 fails properly reparameterize or evict a cached
prepared SQL statement with a collection valued parameter (eg an IN clause),
when the parameter has a different number of elements.

When this occurs, a warning "Parameter  has a value which is not compatible
with the available positions in the parameter list of the prepared query" is
logged. But I don't think this warning appears at the default tracing level.

The manual states:

"If a collection valued parameter across executions are set to different
number of elements, then the parameters of the cached SQL do not correspond.
If such situations are encountered while re-parameterizing the cached SQL,
the cached version is not reused and the original JPQL query is used to
generate a new SQL statement for execution."

What actually happens is that the result set of the query is incorrect and
appears to be a resultset consistent with a prior execution. So it appears
to use the cached query with previous parameters in whole or part, eg say
the reparameterization has 4 elements and the cached query only has 2
positions, so it simply fails to populate the 3rd and 4th, or maybe vice
versa, going from 4 to 2, the 1st 2 are replaced but the 3rd and 4th are
still there from the previous execution. Not sure the details, but it is
clearly not doing the reparameterizing correctly, and not generating a new
statement. Shutting of the caching makes it work, and also this issue did
not occur in JPA1.2.3

--
View this message in context: http://openjpa.208410.n2.nabble.com/Incorrect-reparameterization-of-a-Cached-prepared-SQL-statement-with-IN-clause-with-a-different-numbs-tp6387137p6387137.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Incorrect reparameterization of a Cached prepared SQL statement with IN clause with a different number of elements

[Michael Dick <mi...@gmail.com>]

Hi,

This is a bug, and was reported in 
https://issues.apache.org/jira/browse/OPENJPA-1845 OPENJPA-1845 . The result
of that bug report is to exclude queries that include an IN expression from
the cache with an informative message. This fix went into OpenJPA version
2.1, but not 2.0. 

Recently, an improvement JIRA has been opened to enable these queries in the
cache:  https://issues.apache.org/jira/browse/OPENJPA-2001 OPENJPA-2001 .

The SQL cache was extensively modified between OpenJPA versions 1.2 and 2.0
- which is probably why these queries worked on earlier releases. 

When I get a few cycles I'll look into porting OPENJPA-1845 back to 2.0. 

-mike


--
View this message in context: http://openjpa.208410.n2.nabble.com/Incorrect-reparameterization-of-a-Cached-prepared-SQL-statement-with-IN-clause-with-a-different-numbs-tp6387137p6395804.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.