Null is not zero: the one-character bug that invents energy data

There is a line of code, in some form, in almost every energy platform:

const consumption = reading?.kwh ?? 0;

It is defensive programming. It stops the page crashing when a value is missing. It is also, in an energy product, a fabrication — and it is the single most consequential defect class we know of in this industry.

Two different physical claims

null means we do not know what this building drew. The meter did not report, the gateway was offline, the interval has not settled yet.

0 means this building drew no power. That is a strong statement about the physical world. It is true when a site is de-energised and essentially never otherwise.

Coalescing the first into the second does not handle an error. It replaces an honest "unknown" with a confident falsehood, and every downstream consumer treats it as measured. What follows is the path that falsehood travels, with the arithmetic shown.

One gap, traced end to end

Take one building on an hourly electricity meter. August has 31 days, so 744 intervals are expected. A cellular gateway drops out between 03:00 and 09:00 on five nights, and ten further hours are lost in scattered singles: 40 missing intervals, 704 reported, 94.6% coverage.

Step 1 — the total

The 704 measured hours sum to 132,000 kWh. Zero-fill the 40 missing hours and the total is still 132,000 kWh, because zeros add nothing. The coalesce did not change the sum; it changed the claim attached to the sum. A total computed from 704 intervals is now presented as a total over 744, and nothing in the output records the difference.

Those were overnight hours on a building with a base load near 95 kW, so they would have contributed roughly 3,800 kWh. Actual August consumption was about 135,800 kWh. The reported figure is 2.8% low.

Step 2 — the average

Here the coalesce changes the number. Mean hourly load over the measured intervals is 132,000 / 704 = 187.5 kW. Over the zero-filled series it is 132,000 / 744 = 177.4 kW — 5.4% low, and it is the figure that gets compared against last year, against a sister site, and against a target.

Step 3 — the regression

Gaps do not confine themselves to reporting periods. Put the same five outage nights inside a 60-day baseline and fit a daily model against cooling degree days. Each affected day is short by 6 × 95 = 570 kWh of base load, so five points sit below the line — and if the gateway drops out on the hottest nights, when the cabinet it lives in is under most stress, those points sit at the end of the temperature range where they have most leverage on the slope. The fitted slope falls, and every saving later measured against that baseline is understated. Same defect, opposite direction.

The damage to the goodness-of-fit statistic is almost nothing. Five residuals of −570 kWh across 60 days, on a building averaging 4,400 kWh a day, contribute about sqrt(5 × 570² / 60) = 165 kWh of RMSE — roughly 3.7% CV(RMSE). That sits comfortably inside any acceptance band you are likely to be working to. The fit statistic will not save you: it measures how tightly the model tracks the data it was given, and it has no way to know that some of the data was invented.

Step 4 — the savings claim

This is where the money is. Suppose the adjusted baseline for the month is 141,000 kWh against reported consumption of 132,000 kWh. The apparent saving is 9,000 kWh, or 6.4%.

The real consumption was 135,800 kWh, so the real saving was 5,200 kWh, or 3.7%. Of the 9,000 kWh claimed, 3,800 kWh — 42% of the claim — was manufactured by a gateway that was switched off. Nobody made a decision to claim it. A null was coerced into a number at the edge of a chart component, and forty hours of silence became a performance result.

Step 5 — the carbon number

Emissions are consumption multiplied by an emission factor, so the error carries through at exactly the same proportion: the inventory is 2.8% short. The GHG Protocol Corporate Standard exists so that a company's inventory is "a true and fair account of their emissions". A total that silently treats absence as zero is not a true and fair account of anything; it is an account of the intervals that happened to arrive.

Step 6 — the assurance review

The reviewer asks for the underlying data. The platform exports the same zero-filled series that produced the report, so re-performance agrees — it repeats the same arithmetic on the same fabricated inputs. Only a check from outside finds it: the meter's own cumulative register, or the utility invoice. A platform that cannot export intervals with their gaps intact has removed the one piece of evidence that would have caught this.

Note what every step has in common. The output is plausible. Nothing renders as an error. No alert fires. The system is confidently, quietly wrong, and there is no artefact anywhere that says so.

Where it gets in: SQL

SQL's null handling is careful and well documented. That is why it catches people out: the defaults are safe, and the code written to "fix" them is not.

Aggregates already ignore nulls

The PostgreSQL documentation on aggregate functions states it plainly: "All these functions ignore null values in their aggregated input." So SUM(kwh) and SUM(COALESCE(kwh, 0)) return the same figure. The coalesce protects you from nothing. What it destroys is the ability to say how many intervals went into the answer:

SELECT SUM(kwh)              AS kwh_measured,
       COUNT(*)              AS intervals_present,
       COUNT(kwh)            AS intervals_measured,
       744                   AS intervals_expected
