row

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Build a struct. Returns a struct whose fields have no names, one field per argument. The tuple (a, b) is the same.

Syntax

row(value, ...)
(value1, value2, ...)

Returns: a STRUCT with one unnamed field per argument, of the argument's type: typeof(row(1, 'a')) is STRUCT(INTEGER, VARCHAR).

Aliases: the tuple (a, b, ...) with two or more 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_structs_row. 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 four books, each with a struct of details (meta), a shelf and a number of copies. Book 3 has an empty shelf and a struct of NULL fields, and book 4 has no values. run() also prints the error the Python client raises when it can't convert a result:

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

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}.books"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, meta STRUCT(lang VARCHAR, pages INTEGER), shelf VARCHAR, copies BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, {'lang': 'en', 'pages': 12}, 'A1', 3), "
    "(2, {'lang': 'de', 'pages': NULL}, 'B2', 1), "
    "(3, {'lang': NULL, 'pages': NULL}, '', 0), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

row(1, 'a') builds a struct of two fields, 1 and 'a', that have no names. The tuple (1, 'a') builds the same struct, and row(1, 'a') = (1, 'a') is true.

Because the fields have no names, you read them by position, counting from 1: row(1, 'a')[2] and struct_extract(row(1, 'a'), 2) are 'a'. A name doesn't work: row(1, 'a').x fails with struct_extract with a string key cannot be used on an unnamed struct, use a numeric index instead. To give the fields names, cast the struct: CAST(row(1, 'a') AS STRUCT(x BIGINT, y VARCHAR)) is {'x': 1, 'y': 'a'}.

Two rows compare field by field, in order: row(1, 'a') < row(1, 'b') is true, and row(1, 'a') IN (row(1, 'a'), row(2, 'b')) is true. That makes a tuple a short way to compare several columns at once: WHERE (id, shelf) = (1, 'A1').

The Python client can't convert a struct with two or more unnamed fields: it raises ValueError: Converting to Python dictionary is not supported when duplicate field names are present. The query itself runs. Read such a struct in the query, by position, with to_json, or by casting it to a named struct. A one-field row(42) reads as {'': 42}.

NULLs and edge cases

The same cases, as the cluster returns them. They use the helpers from Set up the examples:

run("SELECT row(NULL), typeof(row(NULL)), row(NULL) IS NULL, row(NULL, 1)[1], to_json(row(NULL, 1))")
run("SELECT row(1, NULL) = row(1, NULL), row(1, 'a')::VARCHAR, row(row(1, 2), 3)[1][2]")
run("SELECT row([1, 2]), row(DATE '2026-10-16')")
run("SELECT row()")
run("SELECT row(1 AS x)")
run("SELECT row(1, 'a').x")

[({'': None}, 'STRUCT("NULL")', False, None, '{"":null,"":1}')]
[(True, '(1, a)', 2)]
[({'': [1, 2]}, {'': datetime.date(2026, 10, 16)})]
InternalError: Invalid Input Error: Can't pack nothing into a struct
InternalError: Parser Error: syntax error at or near "AS"
InternalError: Binder Error: struct_extract with a string key cannot be used on an unnamed struct, use a numeric index instead

Examples

A two-field row can't be read by the Python client as it is:

run("SELECT row(1, 'a')")

ValueError: Converting to Python dictionary is not supported when duplicate field names are present

Build a row from each book's id and shelf, and read it as JSON, by position and by its type:

run(f"SELECT id, to_json(row(id, shelf)), row(id, shelf)[2], typeof(row(id, shelf)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '{"":1,"":"A1"}', 'A1', 'STRUCT(BIGINT, VARCHAR)'), (2, '{"":2,"":"B2"}', 'B2', 'STRUCT(BIGINT, VARCHAR)'), (3, '{"":3,"":""}', '', 'STRUCT(BIGINT, VARCHAR)'), (4, '{"":4,"":null}', None, 'STRUCT(BIGINT, VARCHAR)')]

Cast the row to a struct with named fields, so the client returns a dict:

run(f"SELECT id, CAST(row(id, shelf) AS STRUCT(book BIGINT, place VARCHAR)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, {'book': 1, 'place': 'A1'}), (2, {'book': 2, 'place': 'B2'}), (3, {'book': 3, 'place': ''}), (4, {'book': 4, 'place': None})]

Compare two columns at once with a tuple, against one pair of values or a list of pairs:

run(f"SELECT id FROM {TABLE} WHERE (id, shelf) = (1, 'A1')")
run(f"SELECT id FROM {TABLE} WHERE (id, shelf) IN ((1, 'A1'), (2, 'B2')) ORDER BY id LIMIT 10")

[(1,)]
[(1,), (2,)]

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

• In the SELECT list, the scan reads the columns and the Query Engine builds the struct for each row it returns.
• In WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine computes the condition and keeps the rows that match.

The plan of a condition on a field of a row shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE row(id, shelf)[2] = 'A1'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (array_extract("row"(id, │
│     shelf), 2) = 'A1')    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│           shelf           │
│                           │
└───────────────────────────┘

Make it faster

• Compare the columns one by one. id = 1 AND shelf = 'A1' compares each 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 id = 1 AND shelf = 'A1'")

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

• Build rows in the SELECT list, not in WHERE. A filter on the stored columns is pushed down; a filter on a struct built from them isn't.

Limitations and errors

• Fields have no names. row(1 AS x) and row(a := 1) fail with Parser Error: syntax error at or near .... For named fields, use struct_pack or the literal {'x': 1, 'y': 'a'}, or cast the row to a STRUCT type.
• The Python client can't read two or more unnamed fields. It raises ValueError: Converting to Python dictionary is not supported when duplicate field names are present. Read the fields with [i], to_json or a cast.
• At least one value. row() fails with Can't pack nothing into a struct.
• Not a column type. A table column of type ROW(INTEGER, VARCHAR) fails with Parser Error: syntax error at or near ",". Declare a named STRUCT column, as in data types.

run(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.unnamed (id BIGINT, r ROW(INTEGER, VARCHAR))")

InternalError: Parser Error: syntax error at or near ","

Similar functions

run("SELECT typeof(row(1, 'x')), to_json(row(1, 'x')), struct_pack(a := 1, b := 'x'), {'a': 1, 'b': 'x'}, "
    "struct_pack(a := 1, b := 'x') = {'a': 1, 'b': 'x'}, row_to_json(row(1, 'x'))")

[('STRUCT(INTEGER, VARCHAR)', '{"":1,"":"x"}', {'a': 1, 'b': 'x'}, {'a': 1, 'b': 'x'}, True, '{"":1,"":"x"}')]

Clean up:

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

[]
[]

See also

• struct_pack, struct_extract and struct_extract_at: named structs and their fields.
• List, struct and map functions: all the struct functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.