enum_code

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Position of a value. Returns the position of an ENUM value in its type's list of values, counting from 0.

Syntax

enum_code(e)

Returns: an unsigned integer: UTINYINT for ENUM('low', 'mid', 'high'), USMALLINT for an ENUM of 300 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_enums_enum_code. 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_code"'
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_code returns where a value sits in that list: 'low' is 0, 'mid' is 1 and 'high' is 2. The order is the declared one, not the alphabetical one.

You make an ENUM value with a cast: 'mid'::ENUM('low', 'mid', 'high'), or CAST(priority AS ENUM('low', 'mid', 'high')) on a column. The cast is exact: a string that isn't one of the values, even only in case or spacing, fails with Could not convert string '...' to UINT8. TRY_CAST returns NULL instead.

The result is unsigned, and arithmetic on it stays unsigned: enum_code('mid'::ENUM('low', 'mid', 'high')) + 1 is 2, but a subtraction below zero fails. Cast it to BIGINT for arithmetic.

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_code(NULL::{LEVEL}), typeof(enum_code(NULL::{LEVEL})), enum_code('only'::ENUM('only')), "
    f"enum_code(TRY_CAST('z' AS {LEVEL}))")
run(f"SELECT enum_code('v299'::{BIG}), typeof(enum_code('v299'::{BIG}))")
run(f"SELECT enum_code('LOW'::{LEVEL})")
run(f"SELECT enum_code(1::{LEVEL})")
run("SELECT enum_code(NULL)")
run("SELECT enum_code('low')")

[(None, 'UTINYINT', 0, None)]
[(299, 'USMALLINT')]
OSError: Could not convert string 'LOW' to UINT8
OSError: Unimplemented type for cast (INTEGER -> ENUM('low', 'mid', 'high'))
InternalError: Binder Error: This function needs an ENUM as an argument
InternalError: Binder Error: This function needs an ENUM as an argument

Examples

The position of each ticket's priority; it matches the stored priority_code:

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

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

Keep the tickets of priority mid or higher. Comparing the ENUM values directly gives the same rows:

run(f"SELECT id FROM {TABLE} WHERE enum_code(CAST(priority AS {LEVEL})) >= 1 ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE CAST(priority AS {LEVEL}) >= 'mid'::{LEVEL} ORDER BY id LIMIT 10")

[(2,), (3,)]
[(2,), (3,)]

Read the requested priority with TRY_CAST, so that a value that isn't in the list gives NULL. With CAST, such a value fails the whole query:

run(f"SELECT id, requested, enum_code(TRY_CAST(requested AS {LEVEL})) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, enum_code(CAST(requested AS {LEVEL})) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'high', 2), (2, 'HIGH', None), (3, 'mid', 1), (4, None, None), (5, 'none', None)]
OSError: Could not convert string 'HIGH' to UINT8

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

• In the SELECT list, the scan reads the column and the Query Engine computes the code 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. A comparison of two ENUM values runs the same way.

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

plan(f"SELECT id FROM {TABLE} WHERE enum_code(CAST(priority AS {LEVEL})) >= 1")

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

Make it faster

• Store the code if you filter on the order. Compute it when you write the rows and keep it in its own integer column: priority_code >= 1 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

• ENUM isn't a column type. CREATE TABLE with an ENUM column fails with ENUM type is not supported in table definitions, and CREATE TYPE ... AS ENUM isn't available. Store the value as VARCHAR, or its code as an integer, and cast in the query.
• The Python client can't read an ENUM value. SELECT 'mid'::ENUM('low', 'mid', 'high') fails in the client with Parser error: Cannot find a dictionary batch with dict id: 0. Return enum_code() or cast the value to VARCHAR.
• Only ENUM values. A string, number or untyped NULL fails with This function needs an ENUM as an argument; cast it first.
• Unsigned results. enum_code(...) - enum_code(...) below zero fails with Overflow in subtraction of UINT8 (0 - 1)!. Cast to BIGINT first.

run(f"SELECT 'mid'::{LEVEL}")
run(f"SELECT 'mid'::{LEVEL}::VARCHAR, enum_code('mid'::{LEVEL})")
run(f"SELECT enum_code('low'::{LEVEL}) - enum_code('mid'::{LEVEL})")
run(f"SELECT enum_code('low'::{LEVEL})::BIGINT - enum_code('mid'::{LEVEL})::BIGINT")

ArrowInvalid: Parser error: Cannot find a dictionary batch with dict id: 0
[('mid', 1)]
OSError: Overflow in subtraction of UINT8 (0 - 1)!
[(-1,)]

Similar functions

run(f"SELECT enum_code('mid'::{LEVEL}), list_position(enum_range(NULL::{LEVEL}), 'mid'), "
    f"enum_range(NULL::{LEVEL})[enum_code('mid'::{LEVEL}) + 1]")
run(f"SELECT id, priority, CASE priority WHEN 'low' THEN 0 WHEN 'mid' THEN 1 WHEN 'high' THEN 2 END, "
    f"list_position(['low', 'mid', 'high'], priority) - 1, enum_code(CAST(priority AS {LEVEL})) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

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

Clean up:

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

[]
[]

See also

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