Subqueries in the FROM clause

[beikov]

Up until now, HQL only supported scalar subqueries select * from ... where (select ... from ...) = ....
The proposal is to also support subqueries in FROM clause like e.g. select * from (select ... from ..) x.
This allows to logically separate concerns into smaller queries which helps dealing with complexity and increases reusability.

One of the fundamental questions that has to be answered is, how to represent the type of a subquery in the query model.
Currently, we represent FROM clause nodes as SqmFrom and SqmRoot which require a model type that is a subtype of javax.persistence.metamodel.Bindable and javax.persistence.metamodel.EntityType respectively.

The possible options for modelling this are:

• As an ad-hoc EntityType/ManagedType that is created on-demand per query. Something like a "tuple type"
• Require a user to model an explicit entity class and somehow bind a query to that type

Ad-Hoc type

The idea is, that during query parsing, we figure out the select item aliases and types of such subqueries in FROM clauses and construct an ad-hoc EntityType/ManagedType.
For every select item, we create an attribute in the ad-hoc EntityType/ManagedType with the respective select item result type.
The SqmFrom/SqmRoot object for this subquery then uses the ad-hoc type as model type.

Pros

• Requires less typing

Cons

• Might require cast to specify exact Java/SQL types for attributes
• Not usable with the JPA static metamodel because there is no Java model for the type
• Not able to return objects of that type in a result e.g. select e from (...) e

Explicit entity type

The idea is that a user has to explicitly declare a special kind of entity type, much like @Subselect entities.
In the query, the user then somehow binds the select items to the attributes of the special entity.
A possible syntax could be FROM entityName LP queryExpression RP alias (LP attributeNames+ RP)? which binds select items positionally to attribute names, or matches the select item aliases to attribute names.

In an example like select e from SpecialEntity(select n.id as id, n.name as label, 123 as number from Node n) e or select e from SpecialEntity(select n.id, n.name, 123 from Node n) e(id, label, number)
we bind the first select item to the attribute id, the second to label and the third to number.
The SpecialEntity could look like the following:

@Entity
@__Virtual // Name TBD
public class SpecialEntity {
    @Id
    Integer id;
    String label;
    Long number;
}

Pros

• Can specify exact Java/SQL types to use for result materialization of attributes
• Can declare associations to the "self type"
• Can generate a JPA static metamodel
• Can return objects of that type in a result e.g. select e from (...) e

Cons

• Requires to model an explicit type

Note that subqueries in a FROM clause are very similar to (IMO a subset of) CTEs. A CTE is like a database view, a query with a name, with the exception that it is bound to a main query within which it is declared.

[sebersole]

So at the base, an SqmPath (SqmFrom, SqmRoot, ..) needs a SqmPathSource.

So another option would be to:

1. limit SqmPathSource to exactly what capabilities we need for its role as path-source
2. define the sub-query as an SqmPathSource

Not sure it makes sense to allow such a subquery as a root however

[gavinking]

Thanks @beikov. So the way I naïvely view it is as follows: what is the type of a subquery expression? Or, phrased slightly differently, what is the type of a "table" expression?

An intuitive answer could indeed by that well, in Hibernate we represent tables as entity classes, so table expression are entities. And that's not a terrible idea, however, I fear it makes subqueries (and by extension CTEs) somewhat less useful, and actually isn't anyway a perfect fit.

1. We force the user to write a class to represent a table expression that they might very well only use in one HQL query. That's sort-of the equivalent of if SQL forced you to use create temporary table to define the schema of every SQL subquery.
2. Entities have well-defined primary keys, but SQL-like subqueries don't.
3. We would need some notion of an entity constructor, which neither HQL nor Hibernate currently has.

Indeed, in light of 2 and 3, one sees that if subquery expression corresponds to a class it actually corresponds much more closely to a HQL select new Thing(...), since select new is a construct which (a) maps to a constructor, and (b) doesn't require a key.

Unfortunately select new isn't quite right either, since you can't dereference a select new expression, and it doesn't really have a terribly well-defined type within the language. Here we need to be able to pick out the members of the thing.

But returning to 1, I really question whether we want these table expressions to map to Java classes at all. I think the most natural thing is to treat them as tuples, or more precisely record types. (A record type is a tuple where the tuple elements have names.)

