query

Applies to VAST Cluster 5.5.

Functions › Table functions › Queries and tables named in a string. Runs one SELECT statement given as a string and returns its rows, so it goes in the FROM clause. Use it when your code builds the statement as text.

Syntax

query(sql)

Returns: a table with the columns and rows of the statement.

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_table_functions_query. 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 articles. The last row has no title or view count:

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

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}.articles"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, title VARCHAR, views BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Getting started', 120), "
    "(2, 'Vector search', 45), "
    "(3, 'Tuning queries', 300), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

query(sql) parses the string as a statement and returns its result as a table, with the statement's column names and types. Inside the string, quote a table name as you would in a statement ("<bucket>/<schema>".articles) and double any single quote ('').

• The string must be a constant. It can be built from constants with ||, as in query('SELECT ' || '1 AS x'), but not taken from a column.
• The statement can use anything a SELECT can: a table, another table function such as range, or another query().
• The columns keep their types: typeof(x) on query('SELECT 1.5 AS x') is DECIMAL(2,1).

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 * FROM query('SELECT ' || '1 AS x')")
run("SELECT * FROM query('SELECT * FROM query(''SELECT 7 AS y'')')")
run("SELECT * FROM query(NULL)")
run("SELECT * FROM query('')")
run("SELECT * FROM query('SELECT 1; SELECT 2')")
run("SELECT * FROM query('SELEC 1')")

[(1,)]
[(7,)]
InternalError: Parse query: 'SELECT * FROM query(NULL)' failed with: Parser Error: syntax error at or near "NULL"
InternalError: Parse query: 'SELECT * FROM query('')' failed with: Parser Error: Expected a single SELECT statement
InternalError: Parse query: 'SELECT * FROM query('SELECT 1; SELECT 2')' failed with: Parser Error: Expected a single SELECT statement
InternalError: Parse query: 'SELECT * FROM query('SELEC 1')' failed with: Parser Error: syntax error at or near "SELEC"

Examples

A constant row, and the type of a column:

run("SELECT * FROM query('SELECT 42 AS answer')")
run("SELECT typeof(x) FROM query('SELECT 1.5 AS x')")

[(42,)]
[('DECIMAL(2,1)',)]

The articles, from a statement held in a Python variable. ORDER BY needs a LIMIT (SELECT), inside the string as anywhere else:

statement = f"SELECT id, title FROM {TABLE} ORDER BY id LIMIT 10"
run(f"SELECT * FROM query('{statement}')")

[(1, 'Getting started'), (2, 'Vector search'), (3, 'Tuning queries'), (4, None)]

A condition can go inside the string or outside query():

run(f"SELECT * FROM query('SELECT id, title FROM {TABLE} WHERE id = 2')")
run(f"SELECT * FROM query('SELECT id, title FROM {TABLE}') WHERE id = 2")

[(2, 'Vector search')]
[(2, 'Vector search')]

Rows from another table function:

run("SELECT * FROM query('SELECT * FROM range(3)')")

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

How it runs in VAST

The Query Engine runs on the cluster's CNodes. It plans the statement in the string as if you had written it in place of query(), so a query on a table starts with a scan in VAST DataBase (VAST_SCAN). A comparison of a column with a constant in the string's WHERE is evaluated in that scan, so the plan of query('SELECT id FROM articles WHERE id = 2') is a single VAST_SCAN step, with no FILTER step:

plan(f"SELECT * FROM query('SELECT id FROM {TABLE} WHERE id = 2')")

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

Make it faster

• Write static statements as plain SQL. query() helps only when the statement is built as text; otherwise run the statement directly.
• Put the conditions in the string. A WHERE inside the statement is planned with the scan and pushes down as usual, as the plan above shows.
• Select only the columns you need in the string: the scan reads only the columns the statement uses.

Limitations and errors

• One SELECT only. Two statements, an empty string or any other statement (INSERT, DROP TABLE) fail with Parser Error: Expected a single SELECT statement. Run other statements directly.
• A constant string only. A column can't be the argument: it fails with Table function "query" does not support lateral join column parameters, and query(NULL) fails with Parser Error: syntax error at or near "NULL".
• In FROM only. In the SELECT list it fails with Binder Error: Function "query" is a table function but it was used as a scalar function. This function has to be called in a FROM clause (similar to a table).
• Errors in the statement are reported as usual, for example Catalog Error: Table with name ... does not exist! for a table that isn't there.

run("SELECT * FROM query('SELECT 1; SELECT 2')")
run(f"SELECT * FROM query('INSERT INTO {TABLE} (id) VALUES (90)')")
run(f"SELECT * FROM {TABLE}, query(title)")
run("SELECT query('SELECT 1')")
run(f"SELECT * FROM query('SELECT * FROM {SCHEMA}.nope')")

InternalError: Parse query: 'SELECT * FROM query('SELECT 1; SELECT 2')' failed with: Parser Error: Expected a single SELECT statement
InternalError: Parse query: 'SELECT * FROM query('INSERT INTO "<bucket>/docs_sql_fn_table_functions_query".articles (id) VALUES (90)')' failed with: Parser Error: Expected a single SELECT statement
InternalError: Binder Error: Table function "query" does not support lateral join column parameters - cannot use column "title" in this context.
InternalError: Binder Error: Function "query" is a table function but it was used as a scalar function. This function has to be called in a FROM clause (similar to a table).
InternalError: Catalog Error: Table with name nope does not exist!

Similar functions

The same row three ways:

run(f"SELECT * FROM query('SELECT id, title FROM {TABLE} WHERE id = 2')")
run(f"SELECT id, title FROM query_table('{TABLE}') WHERE id = 2")
run(f"SELECT id, title FROM {TABLE} WHERE id = 2")

[(2, 'Vector search')]
[(2, 'Vector search')]
[(2, 'Vector search')]

Clean up:

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

[]
[]

See also

• Table functions: all the table functions.
• query_table: read a table named in a string.
• SELECT and EXPLAIN: how to read a plan.