A join that compiles fine and dies at runtime

A column name invented inside a SQL string has no compiler behind it: the query builds, ships, and fails at execution with Error 1054 (42S22) Unknown column. The quieter sibling is a column that does exist but never matches, which returns NULL rows instead of an error — so the page loads, the column is empty, and nobody files a bug. Both are prevented by one pre-flight query against information_schema, not by review.

Two tables, one join, two different ways to be wrong. Both reproduced on this machine.

The fixture

CREATE TABLE channels (id INT PRIMARY KEY, name VARCHAR(32));
CREATE TABLE visits   (id INT PRIMARY KEY, channel_id INT, domain_name VARCHAR(64));
INSERT INTO channels VALUES (1,'a'),(2,'b');
INSERT INTO visits VALUES (10,1,'example.com'),(11,NULL,'other.org');

visits has domain_name. It does not have channel_domain.

Failure 1: the invented column name

SELECT c.id, c.name, v.channel_domain
FROM channels c LEFT JOIN visits v ON v.channel_id = c.id;
ERROR 1054 (42S22) at line 14: Unknown column 'v.channel_domain' in 'field list'

There is no compiler between you and this. The Go code that builds this string type-checks perfectly; the mistake is a typo inside a literal, and the first reader of that literal is the server at runtime. In a report handler it surfaces as a 500 with no useful trace beyond the query text.

Note the error is helpful: it names the column and the table alias. This is the lucky case.

Failure 2: the column that exists and never matches

The same join, with the right column:

SELECT c.id, c.name, v.domain_name
FROM channels c LEFT JOIN visits v ON v.channel_id = c.id ORDER BY c.id;
+----+------+-------------+
| id | name | domain_name |
+----+------+-------------+
|  1 | a    | example.com |
|  2 | b    | NULL        |
+----+------+-------------+

No error. Row 2 exists and its domain_name is NULL, because visits has a row with channel_id = NULL, which never equals anything. A LEFT JOIN is designed to produce this row; that is the point of LEFT.

Now hand it to Go and the silence compounds:

var domain string
err := rows.Scan(&id, &name, &domain)   // scan error: NULL → string is unsupported

Scan into a plain string and database/sql refuses, which at least tells you. Scan into sql.NullString instead and you get err == nil with domain.Valid == false — then the display layer prints domain.String, which is the empty string, and you get a blank cell on a page that renders perfectly.

That is the version we hit first: the page loaded, one column was blank, and the report was quietly incomplete for weeks.

The check that runs before the database does

Ask the schema, once, for every column your SQL mentions:

SELECT COUNT(*) AS found
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND table_name = 'visits'
  AND column_name = 'channel_domain';
-- found = 0 → this query cannot work

Two ways to use it:

  1. In CI, against a schema dump. Extract identifiers from the query, assert each one exists. No database server needed beyond the dump, and it runs in milliseconds.
  2. In the handler, once at start-up. Cheap, and it turns a runtime 1054 into a refusal to boot — which is the correct time to find out.

For the NULL shape, the check is different and belongs in the query itself:

SELECT COUNT(*) AS all_rows, COUNT(v.domain_name) AS matched
FROM channels c LEFT JOIN visits v ON v.channel_id = c.id;
-- 2 and 1 → one row is unmapped

COUNT(column) skips NULLs. The gap between the two counts is exactly the rows your join silently failed to explain.

How you know it worked

  • The failing query returns an error, not zero rows. After fixing the name, SELECT returns 2 rows. If it returns rows but a column is still empty, you fixed the name and not the join.
  • COUNT(*) equals COUNT(joined_column) for any join you intend to be complete. Equal counts mean every row matched; unequal means the number on the page is not the number you think.
  • The schema check is in CI. A test that passes because nobody ran it is the same as no test — make it fail deliberately once, with a fake column, before trusting it.

How to undo it

  • Wrong column name: fix the string, redeploy. No data changed; the rollback is the previous binary and there is nothing else to restore.
  • A join that never matched: this one has changed numbers. Do not silently correct the report — compute both versions, diff per entity, and keep the old snapshot. The fix moves money between rows.
  • Adding the missing column instead of fixing the name: ALTER TABLE ... ADD COLUMN is additive and safe for readers, but every writer now has a NULL to explain. Prefer fixing the query over expanding the schema, unless the column was genuinely wanted.
  • A migration that dropped or renamed a column: readers break at runtime, not at deploy. Roll the schema back before rolling the binary forward, or you ship a build that cannot serve.

Questions people actually ask

Why did the mistake survive code review?

Because the column name lived inside a Go string. It compiles, the handler builds, and the only checker is the database — which is not in the build pipeline unless you put a schema fixture there.

Error 1054 or silent NULL — which is worse?

The NULL. A 1054 is a 500 and a stack trace; a NULL is a page that renders with an empty column and a report that is quietly incomplete. We hit the NULL version in production first.

How do you catch it before deploying?

One query against information_schema.columns for every column your SQL mentions, run in CI against a schema dump. It takes milliseconds and it is the only check that runs without a database connection to production.

What about sqlx or an ORM with typed columns?

They help for SELECT into structs and for generated queries. They do not help for a hand-written join string — which is exactly where the invented column name lives.

Sources


Last checked: 2026-11-18 · Both failures reproduced on MySQL 8.4.13 (Ubuntu 26.04, WSL) with a two-table fixture; the error text and the NULL row are pasted from those runs.