So I would think that the following is rather natural

select xy.xid, xy.yid from (select x.id as xid, y.id as yid from X x join x.ys y) as xy

where the as xid bits assign aliases to elements of the "record" xy.

Now, it's true that you wouldn't be able to write:

select xy from (select x.id as xid, y.id as yid from X x join x.ys y) as xy

since we don't have a way to map records into Java.

But what is the actual use case for that anyway? I don't actually see it.

On the other hand, you could write:

select new XYIds(xy.yid, xy.yid) from (select x.id as xid, y.id as yid from X x join x.ys y) as xy

and all the other sorts of things you can already write in HQL select lists.

Hell, perhaps we could even let you write:

select xy.* from (select x.id as xid, y.id as yid from X x join x.ys y) xy

Though I'm still not at all clear on how that would be useful.

Now, on the other hand, what if the subselect select list does include an alias to an entity type? Well, then in that case, I think it would be legal to include it directly in the select list, for example:

select x.x from (select x as x, x.id as id from X x where x-name like :name) as x

But what's happening here is that we're picking the entity-typed member out of the record type.

[sebersole]

The only thing I'd quibble with here is the part about the "implementation detail that the query-author isn't even really aware of" part.

To me, that's the whole point of this... so the user does not have to write a class entity and can use an inline-view. But then we turn around and say that they in fact still have to write a class. If they are writing a class for this situation already, just map it as an entity and be done with it. They could even map it as a @Subselect

I really just do not understand this "explicit class" approach at all. It feels like I must be missing something.

[gavinking]

I think you've misunderstood me. So to clarify:

I hate the idea of an explicit class, since it changes the semantics of a HQL select clause. Currently, a HQL select gives you a tuple. Or, if you assign aliases with as, you can get something that's arguably a record.

I want to not change that. I want to say that subqueries or CTEs are also record types. If the easiest way to handle that on the implementation side is to kinda dynamically set up an anonymous synthetic entity (a MAP as you described it) then I guess that's perfectly fine, because it's essentially opaque to the query author. The query writer can't really tell the difference between a "record" and a "dynamic entity". To me the two concepts differ in the sense that one has a primary key and the other doesn't but I don't think the user would be able to detect that.

What I don't like is the notion that subquery select clauses would correspond to constructors of Java classes. I don't think we should do it like that.

[beikov]

A @Subselect entity uses a static SQL query as source whereas an explicit class could be used multiple times for different HQL subqueries. With @Subselect, you have to

• Define an entity class per subquery
• Can't use parameters i.e. it must be a static query
• Must use SQL

The idea of the explicit entity java class approach was to model the same idea, but get rid of these downsides. So you could have e.g.

@Entity
@__Virtual
public class SpecialEntity {
    @Id
    Integer id;
    String label;
    Long number;
}

And use it once with select e from SpecialEntity(QUERY1) e and another time time with select e from SpecialEntity(QUERY2) e, where QUERY1 and QUERY2 are both HQL.

[sebersole]

I think we are talking past each other to a degree.

I get what you are saying. You want the stuff that the inline view selects and "exports" to be modeled as a tuple of values. Again, I get that and I understand why.

When I said I must be misunderstanding, I meant I must be misunderstanding this feature or at least what about this feature indicates that we should have the user write an unmapped class to represent something they clearly intend to be ad-hoc (again, otherwise, physically write an entity and avoid this whole mess).

I am also missing a piece of information here. If it was covered in prior discussions above, I apologize for missing it. What do we expect to allow in this inline view? SQL? Some subset of HQL? Is the inline view's from clause explicitly based on an entity? I think the answers to these has an effect on my view of this.

Let's keep in mind that HQL and SQM deal with the domain model. Most importantly here, a from-element is "backed" by a (until now) physical part of the domain model. You are focusing on the select of the sub-query; I get that and that's fine. But I am starting from the sub-query's from-clause. For me, just like in SQL, we need to understand the from-clause before we can understand the select-clause. So I'm starting from there and would then like to extrapolate out what the exported selections look like for modeling.

Another aspect to this (and I mention it here because it bears on this exact part of the discussion) is how these inline views can be used. E.g. to me it makes no sense to define them as a root. So that leaves joins...