FROM readings
WHERE meter_id = $1
  AND ts >= $2 AND ts < $3;

COUNT(*) counts rows; COUNT(kwh), in the documentation's words, "computes the number of input rows in which the input value is not null". The gap between those two numbers, and the gap between either of them and the interval count you expected, is the only honest description of what the sum means.

The dangerous coalesce is the one around the aggregate

The same page notes that most aggregates "return a null value when no rows are selected" — "sum of no rows returns null, not zero as one might expect". That null is correct and useful: it is the database telling you it has nothing. Then somebody writes this to make a dashboard tidy:

SELECT COALESCE(SUM(kwh), 0) AS kwh FROM readings WHERE ...

and a meter dead for three months now reports that the building used no electricity in any of them. This is the worst version of the bug: the failure is total, and the output is a clean, confident number.

Averages over a gappy series

AVG(kwh) divides by the number of non-null values, which is usually what you want and never what you documented. It is an average over whatever arrived. Lose the overnight hours and it is biased upward; lose the afternoon peak and it is biased downward. An average without a coverage figure beside it does not tell you which.

Outer joins manufacture rows

To detect missing intervals at all you have to materialise the grid you expected and join to it. The PostgreSQL documentation on table expressions describes what a left join then does: "for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2."

WITH grid AS (
  SELECT generate_series($2::timestamptz, $3::timestamptz, interval '1 hour') AS ts
)
SELECT g.ts, r.kwh
FROM grid g
LEFT JOIN readings r ON r.ts = g.ts AND r.meter_id = $1;

That query is the right shape. The defect appears one edit later, when someone wraps the projection in COALESCE(r.kwh, 0) to keep nulls out of the result set — and the join built to expose forty missing hours invents forty readings instead. The tool for finding the gap became the tool for filling it.

Where it gets in: pandas and numpy

The Python data stack is more permissive than SQL, and its defaults are shaped for exploratory analysis rather than for producing numbers someone will sign.

• DataFrame.fillna(0) does exactly what it says — the documentation describes the value parameter as the "value to use to fill holes (e.g. 0)". No ambiguity, no bug in pandas: it is a deliberate instruction to fabricate, usually written to stop a plotting call complaining.
• DataFrame.mean() takes skipna as bool, default True: "exclude NA/null values when computing the result". So a mean over a gappy series is silently a mean over the surviving points, with no warning and no record of how many there were.
• DataFrame.sum() takes min_count as int, default 0. The documented consequence is stark: pd.Series([np.nan]).sum() returns 0.0. A series containing nothing but missing values sums to zero. Setting min_count=1 returns NaN instead, which is the honest answer, and almost nobody sets it.
• Resampling inherits that behaviour: bins with no observations become zero rather than missing, so a five-hour outage resampled to hourly yields five confident zeros.
• numpy is stricter, and it is worth noticing why: numpy.nanmean ignores NaNs by design, but for an all-NaN slice it "returns NaN and a RuntimeWarning is raised". It refuses, and says so. Copy that.

There is a second, quieter problem: NaN is a float, so introducing missingness can silently change a column's type on the way in and on the way back out at a database boundary. If you are going to represent absence, represent it as absence — not as a special value living inside the number space.

Where it gets in: the typed API layer

This is the one that survives review, because the typechecker endorses it. Declare the interface as { kwh: number } when the endpoint can actually return { kwh: number | null }, and the compiler goes green while the UI coerces missing data into a measurement.

With strictNullChecks off, TypeScript's documentation says, "null and undefined are effectively ignored by the language", which "can lead to unexpected errors at runtime". With it on, they "have their own distinct types and you'll get a type error if you try to use them where a concrete value is expected". Turning it on is necessary and not sufficient, because it checks your program against your declarations and has no opinion about whether those match the server. A hand-written interface is an unverified assertion; once it drifts from the route, the tool meant to protect you is the thing vouching for the error.

So we contract-test every response type against a real response captured from the real route, including one containing a null; and ?? is banned in the presentation layer on any measurement path, enforced by a check in continuous integration rather than a line in a style guide. A rule nobody can break accidentally is worth ten rules everybody agrees with.

Every sentinel fails, and each one fails differently

Zero is the most common substitute for a missing reading. The alternatives are not safer; they are worse in less obvious ways.

