enum_first

Applies to VAST Cluster 5.5.

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

Syntax

enum_first(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_first. 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_first"'
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_first returns the first value of that list: 'low'. It's the first in the declared order, not alphabetically: for ENUM('z', 'a') it's 'z'.

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_first(NULL::ENUM('low', 'mid', 'high')). Any value of the type gives the same result: enum_first('high'::ENUM('low', 'mid', 'high')) is 'low' too, and on a column every row gets the same value.

The result is a VARCHAR, which the Python client reads as a string. To use it as an ENUM value again, cast it back to the type.

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_first(NULL::{LEVEL}), typeof(enum_first(NULL::{LEVEL})), enum_first('high'::{LEVEL}), "
    "enum_first(NULL::ENUM('z', 'a')), enum_first(NULL::ENUM('only'))")
run(f"SELECT enum_first(NULL::{BIG})")
run("SELECT enum_first(NULL)")
run("SELECT enum_first('low')")

[('low', 'VARCHAR', 'low', 'z', 'only')]
[('v0',)]
InternalError: Binder Error: This function needs an ENUM as an argument
InternalError: Binder Error: This function needs an ENUM as an argument

Examples

On a column, every row gets the first value of the type, also the row with no priority:

run(f"SELECT id, priority, enum_first(CAST(priority AS {LEVEL})) FROM {TABLE} ORDER BY id LIMIT 10")

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

Keep the tickets at the lowest priority, and the ones above it:

run(f"SELECT id FROM {TABLE} WHERE priority = enum_first(NULL::{LEVEL}) ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE CAST(priority AS {LEVEL}) > enum_first(NULL::{LEVEL})::{LEVEL} ORDER BY id LIMIT 10")

[(1,), (5,)]
[(2,), (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 cast or a function of a column, so the cast to ENUM and the comparison of ENUM values are computed by the Query Engine:

• In the SELECT list, the Query Engine computes enum_first() for each row it returns.
• In WHERE, a condition on the cast column runs in a FILTER step in the Query Engine, which receives the column for every row and keeps the rows that match.

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

plan(f"SELECT id FROM {TABLE} WHERE CAST(priority AS {LEVEL}) > enum_first(NULL::{LEVEL})::{LEVEL}")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (CAST(priority AS ENUM(  │
│ 'low', 'mid', 'high')) >  │
│           'low')          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          priority         │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare the stored text with a constant. priority = 'low' 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 = 'low'")

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

• Store the position for order comparisons. priority_code > 0 is pushed down too; see enum_code.

Limitations and errors

• 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_first returns a VARCHAR, so its result reads normally; cast other ENUM values to VARCHAR.

run(f"SELECT 'low'::{LEVEL}")
run(f"SELECT 'low'::{LEVEL}::VARCHAR, enum_first(NULL::{LEVEL})")

ArrowInvalid: Parser error: Cannot find a dictionary batch with dict id: 0
[('low', 'low')]

Similar functions

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

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

Clean up:

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

[]
[]

See also

• enum_last: the last 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.