Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] bug? criteria query, "invalid query key in expression"

Can you try it in 2.3.3?
There was a fix in criteria code that may have fixed that.

On 4/23/2012 4:25 PM, Yannick Majoros wrote:
Hello,

A criteria query which used to work in 2.2.0 doesn't anymore in 2.3.2.

Sorry, it's quite long. Basically, a Subscription has a Client. I need
to find clients who had multiple valid supscriptions between a start and
an end date.

Here is the criteria query:

Date minSimultaneousSubscriptionDate =
clientSearch.getMinSimultaneousSubscriptionDate();
Date maxSimultaneousSubscriptionDate =
clientSearch.getMaxSimultaneousSubscriptionDate();
if (minSimultaneousSubscriptionDate != null ||
maxSimultaneousSubscriptionDate != null) {
// subquery 1: subscription begins between given dates
Subquery firstSubscriptionSubquery =
criteriaQuery.subquery(Subscription.class);
Root<Subscription> firstSubscriptionRoot =
firstSubscriptionSubquery.from(Subscription.class);

List<Predicate> firstSubscriptionPredicates = new ArrayList<Predicate>();

// client for subquery 1
Path<Client> firstSubscriptionClientPath =
firstSubscriptionRoot.get(Subscription_.client);
Predicate firstSubscriptionClientPredicate =
builder.equal(firstSubscriptionClientPath, clientRoot);
firstSubscriptionPredicates.add(firstSubscriptionClientPredicate);

// start / end dates
List<Predicate> firstSubscriptionDatePredicates =
createSubscriptionAtDatePredicates(firstSubscriptionRoot,
minSimultaneousSubscriptionDate, maxSimultaneousSubscriptionDate);
firstSubscriptionPredicates.addAll(firstSubscriptionDatePredicates );

// subquery 2: second subscription overlapping first
Subquery secondSubscriptionSubquery =
criteriaQuery.subquery(Subscription.class);
Root<Subscription> secondSubscriptionRoot =
secondSubscriptionSubquery.from(Subscription.class);

// client for second subscription
List<Predicate> secondSubscriptionPredicates = new ArrayList<Predicate>();
Path<Client> secondSubscriptionClientPath =
secondSubscriptionRoot.get(Subscription_.client);
Predicate secondSubscriptionClientPredicate =
builder.equal(secondSubscriptionClientPath, clientRoot);
secondSubscriptionPredicates.add(secondSubscriptionClientPredicate);

// subscriptions of both subqueries must be different
Predicate firstAndSecondSubscriptionDifferentPredicates =
builder.notEqual(secondSubscriptionRoot, firstSubscriptionRoot);
secondSubscriptionPredicates.add(firstAndSecondSubscriptionDifferentPredicates);


// subscription dates of subscription 2 shoud overlap those of
subscription 1
Path<Date> firstSubscriptionStartDatePath =
firstSubscriptionRoot.get(Subscription_.startDate);
Path<Date> firstSubscriptionEffectiveEndDatePath =
firstSubscriptionRoot.get(Subscription_.effectiveEndDate);

Path<Date> secondSubscriptionStartDatePath =
secondSubscriptionRoot.get(Subscription_.startDate);
Path<Date> secondSubscriptionEffectiveEndDatePath =
secondSubscriptionRoot.get(Subscription_.effectiveEndDate);

Predicate firstSubscriptionNoEndDatePredicate =
builder.isNull(firstSubscriptionEffectiveEndDatePath);
Predicate secondSubscriptionNoEndDatePredicate =
builder.isNull(secondSubscriptionEffectiveEndDatePath);

Predicate secondSubscriptionStartsBeforeFirstEndsPredicate =
builder.lessThan(secondSubscriptionStartDatePath,
firstSubscriptionEffectiveEndDatePath);
Predicate secondSubscriptionEndsAfterFirstStartsPredicate =
builder.greaterThan(secondSubscriptionEffectiveEndDatePath,
firstSubscriptionStartDatePath);

Predicate secondSubscriptionStartPredicate =
builder.or(firstSubscriptionNoEndDatePredicate,
secondSubscriptionStartsBeforeFirstEndsPredicate);
Predicate secondSubscriptionEndPredicate =
builder.or(secondSubscriptionNoEndDatePredicate,
secondSubscriptionEndsAfterFirstStartsPredicate);

secondSubscriptionPredicates.add(secondSubscriptionStartPredicate);
secondSubscriptionPredicates.add(secondSubscriptionEndPredicate);

Predicate[] secondSubscriptionPredicateArray =
secondSubscriptionPredicates.toArray(new Predicate[0]);
secondSubscriptionSubquery.where(secondSubscriptionPredicateArray);

// subscription 2 exists for subscription 1
Predicate secondSubscriptionExistsPredicate =
builder.exists(secondSubscriptionSubquery);
firstSubscriptionPredicates.add(secondSubscriptionExistsPredicate);

Predicate[] firstSubscriptionPredicateArray =
firstSubscriptionPredicates.toArray(new Predicate[0]);
firstSubscriptionSubquery.where(firstSubscriptionPredicateArray);

// subscription 1 exists for client
Predicate firstSubscriptionExistsPredicate =
builder.exists(firstSubscriptionSubquery);
predicates.add(firstSubscriptionExistsPredicate);
}

