A filter and its negation return 6,100 and 3,400 rows from a 10,000-row table — where are the other 500?

seniorshould knowfreq 45%

answer

the two passes should account for everything
negation does not rescue the third outcome
neither true nor false, twice over
the shortfall is the absence count
or they all land on the negated side

basics

~20 s

Those 500 rows hold no value in the column the condition tested. The comparison was neither true nor false for them, negating it leaves it neither, and a surface that coerces that outcome to false omits the rows from both passes.

solid answer

~50 s

They are the rows whose tested column holds nothing. A comparison against a cell with no value produces no usable truth, and negation does not rescue it: the negation of an outcome that is neither true nor false is still neither. A selection surface that coerces that outcome to false therefore drops those rows on the positive pass and again on the negated pass, so the gap between 9,500 and 10,000 is an absence census you got for free. Not every design behaves this way, and the alternative is not safer. Where the comparison simply returns false rather than a third outcome, the negated form is true and all 500 rows land on the negated side — the counts add up, and the negated result quietly contains rows that were never measured. A third behaviour also exists: refusing the selection outright when the condition holds absence, which is the only loud option of the three.

code

pseudocode · 7 lines

pseudocode

rows_total    = 10000

kept_positive = keep_rows(table, amount > 100)         # 6100 rows
kept_negated  = keep_rows(table, not (amount > 100))   # 3400 rows

6100 + 3400   = 9500          # 500 rows are in neither result
empty_cells(table, amount) = 500   # the same 500 rows

go deeper

for a junior

Remember that a condition tested against a cell holding no value does not produce a plain true or false, so rows can be absent from both a filter and its opposite.

for a middle

Explain why negation does not help: the negation of an outcome that is neither true nor false is still neither, so coercing it to false removes the row on both passes.

for a senior

Use the arithmetic as a diagnostic — a condition and its negation must account for every row, and the shortfall is exactly the number of untested cells in the condition column.

for a principal

The rule worth setting is that any predicate over a column that may hold absence must state what happens to those rows, so no result set silently means two different populations to two readers.

The arithmetic that should always hold, and does not

A condition and its negation are meant to partition a table. Every row satisfies one or the other, so the two row counts must add to the table's row count. When they do not, the shortfall is not a rounding artefact and it is not an off-by-one at the boundary of the comparison — a boundary error moves rows between the two sides, it cannot remove them from both.

The shortfall is the number of rows for which the condition produced neither answer.

Why negation does not rescue those rows

The condition is a comparison, and the tested cell holds no value. Two behaviours are possible and they diverge exactly here:

A third outcome. Under a design whose typed absence marker follows a logic with a third outcome besides true and false, the comparison declines to commit. Negation is defined over that outcome too, and the negation of "neither" is still "neither". When the selection surface then coerces the indeterminate entries to false so that it has something to select with, the row fails the positive pass and the negated pass. It disappears twice.
A plain false. Under a design that borrows the pattern the floating-point format reserves for a result with no numeric answer, the comparison returns an honest false. Negating false gives true, so every one of those rows lands in the negated result.

That second case is the one worth dwelling on, because the counts do add up and everything looks correct. The negated result — "the rows where the amount was not above 100" — now contains rows where the amount was never recorded at all. The population is wrong and the arithmetic gives you no warning.

A third design refuses the selection outright when the condition column contains indeterminate entries. It is the only one of the three that tells you.

The three behaviours side by side

Behaviour of the condition	Positive pass	Negated pass	Counts add up?	Failure mode
Third outcome, coerced to false	Row excluded	Row excluded	No	Rows silently vanish from both results
Plain false for the comparison	Row excluded	Row included	Yes	Unmeasured rows counted as "not above the threshold"
Selection refused on indeterminate entries	Operation fails	Operation fails	n/a	Loud, and the only one you cannot miss

A candidate who names only the first is describing one design; a candidate who names only the second is describing another. The senior answer names the variation and says which failure is worse: the second, because nothing is missing and therefore nothing prompts you to look.

Turning the bug into a diagnostic

The partition arithmetic is a free absence audit, and it costs two operations you were already writing:

Run the condition and record its row count.
Run the negated condition and record its row count.
Subtract their sum from the table's row count. Under a coercing design, the difference is exactly the number of cells with no value in the tested column.

Under a design that returns plain false, the difference is zero and the audit tells you nothing — which is itself informative, because it identifies which behaviour you are dealing with.

What to do instead of relying on the default

Count the empty cells of every column a condition will touch, before the condition is written. If the count is zero, none of this matters; if it is not, the next step is a decision, not a default.
State the intent in the condition. Decide whether unmeasured rows belong with the positive side, the negative side, or neither, and write that decision into the predicate so the result means one thing to every reader.
Never read a negated condition as "the complement". It is the complement only when the condition is total over the rows, which it is not when the tested column can be empty.
Assert the partition. A cheap assertion that the two counts sum to the row count turns an invisible population error into a failing step.
Be explicit about combining conditions. Once one condition is indeterminate for a row, any combination containing it inherits that, and the combined result loses the row the same way.

Why this is worth an interview question

Every part of the failure is quiet. The operation succeeds, the result is a normal table, the row count looks plausible, and the number that eventually goes on a slide is computed over a population nobody chose. The only signal available is arithmetic the analyst has to do deliberately, and the candidate who does it without being asked is the one you want operating a pipeline.

