What happens to the result of a JPQL query that joins two different collection associations of the same entity in one statement, and how do you avoid the row explosion?

## Why the rows multiply A join is a relational operation on rows, not on objects. Joining `Order` to its `items` gives one row per item. Joining the same order to its `payments` as well gives one row per **combination**, because nothing relates an item to a payment — the database has no reason to pair item 1 only with payment 1. With `n` items and `m` payments you get `n × m` rows per order. Add a third collection and it is `n × m × k`. ``` select o from Order o join o.items i join o.payments p where o.id = :id ``` For an order with 5 items and 4 payments, this returns a list of 20 elements, all pointing at the same `Order` instance (the persistence context guarantees one managed instance per identifier, so it is one object, repeated). ## Why it matters more than "just duplicates" 1. **Aggregates are silently wrong.** `select o.id, count(i), sum(p.amount) from Order o join o.items i join o.payments p group by o.id` reports 20 items and four times the payment total. This is the classic "fan-out" bug: the numbers look plausible, so it ships. 2. **Volume.** The database materialises and transfers `n × m` rows. On an order with 200 items and 50 payments that is 10,000 rows for data that is really 250 rows. 3. **DISTINCT is not a fix.** `select distinct o` deduplicates the returned references, but the 20 rows were still produced, sorted and shipped. And distinct cannot undo an inflated `sum`. 4. **Pagination breaks.** Any row-based limit applies to the multiplied row set, so "first 20 orders" may be a fraction of one order. ## How to avoid it **Filter with EXISTS instead of joining.** If the second collection only appears in the WHERE clause, you don't need it in the FROM clause at all: ``` select o from Order o join o.items i where i.sku = :sku and exists (select 1 from Payment p where p.order = o and p.status = 'FAILED') ``` No multiplication, and the optimizer can short-circuit the subquery. `IS NOT EMPTY`, `SIZE(o.payments) > 0` and `MEMBER OF` are the compact spellings of the same idea. **One aggregate per query.** Compute item counts in one query grouped by order, payment sums in another, and combine in the application. Or use scalar correlated subqueries in the select list, each independent of the others. **One query per collection when you need the data.** Run the query for items, then the query for payments, both restricted to the same order ids. Because both return the *same* managed `Order` instances within one persistence context, the application sees a fully populated object graph without any Cartesian product. **Aggregate in a subquery and join that.** In HQL you can join a derived root (a subquery in the FROM clause) that has already collapsed one collection to one row per order, so the join no longer multiplies. ## The diagnostic habit When a count is inexplicably too large, or a result list has more elements than you expect, count the collection-valued joins in the query. One collection join multiplies by the collection size (usually acceptable, and removed by DISTINCT); two multiply by the product (rarely acceptable). To-one joins never multiply — they add at most one row per existing row — which is why `join o.customer c` is safe to add anywhere. ## A note on ordering and limits Because the multiplication happens in SQL, `ORDER BY` and `setMaxResults` operate on the inflated rows. If you must limit, restrict the root entities first — for example select ids with a non-multiplying query, then fetch details by id. That two-step shape is the general escape hatch whenever row multiplication and row limits meet. ## Summary rule Join a collection when you need its columns in the result. Use EXISTS when you only need to test it. Never join two collections of the same root in one statement unless you genuinely want every pair — and if you do, say so explicitly, because the next reader will assume it is a bug.