union_value

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Union values. Builds a value of the UNION type from one value and a name for it, its tag: union_value(k := 1) is a UNION(k INTEGER) holding 1. A union value holds one of several named members, and its tag says which. Cast it to a wider UNION type to give it more members. Union values exist only inside a query: a table can't have a UNION column.

Syntax

union_value(tag := value)

Returns: UNION(tag type), a union with one member: union_value(k := 1) is a UNION(k INTEGER).

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_utility_union_value. 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 five sensor readings. A reading is either a number or a text status; the last row has no values:

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_utility_union_value"'

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}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, num BIGINT, status VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 21, NULL), "
    "(2, NULL, 'offline'), "
    "(3, -4, NULL), "
    "(4, NULL, 'calibrating'), "
    "(5, NULL, NULL)")

[]
[]
[(5,)]

What it does

union_value(tag := value) wraps value in a union whose only member is tag:

To hold one of several members, cast the result to a UNION type that lists them all: union_value(k := 1)::UNION(k INTEGER, s VARCHAR). Read a member back with union_extract, which returns NULL for a member the value doesn't hold. union_tag(u)::VARCHAR returns the tag a value holds.

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 union_value(k := NULL), union_value(k := 1), union_value(k := 'a'), union_value(k := [1, 2])")
run("SELECT typeof(union_value(k := 1)), typeof(union_value(k := 'a'))")
run(f"SELECT count(*) FROM {TABLE} WHERE union_value(num := num) IS NOT NULL")
run("SELECT union_value(42)")
run("SELECT union_value(k := 1, m := 2)")
run(f"CREATE TABLE {SCHEMA}.unions (id BIGINT, x UNION(k INTEGER, s VARCHAR))")

[(None, 1, 'a', [1, 2])]
[('UNION(k INTEGER)', 'UNION(k VARCHAR)')]
[(5,)]
InternalError: Binder Error: Need named argument for union tag, e.g. UNION_VALUE(a := b)
InternalError: Binder Error: union_value takes exactly one argument
InternalError: Binder Error: UNION type is not supported in table definitions. Column "x" has type: UNION(k INTEGER, s VARCHAR)

Examples

Wrap each numeric reading in a union with the tag num:

run(f"SELECT id, union_value(num := num), typeof(union_value(num := num)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 21, 'UNION(num BIGINT)'), (2, None, 'UNION(num BIGINT)'), (3, -4, 'UNION(num BIGINT)'), (4, None, 'UNION(num BIGINT)'), (5, None, 'UNION(num BIGINT)')]

One value per reading, a number or a status, of one UNION(num BIGINT, status VARCHAR) type. The query returns the tag each row holds and both members, one of which is NULL:

run(f"SELECT id, union_tag(r)::VARCHAR, union_extract(r, 'num'), union_extract(r, 'status') FROM ("
    "SELECT id, CASE WHEN num IS NOT NULL "
    "THEN union_value(num := num)::UNION(num BIGINT, status VARCHAR) "
    "ELSE union_value(status := status)::UNION(num BIGINT, status VARCHAR) END AS r "
    f"FROM {TABLE}) ORDER BY id LIMIT 10")

[(1, 'num', 21, None), (2, 'status', None, 'offline'), (3, 'num', -4, None), (4, 'status', None, 'calibrating'), (5, 'status', None, None)]

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 stores no UNION columns and can't evaluate a function of a column, so union_value() is always computed by the Query Engine:

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

The plan of a WHERE condition on a union shows the FILTER step above the scan. This condition keeps every row, also the rows whose num is NULL, because a union that holds NULL is not itself NULL:

plan(f"SELECT id FROM {TABLE} WHERE union_value(num := num) IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (union_value(num) IS NOT │
│            NULL)          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            num            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter on the raw columns. To keep the rows that have a number, test the column, num IS NOT NULL, not the union. A condition on a raw column is pushed down to VAST DataBase, so rows that don't match never leave storage. Compare its plan with the one above:

plan(f"SELECT id FROM {TABLE} WHERE num IS NOT NULL")

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

• Build unions last, in the SELECT list or a subquery, from rows already filtered on their columns.

Limitations and errors

• No UNION columns. CREATE TABLE with a UNION column fails with UNION type is not supported in table definitions. To store a value that can be one of several types, keep one column per member, as the example table does, or a struct with one field per member.
• One member per call, with a tag. See NULLs and edge cases.
• Read the tag as text. The driver can't read the raw result of union_tag(); cast it: union_tag(u)::VARCHAR.

run("SELECT union_tag(union_value(k := 1))::VARCHAR")

[('k',)]

Similar functions

run("SELECT union_value(k := 1), union_extract(union_value(k := 1), 'k'), union_tag(union_value(k := 1))::VARCHAR")
run("SELECT struct_pack(k := 1, s := 'a')")

[(1, 1, 'k')]
[({'k': 1, 's': 'a'},)]

Clean up:

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

[]
[]
[]

See also

• union_extract.
• List, struct and map functions.
• Data types.
• Where filters run and pushdowns.