Syntax error: Unexpected keyword

BigQuery found a reserved word where a name, an expression or a clause had to be. The usual causes are a reserved word used as an alias or column name, a comma left in front of a clause, and a half-written expression. The word in the message is the one it could not use.

Syntax error: Unexpected keyword ORDER at [2:18]

What BigQuery means

BigQuery reads a word like ORDER, GROUP, END or ALL as part of the SQL grammar, so it only accepts it where the grammar allows it. This message says the word turned up at a spot where it is not allowed. [2:18] is line 2, column 18: the first character of that word.

There are two common reasons:

• The word was meant as a name, for an alias or a column. Reserved words cannot be names unless they are in backticks.
• The word is a real clause or keyword, but something before it is unfinished. A comma, an AND or an empty GROUP BY leaves the parser waiting for an expression, and the next keyword is not one.

The list of reserved words is in the lexical structure page of the GoogleSQL reference. It has about 100 words. Many common names are not on it, such as date, user, type, key and rank.

The smallest query that reproduces it

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

Syntax error: Unexpected keyword ORDER at [2:18]

The fix

Keep the word, put it in backticks:

WITH orders AS (SELECT 10 AS amount)
SELECT amount AS `order`
FROM orders

The backticks stay in the query wherever the name is used. Reading the column later needs them too:

WITH orders AS (SELECT 10 AS `order`)
SELECT `order`
FROM orders

Rename it. order_id, sort_order or total_rank need no quotes and cost nothing.

A field of a struct is fine after the dot. A reserved word can follow a . without backticks:

WITH orders AS (SELECT STRUCT(1 AS `order`, 2 AS `group`) AS s)
SELECT s.order, s.group
FROM orders

Creating the field still needs the backticks.

The causes

A reserved word as an alias

An alias after AS has to be a plain name. The same applies to a table alias and to a column name in the select list:

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

Syntax error: Unexpected keyword ORDER at [2:8]

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

Syntax error: Unexpected keyword ORDER at [3:16]

The same message comes back for group, end, at, all, select, from, limit, case, to, range, rows, current and default. SELECT 1 AS order and SELECT 1 AS Order fail the same way, because keywords ignore case.

A comma in front of a clause

A trailing comma is allowed at the end of the select list (see below). After FROM or ORDER BY a comma means that another item follows:

WITH orders AS (SELECT 10 AS amount, 1 AS id)
SELECT amount
FROM orders,
WHERE id = 1

Syntax error: Unexpected keyword WHERE at [4:1]

WITH orders AS (SELECT 10 AS amount, 1 AS id)
SELECT amount
FROM orders
ORDER BY amount, id,
LIMIT 5

Syntax error: Unexpected keyword LIMIT at [5:1]

The position is the keyword after the stray comma, on the next line. Look one token to the left.

A clause with nothing after it

AND, ELSE, GROUP BY and similar leave the parser waiting for an expression:

WITH orders AS (SELECT 10 AS amount, 1 AS id)
SELECT amount
FROM orders
WHERE id = 1 AND
ORDER BY amount

Syntax error: Unexpected keyword ORDER at [5:1]

WITH orders AS (SELECT 10 AS amount)
SELECT amount, COUNT(*)
FROM orders
GROUP BY
ORDER BY amount

Syntax error: Unexpected keyword ORDER at [5:1]

WITH orders AS (SELECT 10 AS amount)
SELECT CASE WHEN amount > 5 THEN "big" ELSE END AS size
FROM orders

Syntax error: Unexpected keyword END at [2:45]

If the query simply stops after WHERE, the message is Syntax error: Unexpected end of script instead.

The variants of the message

Each line was produced by BigQuery:

Syntax error: Unexpected keyword GROUP at [1:13]
Syntax error: Unexpected keyword END at [1:13]
Syntax error: Unexpected keyword AT at [1:13]
Syntax error: Unexpected keyword ALL at [1:13]
Syntax error: Unexpected keyword JOIN at [1:44]

The word is always printed in capitals, whatever case you wrote. [1:13] is SELECT 1 AS <word>. [1:44] is a comma in front of JOIN:

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

Queries that look wrong but run

A trailing comma at the end of the select list. The list may end with a comma when FROM follows:

WITH orders AS (SELECT 10 AS amount, 1 AS id)
SELECT amount, id,
FROM orders

Words that are not reserved. date, user, type, key and rank work as aliases without backticks:

WITH orders AS (SELECT 10 AS amount)
SELECT amount AS total_rank, amount AS date, amount AS user
FROM orders

A reserved word in backticks. AS `order` is valid, as shown above.

Different errors that look like this one

Syntax error: Unexpected identifier "x". A word that is not reserved turned up in the wrong place. See Unexpected identifier.

Syntax error: Expected end of input but got keyword WHERE. The statement was complete and a clause came after it, such as WHERE after LIMIT. See Expected end of input.

Syntax error: Expected keyword ALL or keyword DISTINCT but got keyword SELECT. A UNION with no ALL or DISTINCT. See Expected X but got Y.

INNER JOIN must have an immediately following ON or USING clause. A JOIN with no ON. Add ON a.id = b.id, or write CROSS JOIN b.

Syntax error: SELECT list must not be empty. SELECT FROM t has no columns.

In Querylab.io

Querylab.io parses in the browser as you type and marks these before anything is sent to BigQuery, with BigQuery’s wording and the marker on the word: a reserved word as a column or table alias (AS order), a comma in front of WHERE or LIMIT, an AND or an empty GROUP BY followed by ORDER, and ELSE END. A JOIN with no ON is marked too, in the editor’s own words. A trailing comma before FROM is accepted, as in BigQuery.

There is no quick fix. Quote the reserved word with backticks or rename it.