Substitute	Why someone chose it	How it fails
0	It is the additive identity, so totals "still work"	Indistinguishable from a genuine de-energised reading. Drags means, baselines and regression slopes downward. Survives every arithmetic check.
-999	Obviously not a real reading, so it will be caught	It is a number, and arithmetic does not know it is special. It is caught right up to the first aggregate nobody remembered to filter — where it is at least catastrophic rather than subtle, which is the only thing to be said for it.
Previous value (carry forward)	Continuity; the chart has no holes	Turns an outage into a plateau, and a plateau is exactly what a stuck sensor looks like. You have made the two most important faults identical. Any step change during the gap is reported late, at the wrong magnitude.
Series mean	Unbiased on average, so the total is "about right"	Makes the gap invisible by construction. Shrinks variance, inflates apparent goodness of fit, and understates the uncertainty on every coefficient computed from the series.
Linear interpolation	It looks like what the building probably did	Fabricates the diurnal shape that the analysis exists to measure. The longer the gap, the smoother and more convincing the invention. Hides precisely the events most worth seeing.

Each one puts a number where there was no measurement, and none carries a marker saying so. Imputation is a legitimate statistical technique. It stops being legitimate the moment its output is stored in the same column, with the same type, as a reading from an instrument.

The rule we build to

Measured, or refused. Never invented.

Concretely, that means our system holds the following, and enforces them in continuous integration rather than in a style guide.

Absence is representable end to end

The interval column is nullable, and a missing interval is either a null value or an absent row against a known expected cadence — never a zero, and never a row created in order to hold one. Absence crosses the API as null with a reason code beside it and reaches the interface intact. No layer in the stack quietly converts a null into something that renders.

Coverage travels with the number

An aggregate is not a scalar here. It is a value, its unit, intervals expected, intervals measured, and the coverage that follows. A monthly total from 94.6% coverage says 94.6% everywhere it appears: chart, export, PDF, API response. If a number can be copied into a slide without its coverage, the design has failed.

Refusal is a shippable outcome

Below a stated coverage threshold we do not produce a smaller, apologetic number. We produce no number and a statement of why: which meter, which intervals, what we think caused it. That is the correct result, and it is the only one that gives an operator something to act on. It also has to be designed first, because a system that can refuse is a different system from one with an error state bolted on.

The intervals are exportable, gaps included

Customers can pull the underlying intervals and reconcile them against their own meter, BMS or utility invoice. Gaps export as gaps. That is the check that would have caught step 6, and it only works if the export is of the same data the report used.

How to test for this, and how to prove the test works

Absence handling is easy to write, easy to believe, and easy to leave broken for a year. Three things make the difference.

1. A fixture with real holes in it

Not one missing point in a synthetic series, but the shapes that actually occur: a multi-hour outage, scattered singles, a fully dead meter, an intermittent meter, and an interval at the end of the window that has not settled yet. Each has a different correct answer, and a suite covering only the first passes on a system that gets the other four wrong.

2. Assert the denominator

Any sweep reporting "no absence defects found" must also report how many code paths, routes or series it examined, and how it knows that is all of them. A zero over an empty corpus is the most repeated failure we have seen in audit tooling, ours included. A check that cannot state its denominator is not evidence.

3. Mutate the control and watch it go red

A green check you have never seen fail is not evidence. Break the guard deliberately and confirm it stops the build. Three mutations, all of which we have seen defeat a first draft:

• The coalesce. Change the refusal path to value ?? 0. The test asserting that a gappy month refuses must fail. If it still passes, it is asserting on the wrong thing — most often on a formatted string that reads "0 kWh" either way.
• The comment-out. Delete the guard, or comment out the line that raises. A surprising number of checks are satisfied by the absence of the thing they check, because the assertion searches for a forbidden pattern and removing the caller removes the pattern too.
• The one character. Change coverage < MIN to coverage <= MIN, or flip a boundary from >= to >. Off-by-one on a coverage threshold is the version of this defect that ships, because it is right in every test that does not sit exactly on the boundary.

Then revert, and confirm green. A control you have watched fail and then watched pass is the only kind you are entitled to describe as working. Everything else is a claim.

"But users hate empty states"

They hate wrong numbers considerably more, and they find out later, in front of someone who matters.

An empty state saying "No reading — gateway offline 03:00–09:00, 6 of 24 hours missing" is more useful than a zero, because it names a fault someone can go and fix. The zero names nothing and gets averaged into a claim.

The uncomfortable part is that this rule makes your product look worse in a demo. A competitor showing a complete, smooth chart over the same gappy data wins the room. They are showing you a chart of their imputation policy. You just cannot see where it starts.

The defence is not to argue about aesthetics. Ask any platform, ours included, one question with a checkable answer: show me a month with a known outage, and show me what the export contains for those hours. If it contains numbers, you know what every savings figure and every carbon total from that system is partly made of.

More on this in practice: measurement integrity, why last year's kWh is not your baseline, and what a defensible savings claim requires under IPMVP. To find the gaps in your own data, start with the first-week integration checklist.