enum_range

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Values of an ENUM type. Returns every value of an ENUM type as a list of VARCHAR, in the declared order. Only the type of the argument matters, not its value.

Syntax

enum_range(e)

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. 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"'
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 returns that list: ['low', 'mid', 'high']. The order is the declared one, not the alphabetical one: for ENUM('z', 'a') it's ['z', 'a'].

The argument only tells the function which type to look at, so the usual way to call it is with a NULL of the type: enum_range(NULL::ENUM('low', 'mid', 'high')). Any value of the type gives the same list, and on a column every row gets the same list.

The values are VARCHAR, so you can use the result with the list functions: unnest turns it into one row per value, and list_position gives a value's position, counting from 1. To go from a code to its value, index the list with the code plus 1: enum_range(NULL::ENUM('low', 'mid', 'high'))[enum_code('mid'::ENUM('low', 'mid', 'high')) + 1] is 'mid'.

NULLs and edge cases

The same cases, as the cluster returns them. They use the helpers from Set up the examples. BIG is an ENUM of 300 values:

BIG = "ENUM(" + ", ".join(f"'v{i}'" for i in range(300)) + ")"
run(f"SELECT enum_range(NULL::{LEVEL}), typeof(enum_range(NULL::{LEVEL})), enum_range('mid'::{LEVEL}), "
    "enum_range(NULL::ENUM('z', 'a')), enum_range(NULL::ENUM('only'))")
run(f"SELECT len(enum_range(NULL::{BIG}))")
run("SELECT enum_range(NULL)")
run(f"SELECT enum_range('low'::{LEVEL}, 'mid'::{LEVEL})")

[(['low', 'mid', 'high'], 'VARCHAR[]', ['low', 'mid', 'high'], ['z', 'a'], ['only'])]
[(300,)]
InternalError: Binder Error: This function needs an ENUM as an argument
InternalError: Binder Error: No function matches the given name and argument types 'enum_range(ENUM('low', 'mid', 'high'), ENUM('low', 'mid', 'high'))'. You might need to add explicit type casts.

Examples

One row per value of the type, and the position of a value in the list:

run(f"SELECT unnest(enum_range(NULL::{LEVEL}))")
run(f"SELECT list_position(enum_range(NULL::{LEVEL}), 'high'), "
    f"enum_range(NULL::{LEVEL})[enum_code('mid'::{LEVEL}) + 1]")

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

Check which requested priorities are valid values before you cast them. 'HIGH' and 'none' aren't in the list:

run(f"SELECT id, requested, list_contains(enum_range(NULL::{LEVEL}), requested) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'high', True), (2, 'HIGH', False), (3, 'mid', True), (4, None, None), (5, 'none', False)]

Keep only the tickets whose requested priority is valid:

run(f"SELECT id FROM {TABLE} WHERE list_contains(enum_range(NULL::{LEVEL}), requested) ORDER BY id LIMIT 10")

[(1,), (3,)]

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() is computed by the Query Engine:

• In the SELECT list, the Query Engine computes enum_range() and the functions around it 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(NULL::{LEVEL}), requested)")

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

Make it faster

• Validate values when you write them. If only valid values reach the table, you can filter with a plain comparison such as priority = 'mid', 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 = 'mid'")

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

Limitations and errors

• One argument. enum_range(from, to), as in some other SQL dialects, fails with No function matches the given name and argument types. For the values between two boundaries, use enum_range_boundary.
• Only ENUM types. A string, number or untyped NULL fails with This function needs an ENUM as an argument. Pass a NULL cast to the type: NULL::ENUM('low', 'mid', 'high').
• 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 returns VARCHAR values, so its result reads normally.

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

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

Similar functions

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

[(['low', 'mid', 'high'], ['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_boundary: the values between two boundaries.
• Binary, hash and UUID functions: all the enum functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.