error

Applies to VAST Cluster 5.5.

Functions › Conditional and conversion functions › Session and diagnostics. Stops the query with the message you give it. Put it in a branch of a CASE or in coalesce, so that the query fails, with a message you choose, when it reaches data that breaks a rule.

Syntax

error(message)

Returns: nothing: when it's evaluated with a message, the query stops. Its type is "NULL", so inside a CASE or coalesce the expression takes the type of its other values.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_string_error. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of three items. The last one has no price:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_string_error"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.items"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, item VARCHAR, price_cents BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'pen', 150), "
    "(2, 'book', 1200), "
    "(3, 'lamp', NULL)")

[]
[]
[(3,)]

What it does

When error(message) is evaluated, the query stops and the client gets an error whose text is message; through the Python driver it's an OSError. No rows are returned.

It's evaluated only where the query reaches it. In CASE WHEN condition THEN error(...) ELSE value END, it runs only for the rows where the condition is true, and in coalesce(value, error(...)) only when value is NULL. So a query with a check passes when every row follows the rule, and fails on the first row that doesn't.

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run("SELECT error(NULL), CASE WHEN false THEN error('x') END, coalesce(1, error('x'))")
run("SELECT error('')")
run("SELECT coalesce(NULL, error('x'))")
run("SELECT typeof(error('x'))")
run("SELECT error(123)")

[(None, None, 1)]
OSError: 
OSError: x
OSError: x
InternalError: Binder Error: No function matches the given name and argument types 'error(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Stop with your own message:

run("SELECT error('boom')")

OSError: boom

Check a rule while you read: the query fails because lamp has no price:

run(f"SELECT id, item, CASE WHEN price_cents IS NULL THEN error('price is missing') ELSE price_cents END "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

OSError: price is missing

The same check passes when the rows that break it are filtered out first:

run(f"SELECT id, item, CASE WHEN price_cents IS NULL THEN error('price is missing') ELSE price_cents END "
    f"FROM {TABLE} WHERE id <> 3 ORDER BY id LIMIT 10")

[(1, 'pen', 150), (2, 'book', 1200)]

The same rule with coalesce:

run(f"SELECT id, coalesce(price_cents, error('price is missing')) FROM {TABLE} ORDER BY id LIMIT 10")

OSError: price is missing

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). error() is evaluated by the Query Engine, on the rows the scan returns:

• In the SELECT list, it's evaluated for each row that reaches it, after the WHERE conditions. A row that WHERE removes never reaches it, as the example with id <> 3 shows.
• In WHERE, a condition that contains error() is a FILTER step in the Query Engine, above the scan.

The plan of a check in WHERE shows the FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE CASE WHEN price_cents IS NULL THEN error('price is missing') "
     f"ELSE price_cents > 1000 END")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  CASE  WHEN ((price_cents │
│    IS NULL)) THEN (CAST   │
│ (error('price is missing')│
│     AS BOOLEAN)) ELSE     │
│  (price_cents > 1000) END │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│        price_cents        │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Find the rows before you fail on them. A check stops at the first row that breaks the rule and tells you nothing about the others. To see them all, select them: SELECT id FROM ... WHERE price_cents IS NULL.
• Filter first. Conditions in WHERE decide which rows reach the check; a comparison of a column with a constant can be evaluated in VAST DataBase. See pushdowns.

Limitations and errors

• The message must be a VARCHAR. A number or a list fails with No function matches the given name and argument types; cast it, or build the message with || or format.
• A NULL message doesn't stop the query. If you build the message from a column that can be NULL, wrap it in coalesce(..., 'unknown').

Similar functions

run("SELECT CASE WHEN TRY_CAST('ten' AS INTEGER) IS NULL THEN error('not a number') ELSE 1 END")
run("SELECT CAST('ten' AS INTEGER)")
run("SELECT TRY_CAST('ten' AS INTEGER), coalesce(NULL, 0)")

OSError: not a number
OSError: Could not convert string 'ten' to INT32
[(None, 0)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Conditional and conversion functions: CASE, coalesce, CAST and the others.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.