SQL traps · Chapter 2 of 16
COUNT(*) against COUNT(column)
Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Everyday query traps
- 3 tasks
- SQL
- Free chapter
Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.
The lesson
By the end of this case
You can tell what count(*) counts from what count(column) counts, spot the extra row a LEFT JOIN gives a parent with no children, fix a count that is one too high, and pin it down with a test.
The ticket
MS-415: brand new posts show "1 comment". Assigned to you by Anya. Priority: medium. Since: the creator dashboard's launch, two weeks ago.
Rohan, in the analytics channel on Tuesday at 4:40 pm: "Kunal posted 'testing, please ignore' a minute ago and his creator dashboard already says 1 comment. It has none, I checked the app. Every single post on the dashboard has at least one comment, so the 'posts waiting for a reply' tile has said 0 since launch."
The dashboard reads the view post_stats. Rohan ran it for the newest posts on production:
minglesphere=> SELECT * FROM post_stats WHERE post_id > 48206 ORDER BY post_id;
post_id │ title │ comments
─────────┼──────────────────────────┼──────────
48207 │ Monsoon in Munnar, day 3 │ 12
48208 │ Rate my chai setup │ 4
48209 │ Hiring two designers │ 1
48210 │ testing, please ignore │ 1
(4 rows)
Anya: "Posts with replies look right: Zoya's post really has 12. It is only the quiet ones, and those are the ones creators care about. The view keeps every post on the list, replies or not. Reproduce it in a fresh database first, then fix it, then make sure it never comes back. You are in post_stats.sql."
What count(*) counts
count(*) counts rows. count(x) counts the rows where x is not NULL. On a table with no NULLs they agree, so most people never notice they are two different questions:
SELECT count(*) AS all_rows, count(x) AS non_null
FROM (VALUES (1), (NULL), (3)) AS v (x);
all_rows │ non_null
──────────┼──────────
3 │ 2
The row a LEFT JOIN makes
A LEFT JOIN keeps every row of the left table. When nothing on the right matches, Postgres still gives that row once, with NULL in every column from the right. In your minglesphere database the tag poetry has no posts:
SELECT t.name, pt.post_id
FROM tags t
LEFT JOIN post_tags pt ON pt.tag_id = t.id
WHERE t.name = 'poetry';
name │ post_id
────────┼─────────
poetry │ (null)
(1 row)
That is one row. Count it two ways, for three tags:
SELECT t.name, count(*) AS rows, count(pt.post_id) AS posts
FROM tags t
LEFT JOIN post_tags pt ON pt.tag_id = t.id
WHERE t.name IN ('chai', 'databases', 'poetry')
GROUP BY t.name ORDER BY t.name;
name │ rows │ posts
───────────┼──────┼───────
chai │ 17 │ 17
databases │ 5 │ 5
poetry │ 1 │ 0
For every tag with posts the two columns agree, which is why nothing looked wrong in review. Only the empty one differs, and an empty group is exactly the case a dashboard is built to show. No error, no warning: both counts are valid SQL that answer different questions.
The other way round
The same rule bites in reverse. count(city) skips members with no city, and count(DISTINCT city) counts distinct cities, NULL not among them:
SELECT count(*) AS members, count(city) AS with_city,
count(DISTINCT city) AS cities
FROM users;
members │ with_city │ cities
─────────┼───────────┼────────
24 │ 22 │ 9
So "how many members" is count(*), and "how many members told us a city" is count(city). Say which one you mean.
Making it happen on purpose
As in case 1, node run.mjs runs repro.sql in a fresh, empty database right after post_stats.sql has made its tables. Build the smallest picture of the ticket: one post with replies, so you can see the view is right for it, and one brand new post with none.
A test that would have caught it
Two hotfixes are tempting here, and each one breaks a side. An inner JOIN makes quiet posts vanish from the dashboard, and subtracting 1 makes busy posts lose a comment. When a subquery inside is(...) finds no row, it gives NULL, so is fails: that is how a test notices a missing row.
Your turn
Three tasks, one file each. In repro.sql, make a new post show a comment it does not have, and run it with node run.mjs. In post_stats.sql, fix the view. In post_stats.test.sql, write the guard and run it with node run.mjs post_stats.test.sql: the check runs it beside the original code, the fix and both hotfixes. Then answer Anya's interview question in your own words.
What you build: 3 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
repro.sql: a comment that is not there, on purpose
Write repro.sql. It runs in a fresh, empty database where post_stats.sql has just made the tables posts and comments and the view post_stats. Add two posts: an older one with two comments and a brand new one with none. End with a SELECT that gives one value: the comments that post_stats shows for the new post.
The check runs your file twice, each time in a fresh database: beside the original post_stats.sql it must give 1 (a comment that is not there), and beside a fixed post_stats.sql it must give 0. So it has to really read the view: a file that only says SELECT 1 fails the second run. Run it with node run.mjs in the terminal.
- Task 2
post_stats.sql: new posts show 0 comments
Fix the view post_stats in post_stats.sql. Each post's comments must be the number of rows in comments for that post: 0 for a post with no comments, 3 for a post with three. Every post is listed exactly once, whether it has comments or not.
Keep the two tables as they are, and keep the view's name and its columns: post_id, title, comments. The check uses its own posts and comments, and a post that drops off the dashboard is as wrong as one that shows 1.
- Task 3
post_stats.test.sql: a test that would have caught it
Write a SQL test file, post_stats.test.sql, that would have caught this bug before the dashboard shipped. Use the lab's ok(condition, name) and is(got, want, name): each call is one test. The check runs your file four times, each in a fresh database where a version of post_stats.sql has just made the tables: beside the original post_stats.sql at least one test must fail; beside the fixed post_stats.sql every test must pass and nothing may raise an error; beside a hotfix that uses an inner JOIN at least one test must fail; beside a hotfix that subtracts 1 from every count at least one test must fail.
So add a post with no comments and a post with three, and test both sides: the quiet post is listed with 0, and the busy post shows 3. Run it with node run.mjs post_stats.test.sql.
What you will learn
- repro.sql: a comment that is not there, on purpose
- post_stats.sql: new posts show 0 comments
- post_stats.test.sql: a test that would have caught it
More in Everyday query traps
Build "COUNT(*) against COUNT(column)" yourself
A year after Chai aur SQL, MingleSphere sends a Sunday digest, pays its creators and shows investors a dashboard, and things break the way they break in real teams. Anya, the lead backend engineer, hands you each ticket. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.