Issue Details (XML | Word | Printable)

Key: NUCCORE-688
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Andy Jefferson
Reporter: Guido Anzuoni
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
DataNucleus Core

JDOQL/JPQL queries with subquery aren't cached with a unique key reflecting the subquery(ies)

Created: 30/Mar/11 04:04 PM   Updated: 01/Apr/11 03:23 PM   Resolved: 31/Mar/11 11:34 AM
Component/s: Queries
Affects Version/s: 2.2.3, 3.0.0.m2
Fix Version/s: 3.0.0.m3

File Attachments: 1. Text File dn-core-full-subq.patch (8 kB)
2. Text File dn-core-full.patch (15 kB)
3. Text File dn-rdbms-full.patch (3 kB)



 Description  « Hide
If query compilation cache is enabled, the key used to register the compiled query in the cache is
AbstractJPQLQuery.toString()
If the query contains a subquery, toString() returns something like:
select xx from ZZ where xx.someMember in DATANUCLEUS_SUBQUERY_1
this means that
select xx from ZZ where xx.someMember in (select kk.aMember from YY where <some condition>)
has the same key of

select xx from ZZ where xx.someMember in (select kk.aMember from AAAAA where <some other condition>)


Sort Order: Ascending order - Click to sort in descending order
Guido Anzuoni added a comment - 30/Mar/11 05:01 PM
The patch affects both compiled and datastore compiled cache.
The key used is the original JPQL statement with complete subqueries.
If, somehow, the statement is unavailable (query built via Criteria API ?),
AND there are subqueries no cache key can be calculated and so query is not cached

Guido Anzuoni added a comment - 30/Mar/11 05:03 PM
Here is the patch for JDOQL queries, assuming it suffers of the same problem.
Unfortunately I haven't the time to setup a test env to verify that the issue holds.

Guido Anzuoni added a comment - 30/Mar/11 05:10 PM
The JPA patch partially solves the issue described here:
http://www.datanucleus.org/servlet/forum/viewthread_thread,6607

In fact, subquery compiler did not receive subquery subqueries.
Now, the same test raises:

aused by: org.datanucleus.store.query.QueryCompilerSyntaxException: Query has variable "DATANUCLEUS_SUBQUERY_1" which is not bound to the query
at org.datanucleus.store.rdbms.query.QueryToSQLMapper.compile(QueryToSQLMapper.java:393)
at org.datanucleus.store.rdbms.query.QueryToSQLMapper.processVariableExpression(QueryToSQLMapper.java:3091)
at org.datanucleus.query.evaluator.AbstractExpressionEvaluator.compilePrimaryExpression(AbstractExpressionEvaluator.java:188)
at org.datanucleus.query.evaluator.AbstractExpressionEvaluator.compileUnaryExpression(AbstractExpressionEvaluator.java:169)
at org.datanucleus.query.evaluator.AbstractExpressionEvaluator.compileAdditiveMultiplicativeExpression(AbstractExpressionEvaluator.java:148)
at org.datanucleus.query.evaluator.AbstractExpressionEvaluator.compileRelationalExpression(AbstractExpressionEvaluator.java:123)
at org.datanucleus.query.evaluator.AbstractExpressionEvaluator.compileOrAndExpression(AbstractExpressionEvaluator.java:65)
at org.datanucleus.query.evaluator.AbstractExpressionEvaluator.evaluate(AbstractExpressionEvaluator.java:46)
at org.datanucleus.query.expression.Expression.evaluate(Expression.java:335)
at org.datanucleus.query.expression.DyadicExpression.evaluate(DyadicExpression.java:70)
at org.datanucleus.store.rdbms.query.QueryToSQLMapper.compileFilter(QueryToSQLMapper.java:437)
at org.datanucleus.store.rdbms.query.QueryToSQLMapper.compile(QueryToSQLMapper.java:357)
at org.datanucleus.store.rdbms.query.JPQLQuery.compileQueryFull(JPQLQuery.java:764)
at org.datanucleus.store.rdbms.query.JPQLQuery.compileInternal(JPQLQuery.java:270)
at org.datanucleus.store.query.Query.executeQuery(Query.java:1657)
at org.datanucleus.store.query.Query.executeWithMap(Query.java:1603)
at org.datanucleus.api.jpa.JPAQuery.getResultList(JPAQuery.java:173)

Andy Jefferson added a comment - 30/Mar/11 07:47 PM
You are caching the JDOQL string. What if the user then calls setFilter(), setGrouping() etc? does the cache key get updated?
Any reason for not just updating toString() ?

Guido Anzuoni added a comment - 30/Mar/11 11:05 PM - edited
OMG, Andy, you are right.
Well, the best solution would be the possibility the get a syntactically standard JDO single string filter
form a given a javax.jdo.Query.
As a intermediate solution, setGrouping, setFilter and any other set<Something> that would result in a query
change should set jdoqlString to null.
P.S.
Too much rust on my JDO knowledge. Damn!!!

P.S.
Anyway, this problem should not apply to JPQL.

Guido Anzuoni added a comment - 31/Mar/11 08:32 AM
What about having a protected void Query.queryChanged() that is invoked by any
setXXX method that would result in a query statement change ?
Subclasses can override the method to clear the cache key.
Anyway, I think that the right solution would be the reconstruction of a spec-compliant single string query

Andy Jefferson added a comment - 31/Mar/11 08:51 AM
Look at discardCompiled(); this is invoked on use of mutators, unsetting the "singleString".
I'd just update toString() to look for subquery "name" in the components of the query and use the subquery.toString() instead of the "name".

Guido Anzuoni added a comment - 31/Mar/11 10:22 AM
Full patch for JDO and JPA case.
The cache key now rebuild a spec-compliant single string query dreferencing the filter
(rather quick&dirty).
Fixed lack of datastore compilation in nested subqueries: AbstractXXXQuery recursively
adds compiled subquery to a parent compiler.


Andy Jefferson added a comment - 31/Mar/11 11:05 AM
Thx.

One case where it fails now is
test.jdo.datastore JDOQLSubqueryTest "testAPISubquery", where the query is

            Query q = pm.newQuery(Employee.class, "salary > averageSalary");
            q.declareVariables("double averageSalary");
            Query averageSalaryQuery = pm.newQuery("SELECT avg(salary) FROM " + Employee.class.getName());
            q.addSubquery(averageSalaryQuery, "double averageSalary", null);

i.e your method dereferenceFilter assumes that all subqueries have names starting "DATANUCLEUS_SUBQUERY", but they obviously don't with JDOQL. The name is defined in the subqueries map.

Andy Jefferson added a comment - 31/Mar/11 11:34 AM
Thx for the patches; now applied to SVN trunk.
Rewritten "dereference" method to be much simpler and to pass JDOQL tests

Guido Anzuoni added a comment - 01/Apr/11 08:28 AM - edited
Previous patch was buggy because it didn't manage apply parameters recursively and
it wrongly added subquery compilation to root instead of parent.
The attached patch is built against revision 12667

Andy Jefferson added a comment - 01/Apr/11 03:23 PM
All patches are in 3.0M3 release