Integration tests routinely pass over code that issues one database query per returned row, and the defect only shows up in production. Why do the tests miss it, and how would you write one that fails when a new per-row query pattern is introduced?
answer
- results are correct -> no assertion can fail
- fixture entities still managed -> zero SQL in test
- 3 rows and one shared parent hides 1+N
- flush + clear, distinct rows, assert exact count
- best assertion: count invariant to N
basics
~20 sTests assert data, not statement counts; fixtures have two or three rows so the extra queries are invisible; entities are already in the persistence context from setup, so no SQL runs at all. Fix: clear the context, seed enough distinct rows, and assert a statement budget.
solid answer
~50 sFour things conspire: 1. **Assertions are about values.** A correct result is produced either way, so no assertion can fail. Nothing in the test observes SQL. 2. **Fixtures are tiny and low-cardinality.** With three parents pointing at one child, N+1 is 1+1 — and repeated parents are deduplicated by the persistence context anyway. 3. **Setup pollutes the persistence context.** If the test persisted the fixture in the same transaction, the associated entities are already managed, so dereferencing them emits *zero* statements. 4. **The session is open for the whole test**, so lazy access always succeeds; and an in-memory database makes hundreds of round trips too fast to notice. A test that catches it must: `flush()` then `clear()` (or use a fresh transaction) so nothing is pre-loaded; seed enough rows with **distinct** associations; and assert a **statement budget** using a counting DataSource wrapper (datasource-proxy/p6spy) or Hibernate's `getPrepareStatementCount()`. Assert an exact small number, not "fewer than a lot".
code
java · 12 lines@Test
void dashboardUsesAFixedNumberOfStatements() {
seedOrders(10, /* distinctCustomers = */ true);
em.flush();
em.clear(); // stop measuring first-level cache hits
statements.reset(); // counting DataSource wrapper
List<OrderView> result = service.loadDashboard();
assertThat(result).hasSize(10);
assertThat(statements.count()).isEqualTo(2); // exact budget, not "< 50"
}go deeper
Understand that tests assert values, and an N+1 returns the correct values — so nothing fails unless the test counts queries.
Name the concrete suppressors: tiny fixtures, shared associated rows, and the still-managed setup entities; know that flush plus clear removes the last one.
Design the test — counting DataSource, cleared context, distinct rows, exact budget — and choose which paths deserve budgets rather than applying them everywhere.
Make fetch cost an explicit part of the contract for read endpoints, enforced in CI, with a review norm that changing the budget requires justification.
## Why the defect passes review and CI An N+1 produces **correct results**. Every assertion about returned data passes. The only observable difference is how many statements were sent, and a normal test never looks at that. So the defect is invisible by construction unless the test is specifically designed to see it. On top of that, three properties of typical test setups actively suppress the symptom. ### 1. Fixtures are small and low-cardinality A fixture with three orders is 1+3 = 4 statements. Nothing looks wrong. Worse, fixtures usually share associated rows — three orders for one customer — and the persistence context returns the already-loaded customer without SQL, so the count is 1+1. Real data has thousands of parents and high cardinality, so nearly every row triggers its own statement. ### 2. The persistence context is already warm This is the biggest and least-noticed one. A test that persists its fixture and then calls the service **in the same transaction** leaves every fixture entity managed. When the code under test dereferences an association, Hibernate finds the instance in the first-level cache and issues **no SQL at all**. The test therefore demonstrates the opposite of production behaviour: zero extra queries where production has hundreds. The remedy is to force a boundary: `em.flush(); em.clear();` after setup, or commit the fixture and run the exercise in a fresh transaction, or persist the fixture through a separate mechanism entirely. ### 3. Everything is fast and the session never closes An in-memory or local database has sub-100-microsecond round trips, so 300 statements cost 30 ms and no timing assertion notices. And because the whole test runs inside one open session, lazy access never fails — so even the error-based signal (a lazy access outside a session) is absent. ### 4. Caching If a second-level cache is enabled in the test profile, repeated loads of reference data are served from cache and the statement count collapses further. ## Designing a test that actually fails The test must convert "how many statements did this take?" into an assertion. **Step 1 — instrument.** Wrap the `DataSource` with a counting proxy (datasource-proxy or p6spy) so you can count every statement regardless of ORM behaviour. Alternatively use `hibernate.generate_statistics=true` and read `getPrepareStatementCount()`, clearing before the exercise. The DataSource wrapper is preferable because it is ORM-agnostic and counts native queries too. **Step 2 — clean the slate.** After creating fixtures, `flush()` and `clear()` the persistence context (and evict the second-level cache if one is enabled). Otherwise you are measuring first-level cache hits. **Step 3 — seed realistic cardinality.** Create at least ~10 parents, each with a **distinct** associated row. Ten is enough to separate an exact budget from a linear pattern while keeping the test fast. **Step 4 — assert an exact budget.** Say the operation costs exactly 2 statements. An exact number documents intent and catches both directions of drift; "less than 50" tolerates a regression from 2 to 40. **Step 5 (optional but strong) — assert invariance to N.** Parameterise over 5 and 25 parents and assert the same count. This directly encodes the property that matters — statement count independent of result size — and cannot be satisfied by luck. ## Where to put these tests Not everywhere: a statement budget on every test is brittle and noisy. Put them on the read paths that matter — list endpoints, exports, batch steps — where the fetch plan is part of the contract. Treat a change to the number as a deliberate review conversation, exactly like a change to an API response. ## Related failure modes worth mentioning - A test whose assertions traverse the object graph *outside* the measured window can trigger loads that inflate the count; measure a tight region. - Tests that use a repository directly rather than through the service boundary may not exercise the real fetch plan. - A test asserting a plain `size()` on a collection can trigger a collection load in some mappings and not in others, so pick assertions deliberately. ## The summary line "Tests pass because the results are correct, the fixture is three rows, and the fixture entities are still in the persistence context — so the extra queries either don't happen or don't matter. To catch it, clear the context, seed distinct rows, and assert an exact statement count that must stay constant as the row count grows."
- Why does clearing the persistence context matter so much in such a test?Because entities persisted during setup remain managed, so dereferencing an association finds them in the first-level cache and issues no SQL whatsoever. The test then measures a scenario that never occurs in production, where the request starts with an empty context. Calling flush followed by clear — or committing the fixture and exercising the code in a new transaction — restores the production starting condition.
- Isn't asserting an exact statement count brittle?It is deliberately strict, and that is the point on the handful of read paths where the fetch plan is part of the contract. When the number changes, someone has to look at why, which is the same conversation you would want for a change in the response payload. Applying it to every test would indeed be noisy, so scope it to list endpoints, exports and batch steps.
saying these in an interview costs you the question
- Believing a passing integration test proves the fetch plan is correct.
- Seeding fixtures where many parents share one associated row and calling that representative.
- Measuring statements without clearing the persistence context after setup.
- Asserting a loose upper bound that a real regression would still satisfy.
- Assuming a fast in-memory database makes the round-trip cost irrelevant in production too.