parametrized 'in' predicate lists

[gavinking]

A very old and extremely widely-used feature of Hibernate that I had frankly just forgotten is not part of JPA is the ability to parameterize in arguments of the in predicate, for example:

s.createQuery("from Entity e where e.id in (:ids)")
        .setParameterList("ids", List.if(1, 2, 3))
        .getResultList(); 

The named parameter :ids gets expanded to multiple JDBC parameters ?, ?, ? when the "parameter list" is bound.

I think this is very much low-hanging fruit, and that we should add something similar to JPA. But I'm not sure that the way it's done in Hibernate is the most elegant option. How about something like this instead:

em.createQuery("select e from Entity e where e.id in (:id,...)")
        .setParameter("id", List.of(1, 2, 3))
        .getResultList(); 

Here it's the syntax of the query that identifies what's going on, instead of the method call.

Thoughts?

[beikov]

Am I missing something in your request or is this already covered by collection_valued_input_parameter?

[gavinking]

OMG that's crazy. So the BNF refers to an undefined rule named collection_valued_input_parameter, but:

• nowhere does the spec say what the syntax or semantics of that rule are (apparently you're just supposed to guess that it's a positional or named parameter), and
• nowhere does the spec mention how one can specify an argument to a collection_valued_input_parameter, nor even say what it is, nor even provide an example.

Apparently, in Hibernate we interpret this as you may pass a List (but not an array) to setParameter().

But that's actually completely undefined, as far as I can tell.

[gavinking]

So we should at least clarify the spec to make clear that this is allowed:

em.createQuery("select e from Entity e where e.id in :ids")
        .setParameter("ids", List.of(1, 2, 3))
        .getResultList(); 

[beikov]

The spec does not directly refer to the BNF rule, but AFAICS it does specify this: https://jakarta.ee/specifications/persistence/3.1/jakarta-persistence-spec-3.1#input-parameters

[gavinking]

OK, but again, that doesn't actually specify anything.

In particular, it doesn't say how you bind multiple arguments to a "collection-valued input parameter", nor even say what a "collection-valued input parameter" actually is.

[gavinking]

"specify something" = explicitly state what the API user is allowed to do, and what the implementor must implement

[gavinking]

We should never have to guess what the authors of the spec intended.

And this is a perfect example of why we should always be explicit, and of why we should always write down clearly in words what our intention is: in this case I was myself one of the authors of the spec and that feature was almost certainly added at my explicit request. But because it's so unspecified, today I had to go and actually try it in Hibernate to figure out what the actual semantics were!

[lukasj]

100 may be related to this

[gavinking]

100 may be related to this

Yeah it's related. We would indeed as part of this need to specify explicitly whether empty list are allowed.