Query result "shape"

[sebersole]

This comes from the work done in 6.0 overall, but specifically in relation to #4890

Specifically concerned we do not always have the best outcomes for the shape of Query results and how uniqueness is applied. Much of this goes back to the feature added in 6.0 for selection of a single entity. To help best explain this, let's consider a simple Customer/Order domain model and ways we might query it. Some of the discussion comes down to "requested result type" also.

The main concern I have is that the same exact query will behave differently depending on the exact "requested result type". Let's look at a few examples...

Explicit entity selection

final String selectOrderHql = "select o from Order o where o.customer.country.id = 1";

You can define the result here as either a single selection (Order) or as a multi-select (Object[] or Tuple). And the outcome is different for each case in terms of uniqueness -

// Here, Orders will be uniqued
final List<Order> orders = session.createQuery( selectOrderHql, Order.class ).list();

// Here, however, they will not
final List<Order> orders = session.createQuery( selectOrderHql, Object[].class ).list();

This is the one that got me thinking about this in terms of a possible inconsistency.

Explicit scalar selection

This one (specifically selecting the entity versus selecting its id and potentially getting back different number of results) also made me think about it being a possible inconsistency.

final String selectOrderHql = "select o.id from Order o where o.customer.country.id = 1";

// neither of these will be uniqued

final List<Integer> ids = session.createQuery( selectOrderHql, Integer.class ).list();
final List<Object[]> ids2 = session.createQuery( selectOrderHql, Object[].class ).list();

Implicit selection

This is was the original use-case behind HHH-15133 (#4890). Specifically a query like

final String selectOrderHql = "from Order o join o.customer cust join cust.country c where c.id = 1";

Historically, Hibernate would return an array with an element for the root (Order) plus any joins (Customer, Country).

6.0 made the switch to instead return just the root (Order).

The work for HHH-15133 allows seamless handling of this simply by specifying the result type. E.g.

final String selectOrderHql = "from Order o join o.customer cust join cust.country c where c.id = 1";

// worth noting again - these will be unique
final List<Order> orders = session.createQuery( selectOrderHql, Order.class ).list();

// and these will not
final List<Object[]> tuple = session.createQuery( selectOrderHql, Object[].class ).list();

Discussion

The main point to discuss here in my opinion is the uniquing of results.

Do we just say, as a blanket statement, that we only implicitly unique the results when there is only a single entity selection and it is requested as the result type? Does the same hold true for implicit selections of a single entity? I am fine with this as it can, at least, be consistently applied and explained/documented. To be clear, both of these would be uniqued:

{
    final String selectOrderHql = "select o from Order o where o.customer.country.id = 1";
    final List<Order> orders = session.createQuery( selectOrderHql, Order.class );
}

{
    final String selectOrderHql = "from Order o where o.customer.country.id = 1";
    final List<Order> orders = session.createQuery( selectOrderHql, Order.class );
}

Agreed?

But why just unique single entity selections? Why not unique any single selection? E.g., why not unique this result as well?

{
    final String selectOrderIdHql = "select o.id from Order o where o.customer.country.id = 1";
    final List<Integer> ids = session.createQuery( selectOrderIdHql, Integer.class );
}

Overall the thing that bothers me is that these queries can all return different number of results which feels odd -

final String selectOrderHql = "select o from Order o where o.customer.country.id = 1";
final String selectOrderIdHql = "select o.id from Order o where o.customer.country.id = 1";

final List<Order> orders = session.createQuery( selectOrderHql, Order.class );
final List<Object[]> tuples = session.createQuery( selectOrderHql, Object[].class );
final List<Integer> ids = session.createQuery( selectOrderIdHql, Integer.class );

[beikov]

IMO we should apply the unique filter only for single entity selects OR not at all (if we prefer consistency).

Applying the unique filter for single entity selects makes sense IMO, because I think that when people use a single entity select, duplicity doesn't matter to them. If duplicity would matter, people could request the type Object[] or Tuple to disable the filtering. In the past, people used DISTINCT to activate the filtering. Since performance suffers though, we also added a hint so that the DISTINCT keyword doesn't have to be passed to SQL, and all of this for exactly this use case, single entity selects.

If we decide to not do unique filtering at all, we should re-introduce the DISTINCT hint.

Overall, I think it's manageable for users to understand that unique filtering is only enabled for single entity selects and that this can be overridden by using a requested type like Object[] or Tuple.

[sebersole]

I think that when people use a single entity select, duplicity doesn't matter to them. If duplicity would matter, people could request the type Object[] or Tuple to disable the filtering

I kind of reject that argument. What about Object[] or Tuple implies anything about uniqueness?

[dreab8]

In my opinion when the query selects just the entity id we should apply the distinct filter automatically as well.

[yrodiere]

I may be a bit dense, but... why would you need to "make" the result of these queries unique to begin with? Aren't these results already unique? I wouldn't expect duplicate orders in either case:

final String selectOrderHql = "select o from Order o where o.customer.country.id = 1";

// Here, Orders will be uniqued
final List<Order> orders = session.createQuery( selectOrderHql, Order.class ).list();

// Here, however, they will not
final List<Order> orders = session.createQuery( selectOrderHql, Object[].class ).list();

Are we talking about more complex queries than this, with explicit joins on mutli-valued associations? Or a more complex model with multiple rows per entity (Envers comes to mind)? Or about duplication caused by how the query is executed internally (implicit cross-joins for collections, ...)?

Also... this should be obvious, but considering that I didn't understand the rest, I'd rather check: the query using Object[] is supposed to return a List<Object[]>, right?

- final List<Order> orders = session.createQuery( selectOrderHql, Object[].class ).list();
+ final List<Object[]> orders = session.createQuery( selectOrderHql, Object[].class ).list();

[beikov]

e.g. select o.customer from Order o

[yrodiere]

Alright. Then if it's about opinions, I would personally not expect this query to implicitly apply a "DISTINCT", ever, regardless of the result type. If I want a "DISTINCT", I'd expect to be able to just state it in my query: select distinct o.customer from Order o.

I suppose automatically applying "DISTINCT" can be convenient at times, but considering there is no keyword for "NOT DISTINCT", it seems to me this would simplify a few use cases to complexify others; not sure the net result is positive.

Of course there may be constaints in the JPA spec, or backard compatibility constraints, that force use to apply "DISTINCT" implicitly in at least some cases. That would be a shame, though.

[beikov]

The unique filtering is new in 6.0 and currently only happens for single entity selects. The point about having no possibility to say "NOT DISTINCT" is why I think it's a bad idea to do the filtering for all single item selects. I think though, that it is a nice feature for single entity selects.

[yrodiere]

I think though, that it is a nice feature for single entity selects.

It can definitely be. But it can also be surprising, so I'm afraid it will hurt as many people as it helps.

Having to add "DISTINCT" to a query isn't that bad, IMO. On the other hand, having a query return distinct results while it's not what your query string expresses... I could see people wasting time tracking a bug because of this behavior :)

