enum_range_boundary

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Values of an ENUM type. Returns the values of an ENUM type from one value to another, both included, as a list of VARCHAR in the declared order. A NULL boundary means the first or the last value.

Syntax

enum_range_boundary(from, to)

Returns: VARCHAR[].

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_enums_enum_range_boundary. 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.

ENUM isn't a column type, so the table stores the priority of five tickets as VARCHAR, and the queries cast it to the ENUM type held in LEVEL. The table also stores the priority's position (priority_code) and a requested priority that isn't always a valid value. Ticket 4 has no values. This block connects, defines the run() and plan() helpers, and creates the table:

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_enums_enum_range_boundary"'
LEVEL = "ENUM('low', 'mid', 'high')"

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.sub(r"^Parse 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, ValueError) 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}.tickets"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, priority VARCHAR, priority_code BIGINT, requested VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'low', 0, 'high'), "
    "(2, 'high', 2, 'HIGH'), "
    "(3, 'mid', 1, 'mid'), "
    "(4, NULL, NULL, NULL), "
    "(5, 'low', 0, 'none')")

[]
[]
[(5,)]

What it does

An ENUM type is a fixed list of values in a declared order, such as ENUM('low', 'mid', 'high'). enum_range_boundary returns the part of that list from from to to, both included: enum_range_boundary('low'::ENUM('low', 'mid', 'high'), 'mid'::ENUM('low', 'mid', 'high')) is ['low', 'mid'].

A NULL of the type as from starts the range at the first value, and as to ends it at the last value, so enum_range_boundary('mid'::ENUM(...), NULL::ENUM(...)) is "mid and above": ['mid', 'high']. With both NULL, it's the whole list, like enum_range. If from comes after to, the list is empty.

Pass constants as boundaries. A column as a boundary doesn't give a range per row: every row gets the same list. In enum_range_boundary(CAST(priority AS ENUM(...)), 'high'::ENUM(...)), a 'high' or 'mid' row also gets ['low', 'mid', 'high']. For a range that starts at each row's value, slice enum_range by the row's code, as in the last example.

NULLs and edge cases

In the table, 'mid' stands for 'mid'::ENUM('low', 'mid', 'high'). The same cases, as the cluster returns them. They use the helpers from Set up the examples:

run(f"SELECT enum_range_boundary('mid'::{LEVEL}, NULL::{LEVEL}), enum_range_boundary(NULL::{LEVEL}, 'mid'::{LEVEL}), "
    f"enum_range_boundary(NULL::{LEVEL}, NULL::{LEVEL}), enum_range_boundary(NULL, 'high'::{LEVEL})")
run(f"SELECT enum_range_boundary('mid'::{LEVEL}, 'mid'::{LEVEL}), enum_range_boundary('high'::{LEVEL}, 'low'::{LEVEL}), "
    f"typeof(enum_range_boundary('low'::{LEVEL}, 'high'::{LEVEL}))")
run("SELECT enum_range_boundary(NULL, NULL)")
run(f"SELECT enum_range_boundary('low'::{LEVEL}, 'high')")
run(f"SELECT enum_range_boundary('low'::{LEVEL}, 'b'::ENUM('a', 'b'))")
run(f"SELECT enum_range_boundary('low'::{LEVEL})")

[(['mid', 'high'], ['low', 'mid'], ['low', 'mid', 'high'], ['low', 'mid', 'high'])]
[(['mid'], [], 'VARCHAR[]')]
InternalError: Binder Error: This function needs an ENUM as an argument
InternalError: Binder Error: This function needs an ENUM as an argument
InternalError: Binder Error: The parameters need to link to ONLY one enum OR be NULL 
InternalError: Binder Error: No function matches the given name and argument types 'enum_range_boundary(ENUM('low', 'mid', 'high'))'. You might need to add explicit type casts.

Examples

The levels at mid and above, and up to mid:

run(f"SELECT enum_range_boundary('mid'::{LEVEL}, NULL::{LEVEL}), enum_range_boundary(NULL::{LEVEL}, 'mid'::{LEVEL})")

[(['mid', 'high'], ['low', 'mid'])]

Keep the tickets whose priority is mid or above, by testing the priority against the range:

run(f"SELECT id FROM {TABLE} WHERE list_contains(enum_range_boundary('mid'::{LEVEL}, NULL::{LEVEL}), priority) "
    "ORDER BY id LIMIT 10")

[(2,), (3,)]

With a column as from (first query) or as to (second query), every row gets the same list, whatever its value. To get the levels from each ticket's priority up, slice enum_range from the priority's code instead:

run(f"SELECT id, priority, enum_range_boundary(CAST(priority AS {LEVEL}), 'high'::{LEVEL}) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, priority, enum_range_boundary(NULL::{LEVEL}, CAST(priority AS {LEVEL})) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, priority, list_slice(enum_range(NULL::{LEVEL}), enum_code(CAST(priority AS {LEVEL})) + 1, 3) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'low', ['low', 'mid', 'high']), (2, 'high', ['low', 'mid', 'high']), (3, 'mid', ['low', 'mid', 'high']), (4, None, ['low', 'mid', 'high']), (5, 'low', ['low', 'mid', 'high'])]
[(1, 'low', ['low']), (2, 'high', ['low']), (3, 'mid', ['low']), (4, None, ['low']), (5, 'low', ['low'])]
[(1, 'low', ['low', 'mid', 'high']), (2, 'high', ['high']), (3, 'mid', ['mid', 'high']), (4, None, None), (5, 'low', ['low', 'mid', 'high'])]

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). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so a condition that uses enum_range_boundary() is computed by the Query Engine:

• In the SELECT list, the Query Engine computes the list for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes the condition and keeps the rows that match. No rows are skipped in storage.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE list_contains(enum_range_boundary('mid'::{LEVEL}, NULL::{LEVEL}), priority)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   list_contains(['mid',   │
│     'high'], priority)    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          priority         │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the position and compare it. "mid or above" is priority_code >= 1 when the table stores each value's enum_code. That compares a column with a constant, which VAST DataBase evaluates, so rows that don't match never leave storage. The plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE priority_code >= 1")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Limitations and errors

• A column as from or to gives the same list on every row. Pass constants, or use list_slice on enum_range for a range per row (see Examples).
• One ENUM type. Boundaries of two different ENUM types fail with The parameters need to link to ONLY one enum OR be NULL. A plain string, or two untyped NULLs, fails with This function needs an ENUM as an argument.
• Two arguments. One argument fails with No function matches the given name and argument types; for every value, use enum_range.
• ENUM isn't a column type. CREATE TABLE with an ENUM column fails with ENUM type is not supported in table definitions. Store the value as VARCHAR and cast in the query.
• The Python client can't read an ENUM value. It fails with Parser error: Cannot find a dictionary batch with dict id: 0. enum_range_boundary returns VARCHAR values, so its result reads normally.

Similar functions

run(f"SELECT enum_range_boundary('mid'::{LEVEL}, NULL::{LEVEL}), enum_range(NULL::{LEVEL}), "
    f"enum_first(NULL::{LEVEL}), enum_last(NULL::{LEVEL}), enum_code('mid'::{LEVEL})")

[(['mid', 'high'], ['low', 'mid', 'high'], 'low', 'high', 1)]

Clean up:

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

[]
[]

See also

• enum_range: every value of an ENUM type.
• Binary, hash and UUID functions: all the enum functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.