functions that range over collections

[gavinking]

So this is not so much a design proposal, as a clarification of what are supposed to be the semantics of all the functions we have for quantifying over collection elements, aggregating over elements, or for qualifying an element.

These come in three varieties:

• aggregation: maxelement() and friends,
• quantification: elements() and indices(), and
• element qualification: value(), index(), and key().

Aggregation

A function like maxelement(author.books) is supposed to :

• be applied to a many-valued attribute, and
• produce a single value per author.

It should be an error if it's applied to a collection element, as in:

//wrong!
select maxelement(b) from Author a join a.books b

That doesn't make any sense: b doesn't have elements. Furthermore, to respect the semantics, we would have to add a group by b.author_id to the generated SQL and we don't. So the query compiler should not accept this.

Quantification

A function like elements(author.books) is supposed to:

• be applied to a many-valued attribute, and
• produce a whole (subquery) result set that can be quantified over.

elements() and indices() were always meant to be quantified somehow! Either with:

• any/every,
• in, or
• exists

It does not make sense to write something like:

//wrong!
select elements(a.books) from Author a

Unfortunately, we started supporting this sort of query at some stage, and users have come to rely on it, and instead of telling them not to do it we've been fixing bugs relating to this usage. So for reasons of backwards compatibility we're going to have to keep accepting this as some sort of a weird way to write a join.

Qualification

A function like index() is supposed to:

• apply to an element of a collection, that is, directly to an alias, and
• produces a single value.

It does not make sense to write:

//wrong!
select index(a.books) from Author a

Apparently in at least some cases we currently interpret this as an implicit join followed by application of index() on the element, which is not the craziest interpretation if it works reliably, which I'm skeptical of.

(So perhaps we should discuss this one.)

Why am I writing all this?

I'm writing this up because in the new query parser there's currently little validation of any of this stuff, and so we need to sort that out.

Also because (totally my fault) there was never a spec for any of this stuff, so it's not surprising that everyone came up with their own interpretations. I can hardly blame them, since the maximum documentation they ever got was two lines in the old docs.

Worse, maxindex() and friends don't really work at all, and result in the addition of extraneous joins to the root query, and duplicate results.

(Note that none of these functions should ever add a join to the root query.)

I care about that because I want to expand the set of aggregate functions for collections, and let you write stuff like max(index my.list) and avg(value your.map).

[gavinking]

(So perhaps we should discuss this one.)

To be clear, perhaps we should allow things like:

select value(a.books).title from Author a

as a way to do implicit joins on collections, something we never supported before. It doesn't seem like a terrible idea to me.

[beikov]

I think the JPA spec says something about value, key and index only to be applicable to identification variables. At least the grammar and all examples in the spec suggest it to be this way:

map_field_identification_variable ::=
  KEY(identification_variable) |
  VALUE(identification_variable)

so IMO this should not produce an implicit join. De-referencing after key() or value() is useful and we do allow that already. At least for key() there are also tests.

[gavinking]

I think the JPA spec says something about value, key and index only to be applicable to identification variables.

Correct, that's what I said above.

But OTOH what the JPA spec says and what we decide to allow when not in strict-JPQL mode are different things. So in principle we could decide to allow this and document it as a feature.

But anyway, I was not really advocating for this change, just throwing it out there as something that we could think about for the future. I'm definitely not saying we should have it now.

For now my point is that: assuming we don't explicitly support this, we need to produce an error, not do something that's just wrong or not well-defined, which is the case with at least some of this stuff right now.

Because we really don't want users to start writing queries that rely on undefined behavior.

[beikov]

Worse, maxindex() and friends don't really work at all, and result in the addition of extraneous joins to the root query, and duplicate results.

I introduced that to allow reusing the same alias in multiple places. I guess you are referring to #4602 when you say this doesn't work? I will look into it.

[gavinking]

Great, yes, the test there should demonstrate one of the issues, but we will still need more tests to make sure we catch them all.

[beikov]

I care about that because I want to expand the set of aggregate functions for collections, and let you write stuff like max(index my.list) and avg(value your.map).

I don't really like that. IMO it's much clearer to use a correlated subquery e.g. select (select index(l) from my.list l order by index(l) desc limit 1) from ... for this purpose, but that's just my 2 cents.

[beikov]

My point here is that these functions are a nice syntax sugar, but the correlated subqueries are way mightier and much more explicit. I often didn't understand what min/max-index/element would use for the ordering and am at this moment still unsure what it does when applied to an entity collection. With the subquery, one is in full control and knows what is happening.

[gavinking]

Sure, of course, it's just a syntax sugar, that it mainly makes sense for @ElementCollections, but obviously can't do all the sorts of things you would do with subqueries. No argument there.

But writing a whole subquery just to get the max or sum of the elements of an @ElementCollection is a bit verbose and these functions are a pretty reasonable shortcut in that case.