Also, I agree with Steve that mixing up the result type (per-row) with "DISTINCT" (multi-row) seems confusing.

[yrodiere]

Also, I agree with Steve that mixing up the result type (per-row) with "DISTINCT" (multi-row) seems confusing.

Really, if you wanted to have the result type drive uniqueness, it would make more sense to have a set() equivalent to the existing list():

final String selectOrderHql = "select o from Order o where o.customer.country.id = 1";

// Here, Orders will be uniqued
final Set<Order> orders = session.createQuery( selectOrderHql, Order.class ).set();

// Here, however, they will not
final List<Order> orders = session.createQuery( selectOrderHql, Order.class ).list();

Not sure it's a great idea, but it seems more fitting, at least.

[sebersole]

Really, if you wanted to have the result type drive uniqueness, it would make more sense to have a set() equivalent to the existing list():

Not a bad idea in general. I very much dislike it returning a Set, so I'd probably rename it something like #uniqueList() - which allows use from JPA via a hint e.g.; returning a Set would never work with JPA #list.

Having to add "DISTINCT" to a query isn't that bad

But why? Specifically, why is a DISTINCT needed? What are these use cases that say I select a single item, but want to keep duplicates? Real use cases... Especially if that selection is an entity; to me any single selection. If I have multiple selections, perhaps I am defining a cross-join / product and want all pairs.. that's a valid argument for me and why I only applied it to single selections.

To me, a single selection wrapped in an array / Type is akin to syntactic sugar, mainly for backwards compatibility. If the consensus is that array / Tuple magically implies non-uniqueness, then so be it.

And really, if the consensus is that we should not do any implicit unique-ing regardless of the selected expressions (entity versus scalar, e.g.) or requested result type... again, so be it.

But then imo we should not do any in-memory unique-ing, period[1]. DISTINCT should just get passed to the database.

[1] Though @yrodiere's suggestion about a new method (plus a new matching hint imo) are good. This would trigger in-memory de-duplication

[beikov]

I really like the idea to introduce a new method. Feels more natural to me 😀

