FAILIF throws a custom error when a condition is true, and returns true when the condition is false. Use it to enforce data validation rules in SQL queries without additional application logic.
Syntax
BOOLEAN FAILIF(BOOLEAN <condition>, STRING <errMsg>);Parameters
| Parameter | Required | Type | Description |
|---|---|---|---|
condition | Yes | BOOLEAN | The condition expression to evaluate. |
errMsg | Yes | STRING | The error message to throw when condition is true. |
Return value
| Condition result | Behavior | Return type |
|---|---|---|
true | Throws an error. The error output contains system error codes (ODPS-0130071, ODPS-0121095) followed by the errMsg value. | — |
false | Returns true. | BOOLEAN |
Examples
Condition is true
When x = -1, the expression x < 0 evaluates to true, so FAILIF throws an error:
SELECT x, FAILIF(x<0,'Error: x must be positive') FROM (SELECT -1 AS x);Output:
ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: SQL Runtime Unretryable Error: ODPS-0121095:Invalid argument - Error: x must be positiveThe text after Invalid argument - is the errMsg value.
Condition is false
When x = 1, the expression x < 0 evaluates to false, so FAILIF returns true:
SELECT x, FAILIF(x<0,'Error: x must be positive') FROM (SELECT 1 AS x);Output:
+------------+------+
| x | _c1 |
+------------+------+
| 1 | true |
+------------+------+_c1 is the auto-generated column alias for the FAILIF expression.