Common BigQuery errors and how to fix them

What the most common BigQuery error messages mean and how to fix each one, from "Unrecognized name" and "neither grouped nor aggregated" to "Cannot access field on a value with type ARRAY", "Resources exceeded" and "Query exceeded limit for bytes billed".

Each error below was produced on BigQuery with the query shown, so the message is exactly what you will see. The position at the end, like at [6:8], is the line and column in your query. Errors in queries are free: a query that fails before it reads data isn’t billed. See How a query is billed.

Name and column errors

Unrecognized name

WITH orders AS (
  SELECT 'c1' AS customer_id, 'o1' AS order_id, 30 AS amount, [STRUCT('apple' AS sku, 2 AS qty)] AS lines UNION ALL
  SELECT 'c1', 'o2', 45, [STRUCT('pear', 1)] UNION ALL
  SELECT 'c2', 'o3', 20, [STRUCT('plum', 6)]
)
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer

The column, alias or table alias isn’t defined where you use it. BigQuery often suggests the closest name. Other causes: an alias defined in the same SELECT used in WHERE (aliases aren’t visible there), or a column that is inside a struct and needs the struct name in front.

Table must be qualified with a dataset

SELECT * FROM orders

BigQuery needs dataset.table (or project.dataset.table), unless the job has a default dataset or you are in a script that created a temp table with that name. See Projects, datasets and tables.

Not found: Table … was not found in location

SELECT * FROM `bigquery-public-data.samples.shakespear`

Either the name is wrong (table and dataset names are case-sensitive), you lack access, or the table exists but in a different location than the one the query runs in.

Syntax error: Unexpected keyword

SELECT 1 AS order

ORDER, GROUP, AT, ALL, END and other reserved words can’t be bare names. Quote them with backticks, AS `order`, or pick another name.

Grouping errors

SELECT list expression references column which is neither grouped nor aggregated

WITH orders AS (
  SELECT 'c1' AS customer_id, 'o1' AS order_id, 30 AS amount, [STRUCT('apple' AS sku, 2 AS qty)] AS lines UNION ALL
  SELECT 'c1', 'o2', 45, [STRUCT('pear', 1)] UNION ALL
  SELECT 'c2', 'o3', 20, [STRUCT('plum', 6)]
)
SELECT customer_id, order_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id

Every selected column must be in GROUP BY or inside an aggregate like SUM or ANY_VALUE. Add it to GROUP BY (or use GROUP BY ALL), or aggregate it. If you want one whole row per group, use QUALIFY with ROW_NUMBER() instead of GROUP BY.

The same applies to ORDER BY after a GROUP BY:

WITH orders AS (
  SELECT 'c1' AS customer_id, 'o1' AS order_id, 30 AS amount, [STRUCT('apple' AS sku, 2 AS qty)] AS lines UNION ALL
  SELECT 'c1', 'o2', 45, [STRUCT('pear', 1)] UNION ALL
  SELECT 'c2', 'o3', 20, [STRUCT('plum', 6)]
)
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
ORDER BY order_id

Type errors

No matching signature for operator

WITH orders AS (
  SELECT 'c1' AS customer_id, 'o1' AS order_id, 30 AS amount, [STRUCT('apple' AS sku, 2 AS qty)] AS lines UNION ALL
  SELECT 'c1', 'o2', 45, [STRUCT('pear', 1)] UNION ALL
  SELECT 'c2', 'o3', 20, [STRUCT('plum', 6)]
)
SELECT order_id
FROM orders
WHERE amount = '30'

BigQuery doesn’t convert strings to numbers for you, not even a string literal. Write the literal with the right type (30), or convert with CAST / SAFE_CAST. The same error appears when a STRING column meets a number:

WITH orders AS (
  SELECT 'c1' AS customer_id, 'o1' AS order_id, 30 AS amount, [STRUCT('apple' AS sku, 2 AS qty)] AS lines UNION ALL
  SELECT 'c1', 'o2', 45, [STRUCT('pear', 1)] UNION ALL
  SELECT 'c2', 'o3', 20, [STRUCT('plum', 6)]
)
, typed AS (SELECT order_id, CAST(amount AS STRING) AS amount_text FROM orders)
SELECT order_id
FROM typed
WHERE amount_text = 30

