SQL NULL: 7 Queries That Return Wrong Rows, No Error
A reader sent me a four-line query that powered his "customers who spent more than 500 last month" report. It ran without a single warning and returned fewer rows than the API endpoint it was supposed to match. The join was fine. The index was fine. One predicate in those four lines happened to meet a NULL, and WHERE total != 0 evaluated to neither true nor false, so the row was dropped. SQL's three-valued logic is not trivia; it edits production data every day without telling anyone.
TL;DR: when NULL takes part in a comparison, the result is never TRUE or FALSE. It is UNKNOWN, and WHERE only lets TRUE through. That one rule produces all seven traps below: != swallowing rows, NOT IN returning nothing, aggregates changing their denominator, a LEFT JOIN collapsing into an INNER JOIN, ORDER BY disagreeing between engines, bare columns in GROUP BY erroring on one engine and passing on the other, and type affinity giving two answers for the same literal. I measured every one of them on a five-row table and put a copy-paste checklist at the end.
What I tested, and the five rows
I tested all of this on one machine: Windows 11, the sqlite3 module shipped with Python 3.13.14 (linking SQLite 3.53.1), and a local PostgreSQL 18.6 instance (x86_64-windows, compiled by msvc-19.44.35228, 64-bit, server encoding UTF8). Both engines got the same data and the same SQL. Every number below was printed by a script, not copied from a blog post.
Confirm versions first, then create the table. These two steps prevent every later argument about "but it works on my machine":
python -c "import sqlite3; print(sqlite3.sqlite_version)"
# 3.53.1
psql -h 127.0.0.1 -p 5433 -U postgres -c "select version();"
# PostgreSQL 18.6 on x86_64-windows, compiled by msvc-19.44.35228, 64-bit
Five rows, with one NULL in customer and two in amount:
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer TEXT, amount INTEGER);
INSERT INTO orders VALUES
(1,'alice',100),
(2,'bob', NULL),
(3,NULL, 50),
(4,'alice',200),
(5,'carol', NULL);
| id | customer | amount | note |
|---|---|---|---|
| 1 | alice | 100 | |
| 2 | bob | NULL | amount missing |
| 3 | NULL | 50 | customer missing |
| 4 | alice | 200 | |
| 5 | carol | NULL | amount missing |
Five rows. Keep that number in mind; every "one row short" below is measured against it.
Trap 1: the not-equal operator swallows the whole NULL row
The obvious query, counting orders that are not alice's:
SELECT count(*) FROM orders WHERE customer != 'alice';
-- SQLite: 2 PostgreSQL: 2
The answer is 2. But the table has five rows, alice owns two, so three rows should qualify. The row where customer IS NULL disappeared. Rewriting with <> changes nothing:
SELECT count(*) FROM orders WHERE customer <> 'alice';
-- 2
SQLite's documentation puts it plainly in the operator section: "All operators generally evaluate to NULL when any operand is NULL, with specific exceptions as stated below." PostgreSQL says the same thing in its comparison functions page: "Ordinary comparison operators yield null (signifying "unknown"), not true or false, when either input is null." Both engines agree, which tells you this is not a bug. It is the SQL standard.
The mechanism is that WHERE accepts only TRUE. UNKNOWN is filtered out exactly like FALSE, but the two mean different things: FALSE is "I know this does not hold," UNKNOWN is "I do not know." Treating "I do not know" as "no" is how a missing value silently becomes a negative answer.
You can measure the size of that third state in one statement:
SELECT NULL AND 0, NULL AND 1, NULL OR 0, NULL OR 1, NOT NULL;
-- SQLite: 0 | NULL | NULL | 1 | NULL
-- PostgreSQL: f | NULL | NULL | t | NULL
NULL AND 0 is false on both engines and NULL OR 1 is true on both, because the known operand already decides the result. Everything else is NULL. SQLite states those two exceptions verbatim: "AND evaluates to 0 (false) when the other operand is false; and OR evaluates to 1 (true) when the other operand is true."
There are only two fixes:
-- A: keep NULL out of the comparison
SELECT count(*) FROM orders
WHERE customer IS NOT NULL AND customer <> 'alice'; -- 2
-- B: count NULL as its own class, after agreeing what it means
SELECT count(*) FROM orders WHERE customer IS NULL; -- 1
If the business question really is "every order that is not alice's," the correct predicate is WHERE customer IS NULL OR customer != 'alice', which returns 3. The missing row is precisely the row that makes the report disagree with the API.
Trap 2: one NULL inside a NOT IN subquery, and the result is always empty
This one is worse than the first because it does not shave a row off. It zeroes the entire result set and stays quiet.
SELECT count(*) FROM orders WHERE id NOT IN (SELECT amount FROM orders);
-- SQLite: 0 PostgreSQL: 0
The amount column contains two NULLs, so the subquery is effectively id NOT IN (100, NULL, 50, 200, NULL). Comparing any id to NULL with != yields UNKNOWN, and a single UNKNOWN anywhere in the OR chain removes the row. All five rows removed, zero returned.
NOT EXISTS behaves correctly on the same data:
SELECT count(*) FROM orders
WHERE NOT EXISTS (SELECT 1 FROM orders x WHERE x.amount = orders.id);
-- SQLite: 5 PostgreSQL: 5
Same data, same intent, 0 versus 5. The reason NOT EXISTS survives is that it tests existence row by row. NULL still produces UNKNOWN inside x.amount = orders.id, but UNKNOWN is treated as "no such row," so the outer row is kept, which is what we wanted. The SQL standard requires EXISTS to return only true or false, never UNKNOWN. That is why it is the safe form.
SQLite's docs add a detail that catches people later: "The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR a = +y OR a = +z OR ...". In other words, the values to the right of the IN operator ... are considered to have no affinity." The right-hand list loses its affinity, so IN does not compare quite like a plain = does. Trap 7 shows where that bites.
The list form of NOT IN fails the same way, just on fewer rows:
SELECT count(*) FROM orders WHERE amount NOT IN (100,200); -- 1
SELECT count(*) FROM orders WHERE amount NOT IN (100,200) OR amount IS NULL; -- 3
One versus three. The difference is the two orders whose amount is unknown.
Trap 3: aggregates skip NULL, so the denominator is not the row count
count(*) and count(column) are different functions that differ by one pair of parentheses:
SELECT count(*), count(amount) FROM orders;
-- SQLite: 5, 3 PostgreSQL: 5, 3
SQLite's own wording: "The count(X) function returns a count of the number of times that X is not NULL in a group. The count(*) function (with no arguments) returns the total number of rows in the group." Five orders and three known amounts are not the same fact.
The expensive part is the average:
SELECT sum(amount), avg(amount), round(avg(amount),2) FROM orders;
-- SQLite: 350 | 116.66666666666667 | 116.67
SELECT round(350.0/5, 2);
-- 70.0
avg reports 116.67 because it divides 350 by 3, the number of non-NULL rows. Total divided by order count gives 70. The same phrase, "average order value," now has two answers 66.7% apart. Both are defensible depending on whether you mean "per order that has an amount" or "per order," but production code usually contains one avg() and nobody inspects the denominator.
count(distinct) splits the question a second time:
SELECT count(DISTINCT customer) FROM orders; -- 3
SELECT count(*) FROM (SELECT customer FROM orders GROUP BY customer); -- 4
How many distinct customers are there, 3 or 4? count(distinct) ignores NULL by definition, so 3. GROUP BY treats NULL as its own group, so 4. SQLite says so directly: "For the purposes of grouping rows, NULL values are considered equal." The table really does contain one "customer unknown" order. That is a fourth category, not a fourth customer.
One more divergence shows up only on SQLite, around sum() on an empty set:
SELECT sum(amount), total(amount) FROM orders WHERE id > 999;
-- SQLite: NULL | 0.0
The documentation is candid about it: "If there are no non-NULL input rows then sum() returns NULL but total() returns 0.0. NULL is not normally a helpful result for the sum of no rows but the SQL standard requires it." PostgreSQL also returns NULL and has no non-standard total(), so you write coalesce(sum(x),0).
Trap 4: put a LEFT JOIN condition in WHERE and it silently becomes an INNER JOIN
This is widely repeated but rarely quantified. Add a customer table where dave has no orders at all:
CREATE TABLE customers(name TEXT PRIMARY KEY, tier TEXT);
INSERT INTO customers VALUES ('alice','gold'),('bob','silver'),('dave','bronze');
SELECT count(*) FROM orders o LEFT JOIN customers c ON o.customer = c.name;
-- 5 rows: the LEFT JOIN keeps every order
SELECT count(*) FROM orders o LEFT JOIN customers c ON o.customer = c.name
WHERE c.tier <> 'bronze';
-- 3 rows (both engines)
SELECT count(*) FROM orders o LEFT JOIN customers c ON o.customer = c.name
AND c.tier <> 'bronze';
-- 5 rows: the condition stayed in ON
Five becomes three. The two lost rows are exactly the unmatched ones, where the right side is all NULL (id 3 and id 5). WHERE c.tier <> 'bronze' evaluates to UNKNOWN for them, so the "keep all left rows" promise is overwritten by WHERE, and the query means the same as an INNER JOIN.
To find orders with no matching customer, do not write WHERE c.name != 'x'. Use IS NULL:
SELECT o.id, c.name FROM orders o LEFT JOIN customers c ON o.customer = c.name
WHERE c.name IS NULL;
-- (3, NULL) and (5, NULL)
A rule you can apply in review: if a LEFT JOIN is followed by a WHERE that mentions a right-side column, ask whether that condition can hold when the right side did not match. If it cannot, the LEFT JOIN is decorative.
Trap 5: ORDER BY puts NULL in opposite places on the two engines
This is the one place in the whole article where the two engines give opposite answers, and it changes what your first page contains.
SELECT customer FROM orders ORDER BY customer;
-- SQLite: NULL, alice, alice, bob, carol
-- PostgreSQL: alice, alice, bob, carol, NULL
SQLite sorts NULL smallest: "A value with storage class NULL is considered less than any other value (including another value with storage class NULL)." PostgreSQL treats NULL as largest: "By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise."
With pagination, that difference swaps the first page. Ten rows, two of them NULL:
CREATE TABLE p(id INTEGER PRIMARY KEY, v INTEGER);
INSERT INTO p VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,NULL),(10,NULL);
SELECT id, v FROM p ORDER BY v LIMIT 4;
-- SQLite: (9,NULL) (10,NULL) (1,1) (2,2)
-- PostgreSQL: (1,1) (2,2) (3,3) (4,4)
Same statement, and only 2 of the 4 first-page rows overlap, a 50% match. Migrate that code from SQLite to PostgreSQL, or the reverse, and page one changes content while the SQL text and the API response shape stay identical.
Fix it by writing the order down instead of inheriting a default. SQLite has supported NULLS FIRST / NULLS LAST since 3.30:
SELECT id, v FROM p ORDER BY v NULLS LAST LIMIT 4;
-- (1,1) (2,2) (3,3) (4,4) -- identical to PostgreSQL's default
ORDER BY v NULLS LAST, id buys a second thing: a stable tie-breaker. Without it, duplicate values guarantee rows that appear on two pages or on none, and that has nothing to do with NULL.
Trap 6: bare columns in GROUP BY, silent on one engine, fatal on the other
One statement, accepted by SQLite, rejected by PostgreSQL:
SELECT id, customer FROM orders GROUP BY customer;
-- SQLite: 4 rows (3,NULL) (1,alice) (2,bob) (5,carol)
-- PostgreSQL: ERROR: column "orders.id" must appear in the GROUP BY clause
-- or be used in an aggregate function
SQLite returned four rows with ids 3, 1, 2, 5 in no discernible pattern, because it picked a row for you. The documentation is unambiguous: "Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset," and "in many cases the value for "b" is undefined." It also admits this is an extension: "Most other SQL database engines disallow bare columns... This is considered a feature, not a bug."
So a reporting query that works on SQLite is refused outright on PostgreSQL. The reverse migration is worse: SQL that used to error now runs, but the id is arbitrary and may differ between executions ("The particular row chosen might change from one invocation to the the next.").
The one exception is min() / max(). With exactly one such aggregate, bare columns come from the row that produced the extreme value. Ties make it arbitrary again:
SELECT id, customer, amount FROM orders GROUP BY customer HAVING max(amount);
-- SQLite: (3,NULL,50) (4,alice,200)
alice correctly yields 200 because her larger order is 200, but this is SQLite-specific behavior and does not port.
Trap 7: type affinity gives the same literal two different answers
This is the subtlest trap, because on SQLite it is right sometimes and wrong other times, depending on whether a column appears on either side of the comparison.
Without a column:
SELECT 1 = '1';
-- SQLite: 0 PostgreSQL: t
SQLite says 0 and PostgreSQL says true. SQLite's reason is its type ordering: an integer is always less than any text value, so integer 1 never equals text '1' ("An INTEGER or REAL value is less than any other TEXT or BLOB value."). PostgreSQL coerces the untyped literal to integer, so the comparison is 1 = 1.
Put a column on one side and SQLite flips:
CREATE TABLE t(id INTEGER PRIMARY KEY, n INTEGER, s TEXT);
INSERT INTO t(n,s) VALUES ('123','123'), ('123abc','123'), (45.9,'x');
SELECT count(*) FROM t WHERE n = '123'; -- 1 (integer column vs text literal: affinity converts '123' to 123)
SELECT count(*) FROM t WHERE s = 123; -- 2 (text column vs integer literal: affinity converts 123 to '123')
SELECT 1 = '1'; -- 0 (no column on either side: no affinity applied)
That is how type affinity actually feels. The documented rules are: "If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to the other operand," and "Otherwise, no affinity is applied and both operands are compared as is." Whether a column is involved decides which branch runs.
Writes are just as quiet. After the inserts above, look at typeof:
SELECT id, typeof(n), n FROM t;
-- (1,'integer',123) '123' became an integer
-- (2,'text','123abc') not convertible, stored as text
-- (3,'real',45.9) 45.9 stays real
A column declared INTEGER now holds integer, text and real values at once, with no warning. A later ORDER BY n sorts by type class, so every number comes before every string rather than sorting by numeric value.
Casting follows the same "convert as much as you can" policy:
SELECT CAST('12abc' AS INTEGER), CAST('abc' AS INTEGER), '12abc'+0;
-- 12 | 0 | 12
'abc' becomes 0 and '12abc' becomes 12, silently. PostgreSQL is the one that saves you here:
SELECT count(*) FROM orders WHERE note = 0;
-- ERROR: operator does not exist: text = integer
-- HINT: No operator matches the given name and argument types.
-- You might need to add explicit type casts.
SELECT 1 = '1a';
-- ERROR: invalid input syntax for type integer: "1a"
The same statement on SQLite returns 0 rows without a word.
Rounding has two coexisting answers as well. SQLite's round() always rounds halves away from zero:
SELECT round(2.5), round(0.5), round(1.5), round(-0.5), round(2.675,2);
-- 3.0 | 1.0 | 2.0 | -1.0 | 2.67
PostgreSQL uses one function name and two rule sets, chosen by argument type:
SELECT round(2.5::float8), round(0.5::float8), round(1.5::float8),
round(-2.5::float8), round(2.5::numeric);
-- 2 | 0 | 2 | -2 | 3
float8 rounds halves to even; numeric rounds halves away from zero. The docs say it exactly: "For numeric, ties are broken by rounding away from zero. For double precision, the tie-breaking behavior is platform dependent, but "round to nearest even" is the most common rule." Pick float8 for a money column and you will spend an afternoon explaining a one-cent discrepancy.
Two more differences in how the same expression is read, both measured:
SELECT 'ABC' LIKE 'a%'; -- SQLite: 1 PostgreSQL: f (use ILIKE)
SELECT 'ABC' GLOB 'a*'; -- SQLite: 0 (GLOB is case-sensitive)
And unique constraints. By default neither engine treats two NULLs as equal, so a "unique" column happily accepts many NULLs:
-- the explicit switch exists from PostgreSQL 15
CREATE TABLE u2(x int, y text, UNIQUE NULLS NOT DISTINCT (x));
INSERT INTO u2 VALUES (NULL,'c'),(NULL,'d');
-- ERROR: duplicate key value violates unique constraint "u2_x_key"
-- DETAIL: Key (x)=(null) already exists.
PostgreSQL's documentation states the default: "By default, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns." It also warns that "The default null treatment in unique constraints is implementation-defined according to the SQL standard," which means you cannot assume another engine matches.
Troubleshooting: six real errors and the fix for each
All six below are verbatim output from the tables above, not forum quotes. Note the asymmetry: on this test set SQLite raised two errors and stayed silent on the rest, while PostgreSQL raised all six. When a migration suddenly produces a pile of errors, the new engine is usually not worse. The old one was covering for you.
Error 1: a column in the select list is not grouped
ERROR: column "orders.id" must appear in the GROUP BY clause or be used in
an aggregate function
LINE 1: SELECT id, customer FROM orders GROUP BY customer;
^
Cause: the SELECT list contains a column that is neither in GROUP BY nor inside an aggregate (trap 6). PostgreSQL refuses to guess; SQLite guesses for you.
Two fixes, pick one:
SELECT min(id) AS any_id, customer FROM orders GROUP BY customer;
SELECT id, customer FROM orders ORDER BY customer;
If you genuinely want "the largest order per customer," use a window function rather than a bare column:
SELECT * FROM (
SELECT *, row_number() OVER (PARTITION BY customer ORDER BY amount DESC NULLS LAST) rn
FROM orders
) x WHERE rn = 1;
Error 2: an aggregate inside WHERE
-- PostgreSQL
ERROR: aggregate functions are not allowed in WHERE
-- SQLite (same statement)
sqlite3.OperationalError: misuse of aggregate function count()
Cause: WHERE is evaluated before grouping, aggregates only exist after grouping. The same mistake produces two different strings, so search both when you look this up.
Fix: move the condition into HAVING.
SELECT customer, count(*) FROM orders GROUP BY customer HAVING count(*) > 1;
-- alice | 2
Error 3: WHERE is followed by a non-boolean column
ERROR: argument of WHERE must be type boolean, not type integer
LINE 1: SELECT count(*) FROM orders WHERE amount;
^
Cause: PostgreSQL requires a boolean expression in WHERE. SQLite treats integers as truthy, so the same statement returns 3 rows there (amounts 100, 50, 200; the two NULL rows are removed by UNKNOWN) with no hint at all. A portability trap like this can never surface while you develop on SQLite.
Fix: write the intent down.
SELECT count(*) FROM orders WHERE amount IS NOT NULL; -- 3
SELECT count(*) FROM orders WHERE amount != 0; -- 3 (still drops NULL)
Error 4: comparing a text column to a number
ERROR: operator does not exist: text = integer
LINE 1: SELECT count(*) FROM orders WHERE note = 0;
^
HINT: No operator matches the given name and argument types. You might need
to add explicit type casts.
Cause: PostgreSQL does not perform implicit cross-type comparison (trap 7). SQLite converts by affinity, including turning 'abc' into 0 and comparing that.
Fix: cast explicitly, or use a literal of the right type.
SELECT count(*) FROM orders WHERE note = '0';
SELECT count(*) FROM orders WHERE note::int = 0; -- throws on non-numeric data, which is good news
Error 5: the literal cannot be converted at all
ERROR: invalid input syntax for type integer: "1a"
LINE 1: SELECT 1 = '1a';
^
Cause: PostgreSQL folds untyped literals into integers during parsing, so '1a' fails before comparison begins. That is why the message says invalid input syntax rather than something about comparison. SQLite returns 0 for 1 = '1a' and also 0 for 1 = '1', so two very different mistakes look identical.
Fix: parameterize and validate at the application boundary.
-- don't: f"SELECT * FROM orders WHERE id = {user_input}"
-- do: cur.execute("SELECT * FROM orders WHERE id = ?", (user_input,))
There is a Python-level sibling to this trap that I hit while testing:
cur.execute("SELECT count(*) FROM orders WHERE amount = ?", (None,)) # 0 rows
cur.execute("SELECT count(*) FROM orders WHERE amount IS ?", (None,)) # 2 rows
Binding None is the same as writing amount = NULL, which is never true. Null checks have to be part of the SQL text; you cannot parameterize = into a universal comparison.
Error 6: a unique constraint that does not stop NULL
ERROR: duplicate key value violates unique constraint "u2_x_key"
DETAIL: Key (x)=(null) already exists.
Cause: UNIQUE treats two NULLs as unequal by default (end of trap 7), so any number of NULLs fits. This error only appears when you explicitly asked for UNIQUE NULLS NOT DISTINCT (PostgreSQL 15 and later), which means the constraint is doing exactly what you wanted.
Fix: when you need "at most one empty value," a partial unique index is more flexible than changing the constraint.
CREATE UNIQUE INDEX u2_x_one_null ON u2(x) WHERE x IS NULL;
The checklist I paste into code review
Seven traps compressed into seven questions:
| What you see | Ask first | Fix |
|---|---|---|
col != 'x' or col <> 'x' | Can this column be NULL | Add col IS NOT NULL, or decide which side NULL belongs to |
NOT IN (subquery) | Does that subquery column contain NULL | Use NOT EXISTS, or add WHERE col IS NOT NULL inside |
avg(col) / count(col) | Is the denominator rows or non-null values | Use count() for rows, sum(col)/count() for the other question |
LEFT JOIN ... WHERE right.col | Can this hold when the right side missed | Move it into ON, or test IS NULL for orphans |
ORDER BY col | Any NULLs, any ties | Write NULLS LAST (or FIRST) plus a unique tie-breaker |
SELECT ungrouped_col ... GROUP BY | Would another engine reject this | Wrap in an aggregate, add to GROUP BY, or use a window function |
Cross-type compare / round() / LIKE | Which engine will run this | Cast explicitly, use numeric for money, ILIKE for case-insensitive |
Two structural suggestions beat patching statements one by one:
- SQLite has offered
STRICTtables since 3.37.0 (2021-11-27). With STRICT, types are enforced as declared and the "one column, three storage classes" situation is rejected. Use STRICT for new tables and migrate old ones as needed. - When you need NULL compared as an ordinary value, use the standard
IS DISTINCT FROM/IS NOT DISTINCT FROM. Both engines support them, and SQLite'sIS/IS NOTare the short spellings, with the documented guarantee that "It is not possible for an IS or IS NOT expression to evaluate to NULL." These predicates always return true or false, which makes them the cleanest way around three-valued logic.
FAQ
Q: Why not just make every column NOT NULL with a default and be done?
Most tables should do that, but "unknown" and "zero" carry different information. Writing 0 for a missing amount corrupts avg(), sum() and every report downstream; writing an empty string for an unknown customer adds a fake category to count(distinct). The real work is splitting columns where absence is impossible (add NOT NULL) from columns where unknown is legitimate (keep NULL and handle it explicitly in queries).
Q: Is count(1) faster than count(*)?
No. Both engines read it as "count rows" and ignore the expression. The folklore about count(1) being faster does not hold on modern optimizers.
Q: How do I find the rows that WHERE col != 'x' dropped?
Run SELECT count(*) FROM t WHERE col IS NULL once and compare with the total. The gap is exactly what three-valued logic ate.
Q: PostgreSQL errors while SQLite passes. Is SQLite broken?
It is a trade-off, not a defect. SQLite documents bare columns and dynamic typing as features ("This is considered a feature, not a bug."), and gets near-zero deployment cost and strong backward compatibility in return. The price is that some mistakes only appear when you switch engines or when a report stops reconciling. Knowing the trade-off tells you where to add validation.
Q: When migrating to PostgreSQL, how do I find the SQL that will break?
Run the full test suite, then grep for the four shapes that fail: bare columns with GROUP BY, aggregates in WHERE, non-boolean WHERE clauses, cross-type comparisons. The first three are usually a few dozen hits. The fourth needs type annotations, and that is also why PostgreSQL is easier to maintain long term: it moves the failure to execution time instead of leaving it for report time.
Closing
Three-valued logic is not a syntax detail. It is the one large region of SQL where nothing errors, nothing warns, and the answer is simply wrong. On one machine, one dataset, the seven traps all reduce to a sentence: NULL is not a value, it is "unknown," and WHERE does not accept "unknown."
Start your investigation with the denominator. Compare count(*) against count(column) first, then check whether NULL slipped into a NOT IN, and only then suspect joins and sorting. That order covers most of the "the report is a few rows short" incidents I have seen.
If you want more measured cases of defaults quietly changing results, these are the closest neighbours:
- PostgreSQL replication slots eating 159 MB of WAL, the same "space disappears silently" shape.
- SQLite hot backup: a backup that opens fine and is 14500 rows short, "the job succeeded and the data is wrong."
- What Alembic autogenerate misses, the schema-migration version of a silent gap.
- DuckDB on a 40 MB CSV if you plan to take this kind of analysis off the server entirely.
Every number in this article was produced on Windows 11 with SQLite 3.53.1 and PostgreSQL 18.6; the commands are copy-paste ready. No affiliate links, no Amazon tracking parameters.
👉 Join Xiaomi MiMo Platform: Leading AI model platform with cost-effective inference
👉 Join Aliyun AI: Top AI products with exclusive coupons for business innovation
📌 This article was AI-assisted generated and human-reviewed | TechPassive — An AI-driven content testing site focused on real tool reviews
🔗 Recommended Tools
These are carefully selected tools. Using our affiliate links supports us to keep producing quality content:
As an Amazon Associate, I earn from qualifying purchases at no extra cost to you.