GoogleSQL for BigQuery supports the following debugging functions.

Function list

ERROR

ERROR(error_message)

Description

Returns an error.

Definitions

• error_message: ASTRING value that represents the error message toproduce. Any whitespace characters beyond asingle space are trimmed from the results.

Details

ERROR is treated like any other expression that mayresult in an error: there is no special guarantee of evaluation order.

Return Data Type

GoogleSQL infers the return type in context.

Examples

In the following example, the query produces an error message:

-- ERROR: Show this error message (while evaluating error("Show this error message"))SELECTERROR('Show this error message')

In the following example, the query returns an error message if the value of therow doesn't match one of two defined values.

SELECTCASEWHENvalue='foo'THEN'Value is foo.'WHENvalue='bar'THEN'Value is bar.'ELSEERROR(CONCAT('Found unexpected value: ',value))ENDASnew_valueFROM(SELECT'foo'ASvalueUNIONALLSELECT'bar'ASvalueUNIONALLSELECT'baz'ASvalue);-- Found unexpected value: baz

The following example demonstrates bad usage of theERROR function. In thisexample, GoogleSQL might evaluate theERROR function before or afterthex > 0 condition, because GoogleSQL doesn't guaranteeordering betweenWHERE clause conditions. Therefore, the results with theERROR function might vary.

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

In the next example, theWHERE clause evaluates anIF condition, whichensures that GoogleSQL only evaluates theERROR function if thecondition fails.

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