## The arithmetic that should always hold, and does not A condition and its negation are meant to partition a table. Every row satisfies one or the other, so the two row counts must add to the table's row count. When they do not, the shortfall is not a rounding artefact and it is not an off-by-one at the boundary of the comparison — a boundary error moves rows between the two sides, it cannot remove them from both. The shortfall is the number of rows for which the condition produced **neither** answer. ## Why negation does not rescue those rows The condition is a comparison, and the tested cell holds no value. Two behaviours are possible and they diverge exactly here: - **A third outcome.** Under a design whose typed absence marker follows a logic with a third outcome besides true and false, the comparison declines to commit. Negation is defined over that outcome too, and the negation of "neither" is still "neither". When the selection surface then coerces the indeterminate entries to false so that it has something to select with, the row fails the positive pass *and* the negated pass. It disappears twice. - **A plain false.** Under a design that borrows the pattern the floating-point format reserves for a result with no numeric answer, the comparison returns an honest false. Negating false gives true, so every one of those rows lands in the negated result. That second case is the one worth dwelling on, because the counts *do* add up and everything looks correct. The negated result — "the rows where the amount was **not** above 100" — now contains rows where the amount was never recorded at all. The population is wrong and the arithmetic gives you no warning. A third design refuses the selection outright when the condition column contains indeterminate entries. It is the only one of the three that tells you. ## The three behaviours side by side | Behaviour of the condition | Positive pass | Negated pass | Counts add up? | Failure mode | |---|---|---|---|---| | Third outcome, coerced to false | Row excluded | Row excluded | No | Rows silently vanish from both results | | Plain false for the comparison | Row excluded | Row **included** | Yes | Unmeasured rows counted as "not above the threshold" | | Selection refused on indeterminate entries | Operation fails | Operation fails | n/a | Loud, and the only one you cannot miss | A candidate who names only the first is describing one design; a candidate who names only the second is describing another. The senior answer names the variation and says which failure is worse: the second, because nothing is missing and therefore nothing prompts you to look. ## Turning the bug into a diagnostic The partition arithmetic is a free absence audit, and it costs two operations you were already writing: 1. Run the condition and record its row count. 2. Run the negated condition and record its row count. 3. Subtract their sum from the table's row count. Under a coercing design, the difference is exactly the number of cells with no value in the tested column. Under a design that returns plain false, the difference is zero and the audit tells you nothing — which is itself informative, because it identifies which behaviour you are dealing with. ## What to do instead of relying on the default - **Count the empty cells of every column a condition will touch**, before the condition is written. If the count is zero, none of this matters; if it is not, the next step is a decision, not a default. - **State the intent in the condition.** Decide whether unmeasured rows belong with the positive side, the negative side, or neither, and write that decision into the predicate so the result means one thing to every reader. - **Never read a negated condition as "the complement".** It is the complement only when the condition is total over the rows, which it is not when the tested column can be empty. - **Assert the partition.** A cheap assertion that the two counts sum to the row count turns an invisible population error into a failing step. - **Be explicit about combining conditions.** Once one condition is indeterminate for a row, any combination containing it inherits that, and the combined result loses the row the same way. ## Why this is worth an interview question Every part of the failure is quiet. The operation succeeds, the result is a normal table, the row count looks plausible, and the number that eventually goes on a slide is computed over a population nobody chose. The only signal available is arithmetic the analyst has to do deliberately, and the candidate who does it without being asked is the one you want operating a pipeline.

Under a design where the comparison returns plain false rather than a third outcome, what goes wrong instead?
The counts add up and the error moves. The negation of a false comparison is true, so every row with no value in the tested column lands in the negated result: "not above the threshold" now contains rows that were never measured. Nothing is missing, the partition arithmetic is clean, and the population is still wrong — which is considerably harder to notice than a shortfall.
What is the cheapest guard against this before any predicate is written?
Count the cells with no value in every column a condition will touch, then decide explicitly what should happen to those rows: keep them, exclude them, or refuse to run. Writing that decision into the condition, instead of inheriting whichever coercion the selection surface applies, is what makes the result mean the same thing to everyone who reads it.
Two conditions are combined and one of them is indeterminate for a row. What happens to that row?
It inherits the indeterminacy. A combination containing an outcome that is neither true nor false is itself not definitely true, so the row is treated the same way the single condition would have been — coerced away, or refusing the operation, depending on the design. Combining conditions never recovers a row that one of them could not decide.

saying these in an interview costs you the question

Assumes a filter and its negation always partition the table
Thinks negating the condition recovers the rows with no value
Blames the shortfall on an off-by-one at the comparison boundary
Believes every design loses those rows the same way
Treats the negated result as trustworthy because it is the complement

A filter and its negation return 6,100 and 3,400 rows from a 10,000-row table — where are the other 500?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The arithmetic that should always hold, and does not

Why negation does not rescue those rows

The three behaviours side by side

Turning the bug into a diagnostic

What to do instead of relying on the default

Why this is worth an interview question

▾common follow-ups3 questions

▾red flags5 flags

The arithmetic that should always hold, and does not

Why negation does not rescue those rows

The three behaviours side by side

Turning the bug into a diagnostic

What to do instead of relying on the default

Why this is worth an interview question

answer

go deeper