GoogleSQL for Spanner supports the following debugging functions.
Function list
| Name | Summary |
|---|---|
ERROR
|
Produces an error with a custom error message. |
ERROR
ERROR
(
error_message
)
Description
Returns an error.
Definitions
-
error_message: ASTRINGvalue that represents the error message to produce. Any whitespace characters beyond a single space are trimmed from the results.
Details
ERROR
is treated like any other expression that may
result 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"))
SELECT
ERROR
(
'Show this error message'
)
In the following example, the query returns an error message if the value of the row doesn't 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
The following example demonstrates bad usage of the ERROR
function. In this
example, GoogleSQL might evaluate the ERROR
function before or after
the x > 0
WHERE
clause conditions. Therefore, the results with the ERROR
function might vary.
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 GoogleSQL 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
