A Number That Isn’t in the Ledger
Quick quiz. In an inventory management system, where is the ‘remaining quantity’ stored? Most people would picture a column like stock or remain_qty. So did I.
But in the system I help run (agricultural production history tracking, Spring Boot + JPA/Hibernate + MyBatis + PostgreSQL), the most important number of all, the ‘remaining stock of dried goods,’ isn’t stored in any table. The number that shows up on screen just fine every time is nowhere to be found, no matter where you dig in the database. While reading through those 245 MyBatis SELECTs one at a time, I ran into the query that solves the mystery: remaining stock isn’t a stored value. It’s a value SQL calculates from scratch every time you query it.
In warehouse terms, this system doesn’t ‘keep its stock in a ledger.’ Every time someone asks, it walks into the warehouse, picks up a scale, and does a physical count. This post dissects that one physical-count query from start to finish.
What Remaining Stock Really Is: Not a Column but a Formula
In this system, the remaining stock of dried goods (produce that has finished drying after harvest) is defined like this.
actual remaining = (weight after drying × 995/1000) ← 0.5% natural loss adjustment
- amount shipped out as packaging (sum of packaging)
- amount marked for disposal (sum of dispose_target)
- amount used as quality inspection samples (sum of drying_exam)
The domain’s physics shows up in the very first line. Even after drying, produce keeps losing a little weight as moisture evaporates. So the calculation starts by multiplying by 995/1000, shaving off 0.5% as natural loss. The rest is subtraction: take out what was packed and shipped, what was marked for disposal, and what was used as inspection samples, and you get the amount that should be sitting in the warehouse right now.
What’s interesting is where this rule lives. The formula isn’t anywhere in the Java code. Not in the service layer, not in the entities. One of this system’s core business rules exists ‘only inside the SQL in a MyBatis XML file.’ Which means that if you can’t read SQL, you’ll never find out how this system arrives at its remaining stock.
Dissecting the Query: Build Two Subtotals, Then Join Them
Here’s the skeleton of the real query (the table and column structure is unchanged from the original; I only cleaned up identifying details).
select coalesce(sum(
cast(x.after_weight as numeric)
- x.use_packing - x.use_exam - x.use_disp_target), 0)
from (
select d.after_weight,
coalesce(sum(cast(p.weight as numeric)), 0) as use_packing,
coalesce(t_disp.volume, 0) as use_disp_target,
coalesce(t_exam.use_exam, 0) as use_exam
from drying d
left join packaging p on p.drying_id = d.id
left join ( -- disposal subtotal per drying batch
select d.id, sum(cast(dt.volume as numeric)) as volume
from drying d left join dispose_target dt on dt.drying_id = d.id
group by d.id
) t_disp on t_disp.id = d.id
left join ( -- inspection sample subtotal per drying batch
select dqe.drying_id, coalesce(sum(cast(de.volume as numeric)), 0) as use_exam
from drying_exam de
inner join drying_qa_exam dqe on de.id = dqe.exam_id
group by dqe.drying_id
) t_exam on t_exam.drying_id = d.id
group by d.id, d.after_weight, t_disp.volume, t_exam.use_exam
) x
where coalesce(x.after_weight, 0) = 0
or ((x.after_weight * 995/1000) - x.use_packing - x.use_disp_target - x.use_exam > 0);
The first time I saw it, it looked like a wall. But read it one layer at a time, and the structure is simple: three layers, from the inside out.
‘Layer 1: two subtotal subqueries.’ The parenthesized SELECTs named t_disp and t_exam are temporary tables that prepare the ‘total disposal per drying batch’ and the ‘total sample amount per drying batch’ in advance. A SELECT placed inside the FROM clause like this is called an inline view. Don’t try to do a complex aggregation in one shot; build the subtotals first and join them like tables. That’s the first technique this query teaches.
‘Layer 2: attach them with LEFT JOIN.’ Why is every join a LEFT JOIN? Because drying batches that have never been packed, or that have no disposals, still need their remaining stock calculated. With an INNER JOIN, any drying batch without a packaging record would vanish from the results entirely. That’s a pretty scary bug: the batches with the most stock left evaporate from the list.
‘Layer 3: COALESCE and CAST.’ There’s a reason coalesce shows up six times in this query. A row with no match in a LEFT JOIN comes back with NULL, and in SQL, NULL contaminates arithmetic: 100 - NULL isn’t 0, and it isn’t 100. It’s NULL. coalesce(value, 0) is a safety catch that says “if it’s NULL, count it as 0.” Most queries that use LEFT JOIN and SUM without COALESCE are ticking time bombs. cast(... as numeric) takes values of mismatched types and unifies them into a single numeric type that supports exact decimal arithmetic: packaging weights and volumes stored as strings, and drying weights stored as floating point (double). Column types this inconsistent are another trace you run into fairly often in systems with some years on them.
Read through to the > 0 condition in the final WHERE, and the query’s purpose is complete: it picks out ‘only the drying batches that, once calculated, still have stock remaining.’
You can run this query yourself with the reproduction script. It includes three sample drying batches (one with stock left, one that comes out exactly to zero and gets excluded, and one with no weight entered), with hand-calculated expected values in the comments. Run it on PostgreSQL and you can confirm it returns the same values as the calculations in the comments.
Over on the JPA Side, the Entity Calculates Nothing
So what does the JPA entity that looks at the same data look like?
@Entity
public class Drying {
private double beforeWeight = 0.0; // weight before drying
private Double afterWeight = 0.0; // weight after drying
@OneToMany(mappedBy = "drying") private List<DisposeTarget> disposeTargets;
@OneToMany(mappedBy = "drying") private List<Packaging> packagings;
@OneToMany(mappedBy = "drying") private List<DryingQaExam> qaExams;
}
Two weights and three collections. Not a trace of remaining stock. It holds all the raw materials (the associations), but it doesn’t do any calculating. That’s entirely SQL’s job.
This contrast is also a real-world example of the division-of-labor rule I discovered in the first post. I said that lookups needing multi-table joins and aggregation belong to MyBatis, and this query is the star player of that category. For the record, multi-layer inline views and conditional aggregation like this are hard to port to JPQL, and even if you could port them, there’d be no reason to. This isn’t something to express as an entity graph. It’s pure set operations.
Store It, or Compute It?
That raises a design question. Is calculating remaining stock every time really the best approach? Wouldn’t lookups be far cheaper with a stock column that gets updated on every packing or disposal? The actual design document recorded three options.
| Option | Advantage | Cost |
|---|---|---|
| ① Aggregate dynamically at query time (current) | Always accurate: it’s recalculated from the source records every time, so it can never drift | Query cost. The aggregation runs on every call |
| ② Stock column + updates in the application | Fast lookups | If even one of the packaging, disposal, or inspection code paths forgets to update it, the ledger and the physical stock drift apart. And that bug piles up silently |
| ③ Trigger/scheduled updates | A middle ground | Adds new operational complexity: DB triggers and batch jobs |
In warehouse terms, ① is a physical count every time, ② is keeping a ledger, and ③ is a periodic stocktake. It’s not that one of them is the right answer. It’s a choice about what you’re willing to pay. This system is in a regulated industry, where the cost of ‘a wrong number’ dwarfs the cost of ‘a slow lookup,’ so it sticks with ①. For e-commerce inventory queried hundreds of times a second, on the other hand, the same logic would have tipped the scale toward ② or ③.
Back to my first instinct, ‘surely something like a stock quantity lives in a column’: whether to store or compute a derived value (a value calculated from other values) isn’t a given. It’s a design decision, and every option comes with a price tag.
Practical Advice: Questions to Ask When You Meet a Derived Value
- Where is this value’s ‘source of truth’? A stored column, a formula, or both? If both, has anyone decided which one to trust when they disagree?
- Check for COALESCE whenever LEFT JOIN meets aggregation. The accident where a NULL from an unmatched row turns an entire sum into NULL only surfaces after the data has piled up.
- If your system has business rules that live only in SQL, at least copy those formulas into a document somewhere. A rule like this system’s 0.5% adjustment was knowledge visible only to people who could read SQL.
Wrapping Up: The Weight of a Number
What I learned from dissecting one remaining-stock query was closer to an attitude than to syntax. Inline views and COALESCE are just tools in the end; the real question was ‘where does this number come from?’ Once you know that the most important number in a system is a formula rather than a column, even a single number on the screen starts to look different.
But these numbers’ work doesn’t end here. At the end of every month, these remaining-stock and disposal figures get called up into reports with per-vendor subtotals, grand totals, and columns lined up from January through December. The world of those reports is where JPA throws up its hands entirely and SQL plays on home turf.
