# 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".

Source: https://docs.querylab.io/bigquery/troubleshooting-errors/

Updated: 2026-10-30

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](https://docs.querylab.io/bigquery/how-queries-are-billed/).

This page is the short version. Many of these messages have a page of their own with every variant and fix, listed in [BigQuery error messages](https://docs.querylab.io/bigquery-errors/).

## Name and column errors

### Unrecognized name

**a column that doesn't exist**

```sql
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
```

BigQuery rejects this query:

```text
Unrecognized name: customer; Did you mean customer_id? at [6:8]
```

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. See [Unrecognized name](https://docs.querylab.io/bigquery-errors/unrecognized-name/) for every cause.

### Table must be qualified with a dataset

**FROM orders with no dataset**

```sql
SELECT * FROM orders
```

BigQuery rejects this query:

```text
Table "orders" must be qualified with a dataset (e.g. dataset.table).
```

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 [Table must be qualified with a dataset](https://docs.querylab.io/bigquery-errors/table-must-be-qualified-with-a-dataset/) and [Projects, datasets and tables](https://docs.querylab.io/bigquery/projects-datasets-tables/).

### Table was not found in location

**a misspelled public table**

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

BigQuery rejects this query:

```text
Not found: Table bigquery-public-data:samples.shakespear was not found in location US
```

Either the name is wrong (table and dataset names are case-sensitive), you lack access, or the table exists but in a different [location](https://docs.querylab.io/bigquery/locations/) than the one the query runs in. See [Table was not found in location](https://docs.querylab.io/bigquery-errors/not-found-table-was-not-found-in-location/), and for the same error on a dataset, [Dataset was not found in location](https://docs.querylab.io/bigquery-errors/not-found-dataset-was-not-found-in-location/). A table you can't read gives a different message: [Access Denied: User does not have permission to query table](https://docs.querylab.io/bigquery-errors/access-denied-user-does-not-have-permission-to-query-table/).

### Syntax error: Unexpected keyword

**a reserved word as an alias**

```sql
SELECT 1 AS order
```

BigQuery rejects this query:

```text
Syntax error: Unexpected keyword ORDER at [1:13]
```

`ORDER`, `GROUP`, `AT`, `ALL`, `END` and other reserved words can't be bare names. Quote them with backticks, `` AS `order` ``, or pick another name. See [Syntax error: Unexpected keyword](https://docs.querylab.io/bigquery-errors/syntax-error-unexpected-keyword/).

## Grouping errors

### Neither grouped nor aggregated

**order_id isn't grouped**

```sql
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
```

BigQuery rejects this query:

```text
SELECT list expression references column order_id which is neither grouped nor aggregated at [6:21]
```

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](https://docs.querylab.io/bigquery/qualify/) with `ROW_NUMBER()` instead of `GROUP BY`. See [Neither grouped nor aggregated](https://docs.querylab.io/bigquery-errors/neither-grouped-nor-aggregated/) for all four variants of the message.

The same applies to `ORDER BY` after a `GROUP BY`:

**ORDER BY a column that isn't grouped**

```sql
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
```

BigQuery rejects this query:

```text
ORDER BY clause expression references column order_id which is neither grouped nor aggregated at [9:10]
```

## Type errors

### No matching signature for operator

**an INT64 column compared with '30'**

```sql
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 rejects this query:

```text
No matching signature for operator = for argument types: INT64, STRING
  Signature: T1 = T1
    Unable to find common supertype for templated argument <T1>
      Input types for <T1>: {INT64, STRING} at [8:7]
```

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:

**a STRING column compared with 30**

```sql
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
```

BigQuery rejects this query:

```text
No matching signature for operator = for argument types: STRING, INT64
  Signature: T1 = T1
    Unable to find common supertype for templated argument <T1>
      Input types for <T1>: {INT64, STRING} at [9:7]
```

The `Input types for <T1>` line tells you which two types met. See [No matching signature for operator](https://docs.querylab.io/bigquery-errors/no-matching-signature-for-operator/) and [How BigQuery SQL differs](https://docs.querylab.io/bigquery/googlesql/).

### Cannot access field on an ARRAY

**lines.sku on an array of structs**

```sql
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
```

BigQuery rejects this query:

```text
Cannot access field sku on a value with type ARRAY<STRUCT<sku STRING, qty INT64>> at [6:24]
```

`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:

**UNNEST first, then read the field**

```sql
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
```

Result in BigQuery (3 rows):

| order_id | sku |
| --- | --- |
| o1 | apple |
| o2 | pear |
| o3 | plum |

See [UNNEST](https://docs.querylab.io/bigquery/unnest/) and [Cannot access field on a value with type ARRAY](https://docs.querylab.io/bigquery-errors/cannot-access-field-on-a-value-with-type-array/).

### UNNEST values must be arrays

**UNNEST of an INT64**

```sql
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
```

BigQuery rejects this query:

```text
Values referenced in UNNEST must be arrays. UNNEST contains expression of type INT64 at [7:21]
```

`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: more than one element

**a subquery that returns two rows**

```sql
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
```

BigQuery rejects this query:

```text
Scalar subquery produced more than one element
```

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. BigQuery doesn't say which subquery failed. Querylab.io highlights the ones in your query that can return more than one row. See [Scalar subquery produced more than one element](https://docs.querylab.io/bigquery-errors/scalar-subquery-produced-more-than-one-element/).

## Errors from data values

These depend on the data, so a query can work for months and fail on the first bad row. Four have their own pages: [Bad int64 value](https://docs.querylab.io/bigquery-errors/bad-int64-value/), [Failed to parse input string](https://docs.querylab.io/bigquery-errors/failed-to-parse-input-string/), [Division by zero](https://docs.querylab.io/bigquery-errors/division-by-zero/) and [UPDATE/MERGE must match at most one source row](https://docs.querylab.io/bigquery-errors/update-merge-must-match-at-most-one-source-row/).

| Message                                                                                   | Cause                                                           | Fix                                                                                 |
| ----------------------------------------------------------------------------------------- | --------------------------------------------------------------- | ----------------------------------------------------------------------------------- |
| `Bad int64 value: n/a` (also `Bad double value`, `Invalid NUMERIC value`, `Invalid date`) | a `CAST` of text that isn't a valid value                       | `SAFE_CAST`, and find the bad rows; see [SAFE functions](https://docs.querylab.io/bigquery/safe-functions/) |
| `division by zero: 10 / 0`                                                                | dividing by zero                                                | `SAFE_DIVIDE(x, y)`                                                                 |
| `Array cannot have a null element; error in writing field x`                              | an array with a NULL in the output                              | `ARRAY_AGG(x IGNORE NULLS)`; see [NULL](https://docs.querylab.io/bigquery/null-handling/)                   |
| `UPDATE/MERGE must match at most one source row for each target row`                      | duplicate keys in the source of an `UPDATE ... FROM` or `MERGE` | deduplicate the source; see [MERGE](https://docs.querylab.io/bigquery/merge/)                               |
| `Operands of = cannot be literal NULL`                                                    | `x = NULL`                                                      | `x IS NULL`                                                                         |

## Cost and quota errors

| Message                                                                                                                              | Cause                                                                     | Fix                                                                                                         |
| ------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------- |
| `Query exceeded limit for bytes billed: 1000000. 10485760 or higher required.`                                                       | the query would bill more than its maximum bytes billed setting           | read fewer columns or partitions, or raise the limit; see [Controlling costs](https://docs.querylab.io/bigquery/controlling-costs/) |
| `Cannot query over table 'mydataset.orders' without a filter over column(s) 'order_date' that can be used for partition elimination` | the table has `require_partition_filter`                                  | add a constant filter on the partitioning column; see [Partitioning](https://docs.querylab.io/bigquery/partitioning/)               |
| `Custom quota exceeded: Your usage exceeded the custom quota for QueryUsagePerDay` (or `QueryUsagePerUserPerDay`)                    | a per-project or per-user daily byte quota set by an admin                | wait for the next day or ask for a higher quota                                                             |
| `Exceeded rate limits: too many table update operations for this table`                                                              | more than 5 metadata changes to one table in 10 seconds (loads, DDL, DML) | retry with backoff; batch the writes                                                                        |
| `Your table exceeded quota for imports or query appends per table`                                                                   | more than 1,500 loads or appends to one table in a day                    | batch files into fewer loads, or stream                                                                     |

Two of these have their own pages: [Cannot query over table without a filter](https://docs.querylab.io/bigquery-errors/cannot-query-over-table-without-a-filter-over-columns/) and, for the free tier's 1 TiB a month, [Quota exceeded: free query bytes scanned](https://docs.querylab.io/bigquery-errors/quota-exceeded-free-query-bytes-scanned/).

## Resources exceeded

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 full message is `Resources exceeded during query execution: <reason>`, and the reason 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](https://docs.querylab.io/bigquery/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](https://docs.querylab.io/bigquery/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](https://docs.querylab.io/bigquery/query-execution/) shows which stage failed. The memory case has its own page: [Resources exceeded: allotted memory](https://docs.querylab.io/bigquery-errors/resources-exceeded-allotted-memory/).

## 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](https://docs.querylab.io/writing-sql/code-quality/)). The query history tab keeps failed jobs, and its search matches error text ([Query history](https://docs.querylab.io/writing-sql/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.

See these errors flagged in the editor before the query runs. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Troubleshoot query issues](https://cloud.google.com/bigquery/docs/troubleshoot-queries)

- [Troubleshoot quota and limit errors](https://cloud.google.com/bigquery/docs/troubleshoot-quotas)

- [Error messages](https://cloud.google.com/bigquery/docs/error-messages)