GLOB

Applies to VAST Cluster 5.5.

Functions › Search and split › Pattern matching. s GLOB pattern is true if the whole string matches a shell-style pattern: * stands for any run of characters, ? for one, and [...] for one of a set. It's case-sensitive. ~~~ is the operator form, and the name that plans and errors show. NOT GLOB isn't available: write NOT (s GLOB pattern).

Syntax

s GLOB pattern
s ~~~ pattern
NOT (s GLOB pattern)

Returns: BOOLEAN.

Aliases: ~~~.

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_string_glob. 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 file paths. Row 2 has an upper-case extension. 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_string_glob"'

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}.files"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, path VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '/data/logs/app.log'), "
    "(2, '/data/logs/db.LOG'), "
    "(3, '/data/tmp/run1.txt'), "
    "(4, '/data/tmp/run12.txt'), "
    "(5, NULL)")

[]
[]
[(5,)]

What it does

The pattern must match the whole string, and letters match only the same case.

• % and _ are ordinary characters, unlike in LIKE.
• ^ doesn't negate a set. 'abc' GLOB '[^a]*' is true: [^a] is the set of ^ and a. Use [!a].
• A malformed set doesn't fail. 'abc' GLOB '[a-' and 'abc' GLOB '[]' are false.
• ? doesn't match a non-ASCII character such as é, whether it's precomposed or written with a combining accent, and ?? doesn't match a flag emoji. For one character of any script, use LIKE '_'.
• * and ? match a line break.

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 NULL GLOB 'H*', 'abc' GLOB NULL, NULL GLOB NULL, '' GLOB '*', '' GLOB '', 'abc' GLOB '', "
    "'abc' GLOB 'a??c', 'abc' GLOB 'A*'")
run("SELECT 'abc' GLOB '[!a]*', 'abc' GLOB '[^a]*', 'abc' GLOB '[a-', 'abc' GLOB '[]'")
run("SELECT 'a*c' GLOB 'a\\*c', 'abc' GLOB 'a\\*c', 'a%c' GLOB 'a%c', 'abc' GLOB 'a%'")
run("SELECT '\u00e9' GLOB '?', 'a\nb' GLOB 'a?b'")
run("SELECT 123 GLOB 'H*'")

[(None, None, None, True, True, False, False, False)]
[(False, True, False, False)]
[(True, False, True, False)]
[(False, True)]
InternalError: Binder Error: No function matches the given name and argument types '~~~(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Match the log files, the files in any subdirectory with a .txt extension, and a one-digit run number. The row with no path gives NULL:

run(f"SELECT id, path GLOB '/data/logs/*.log', path GLOB '/data/*/*.txt', path GLOB '*/run[0-9].txt' "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, True, False, False), (2, False, False, False), (3, False, True, True), (4, False, True, False), (5, None, None, None)]

GLOB is case-sensitive. To accept either case of an extension, list both in sets:

run(f"SELECT id, path FROM {TABLE} WHERE path GLOB '*.[lL][oO][gG]' ORDER BY id LIMIT 10")

[(1, '/data/logs/app.log'), (2, '/data/logs/db.LOG')]

Keep the rows that don't match, and use the operator form:

run(f"SELECT id, NOT (path GLOB '/data/logs/*'), path ~~~ '/data/tmp/*' FROM {TABLE} ORDER BY id LIMIT 10")

[(1, False, False), (2, False, False), (3, True, True), (4, True, True), (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). VAST DataBase can evaluate a comparison of a column with a constant, but not a pattern match, so GLOB is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine evaluates GLOB for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine keeps the rows that match. No rows are skipped in storage.

Unlike LIKE, GLOB isn't rewritten: a prefix pattern such as '/data/logs/*' and a pattern with no wildcard both stay s ~~~ pattern in the FILTER step. The plans:

plan(f"SELECT id FROM {TABLE} WHERE path GLOB '/data/logs/*'")
plan(f"SELECT id FROM {TABLE} WHERE path GLOB '/data/logs/app.log'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (path ~~~ '/data/logs/*') │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (path ~~~ '/data/logs/app │
│           .log')          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare whole values with =. path = '/data/logs/app.log' is pushed down to VAST DataBase; the same test written with GLOB isn't.
• Use a range for a prefix. path >= '/data/logs/' AND path < '/data/logs0' is pushed down; GLOB '/data/logs/*' and LIKE '/data/logs/%' run in the FILTER step. The plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE path >= '/data/logs/' AND path < '/data/logs0'")

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

• Combine it with a pushed-down condition. In id > 1 AND path GLOB '*.txt', id > 1 is still evaluated in VAST DataBase, so GLOB runs only on the rows that pass it.

Limitations and errors

• No NOT GLOB. s NOT GLOB pattern fails with Parser Error: syntax error at or near "GLOB", and !~~~ fails with Scalar Function with name !~~~ does not exist!. Write NOT (s GLOB pattern).
• No ESCAPE. s GLOB pattern ESCAPE '$' is a syntax error. Put a wildcard in a set, [*] or [?], to match it literally.
• No glob() scalar function. glob('abc', 'a*') fails with Function "glob" is a table function but it was used as a scalar function; the glob table function is called in a FROM clause.
• Strings only. A number, a date, a boolean, a BLOB or a list fails with No function matches the given name and argument types '~~~(...)'. Cast the value to VARCHAR first.

run("SELECT 'abc' NOT GLOB 'x*'")
run("SELECT 'abc' GLOB 'a*' ESCAPE '$'")
run("SELECT glob('abc', 'a*')")
run("SELECT NOT ('abc' GLOB 'x*'), CAST(2026 AS VARCHAR) GLOB '20*'")

InternalError: Parse query: 'SELECT 'abc' NOT GLOB 'x*'' failed with: Parser Error: syntax error at or near "GLOB"
InternalError: Parse query: 'SELECT 'abc' GLOB 'a*' ESCAPE '$'' failed with: Parser Error: syntax error at or near "ESCAPE"
InternalError: Binder Error: Function "glob" 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).
[(True, True)]

Similar functions

run("SELECT 'abc' GLOB 'a*', 'abc' LIKE 'a%', 'abc' ILIKE 'A%', 'abc' SIMILAR TO 'a.*', regexp_matches('abc', '^a')")
run("SELECT 'abc' GLOB 'a?c', 'abc' LIKE 'a_c', 'abc' SIMILAR TO 'a.c', regexp_matches('abc', '^a.c$')")

[(True, True, True, True, True)]
[(True, True, True, True)]

Clean up:

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

[]
[]

See also

• Search and split: all the search and pattern functions.
• Operators and predicates.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.