Unrecognized name

BigQuery found a name that is not a column, alias or table in scope at that point of the query. The usual causes are a SELECT alias used in WHERE, a struct field without its parent, a typo, a table alias that does not exist, and a column that a CTE renamed.

Unrecognized name: double_amount at [4:7]

What BigQuery means

BigQuery looked for that name in the places the query can see at that spot, and found nothing. [4:7] is line 4, column 7: the first character of the name. The names a spot can see depend on the clause it is in:

• the columns of the tables in FROM and their joins,
• the aliases and struct fields those tables bring,
• names from an outer query, for a correlated subquery.

Often the name exists in the query but not at that spot. Check where it is defined and where it is read before you check the spelling.

When a similar name is in scope, the message adds it:

Unrecognized name: amout; Did you mean amount? at [2:8]

No suggestion does not mean the name is far off. Did you mean appears only when something close is in scope. A struct field that needs its parent gets none.

The smallest query that reproduces it

WITH orders AS (SELECT 10 AS amount)
SELECT amount * 2 AS double_amount
FROM orders
WHERE double_amount > 10

Unrecognized name: double_amount at [4:7]

WHERE runs before the SELECT list is computed, so it cannot see double_amount yet.

The causes and the fixes

A SELECT alias used in WHERE

WHERE cannot read an alias from the same SELECT. Repeat the expression, or compute it in a subquery:

SELECT amount * 2 AS double_amount
FROM orders
WHERE amount * 2 > 10

SELECT *
FROM (SELECT amount * 2 AS double_amount FROM orders)
WHERE double_amount > 10

The same applies to another item in the same select list. SELECT 1 AS x, x + 1 AS y fails with Unrecognized name: x. GROUP BY, HAVING and ORDER BY can read an alias, so ORDER BY double_amount is fine.

BigQuery has an option, reusable_column_aliases_mode, that lets WHERE and later select items read an alias. It is off unless an administrator turns it on, so a query that works in one project can fail in another. See Reusable column aliases.

A struct field without its parent

A field inside a STRUCT is reached through the column that holds it. city is not a column of orders; address.city is:

WITH orders AS (SELECT STRUCT('x' AS city) AS address)
SELECT city
FROM orders

Unrecognized name: city at [2:8]

SELECT address.city
FROM orders

The output column is named city. A field inside an ARRAY<STRUCT<...>> needs UNNEST as well, and gives a different error: see Cannot access field on a value with type ARRAY.

A typo

BigQuery usually names the right column:

WITH orders AS (SELECT 10 AS amount)
SELECT amout
FROM orders

Unrecognized name: amout; Did you mean amount? at [2:8]

The same message appears in GROUP BY and ORDER BY: GROUP BY amout and ORDER BY totl both come back with a suggestion. Names are not case sensitive, so AMOUNT is fine.

An unquoted value

A word without quotes is a name. A string needs quotes:

WITH orders AS (SELECT 'a' AS status)
SELECT *
FROM orders
WHERE status = active

Unrecognized name: active at [4:16]

WHERE status = 'a'

Double quotes also make a string in BigQuery, so "active" works. Backticks make a name.

A table alias that does not exist

Once a table has an alias, only the alias works. The table name no longer does:

WITH orders AS (SELECT 'a' AS customer_id, 10 AS amount)
SELECT amount
FROM orders AS o
WHERE orders.customer_id = 'a'

Unrecognized name: orders at [4:7]

Use o.customer_id. A prefix that was never defined gives the same error:

WITH orders AS (SELECT 10 AS amount, 'a' AS customer_id),
customers AS (SELECT 'a' AS customer_id, 'Ann' AS name)
SELECT o.amount
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
WHERE d.name = 'Ann'

Unrecognized name: d at [5:7]

Check the aliases in the FROM and JOIN clauses.

When the qualifier exists but the column does not, the message changes:

WITH orders AS (SELECT 'a' AS customer_id, 10 AS amount)
SELECT o.amout
FROM orders AS o

Name amout not found inside o at [2:10]

A table that is not in scope

A subquery can read the columns of the query around it, but only through a name the outer query defines. If the outer FROM has no o, o.customer_id inside the subquery fails with Unrecognized name: o:

WITH orders AS (SELECT 'a' AS customer_id, 10 AS amount)
SELECT amount,
  (SELECT 1 FROM orders AS i WHERE i.customer_id = o.customer_id) AS x
FROM orders

Unrecognized name: o at [3:52]

Give the outer table the alias: FROM orders AS o. See also Subqueries.

A column a CTE renamed

A CTE exposes the names in its SELECT list, not the names of the tables inside it:

WITH orders AS (SELECT 10 AS amount),
totals AS (SELECT amount AS total FROM orders)
SELECT amount
FROM totals

Unrecognized name: amount at [3:8]

Use total, or keep the old name in the CTE. SELECT * in the CTE keeps every original name.

The variants of the message

Some functions are written without brackets, such as CURRENT_DATE. Most are not, and SELECT GENERATE_UUID fails with Unrecognized name. Write GENERATE_UUID().

Queries that look wrong but run

An alias in GROUP BY, HAVING or ORDER BY. All three can read a SELECT alias:

SELECT amount * 2 AS d FROM orders GROUP BY d HAVING d > 1

A name in a different case. SELECT AMOUNT FROM orders runs. The column comes back as AMOUNT, as typed.

A struct field with its parent. WHERE address.city = 'x' is fine.

A correlated subquery with an alias. FROM orders AS o and o.customer_id in the subquery runs.

Different errors that look like this one

Table "customers" must be qualified with a dataset (e.g. dataset.table). A table name with no dataset, including a CTE name you forgot to define. BigQuery thinks you meant a table in a dataset.

Column name customer_id is ambiguous. The name exists twice. Qualify it with an alias.

SELECT list expression references column x which is neither grouped nor aggregated. The column exists, but it is not in the GROUP BY and not inside an aggregate. See neither grouped nor aggregated.

Cannot access field a on a value with type ARRAY<...>. The parent exists, but is an array. See Cannot access field on a value with type ARRAY.

Field name b does not exist in STRUCT<a INT64>. The parent is a struct and the field name is wrong.

In Querylab.io

Querylab.io checks names against the tables and CTEs in FROM as you type. For the queries above it shows:

Names inside a subquery in FROM are not checked. SELECT amout FROM (SELECT 10 AS amount) and SELECT * FROM (SELECT 10 AS amount) WHERE amout = 1 showed no diagnostics, so BigQuery’s dry run is what reports them, when you pause. Move the subquery into a WITH clause and the editor checks it.

When BigQuery’s dry run or a failed run returns Unrecognized name: amout; Did you mean amount?, Querylab.io offers the suggestion as a fix that replaces the name. The fix uses BigQuery’s hint as it is, so read the hint before you apply it. For a table name used after the table was aliased, as in SELECT orders.amount FROM orders o, the hint names an unrelated column, and the right fix is the alias o. A bare function name such as GENERATE_UUID gets a fix that adds the brackets.