SQL generated by odata-jpa-processor-cb is incompatible with Hibernate for scenarios where parameter ordinal order is not maintained.

[dbhandari555]

odata-jpa version used: 2.1.3 along with dependency on odata-jpa-processor-cb

Problem statement: We want to support pagination on expanded entities and for that we use the library provided odata-jpa-processor-cb extension. The SQL generated by the criteria builder has incompatibility issues with Hibernate because the criteria builder doesn't guarantee sequential parameter ordinals without gaps.

Let's take an example to demonstrate this
Given odata query : ("Organizations('1')?$expand=SupportEngineers($top=5)"))

The SQL native queries generated are attached in the file
queries generated for example odata.txt

The query of interest is the last one from the file that's referenced below:

SELECT DISTINCT E0."ID" S0, E1."Text" S1 FROM "OLINGO"."BusinessPartner" E0 INNER JOIN "OLINGO"."Comment" E1 ON (E0."ID" = E1."BusinessPartnerID") WHERE ((E0."ID" = ?5) AND (E0."Type" = ?6)) ORDER BY E0."ID" ASC

The above just has ordinal 5 and ordinal 6 as parameters(sno 1, 2, 3, 4 as they are not required for this query) and this is allowed in Eclipselink. Unfortunately the same kind of query when run on Hibernate results in "org.hibernate.QueryException: Gap in ordinal parameter positions; skipped 4" because Hibernate expects the ordinals to start from 1 and go incrementally up without any gaps for every SQL query.

@wog48 Since the only way to support pagination on expanded entities currently is through odata-jpa-processor-cb dependency and because this would affect major chunk of scenarios when the library dependency is used with Hibernate, is it possible to provide a fix?