Debugging functions

GoogleSQL for Bigtable supports the following debugging functions.

Function list

Name Summary
IFERROR Evaluates a try expression, and if an evaluation error is produced, returns the result of a catch expression.
ISERROR Evaluates a try expression, and if an evaluation error is produced, returns TRUE.
NULLIFERROR Evaluates a try expression, and if an evaluation error is produced, returns NULL.

IFERROR

IFERROR(try_expression, catch_expression)

Description

Evaluates try_expression.

When try_expression is evaluated:

  • If the evaluation of try_expression doesn't produce an error, then IFERROR returns the result of try_expression without evaluating catch_expression.
  • If the evaluation of try_expression produces a system error, then IFERROR produces that system error.
  • If the evaluation of try_expression produces an evaluation error, then IFERROR suppresses that evaluation error and evaluates catch_expression.

If catch_expression is evaluated:

  • If the evaluation of catch_expression doesn't produce an error, then IFERROR returns the result of catch_expression.
  • If the evaluation of catch_expression produces any error, then IFERROR produces that error.

Arguments

  • try_expression: An expression that returns a scalar value.
  • catch_expression: An expression that returns a scalar value.

The results of try_expression and catch_expression must share a supertype.

Return Data Type

The supertype for try_expression and catch_expression.

Example

In the following example, the query successfully evaluates try_expression.

SELECT IFERROR('a', 'b') AS result

/*--------*
 | result |
 +--------+
 | a      |
 *--------*/

In the following example, IFERROR catches an evaluation error in the try_expression and successfully evaluates catch_expression.

SELECT IFERROR(ERROR('a'), 'b') AS result

/*--------*
 | result |
 +--------+
 | b      |
 *--------*/

In the following query, the error is handled by the innermost IFERROR operation, IFERROR(ERROR('a'), 'b').

SELECT IFERROR(IFERROR(ERROR('a'), 'b'), 'c') AS result

/*--------*
 | result |
 +--------+
 | b      |
 *--------*/

In the following query, the error is handled by the outermost IFERROR operation, IFERROR(..., 'c').

SELECT IFERROR(IFERROR(ERROR('a'), ERROR('b')), 'c') AS result

/*--------*
 | result |
 +--------+
 | c      |
 *--------*/

In the following example, IFERROR catches an evaluation error in ERROR('a') and then evaluates ERROR('b'). Because there is also an evaluation error in ERROR('b'), IFERROR produces an evaluation error for ERROR('b').

SELECT IFERROR(ERROR('a'), ERROR('b')) AS result

--ERROR: OUT_OF_RANGE 'b'

ISERROR

ISERROR(try_expression)

Description

Evaluates try_expression.

  • If the evaluation of try_expression doesn't produce an error, then ISERROR returns FALSE.
  • If the evaluation of try_expression produces a system error, then ISERROR produces that system error.
  • If the evaluation of try_expression produces an evaluation error, then ISERROR returns TRUE.

Arguments

  • try_expression: An expression that returns a scalar value.

Return Data Type

BOOL

Example

In the following examples, ISERROR successfully evaluates try_expression.

SELECT ISERROR('a') AS is_error

/*----------*
 | is_error |
 +----------+
 | false    |
 *----------*/

SELECT ISERROR(2/1) AS is_error

/*----------*
 | is_error |
 +----------+
 | false    |
 *----------*/

In the following examples, ISERROR catches an evaluation error in try_expression.

SELECT ISERROR(ERROR('a')) AS is_error

/*----------*
 | is_error |
 +----------+
 | true     |
 *----------*/

SELECT ISERROR(2/0) AS is_error

/*----------*
 | is_error |
 +----------+
 | true     |
 *----------*/

NULLIFERROR

NULLIFERROR(try_expression)

Description

Evaluates try_expression.

  • If the evaluation of try_expression doesn't produce an error, then NULLIFERROR returns the result of try_expression.

  • If the evaluation of try_expression produces a system error, then NULLIFERROR produces that system error.

  • If the evaluation of try_expression produces an evaluation error, then NULLIFERROR returns NULL.

Arguments

  • try_expression: An expression that returns a scalar value.

Return Data Type

The data type for try_expression or NULL

Example

In the following example, NULLIFERROR successfully evaluates try_expression.

SELECT NULLIFERROR('a') AS result

/*--------*
 | result |
 +--------+
 | a      |
 *--------*/

In the following example, NULLIFERROR catches an evaluation error in try_expression.

SELECT NULLIFERROR(ERROR('a')) AS result

/*--------*
 | result |
 +--------+
 | NULL   |
 *--------*/