The Input types for <T1> line tells you which two types met. See How BigQuery SQL differs.

Cannot access field on a value with type ARRAY

WITH orders AS (
  SELECT 'c1' AS customer_id, 'o1' AS order_id, 30 AS amount, [STRUCT('apple' AS sku, 2 AS qty)] AS lines UNION ALL
  SELECT 'c1', 'o2', 45, [STRUCT('pear', 1)] UNION ALL
  SELECT 'c2', 'o3', 20, [STRUCT('plum', 6)]
)
SELECT order_id, lines.sku
FROM orders

lines is an array, so it has no sku field; each of its elements does. Unnest the array and read the field from each element:

WITH orders AS (
  SELECT 'c1' AS customer_id, 'o1' AS order_id, 30 AS amount, [STRUCT('apple' AS sku, 2 AS qty)] AS lines UNION ALL
  SELECT 'c1', 'o2', 45, [STRUCT('pear', 1)] UNION ALL
  SELECT 'c2', 'o3', 20, [STRUCT('plum', 6)]
)
SELECT order_id, line.sku
FROM orders, UNNEST(lines) AS line
ORDER BY order_id

See UNNEST.

Values referenced in UNNEST must be arrays

WITH orders AS (
  SELECT 'c1' AS customer_id, 'o1' AS order_id, 30 AS amount, [STRUCT('apple' AS sku, 2 AS qty)] AS lines UNION ALL
  SELECT 'c1', 'o2', 45, [STRUCT('pear', 1)] UNION ALL
  SELECT 'c2', 'o3', 20, [STRUCT('plum', 6)]
)
SELECT x
FROM orders, UNNEST(amount) AS x

UNNEST takes an array. For a JSON array use UNNEST(JSON_QUERY_ARRAY(...)), and for a comma-separated string use UNNEST(SPLIT(s, ',')).

Scalar subquery produced more than one element

WITH orders AS (
  SELECT 'c1' AS customer_id, 'o1' AS order_id, 30 AS amount, [STRUCT('apple' AS sku, 2 AS qty)] AS lines UNION ALL
  SELECT 'c1', 'o2', 45, [STRUCT('pear', 1)] UNION ALL
  SELECT 'c2', 'o3', 20, [STRUCT('plum', 6)]
)
SELECT (SELECT order_id FROM orders WHERE customer_id = 'c1') AS first_order

A subquery used as a single value must return at most one row. Add a condition that makes it unique, aggregate (MAX(...), ARRAY_AGG(...)), or add LIMIT 1 with an ORDER BY if any one row will do.

Errors from data values

These depend on the data, so a query can work for months and fail on the first bad row:

Cost and quota errors

Resources exceeded during query execution

This is the error for a query that ran out of memory or disk on a slot, not for one that was too expensive. The text after the colon says why:

• The query could not be executed in the allotted memory ... Top memory consumer(s): ORDER BY operations: a global ORDER BY on a large result, without LIMIT or with a large OFFSET. Sort less (ORDER BY ... LIMIT with no big OFFSET; page with ROW_NUMBER() instead), or don’t sort a result that is written to a table.
• Not enough resources for query planning - too many subqueries or query is too complex: a query with very many CTEs, subqueries or views stacked on views. Split it into steps with temporary tables.
• Your project or organization exceeded the maximum disk and memory limit available for shuffle operations: too much data moved between stages, often from a join that multiplies rows or from skewed keys. Filter and aggregate before joining; see Optimizing joins.

The usual fix is to change the shape of the query. Only the shuffle error also responds to capacity: Google suggests more reservation slots or fewer concurrent queries, and on-demand is capped at 2,000 slots per project. The execution graph shows which stage failed.

Where to find the full error

In Querylab.io, dry-run errors appear in the status bar, and many of the errors above (an undefined column, a column that isn’t grouped, a missing partition filter) are underlined as you type (Code quality). The query history tab keeps failed jobs, and its search matches error text (Query history). The BigQuery console shows the message too. bq show -j <job_id> or INFORMATION_SCHEMA.JOBS (error_result) give the reason code too, such as invalidQuery, resourcesExceeded, quotaExceeded, usageQuotaExceeded, rateLimitExceeded or accessDenied. A rateLimitExceeded error is temporary and worth retrying. The others need a change to the query or the setup.