decode

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Binary data and encodings. Reads the bytes of a BLOB as UTF-8 and returns the text as a VARCHAR. It's the reverse of encode. Bytes that aren't valid UTF-8 stop the query with an error.

Syntax

decode(b)

Returns: VARCHAR.

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_decode. 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 six rows with two BLOB columns. Every value in data is valid UTF-8; in raw, row 5 holds the bytes \xFF\xFE, which aren't. Row 3 has NULLs:

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

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}.payloads"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, data BLOB, raw BLOB)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '\\x25\\x50\\x44\\x46'::BLOB, '\\x68\\x69'::BLOB), "
    "(2, ''::BLOB, ''::BLOB), "
    "(3, NULL, NULL), "
    "(4, encode('h\u00e9llo'), '\\x00\\x7F'::BLOB), "
    "(5, '\\x00\\x01'::BLOB, '\\xFF\\xFE'::BLOB), "
    "(6, '\\x61'::BLOB, '\\x61'::BLOB)")

[]
[]
[(6,)]

What it does

decode(b) reads b as a sequence of UTF-8 characters:

• decode('\x68\x69'::BLOB) is 'hi', and decode('\xC3\xA9'::BLOB) is 'é'.
• Every byte is kept, control characters included: decode('a\x00b'::BLOB) is a three-character string with a NUL in the middle.
• decode(encode(s)) gives back s.

If the bytes aren't valid UTF-8, the query fails with Failure in decode: could not convert blob to UTF8 string, the blob contained invalid UTF8 characters. That covers a byte that can't start a character (\xFF), a character cut short (\xC3 alone), an encoded surrogate (\xED\xA0\x80) and an overlong form (\xC0\xAF). One such value fails the whole query, not only its row.

A string constant is cast to BLOB, so decode('abc') is 'abc'. A VARCHAR column isn't: decode(s) fails.

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 decode(NULL), decode(''::BLOB), decode('\\xC3\\xA9'::BLOB), decode('a\\x00b'::BLOB), "
    "length(decode('a\\x00b'::BLOB)), decode('abc')")
run("SELECT decode('\\xFF'::BLOB)")
run("SELECT decode('\\xC3'::BLOB)")
run("SELECT decode('\\x68\\x69'::BLOB, 'utf-8')")
run("SELECT decode(123)")
run(f"SELECT decode(CAST(id AS VARCHAR)) FROM {TABLE} LIMIT 1")

[(None, '', 'é', 'a\x00b', 3, 'abc')]
OSError: Failure in decode: could not convert blob to UTF8 string, the blob contained invalid UTF8 characters
OSError: Failure in decode: could not convert blob to UTF8 string, the blob contained invalid UTF8 characters
InternalError: Binder Error: No function matches the given name and argument types 'decode(BLOB, STRING_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'decode(INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'decode(VARCHAR)'. You might need to add explicit type casts.

Examples

The text in each data value. Row 1's bytes are the ASCII characters %PDF; row 5's are two control characters:

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

[(1, '%PDF'), (2, ''), (3, None), (4, 'héllo'), (5, '\x00\x01'), (6, 'a')]

raw has a value that isn't UTF-8 in row 5, so decoding the column fails for the whole query. Leave that row out, in WHERE or with CASE, and the rest decode:

run(f"SELECT id, decode(raw) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, decode(raw) FROM {TABLE} WHERE id <> 5 ORDER BY id LIMIT 10")
run(f"SELECT id, CASE WHEN id = 5 THEN NULL ELSE decode(raw) END FROM {TABLE} ORDER BY id LIMIT 10")

OSError: Failure in decode: could not convert blob to UTF8 string, the blob contained invalid UTF8 characters
[(1, 'hi'), (2, ''), (3, None), (4, '\x00\x7f'), (6, 'a')]
[(1, 'hi'), (2, ''), (3, None), (4, '\x00\x7f'), (5, None), (6, 'a')]

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 function of a column, so decode() of a column is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine decodes it for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step decodes it and keeps the rows that match.

The plan of decode(data) = 'a' shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE decode(data) = 'a'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│    (decode(data) = 'a')   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            data           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare the bytes, not the text. data = encode('a') finds the same rows as decode(data) = 'a', and is pushed down to VAST DataBase: encode of a constant is computed once, and the scan compares the stored bytes. The plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE data = encode('a')")

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

• Decode a constant, not the column. In the other direction, s = decode('\x61'::BLOB) on a VARCHAR column s is a comparison with a constant, and is pushed down too.
• Store text as VARCHAR. If you filter or search on the text, keep it in a VARCHAR column when you load the data, so you don't decode it in every query.

Limitations and errors

• Invalid UTF-8 fails the query. There's no form that returns NULL instead: TRY(...) doesn't exist. Leave such rows out with WHERE or CASE, as in Examples, or read the bytes with hex or CAST(b AS VARCHAR), which take any bytes.
• BLOB only. A VARCHAR column fails with No function matches the given name and argument types 'decode(VARCHAR)'; it's already text.
• UTF-8 only. decode has no encoding argument. Convert other encodings in your client.
• Not the decode(x, search, result, ...) of other SQL dialects. That form fails with No function matches the given name and argument types. Use CASE.

run("SELECT TRY(decode('\\xFF'::BLOB))")
run("SELECT decode(1, 1, 'one', 'other')")

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

Similar functions

run("SELECT decode(b), CAST(b AS VARCHAR), hex(b), to_base64(b) FROM (SELECT encode('h\u00e9llo') AS b)")
run("SELECT CAST(b AS VARCHAR), hex(b), to_base64(b) FROM (SELECT '\\xFF\\xFE'::BLOB AS b)")

[('héllo', 'h\\xC3\\xA9llo', '68C3A96C6C6F', 'aMOpbGxv')]
[('\\xFF\\xFE', 'FFFE', '//4=')]

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 binary functions.
• encode and BLOB values.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.