repeat (BLOB)

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Binary data and encodings. Returns the bytes of a BLOB repeated n times, as a BLOB. This page covers repeat on bytes; for a string, see repeat, and for repeat in FROM, which makes rows, see repeat (table function).

Syntax

repeat(b, n)

Returns: BLOB.

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_binary_hash_uuid_repeat. 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 patterns, each with its bytes and a repeat count. The last row has no bytes:

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

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}.patterns"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, data BLOB, n BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '\\x25\\x50\\x44\\x46'::BLOB, 2), "
    "(2, ''::BLOB, 5), "
    "(3, '\\x00\\x01'::BLOB, 3), "
    "(4, '\\x61'::BLOB, -1), "
    "(5, NULL, 2)")

[]
[]
[(5,)]

What it does

repeat(b, n) joins n copies of the bytes of b: repeat('ab'::BLOB, 3) gives b'ababab'. Every byte is copied as it is, zero bytes and bytes that aren't valid UTF-8 included, and the result is n times as long as b.

An n of 0 or less gives empty bytes, not an error.

The type of the first argument picks the function: a BLOB gives a BLOB, and a VARCHAR gives a VARCHAR (repeat for strings). A string constant such as 'ab' is a VARCHAR, so cast it, 'ab'::BLOB, to repeat bytes.

NULLs and edge cases

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

run("SELECT repeat(NULL::BLOB, 2), repeat('ab'::BLOB, NULL), repeat('ab'::BLOB, 0), repeat('ab'::BLOB, -1), repeat('ab'::BLOB, 1)")
run("SELECT repeat(''::BLOB, 5), repeat('\\x00'::BLOB, 4), repeat('ab'::BLOB, '2')")
run("SELECT repeat('ab'::BLOB, 2.6)")
run("SELECT repeat('ab'::BLOB, 9223372036854775807)")

[(None, None, b'', b'', b'ab')]
[(b'', b'\x00\x00\x00\x00', b'abab')]
InternalError: Binder Error: No function matches the given name and argument types 'repeat(BLOB, DECIMAL(2,1))'. You might need to add explicit type casts.
OSError: Cannot create a string of size: '-2', the maximum supported string size is: '4294967295'

Examples

Each pattern repeated three times:

run(f"SELECT id, repeat(data, 3) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, b'%PDF%PDF%PDF'), (2, b''), (3, b'\x00\x01\x00\x01\x00\x01'), (4, b'aaa'), (5, None)]

The count can come from a column. Row 4's count is negative, so it gives empty bytes:

run(f"SELECT id, repeat(data, n), octet_length(repeat(data, n)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, b'%PDF%PDF', 8), (2, b'', 0), (3, b'\x00\x01\x00\x01\x00\x01', 6), (4, b'', 0), (5, 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). repeat() of a column is computed by the Query Engine:

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

The plan of a filter on repeat() shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE repeat(data, 3) IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (repeat(data, 3) IS NOT  │
│            NULL)          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            data           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Test the source column, not the repeated bytes. repeat(data, 3) is NULL only when data is, and a test of the column itself, such as data IS NULL, is evaluated in VAST DataBase. The plan has no FILTER step:

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

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

• Build large values only in the SELECT list. A repeated value is as large as n copies of the input, for every row it's computed on; compute it on the rows you return, after the other conditions have filtered them.

Limitations and errors

• A whole-number count. A DECIMAL count fails with No function matches the given name and argument types, and the error lists the forms of repeat: repeat(VARCHAR, BIGINT), repeat(BLOB, BIGINT) and repeat(ANY[], BIGINT). Round the count and cast it to BIGINT.
• A size limit. A count so large that the result can't be built fails with Cannot create a string of size ....
• Not the table function. In FROM, repeat(value, n) returns n rows instead: see repeat (table function).

run("SELECT repeat('ab'::BLOB, CAST(round(2.6) AS BIGINT))")

[(b'ababab',)]

Similar functions

run("SELECT repeat('ab'::BLOB, 2), typeof(repeat('ab'::BLOB, 2)), repeat('ab', 2), typeof(repeat('ab', 2))")
run("SELECT * FROM repeat('ab', 2) LIMIT 10")
run("SELECT 'ab'::BLOB || 'ab'::BLOB")

[(b'abab', 'BLOB', 'abab', 'VARCHAR')]
[('ab',), ('ab',)]
[(b'abab',)]

Clean up:

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

[]
[]

See also

• Binary, hash and UUID functions: all the functions in this family.
• Data types in SQL: BLOB columns.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.