Here is the sql generated in 2.2.0:

SELECT ... FROM subscription t0, client t1 WHERE (EXISTS (SELECT ? FROM
subscription t2 WHERE ((((t2.CLIENT_ID = t1.ID) AND
(t2.SUBSCRIPTION_START_DATE <= ?)) AND ((t2.effective_end_date IS NULL)
OR (t2.effective_end_date > ?))) AND EXISTS (SELECT ? FROM subscription
t3 WHERE ((((t3.CLIENT_ID = t1.ID) AND NOT ((t2.ID = t3.ID))) AND
((t2.effective_end_date IS NULL) OR (t3.SUBSCRIPTION_START_DATE <
t2.effective_end_date))) AND ((t3.effective_end_date IS NULL) OR
(t3.effective_end_date > t2.SUBSCRIPTION_START_DATE)))) )) AND (t0.ID =
t1.SUBSCRIPTION_ID)) ORDER BY t1.ID DESC LIMIT ?, ?
bind => [1, 2012-05-01 00:00:00.0, 2012-04-01, 1, 0, 50]|#]

Here is the error I get in 2.3.2:

Exception [EclipseLink-6015] (Eclipse Persistence Services -
2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.QueryException
Exception Description: Invalid query key [effectiveEndDate] in expression.
Query: ReportQuery(referenceClass=Client )
at
org.eclipse.persistence.exceptions.QueryException.invalidQueryKeyInExpression(QueryException.java:670)

at
org.eclipse.persistence.internal.expressions.QueryKeyExpression.validateNode(QueryKeyExpression.java:851)

at
org.eclipse.persistence.internal.expressions.FunctionExpression.normalize(FunctionExpression.java:392)

at
org.eclipse.persistence.internal.expressions.CompoundExpression.normalize(CompoundExpression.java:218)

at
org.eclipse.persistence.internal.expressions.CompoundExpression.normalize(CompoundExpression.java:226)

at
org.eclipse.persistence.internal.expressions.CompoundExpression.normalize(CompoundExpression.java:218)

at
org.eclipse.persistence.internal.expressions.SQLSelectStatement.normalize(SQLSelectStatement.java:1300)

at
org.eclipse.persistence.internal.expressions.SubSelectExpression.normalizeSubSelect(SubSelectExpression.java:197)

at
org.eclipse.persistence.internal.expressions.ExpressionNormalizer.normalizeSubSelects(ExpressionNormalizer.java:93)

at
org.eclipse.persistence.internal.expressions.SQLSelectStatement.normalize(SQLSelectStatement.java:1373)

at
org.eclipse.persistence.internal.expressions.SubSelectExpression.normalizeSubSelect(SubSelectExpression.java:197)

at
org.eclipse.persistence.internal.expressions.ExpressionNormalizer.normalizeSubSelects(ExpressionNormalizer.java:93)

at
org.eclipse.persistence.internal.expressions.SQLSelectStatement.normalize(SQLSelectStatement.java:1373)

at
org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.buildReportQuerySelectStatement(ExpressionQueryMechanism.java:586)

at
org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.buildReportQuerySelectStatement(ExpressionQueryMechanism.java:531)

at
org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.prepareReportQuerySelectAllRows(ExpressionQueryMechanism.java:1590)

at
org.eclipse.persistence.queries.ReportQuery.prepareSelectAllRows(ReportQuery.java:1296)

at
org.eclipse.persistence.queries.ReadAllQuery.prepare(ReadAllQuery.java:644)
at
org.eclipse.persistence.queries.ReportQuery.prepare(ReportQuery.java:1056)
at
org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:613)

at
org.eclipse.persistence.queries.ObjectLevelReadQuery.checkPrepare(ObjectLevelReadQuery.java:823)

at
org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:575)

at
org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:812)

at
org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1040)

at
org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:392)
at
org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1128)

at
org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2871)

at
org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1516)

at
org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1498)

at
org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1463)

at
org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:485)

at
org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:773)

at [my classes]

If I remove all correlating predicates, and put the "exists" predicate
on the client query instead of the first subscription subquery, it
doesn't complain and executes corresponding sql. Any correlation between
the two subqueries, and it goes wrong.

Best regards,

Yannick Majoros
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users


Back to the top