The SQM model expects any from-element to be backed by a managed-type or an attribute. I think that is perfectly reasonable and do not want that changed. So the question becomes how we can model the from-element backing in some domain model (ish) form. You even bring up a struct which maps nicely to a composite imo, which is actually what I was thinking of. That was the point above about allowing the more general ManagedType as the base of a from-element rather than the more specific entity-type as we do now. EmbeddableType is ofc also a ManagedType. A composite can also be dynamic btw

I already said above that I totally agree that the identifier is the biggest hole here trying to use EntityType. I agree that that is too much square page meeting round hole.

But to me, a composite would be a very reasonable and practical way to model this.

No matter what direction we choose though, I just think that forcing the user to write an "unmapped" class to represent this ad-hoc-ish thing is not reasonable. Again, my point is simply that if we are forcing them to write a class anyway why not just map that as an entity?

[sebersole]

And use it once with select e from SpecialEntity(QUERY1) e and another time time with select e from SpecialEntity(QUERY2) e, where QUERY1 and QUERY2 are both HQL.

Ah, I missed this part. Ok, yeah, I really do not like this then

[gavinking]

I mean, arguing purely on the basis of regularity, a select query always contains a list of things. Some of them can be entities. But we've never treated the query itself as being of an entity type.

[gavinking]

I mean, arguing purely on the basis of regularity, a select query always contains a list of things. Some of them can be entities. But we've never treated the query itself as being of an entity type.

What I'm trying to say here is, yeah, sure, SQL has this very nice closure property where the language consists of operators which map relations to relations. But HQL doesn't have that nice property, and I don't really see how it would make sense to try and shoehorn it in at this level.

[beikov]

My intention wasn't to require a query to treated as a kind of entity type, but about the type of a SqmRoot/SqmFrom for such a subquery.

[sebersole]

I mean, arguing purely on the basis of regularity, a select query always contains a list of things. Some of them can be entities. But we've never treated the query itself as being of an entity type.

True, but we do allow mapping an entity to a query via @Subselect. So we have some capability to deal with today. The identifier point is still problematic

[sebersole]

Again, I personally think that the proper model here is at the SQM level.

Sure that approach won't match nicely with the JPA contracts, but honestly who cares? This is explicitly something JPA does not support and so its not necessarily going to map to the JPA contracts cleanly.

BTW we have a similar problem with "entity joins" as well in terms of not mapping cleanly. In JPA a join is always based on an attribute. So we have to fudge some of the JP contract impls to account for that.

[sebersole]

So let's put this to a vote...

Which approach do we think is the best?

Also, who is going to implement this (because their opinion should have a certain weight to it)?

[beikov]

I can see value in supporting both approaches, but I am in favor of and willing to implement the explicit entity java class approach as I like the duality between SQL (everything is a relation) and ORM (everything is a managed-type/entity). IMO this also plays nicely with the fact that Java records are named types and not just their structure.

Having said that, if we can agree on a representation of the ad-hoc approach, I would also be willing to work on that, but only as an additional feature.

Vote +1/-1 if you are in favor of the explicit entity class approach.

[gavinking]

-1: it seems to me that it would be better to not have the feature yet, than to introduce it in this way.

(I guess I wouldn't mind if under the covers there was some sort of mapping to a "dynamic" entity type, but mapping to an explicit entity type just doesn't seem right at all.)

Until we can reach a consensus about how this should look to the user, we should simply not add the feature. This also comes with the great and wonderful advantage that not adding features means not doing work. So let's just work on something else, something we do agree on.

[gavinking]

Subqueries and CTEs are, after all, rather low-priority features in terms of getting H6 out the door, and it seems to me that we've already wasted too much time arguing about it.

Let's work on things which are needed in 6.0.

[dreab8]

I lean towards not using an explicit entity type, I think it would be also better from a user experience point of view not having to define a special entity.

[sebersole]

I'm going to agree with Gavin here in that I do not at all like this approach and if we cannot get a consensus and a "good" solution then we really ought to add this to the "wishlist" and come back to it later

[beikov]

I'll close this discussion for now, since the "dynamic" approach that everyone preferred was implemented as part of this PR: #5086

Further discussion can happen on that PR, or if we think this is not ready yet, reopen this here.