Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
[eclipselink-users] bug? criteria query, "invalid query key in expression" 
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

Back to the top