Debugging Functions in Standard SQL

BigQuery supports the following debugging functions.

ERROR

ERROR(error_message)

Description

Returns an error. The error_message argument is a STRING.

BigQuery treats ERROR in the same way as any expression that may result in an error: there is no special guarantee of evaluation order.

Return Data Type

BigQuery infers the return type in context.

Examples

In the following example, the query returns an error message if the value of the row does not match one of two defined values.

SELECT
  CASE
    WHEN value = 'foo' THEN 'Value is foo.'
    WHEN value = 'bar' THEN 'Value is bar.'
    ELSE ERROR(concat('Found unexpected value: ', value))
  END AS new_value
FROM (
  SELECT 'foo' AS value UNION ALL
  SELECT 'bar' AS value UNION ALL
  SELECT 'baz' AS value);

Found unexpected value: baz

In the following example, BigQuery may evaluate the ERROR function before or after the x > 0 condition, because BigQuery generally provides no ordering guarantees between WHERE clause conditions and there are no special guarantees for the ERROR function.

SELECT *
FROM (SELECT -1 AS x)
WHERE x > 0 AND ERROR('Example error');

In the next example, the WHERE clause evaluates an IF condition, which ensures that BigQuery only evaluates the ERROR function if the condition fails.

SELECT *
FROM (SELECT -1 AS x)
WHERE IF(x > 0, true, ERROR(FORMAT('Error: x must be positive but is %t', x)));'

Error: x must be positive but is -1
Was this page helpful? Let us know how we did:

Send feedback about...

Need help? Visit our support page.