Debugging functions

GoogleSQL for Bigtable supports the following debugging functions.

Function list

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   | 
 *--------*/