[sebersole]

I really like the idea to introduce a new method. Feels more natural to me grinning

👍

https://hibernate.atlassian.net/browse/HHH-15149

[yrodiere]

But why? Specifically, why is a DISTINCT needed?

To me "DISTINCT" should be explicit mainly because my basic assumption is that results are not distinct by default. That's what SQL does, after all.

When I run select o.customer from Order o, it stands to reason that I may get distinct duplicate customers. If I run select o.customer from Order o order by o.order_date DESC, I'd expect one customer per order, from the newer order to the older order, with nulls in-between, and potentially duplicate customers.

Really, my opinion is not based on what's useful or convenient, but about what's consistent, what's instinctive to me. Convenience is nice, but if it adds one more quirk to the library, one more "you would know if you RTFM", then it should pretty damn convenient. And sparing people one "DISTINCT" keyword is a small enough improvement that I don't think it's worth the trouble.

What are these use cases that say I select a single item, but want to keep duplicates? Real use cases... Especially if that selection is an entity; to me any single selection. If I have multiple selections, perhaps I am defining a cross-join / product and want all pairs.. that's a valid argument for me and why I only applied it to single selections.

Just the fact that "degenerate" queries such as select o.customer from Order o would behave strangely is enough to me. If you want real-world examples, I'm afraid I don't have one, from the top of my head.

But if just one example is enough to change your opinion, well... I would be very surprised if nobody in the world depended on this behavior one way or another.

[sebersole]

So consensus -

1. Add new #uniqueList method - https://hibernate.atlassian.net/browse/HHH-15149
2. #list does not perform any in-memory de-duplication
3. DISTINCT applies at the database level - we will simply use DISTINCT in the SQL select-clause.

[sebersole]

Another thing to consider... I've had to fix quite a number of tests regarding the now-removed de-duplication of single entity results. Where this is extremely useful is the case of collection fetches.

List<Customer> recentCustomers = session.createQuery( "select c from Customer c join fetch c.orders ..." );

This results in a List containing duplicate Customer entries, one per number of orders they have.

I think this is something we should not remove. This is beyond simply "useful or convenient" imo

[yrodiere]

Isn't this a more specific situation, though? The duplication here is no caused by what the user expressed, but by the join that Hibernate ORM is forced to add for the "join fetch", which leads to the side-effect of having a resultset that contains multiple rows for each customer.

I agree in this case, the query string would intuitively be assumed to return unique customers. The implicit de-duplication would make sense.

I would be inclined to say that de-duplication would feel more natural if it wasn't based on what's returned, but where what's returned comes from.

For instance, this would return one row per customer, and that's exactly what we return, so no de-duplication is necessary:

select c from Customer c

This should also return one row per customer, but the underlying query with a join fetch will lead to duplicates, so we need to de-duplicate customers:

select c from Customer c join fetch c.orders ...

In essence we need an in-memory select first(c.*) from <ResultSet> group by c.id.

Same goes here:

select c.id, c.name from Customer c join fetch c.orders ...

Here however, we should return one row per... o.customer, so one row per Order, which is exactly what we get, so still no de-duplication necessary:

select o.customer from Order o

And assuming the following is valid HQL, we would still need to return one row per o.customer, but because of the join fetch we don't. So what we need here is an in-memory select first(o.customer.*) from <ResultSet> group by o.id.

select o.customer from Order o join fetch o.customer.shops

I think the definition of "in-memory group-by" can be adapted to more complicated queries as well:

select o.customer, o.invoice from Order o join fetch o.customer.shops

Would lead to select first(o.customer.*), first(o.invoice.*) from <ResultSet> group by o.id. (again, I'm using pseudo SQL here but obviously I'm talking of an in-memory de-duplication process implemented by Hibernate ORM)

Essentially we would be de-duplicating rows, but using the root of the query as grouping key, not the full content of the rows. That, in my opinion, would be intuitive behavior.

Not sure how easy it would be to implement, though. I'm glad I'm not you 😁

[sebersole]

select c.id, c.name from Customer c join fetch c.orders ...

That is actually illegal.

[yrodiere]

Good, that's one fewer problem I guess :) Are the other examples illegal too?

[sebersole]

TBH, I am not sure how select o.customer from Order o join fetch o.customer.shops is translated. Its not illegal at all, just not sure if the to implicit-paths "match".

The others all look fine.

And yes, getting this all correct is not trivial.

[gavinking]

I just commented over on the issue, that I think dupe removal should only happen to undo the effect of a join fetch.

It should not happen in any other case.