Malformed HQL when QTuple in where clause

Bug #810835 reported by Brad on 2011-07-15
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Querydsl
Fix Released
Undecided
Unassigned

Bug Description

I was trying to use QueryDsl to produce the following (working) SQL:

  SELECT status.cas_id,
  status.status_reason,
  status.status_date
FROM applicstat_apst status
WHERE (status.cas_id, status.status_date) IN
  (SELECT s.cas_id,
    MAX(s.status_date)
  FROM applicstat_apst s
  GROUP BY s.cas_id
  )

Its purpose is to select the most recent ApplicationStatus, which is a One-To-Many bidirectional list on another entity called CaseRecord, where most recent is not the order of creation, but another Date field called statusDate.

What I came up with is:
List<Tuple> tuples = query()
   .from(qApplicationStatus)
   .where(subQuery()
      .from(qApplicationStatus)
      .groupBy(qApplicationStatus.caseRecord())
      .list(new QTuple(qApplicationStatus.caseRecord(),
          qApplicationStatus.statusDate.max()))
        .contains(new QTuple(qApplicationStatus.caseRecord(),
             qApplicationStatus.statusDate)))
   .list(new QTuple(qApplicationStatus.statusDate,
       qApplicationStatus.statusReason(),
       qApplicationStatus.caseRecord()));

Unfortunately this produces:
class java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: , near line 3, column 35 [select applicationStatus.statusDate, applicationStatus.statusReason, applicationStatus.caseRecord
from nz.govt.justice.domain.ApplicationStatus applicationStatus
where applicationStatus.caseRecord, applicationStatus.statusDate in (select applicationStatus.caseRecord, max(applicationStatus.statusDate)
from nz.govt.justice.domain.ApplicationStatus applicationStatus
group by applicationStatus.caseRecord)]

You can see that in the where clause, the tuple fields are not bracketed in the generated SQL, making it malformed.

FactoryExpressions like the QTuple are only allowed in the projection. I will add better error handling for this.

Changed in querydsl:
status: New → In Progress
Brad (brad-murray) wrote :

Is there any important reason why the Qtuples shouldn't be allowed in the where clause? The code so far comes so close to producing the desired valid sql.

The serializer recognizes from the QTuple only the fact that it is a FactoryExpression instance, it is difficult to customize the serialization to recognize all possible FactoryExpression types.

In your case you tried to use QTuple as a JPQL tuple expression, which is understandable. I will see what I can do.

Added support for QTuples to act as JPQL tuples in WHERE conditions.

Could you verify that this works for you with the latest snapshot 2.2.0.BUILD-SNAPSHOT? I just deployed it to our Maven repository.

Changed in querydsl:
status: In Progress → Fix Committed

Released in 2.2.1

Changed in querydsl:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers