setseed

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Random numbers. Takes a seed, a number from -1 to 1, and returns NULL. A seed outside that range is an error. Setting a seed doesn't make the values of random repeat.

Syntax

setseed(x)

Returns: NULL. typeof(setseed(0.5)) is "NULL"; the driver returns the column as a 32-bit integer column holding NULLs.

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_numeric_setseed. 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 seeds. The last row has no seed:

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

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}.seeds"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, seed DOUBLE)")
run(f"INSERT INTO {TABLE} VALUES (1, 0.5), (2, -0.5), (3, 1.0), (4, NULL)")

[]
[]
[(4,)]

What it does

setseed(x) checks that x is from -1 to 1 and returns NULL; there's nothing else in its result. A string is converted to a DOUBLE first, so setseed('0.5') works. A seed outside the range, or NaN, fails the query.

Setting the same seed twice doesn't give the same random() values, whether random() runs in the next statement or in the same one: see the block in Examples. For a random sample that's the same on every run, see random: make it faster.

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 setseed(NULL), setseed(0.5), setseed(0), setseed(1), setseed(-1), setseed('0.5')")
run("SELECT setseed(2)")
run("SELECT setseed(1.0000001)")
run("SELECT setseed(-1.5)")
run("SELECT setseed('nan'::DOUBLE)")
run(f"SELECT setseed('abc') FROM {TABLE} LIMIT 1")
run("SELECT setseed(true)")
run("SELECT setseed()")

[(None, None, None, None, None, None)]
OSError: SETSEED accepts seed values between -1.0 and 1.0, inclusive
OSError: SETSEED accepts seed values between -1.0 and 1.0, inclusive
OSError: SETSEED accepts seed values between -1.0 and 1.0, inclusive
OSError: SETSEED accepts seed values between -1.0 and 1.0, inclusive
OSError: Could not convert string 'abc' to DOUBLE
InternalError: Binder Error: No function matches the given name and argument types 'setseed(BOOLEAN)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'setseed()'. You might need to add explicit type casts.

Examples

On a table, setseed() returns NULL for every row, so a condition such as setseed(seed) IS NOT NULL keeps no rows:

run(f"SELECT id, setseed(seed) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE setseed(seed) IS NOT NULL ORDER BY id LIMIT 10")

[(1, None), (2, None), (3, None), (4, None)]
[]

A seed doesn't repeat random(). The first check sets the seed in one statement and reads random() in the next; the second does both in one statement. Each compares two runs with the same seed, and prints False because they gave different values:

def random_after_seed(seed):
    with conn.cursor() as cur:
        cur.execute(f"SELECT setseed({seed})")
        cur.fetchall()
        cur.execute("SELECT random()")
        return cur.fetchall()[0][-1]

def random_with_seed(seed):
    with conn.cursor() as cur:
        cur.execute(f"SELECT setseed({seed}), random()")
        return cur.fetchall()[0][-1]

print(random_after_seed(0.5) == random_after_seed(0.5))
print(random_with_seed(0.5) == random_with_seed(0.5))

False
False

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). setseed() is always computed by the Query Engine: the scan reads the column you pass, and the Query Engine calls setseed() for each row and casts its NULL result to INTEGER. The plan shows both steps:

plan(f"SELECT setseed(seed) FROM {TABLE}")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│   CAST(setseed(seed) AS   │
│          INTEGER)         │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│     Projections: seed     │
│                           │
└───────────────────────────┘

Make it faster

• Call it on its own, not on a table. SELECT setseed(0.5) reads no table. SELECT setseed(seed) FROM seeds reads the column and returns a NULL for every row.
• Don't filter on it. setseed() is always NULL, so a condition on it either keeps no rows or every row, after a FILTER step that reads the whole column.

Limitations and errors

• A seed from -1 to 1 only. A larger or smaller number, or NaN, fails with SETSEED accepts seed values between -1.0 and 1.0, inclusive. To use a whole-number seed such as 42, scale it into the range first, for example setseed(42 / 100).
• One argument. setseed() and setseed(0.5, 1) fail with No function matches the given name and argument types. A BOOLEAN, DATE, INTERVAL, BLOB or list fails the same way.
• A string must be a number. setseed('abc') fails with Could not convert string 'abc' to DOUBLE.
• A seed doesn't make random() repeatable. Two runs after the same seed give different values, as the block in Examples shows. For a sample that's the same on every run, store the random values in a column; see random: make it faster.

run("SELECT setseed(0.5, 1)")
run("SELECT setseed(42 / 100)")

InternalError: Binder Error: No function matches the given name and argument types 'setseed(DECIMAL(2,1), INTEGER_LITERAL)'. You might need to add explicit type casts.
[(None,)]

Similar functions

run("SELECT setseed(0.5), random() >= 0")

[(None, True)]

Clean up:

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

[]
[]

See also

• Numeric and math functions: all the numeric functions.
• random.
• EXPLAIN: how to read a plan.