Syntax error: Expected X but got Y

BigQuery was in the middle of a construct and the next token was not one it could accept. The message lists what it would have accepted. The usual causes are a UNION with no ALL or DISTINCT, a bracket that is never closed, and a missing keyword such as BY or AS.

Syntax error: Expected keyword ALL or keyword DISTINCT but got keyword SELECT at [3:1]

What BigQuery means

The parser was part way through a construct, such as a UNION or a function call, and the next token could not continue it. This message says which tokens would have worked (Expected ...) and which one it found (but got ...). [3:1] is line 3, column 1: the token it found.

Both halves are useful. The expected list names the construct. ALL or DISTINCT is a set operator, ")" is an open bracket, BY is an ORDER or GROUP without its BY. The got part is the token that came instead, which is often the start of the next clause. The mistake is normally just before it.

When the statement was complete and extra text followed, the message is a different one: Expected end of input. See Expected end of input.

The smallest query that reproduces it

SELECT 1 AS a
UNION
SELECT 2 AS a

Syntax error: Expected keyword ALL or keyword DISTINCT but got keyword SELECT at [3:1]

The fixes

UNION, INTERSECT with no ALL or DISTINCT

BigQuery does not accept a bare UNION. Say whether duplicates stay:

SELECT 1 AS a
UNION ALL
SELECT 2 AS a

SELECT 1 AS a
UNION DISTINCT
SELECT 2 AS a

UNION ALL keeps every row and does less work, because rows are not compared. UNION DISTINCT removes duplicate rows. INTERSECT gives the same error and works with INTERSECT DISTINCT. EXCEPT has its own message:

SELECT 1 AS a
EXCEPT
SELECT 2 AS a

EXCEPT must be followed by ALL, DISTINCT, or "(" at [2:1]

EXCEPT DISTINCT works.

In a chain, the error is at the bare UNION:

SELECT 1 AS a
UNION ALL
SELECT 2 AS a
UNION
SELECT 3 AS a

Syntax error: Expected keyword ALL or keyword DISTINCT but got keyword SELECT at [5:1]

A chain must use one operator throughout. If you change from UNION ALL to UNION DISTINCT partway, you get another message:

SELECT 1 AS a
UNION ALL
SELECT 2 AS a
UNION DISTINCT
SELECT 3 AS a

Syntax error: Different set operations cannot be used in the same query without using parentheses for grouping at [4:1]

Group the operation that goes first in brackets:

(SELECT 1 AS a UNION ALL SELECT 2 AS a)
UNION DISTINCT
SELECT 3 AS a

A bracket that is never closed

SELECT COUNT(* FROM UNNEST([1])

Syntax error: Expected ")" but got keyword FROM at [1:16]

The position is the first token after the point where the bracket should have closed. Count the brackets from the one that opens the call. Subqueries and WITH clauses give the same message:

SELECT 1 FROM (SELECT 1

Syntax error: Expected ")" but got end of script at [1:24]

WITH t AS (SELECT 1 AS a SELECT a FROM t

Syntax error: Expected ")" but got keyword SELECT at [1:26]

When the bracket holds a list, a comma would also have been fine, and the message says so:

SELECT 1 FROM UNNEST([1,2,3]

Syntax error: Expected ")" or "," but got end of script at [1:29]

Square brackets work the same way: SELECT [1, 2 gives Expected "]" but got end of script at [1:13].

A bracket around a single value asks for a comma first, because (a, b) is also valid: SELECT (1 + 2 gives Expected "," but got end of script at [1:14].

A missing comma in a list

Two values next to each other in an IN list:

WITH t AS (SELECT 1 AS a) SELECT a FROM t WHERE a IN (1 2)

Syntax error: Expected "," but got integer literal "2" at [1:57]

A keyword without its partner

GROUP and ORDER are always followed by BY, and a window’s PARTITION too:

WITH t AS (SELECT 1 AS a) SELECT a FROM t GROUP a

Syntax error: Expected @ for hint or keyword BY but got identifier "a" at [1:49]

The @ is for a query hint, which is why it is in the list. ORDER a and OVER (PARTITION a) give the same text. A SELECT 1 order that ends there gives Expected @ for hint or keyword BY but got end of script at [1:15].

CAST needs AS:

SELECT CAST(1 INT64)

Syntax error: Expected keyword AS but got identifier "INT64" at [1:15]

CASE needs WHEN, and an END:

SELECT CASE 1 = 1 THEN 2 END

Syntax error: Expected keyword WHEN but got keyword THEN at [1:19]

SELECT CASE WHEN 1 = 1 THEN 2 ELSE 3

Syntax error: Expected keyword END but got end of script at [1:37]

An outer join needs JOIN after LEFT. BigQuery found t where JOIN had to be:

WITH t AS (SELECT 1 AS a) SELECT * FROM t LEFT t AS u ON TRUE

Syntax error: Expected keyword JOIN but got identifier "t" at [1:48]

A statement that stops early

SELECT 1 AS a
UNION ALL

Syntax error: Expected "(" or keyword SELECT or keyword TABLE but got end of script at [2:10]

WITH t AS with nothing after it gives Expected "(" but got end of script at [1:10], and WITH t AS (SELECT 1 AS a), with nothing after it gives Expected keyword SELECT but got end of script at [1:27].

The variants of the message

Four more texts, each produced by BigQuery. A UNION at the end of the script:

SELECT 1 UNION

Syntax error: Expected keyword ALL or keyword DISTINCT but got end of script at [1:15]

A CAST with no closing bracket, where a FORMAT clause would also have been fine:

SELECT CAST(1 AS INT64

Syntax error: Expected ")" or keyword FORMAT but got end of script at [1:23]

A CASE that continues with FROM where ELSE or END had to be:

SELECT CASE WHEN 1 = 1 THEN 2 FROM UNNEST([1])

Syntax error: Expected keyword ELSE or keyword END but got keyword FROM at [1:31]

A MERGE clause that stops after WHEN MATCHED, where AND or THEN had to follow:

MERGE t USING u ON TRUE WHEN MATCHED

Syntax error: Expected keyword AND or keyword THEN but got end of script at [1:37]

Queries that look wrong but run

UNION ALL after UNION ALL. A chain of the same operator can be as long as you like:

SELECT 1 AS a
UNION ALL
SELECT 2 AS a
UNION ALL
SELECT 3 AS a

A trailing comma in the select list. SELECT 1 AS a, is valid.

Different errors that look like this one

Syntax error: Unexpected keyword X. The parser did not have a list of what it wanted. See Unexpected keyword.

Syntax error: Unexpected end of script. The query stops where an expression had to be, such as WHERE a IN ( or JOIN with nothing after it.

Syntax error: Expected end of input but got .... See Expected end of input.

Syntax error: Expected keyword MODULE or keyword PROTO but got .... The query began with import. See Expected keyword MODULE or keyword PROTO.

In Querylab.io

Querylab.io parses in the browser as you type and marks these before anything is sent to BigQuery. UNION or INTERSECT with no ALL or DISTINCT, EXCEPT with no ALL or DISTINCT, a set operation with nothing after it, an unclosed COUNT( and a CASE with no END get BigQuery’s wording at the same token.

An unclosed subquery or CAST, a list like IN (1 2) and GROUP without BY are marked in the editor’s own words, such as Expected ) to close subquery.

There is no quick fix for these errors. Add the missing keyword or bracket by hand.