Function calls in Google Standard SQL

Stay organized with collections Save and categorize content based on your preferences.

When you call a function, specific rules may apply. You can also add the SAFE. prefix, which prevents functions from generating some types of errors. To learn more, see the next sections.

Function call rules

The following rules apply to all functions unless explicitly indicated otherwise in the function description:

  • For functions that accept numeric types, if one operand is a floating point operand and the other operand is another numeric type, both operands are converted to FLOAT64 before the function is evaluated.
  • If an operand is NULL, the result is NULL, with the exception of the IS operator.
  • For functions that are time zone sensitive (as indicated in the function description), the default time zone, America/Los_Angeles, is used if a time zone is not specified.

SAFE. prefix

Syntax:

SAFE.function_name()

Description

If you begin a scalar function with the SAFE. prefix, it will return NULL instead of an error. The SAFE. prefix only prevents errors from the prefixed function itself: it doesn't prevent errors that occur while evaluating argument expressions. The SAFE. prefix only prevents errors that occur because of the value of the function inputs, such as "value out of range" errors; other errors, such as internal or system errors, may still occur. If the function doesn't return an error, SAFE. has no effect on the output.

Exclusions

  • Operators, such as + and =, don't support the SAFE. prefix. To prevent errors from a division operation, use SAFE_DIVIDE.
  • Some operators, such as IN, ARRAY, and UNNEST, resemble functions but don't support the SAFE. prefix.
  • The CAST and EXTRACT functions don't support the SAFE. prefix. To prevent errors from casting, use SAFE_CAST.

Example

In the following example, the first use of the SUBSTR function would normally return an error, because the function doesn't support length arguments with negative values. However, the SAFE. prefix causes the function to return NULL instead. The second use of the SUBSTR function provides the expected output: the SAFE. prefix has no effect.

SELECT SAFE.SUBSTR('foo', 0, -2) AS safe_output UNION ALL
SELECT SAFE.SUBSTR('bar', 0, 2) AS safe_output;

+-------------+
| safe_output |
+-------------+
| NULL        |
| ba          |
+-------------+

Function hints

The following hints are available for Google Standard SQL functions:

DISABLE_INLINE

function_name() @{DISABLE_INLINE = TRUE}

To disable other parts of a query from using the function as an inline expression, add the @{DISABLE_INLINE = TRUE} hint after a scalar function. This allows the function to be computed once instead of each time another part of a query references it.

DISABLE_INLINE works with top-level functions.

You can't use DISABLE_INLINE with a few functions, including those that don't produce a scalar value and CAST. Although you can't use DISABLE_INLINE with the CAST function, you can use it with the first expression inside this function.

Examples

In the following example, inline expressions are enabled by default for x. x is computed twice, once by each reference:

SELECT
  SUBSTRING(CAST(x AS STRING), 2, 5) AS w,
  SUBSTRING(CAST(x AS STRING), 3, 7) AS y
FROM (SELECT SHA512(z) AS x FROM t)

In the following example, inline expressions are disabled for x. x is computed once, and the result is used by each reference:

SELECT
  SUBSTRING(CAST(x AS STRING), 2, 5) AS w,
  SUBSTRING(CAST(x AS STRING), 3, 7) AS y
FROM (SELECT SHA512(z) @{DISABLE_INLINE = TRUE} AS x FROM t)