Since we upgraded from Glassfish 3 to Glassfish 4.1 we've been getting this error. I'm trying to get data from a database but it fails most of the time. Yes, most of the time, not always. When I run it and it fails, it gives me this exception:
Exception Description: The query has not been defined correctly, the expression builder is missing. For sub and parallel queries ensure the queries builder is always on the left.
Query: ReportQuery(referenceClass=Employee )
at org.eclipse.persistence.exceptions.QueryException.invalidBuilderInQuery(QueryException.java:689)
at org.eclipse.persistence.internal.expressions.SQLSelectStatement.appendFromClauseToWriter(SQLSelectStatement.java:529)
at org.eclipse.persistence.internal.expressions.SQLSelectStatement.printSQL(SQLSelectStatement.java:1679)
at org.eclipse.persistence.internal.expressions.SubSelectExpression.printSQL(SubSelectExpression.java:267)
at org.eclipse.persistence.expressions.ExpressionOperator.printDuo(ExpressionOperator.java:2239)
at org.eclipse.persistence.internal.expressions.CompoundExpression.printSQL(CompoundExpression.java:286)
at org.eclipse.persistence.internal.expressions.RelationExpression.printSQL(RelationExpression.java:899)
at org.eclipse.persistence.expressions.ExpressionOperator.printDuo(ExpressionOperator.java:2234)
at org.eclipse.persistence.internal.expressions.CompoundExpression.printSQL(CompoundExpression.java:286)
at org.eclipse.persistence.expressions.ExpressionOperator.printDuo(ExpressionOperator.java:2234)
at org.eclipse.persistence.internal.expressions.CompoundExpression.printSQL(CompoundExpression.java:286)
at org.eclipse.persistence.internal.expressions.ExpressionSQLPrinter.translateExpression(ExpressionSQLPrinter.java:306)
at org.eclipse.persistence.internal.expressions.ExpressionSQLPrinter.printExpression(ExpressionSQLPrinter.java:129)
at org.eclipse.persistence.internal.expressions.SQLSelectStatement.printSQL(SQLSelectStatement.java:1683)
at org.eclipse.persistence.platform.database.PostgreSQLPlatform.printSQLSelectStatement(PostgreSQLPlatform.java:534)
at org.eclipse.persistence.internal.expressions.SQLSelectStatement.buildCall(SQLSelectStatement.java:782)
at org.eclipse.persistence.internal.expressions.SQLSelectStatement.buildCall(SQLSelectStatement.java:792)
at org.eclipse.persistence.descriptors.ClassDescriptor.buildCallFromStatement(ClassDescriptor.java:813)
at org.eclipse.persistence.internal.queries.StatementQueryMechanism.setCallFromStatement(StatementQueryMechanism.java:390)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.prepareReportQuerySelectAllRows(ExpressionQueryMechanism.java:1696)
at org.eclipse.persistence.queries.ReportQuery.prepareSelectAllRows(ReportQuery.java:1203)
at org.eclipse.persistence.queries.ReadAllQuery.prepare(ReadAllQuery.java:744)
at org.eclipse.persistence.queries.ReportQuery.prepare(ReportQuery.java:1071)
at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:661)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.checkPrepare(ObjectLevelReadQuery.java:901)
at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:613)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:867)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1751)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:469)
at com.example.LazyEmployeeSessionBean.getEmployeeByFilter(LazyEmployeeSessionBean.java:94)
If I go and take a look at that line of code, it gives me this:
public List<LazyEmployee> getEmployeeByFilter(final Map<String, String> filters, final String sortField, final Boolean sortOrder, final int first, final int pageSize, final ResourceBundle bundle) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<LazyEmployee> m = cb.createQuery(LazyEmployee.class);
Root<Employee> c = m.from(Employee.class);
Join<Employee, Timeregistration> c1 = c.join(Employee_.timeregistrationList);
m.multiselect(c, c1.get(Timeregistration_.checkout));
//filter
if(filters != null) {
//get all where statements
List<Predicate> list = this.getEmployeePredicateByFilter(cb, m, c, c1, filters, bundle);
if(!list.isEmpty()) {
if(list.size() == 1) {
m.where(list.get(0));
} else {
m.where(cb.and(list.toArray(new Predicate[0])));
}
}
}
//sort
if(sortField != null) {
if(sortOrder) {
m.orderBy(cb.asc(c.get(sortField)));
} else {
m.orderBy(cb.desc(c.get(sortField)));
}
} else {
//default sort: name + firstname
List<Order> orderBy = new ArrayList<Order>();
orderBy.add(cb.asc(cb.upper(c.get(Employee_.name))));
orderBy.add(cb.asc(cb.upper(c.get(Employee_.firstName))));
m.orderBy(orderBy);
}
//paginate
Query query = em.createQuery(m).setFirstResult(first).setMaxResults(pageSize);
List<LazyEmployee> employeeList = query.getResultList();
if(SLOGGER.isDebugEnabled()) {
SLOGGER.debug(query.unwrap(JpaQuery.class).getDatabaseQuery().getSQLString());
}
return employeeList;
}
private List<Predicate> getEmployeePredicateByFilter(final CriteriaBuilder cb, final CriteriaQuery<?> m, final Root<Employee> c, final Join<Employee, Timeregistration> c1, final Map<String, String> filters, final String site, final ResourceBundle bundle) {
final List<Predicate> list = new ArrayList<Predicate>();
//join => where t.id = (select max(id) from timeregistration tr where employee = u.id)
Subquery<Long> sq = m.subquery(Long.class);
Root<Timeregistration> r = m.from(Timeregistration.class);
sq.select(cb.max(r.get(Timeregistration_.id)));
sq.where(cb.equal(r.get(Timeregistration_.employee), c));
// list.add(cb.equal(c1.get(Timeregistration_.id), sq));
list.add(cb.in(c1.get(Timeregistration_.id)).value(sq));
//always filter by site
Site s = siteSessionBean.getSiteByName(site);
list.add(cb.equal(c.get(Employee_.depot).get(Depot_.site), s));
//specific filters
for(Map.Entry<String, String> entry : filters.entrySet()) {
try {
final String filterProperty = entry.getKey();
final String filterValue = entry.getValue();
if(filterValue != null) {
if(filterProperty.equalsIgnoreCase("keyDepot")) {
Depot d = lazyCommonSessionBean.getDepotByNameSite(filterValue, site);
if(d != null) {
list.add(cb.equal(c.get(Employee_.depot), d));
}
} else if(filterProperty.equalsIgnoreCase("keyName")) {
list.add(cb.like(cb.upper(cb.concat(cb.concat(c.get(Employee_.name), " "), c.get(Employee_.firstName))), "%" + filterValue.toUpperCase() + "%"));
} else if(filterProperty.equals("status")) {
if(filterValue.equalsIgnoreCase(EmployeeStatusEnum.IN.getStatus())) {
list.add(cb.equal(c1.get(Timeregistration_.checkout), cb.nullLiteral(Date.class)));
} else if(filterValue.equalsIgnoreCase(EmployeeStatusEnum.OUT.getStatus())) {
list.add(cb.notEqual(c1.get(Timeregistration_.checkout), cb.nullLiteral(Date.class)));
}
} else if(filterProperty.equals("keyActive")) {
if(filterValue.equals(bundle.getString("lbl_yes"))) {
list.add(cb.equal(c.get(Employee_.activeInactive), 'A'));
} else if(filterValue.equals(bundle.getString("lbl_no"))) {
list.add(cb.equal(c.get(Employee_.activeInactive), 'I'));
}
} else if(filterProperty.equals("e.role.exportRole.name")) {
list.add(cb.equal(c.get(Employee_.role).get(Roles_.exportRole).get(ExportRoles_.name), filterValue));
} else {
list.add(cb.like(cb.upper(c.<String>get(filterProperty)), "%" + filterValue.toUpperCase() + "%"));
}
}
} catch(AlgecoException e) {
SLOGGER.error("Inside exception getFilteredMessagePredicateByFilter", e);
}
}
return list;
}
The query we are trying to execute is the following:
SELECT u.*, t.check_out FROM employee u JOIN timeregistration t ON u.id = t.employee WHERE t.id = (SELECT max(id) FROM timeregistration tr WHERE employee = u.id)
The error is on this line:
List<LazyEmployee> employeeList = query.getResultList();
I've been looking for the past weekend, but I'm not able to find it.
As you can see, I've added in the getEmployeePredicateByFilter method another way to try and add the subquery. When I use the IN statement, the subquery does work, when I use the = statement, it doesn't.
So running this code on Glassfish 3.1.2.2 works perfectly, using Glassfish 4.1 it fails most of the time.
Does anybody have a solution for this problem?
Edit: We have found out that the problem is occuring at the subquery in the getEmployeePredicateByFilter method. When we use = it fails most of the time, but when we use IN it works every time. The problem is that the query with using IN takes about 1600ms to execute, while the original one takes about 70ms.
