random

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Random numbers. Returns a random DOUBLE from 0 up to, not including, 1. Each row gets its own value, and every run of a query gives new values.

Syntax

random()

random takes no arguments.

Returns: DOUBLE, from 0 up to, not including, 1.

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_random. 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. Because random() gives new values on every run, the examples print checks and counts rather than the values themselves.

This block connects, defines the run() and plan() helpers, and creates a table of four sensor readings. Each row has a sample_key that was filled with a random number when the row was written; 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_numeric_random"'

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, sensor VARCHAR, reading DOUBLE, sample_key DOUBLE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'north', 20.5, 0.12), "
    "(2, 'south', 21.0, 0.87), "
    "(3, 'east', 19.5, 0.45), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

random() returns a DOUBLE that is at least 0 and less than 1. In a query on a table, it's evaluated for each row, so each row gets its own value; it doesn't depend on the row's columns, so a row whose columns are all NULL gets a value too.

Scale it for other ranges: random() * 10 is from 0 up to 10, and CAST(floor(random() * 6) AS BIGINT) + 1 is a whole number from 1 to 6.

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 random() >= 0 AND random() < 1, typeof(random())")
run("SELECT min(r) >= 0, max(r) < 1, count(*) FROM (SELECT random() AS r FROM range(10000))")
run(f"SELECT id FROM {TABLE} WHERE random() IS NOT NULL ORDER BY id LIMIT 10")
run("SELECT random(42)")
run("SELECT rand()")

[(True, 'DOUBLE')]
[(True, True, 10000)]
[(1,), (2,), (3,), (4,)]
InternalError: Binder Error: No function matches the given name and argument types 'random(INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Catalog Error: Scalar Function with name rand does not exist!

Examples

A random value for each row. The block prints how many rows came back, how many different values they hold, and whether every value is from 0 up to 1:

with conn.cursor() as cur:
    cur.execute(f"SELECT random() FROM {TABLE} ORDER BY id LIMIT 10")
    values = [row[0] for row in cur.fetchall()]
print(len(values), len(set(values)), all(0 <= v < 1 for v in values))

4 4 True

A whole number from 1 to 6, like a die. Over 1,000 rolls, the smallest and largest values come out as 1 and 6:

run("SELECT min(d), max(d) FROM (SELECT CAST(floor(random() * 6) AS BIGINT) + 1 AS d FROM range(1000))")

[(1, 6)]

Keep about half of the rows: random() < 0.5 keeps each row with a chance of one in two. Of 10,000 rows, between 4,500 and 5,500 are kept:

run("SELECT count(*) BETWEEN 4500 AND 5500 FROM (SELECT random() AS r FROM range(10000)) WHERE r < 0.5")

[(True,)]

Pick two rows at random. The block prints how many ids came back and whether they're all ids of the table:

with conn.cursor() as cur:
    cur.execute(f"SELECT id FROM {TABLE} ORDER BY random() LIMIT 2")
    ids = [row[0] for row in cur.fetchall()]
print(len(ids), set(ids) <= {1, 2, 3, 4})

2 True

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

• In the SELECT list, the Query Engine computes random() for each row the scan returns. random() reads no column, so the scan of SELECT random() FROM readings projects no columns.
• In WHERE, the scan sends every row, and a FILTER step in the Query Engine evaluates the condition on each one. A random sample is taken after the rows have left storage, so random() < 0.01 still reads the whole table.

The plan of a half sample shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE random() < 0.5")

┌───────────────────────────┐
│           FILTER          │
│    ────────────────────   │
│      (random() < 0.5)     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Make it faster

• Store a random number with each row if you sample often. Fill a column such as sample_key with a random value from 0 to 1 when you write the rows. A comparison of that column with a constant is pushed down to VAST DataBase, so the rows outside the sample never leave storage, and the sample is the same on every run. The plan for sample_key < 0.5 has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE sample_key < 0.5")
run(f"SELECT id FROM {TABLE} WHERE sample_key < 0.5 ORDER BY id LIMIT 10")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
[(1,), (3,)]

Limitations and errors

• No seed argument. random(42) fails with No function matches the given name and argument types 'random(INTEGER_LITERAL)'. For a sample that's the same on every run, store the random value in a column, as in Make it faster. setseed accepts a seed, but it doesn't make random() repeat.
• rand isn't available. rand() fails with Scalar Function with name rand does not exist!. Use random().
• Values change on every run. random() isn't repeatable: print checks on its values, as these examples do, or store the values in a column.

run("SELECT random(42)")
run("SELECT rand()")

InternalError: Binder Error: No function matches the given name and argument types 'random(INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Catalog Error: Scalar Function with name rand does not exist!

Similar functions

run("SELECT random() >= 0 AND random() < 1, setseed(0.5) IS NULL, typeof(gen_random_uuid())")

[(True, True, 'UUID')]

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.
• setseed.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.