Query with case and special orderBy/offset/limit leads to 'Missing IN or OUT parameter at index'

[Heiko Kopp]

Hello,

we have a special use-case where we need to sort by database field with a variable sort order. Additonally, and importantly we display these task objects in pages, thus we use limit() and offset() to retrieve only a certain subset of rows.

The application uses QueryDSL 3.3.1 with JPA under WebSphere Application Server 8.0.0.8 (with OpenJPA) and Oracle 11g as underlying database.

Assume the following setup:

• We have a Task object, that contains a state field with the following elements 'V', 'N', 'G' and null.
• We need to sort the Task objects in the following way:
  
  OTHERS first
  followed by N
  followed by V
  followed by G

That means we can't simply use a standary orderBy(task.state.desc()). We therefore implemented a special case structure and do our list of tasks the following way:

final Expression<String> state 

    =  task.state.when("N").then("1")

                 .when("V").then("2")

                 .when("G").then("3")

                 .otherwise("0")

                 .as("vehicleState");

                

final StringPath asVehicleState = new StringPath("vehicleState");

final List<Tuple> dbResult = jpaQuery.orderBy(asVehicleState.asc()).offset(0).limit(18).list(task, vehicleState);

This however leads to the following error:

Missing IN or OUT parameter at index:: 4 {prepstmnt 442344100 SELECT * FROM (SELECT t0.PSF_ID AS c0, CASE WHEN t2.VTR_MOBILIENZUSTAND = ? THEN ? WHEN t2.VTR_MOBILIENZUSTAND = ? THEN ? WHEN t2.VTR_MOBILIENZUSTAND = ? THEN ? ELSE ? END AS vehicleState FROM MAKS.MAKS_POSTFACH t0, MAKS.MAKS_GENEHMIGUNG t1, MAKS.MAKS_VERTRAG t2 WHERE (t0.PSF_GEN_ID IS NOT NULL AND t0.PSF_WV_DATUM_AKTUELL <= ? AND t0.PSF_GESCHAEFTPROZESS = ?) AND t0.PSF_GEN_ID = t1.GEN_ID AND t1.GEN_VTR_ID = t2.VTR_ID ORDER BY vehicleState ASC, t0.PSF_ID ASC) WHERE ROWNUM <= ? [params=(Timestamp) 2014-04-29 08:13:58.041, (int) 2, (long) 18]}

I've configured JPA to display the parameters given and there are only three parameters given (a timestamp, and integer and a long value).

If I remove the offset and limit (which is out of question, as the table contains hundred thousands of tasks), the query works as expected. 

Can somebody please help to figure out what we are doing wrong here or if this might be a bug.

Thank you very much

Heiko

[timowest]

Hi.

Could you also log the JPQL query? Because that's what Querydsl gives to OpenJPA.

Timo

[Heiko Kopp]

Hello,

of course I can do that. Its actually not so easy cause how do I print the query created from list(a, b) ? ... I managed to find the JPQL query created 

AbstractJPAQuery.java (method list()).

The query is:

select dbTask, case when dbTask.state = ?1 then ?2 when dbTask.state = ?3 then ?4 when dbTask.state = ?5 then ?6 else ?7 end state

from DbTask dbTask

where dbTask.approval is not null and dbTask.currentFollowUpDate <= ?8 and dbTask.businessProcess = ?9

order by state asc, dbTask.id asc

The bound parameters look correct. They include: 

Parameter<Date>(8)=Wed Apr 30 11:26:10 CEST 2014, Parameter<String>(5)=G, Parameter<Object>(2)=1, Parameter<int>(9)=2, Parameter<String>(3)=V, Parameter<Object>(4)=2, Parameter<String>(1)=N, Parameter<Object>(7)=0, Parameter<Object>(6)=3

I have the assumption that QueryDSL afterwards adds the limit() and offset() and that somehow confuses JPA, because the real SQL-Query send to oracle only contains two bind values.

Any ideas?

Regards,

Heiko

[Timo Westkämper]

Querydsl uses setMaxResults and setFirstResult on the JPA query to set limit and offset. This appears to be a bug on a lower level than Querydsl.

Did you try to create and execute the JPA query without Querydsl?

--
You received this message because you are subscribed to the Google Groups "Querydsl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to querydsl+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Timo Westkämper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

[Heiko Kopp]

Hello Timo,

thanks for leading in to the right direction.

We had used a simple JQPL before changing to QueryDSL which worked. However, there is a slight difference. QueryDSL rewrites the with.then.else construct to use parameters, while we simply used constant values.

Apparently, when a select alias contains parameters, OpenJPA does not copy these into the final parameter list of the SQL-Buffer used for the select-Statement. After further investigation, this error has been corrected with OPENJPA-2131 (JIRA) for Version 2.3.0 - unfortunately WebSphere uses an older version. The error occurs within the DB Dictionary used for Oracle, so we had a chance to use the patch applied to the JIRA-Issue and created a patched version of the dictionary.

We will nevertheless open a PMR with IBM to have them correct this issue and back port the JIRA bugfix into their used versions.

Thanks again,

Regards,

Heiko