Read the error and fix the query
A failed query tells you what went wrong and where. Most errors in the first weeks are one of a handful, and each message points at the thing to look at.
How it works
An error has two parts. The top is a plain sentence for learners. Under it is the text BigQuery wrote, and that is where the position and the types are. After Run it sits in small grey type under the sentence. After Check it follows the label Original message:. The position is written at [line:column]. Lines count from 1 at the top of the query and columns from 1 at the start of each line. It marks the spot where BigQuery stopped. Read the sentence for the idea, then use the original message to find the exact spot and the name or type it mentions.
Unrecognized name: km at [4:7]means no column, alias or table has that name where the query uses it. By default a name you give withASinSELECTis not visible inWHERE. AfterDid you meanBigQuery guesses a similar name, so check the guess against what you meant. The sentence may suggest quotes. Quotes make the name a text value, so use them for text and never for a column.- In a query with
GROUP BY, every column outside an aggregate must be inGROUP BY. That holds inSELECT,HAVINGandORDER BYalike. The message begins with the clause that holds the problem, and the position is inside that clause. The sentence may offer a function such asCOUNTorMAX. When a message offers two fixes, choose by what each row should mean. A condition on single rows belongs inWHERE, before the grouping. A condition on a total belongs inHAVING. No matching signature for operator = for argument types: STRING, INT64means the two sides have types that do not go together. The text afterInput types for <T1>:names the two types that met, here{INT64, STRING}. That list has its own fixed order, so it does not tell you which side is which. BigQuery does not turn a text value into a number, so write the number without quotes, or convert one side withCAST. Casting a number to text makes the comparison go letter by letter, so'9'counts as greater than'10'.- A column that holds an array has no fields of its own. The fields belong to its elements. Put
UNNESTinFROM, next to the table, as inFROM table, UNNEST(array_column) AS item, and selectitem.field.UNNESTcannot go inSELECTlike a function. Unnesting a value that is not an array fails as well. - A subquery used as a single value must return at most one row. When it returns more, the query fails with
Scalar subquery produced more than one element. That message has no position, because BigQuery finds it only while the query runs. - BigQuery stops at the first error it finds, so a query with two mistakes shows one at a time. Fix it, run again and read the new message.
- The Querylab.io editor often underlines an unknown name before you run. Use the underline as a pointer to the spot. Some errors have no underline, so the message is still the thing to read.
Common mistake
The quickest way to silence an error is often the wrong one. ANY_VALUE(x) returns one arbitrary value of x from each group. Using it, or casting a number to text, removes the message and changes the rows. Fix what the message names, then read the result.
Worked example
SELECT vehicle_id, test_result
FROM `learn.mot.fact_mot_test`
WHERE completed_date = '2024-06-15'The first version of this query wrote test_reslt. The original message was Unrecognized name: test_reslt; Did you mean test_result? at [1:20].
It gives the wrong name, a guess at the right one and the place: line 1, column 20, which is the start of test_reslt in the SELECT list.