Reading BigQuery errors

A packet marked "refused"

A packet surfaced in the station's queue: queries that earlier operators sent to the archive years ago. All of them marked "refused". The commandant wants to know what they were asking about: perhaps the operators were looking for the same thing you are.

You open the first one — and see not data but the archive's answer: a few lines of text, coordinates, a list of some signatures. The archive was not silent. It was explaining what was wrong — in its own language.

QUERY: I have no translator for the language of refusals — I have you. Five queries in the packet, and the refusals differ. Let us take all five apart.

Refusal #1: Unrecognized name

The first query from the packet:

SELECT name, colonny
FROM fa_users
ORDER BY user_id;

The archive's answer:

Unrecognized name: colonny; Did you mean colony? [at 1:14]

Read it piece by piece — the order works for any BigQuery error:

1. Class: Unrecognized name — no such name (a column or an alias) was found in the query's scope.
2. Culprit: colonny — the name exactly as you wrote it.
3. Hint: Did you mean colony? — BigQuery looks for a similar name and more often than not guesses right. But it is a heuristic: with a two-letter typo there may be no hint at all, and sometimes it offers a similar column that is not the one you wanted. Check against the schema, not on faith.
4. Coordinates: [at 1:14] — line 1, character 14 of YOUR query. In a forty-line query that is the main navigator.

The fix is obvious: colony. The cell below is the repaired one. Now work the refusal archive yourself: put the typo colonny back, run it and compare the error text with the one we took apart.

Refusal #2: Table not found

SELECT action, COUNT(*) AS n
FROM fa_logs_20291113
GROUP BY action;

Table not found: fa_logs_20291113 [at 2:6]

The class speaks for itself: there is no such table. A migrant usually has three reasons for it:

• a typo in the name — as with a column, only the hint fires less often;
• the wrong dataset or project: the full address is project.dataset.table; it is easy to forget the backticks in it, and then FROM my-project.logs.events falls apart into the meaningless subtraction my - project;
• the table really is not there: our log is sharded by day — fa_logs_20291110, ..._11, ..._12. The query above asks for the shard of 13 November, which the archive never got to write.

And yes: table and dataset names in BigQuery are case-sensitive — FA_LOGS_20291112 and fa_logs_20291112 are different names to the archive (column and function names are not).

The last recorded day is 12 November:

Refusal #3: No matching signature — a wall of text that can be read

The third query in the packet wanted to add up the revenue:

SELECT SUM(status) AS paid_total
FROM fa_orders
WHERE status = 'paid';

No matching signature for aggregate function SUM
  Argument types: STRING
  Signature: SUM(INT64)
    Argument 1: Unable to coerce type STRING to expected type INT64
  Signature: SUM(UINT64)
    Argument 1: Unable to coerce type STRING to expected type UINT64
  ...
  Signature: SUM(NUMERIC)
    Argument 1: Unable to coerce type STRING to expected type NUMERIC
  ... [at 1:8]

It looks like a wall of text, but its structure is rigid:

1. Class: No matching signature — the function exists, but NOT with argument types like these.
2. What you passed: Argument types: STRING.
3. The list of signatures — every variant the function accepts and, for each of them, which argument did not fit (Unable to coerce ...). That is not noise, it is the list of what is allowed. The first question to ask of it is not "how do I cast the type" but "did the right argument even go into the function". Casting a type makes sense only when the argument is the correct one and the signature genuinely does not accept it.

Here the intent was a sum over total, while SUM was handed the string column status: the operator mixed up the columns. BigQuery, unlike MySQL, never coerces a string to a number in silence:

Refusals #4 and #5: types in a function and in an operator

The last two queries in the packet break the same way — on types. They are shown side by side for comparison, and the coordinates in the messages refer to each query separately, as if you were running it alone:

-- #4: a short order number — "the last two digits"
SELECT order_id, SUBSTR(order_id, 2, 2) AS short_id
FROM fa_orders;
-- No matching signature for function SUBSTR
--   Argument types: INT64, INT64, INT64
--   Signature: SUBSTR(STRING, INT64, [INT64])
--     Argument 1: Unable to coerce type INT64 to expected type STRING
--   ... [at 1:18]

-- #5: the docking fee out of a string config
SELECT order_id, total + '25' AS with_docking_fee
FROM fa_orders;
-- No matching signature for operator +
--   Argument types: NUMERIC, STRING
--   ... [at 1:18]

Both refusals come from one migrant habit: hoping for an implicit cast. MySQL forgave SUBSTR over a number; many languages forgive number + 'string'. BigQuery is strict: cast explicitly. In #4 the intent is a string one — so CAST(order_id AS STRING); in #5 it is numeric — so CAST('25' AS NUMERIC).

Both fixes in one cell:

Refusals the sandbox will never show you

The five refusals we took apart came down to three classes — an unknown name, a missing table and a signature mismatch: the archive (and our sandbox) finds them BEFORE reading any data, which is why such refusals are free. Production BigQuery has refusals of other levels too — the kind that leave a migrant staring:

• billing and quotas: Quota exceeded and its relatives — the query hit the project's limits or a disabled billing account; the fix is not in the SQL but in the project settings;
• the partition filter: Cannot query over table ... without a filter over column(s) ... that can be used for partition elimination — the table's owner has banned expensive queries that carry no partition filter; what that machinery is, the chapter "Money and scale" explains;
• the resource limit: Resources exceeded — the query is too greedy for the memory it was given; a frequent culprit is ORDER BY over an entire log with no LIMIT.

They cannot be reproduced in the sandbox — there is neither billing nor quotas here. Remember the fact itself: if the text of a refusal is not about names and not about types, read it as an invoice or as a ban, not as a bug in your SQL.