list_distinct

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Search a list. Returns a list that holds each distinct value of a list once, without NULL elements. The order of the result isn't the order of the input. array_distinct is the same function.

Syntax

list_distinct(l)
array_distinct(l)

Returns: a list of the same type as l.

Aliases: array_distinct.

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_lists_list_distinct. 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 rows of lists: numbers, words, measurements and lists of lists. Row 3 has empty lists and row 4 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_lists_list_distinct"'

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}.samples"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, nums BIGINT[], words VARCHAR[], vals DOUBLE[], nested BIGINT[][])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [1, 2, 3], ['a', 'b', 'c'], [0.5, 2.5, -1.0], [[1, 2], [3]]), "
    "(2, [3, NULL, 3], ['b', NULL, 'B'], [2.0, NULL], [[], NULL, [4]]), "
    "(3, [], [], [], []), "
    "(4, NULL, NULL, NULL, NULL), "
    "(5, [-5, 0, -5, 10], ['\u00e9', 'Zo\u00eb', '\U0001F1EB\U0001F1F7', ''], [1e308, 1e308, -1e-308], [[5, NULL], [], [6, 7, 8]])")

[]
[]
[(5,)]

What it does

list_distinct(l) keeps one copy of each value in l and drops the NULL elements:

• Duplicates are removed. [3, NULL, 3] becomes [3], and [-5, 0, -5, 10] holds three values.
• NULL elements are dropped. [1, NULL, 1] becomes [1], and [NULL, NULL] becomes [].
• The order isn't kept. list_distinct([1, 2, 3]) returns [3, 1, 2]. When the order matters, sort the result: list_sort(list_distinct(l)) returns [1, 2, 3].
• Values are compared exactly. Text is case-sensitive ('a' and 'A' are both kept), and a list element of a list of lists is compared as a whole list ([[1], [1], [2]] keeps [1] and [2]).
• NaN values aren't merged. ['NaN'::DOUBLE, 'NaN'::DOUBLE, 1.0] keeps both NaN values.

NULLs and edge cases

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

run("SELECT list_distinct(NULL), list_distinct([]), list_distinct([1, NULL, 1]), list_distinct([NULL, NULL])")
run("SELECT list_sort(list_distinct([3, 1, 3, 2])), list_sort(list_distinct(['a', 'A', 'a']))")
run("SELECT list_distinct(['NaN'::DOUBLE, 'NaN'::DOUBLE, 1.0]), list_distinct([[1], NULL, NULL])")
run("SELECT list_distinct(123)")

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

Examples

The distinct numbers and words of each row, sorted so the order is stable:

run(f"SELECT id, list_sort(list_distinct(nums)), list_sort(array_distinct(words)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [1, 2, 3], ['a', 'b', 'c']), (2, [3], ['B', 'b']), (3, [], []), (4, None, None), (5, [-5, 0, 10], ['', 'Zoë', 'é', '🇫🇷'])]

The rows whose list has a repeated value: the distinct list is shorter than the list. A NULL element counts as a difference too, because list_distinct drops it:

run(f"SELECT id, nums FROM {TABLE} WHERE len(list_distinct(nums)) < len(nums) ORDER BY id LIMIT 10")

[(2, [3, None, 3]), (5, [-5, 0, -5, 10])]

The union of two lists: concatenate them, then remove the duplicates:

run("SELECT list_sort(list_distinct(list_concat([1, 2], [2, 3])))")

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

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). The scan reads a list column whole, and list_distinct() always runs in the Query Engine:

• In the SELECT list, a projection step computes list_distinct() for each row the scan returns.
• In WHERE, the scan sends the list for every row, and a FILTER step computes the condition. No rows are skipped in storage.

The plan of the repeated-values example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE len(list_distinct(nums)) < len(nums)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (len(list_distinct(nums)) │
│        < len(nums))       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            nums           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Add a condition on a plain column. A comparison of a column with a constant, such as id > 1, is evaluated in VAST DataBase, so list_distinct() runs only on the rows that pass it. The plan for id > 1 alone has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE id > 1")

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

• Count with list_unique instead of measuring the list. If you only need how many distinct values there are, list_unique(l) returns the number directly.
• Store the distinct values if you filter on them often. Remove the duplicates when you write the rows, and keep the count in its own column, so a filter on it can be pushed down.

Limitations and errors

• Lists only. A number or a string fails with No function matches the given name and argument types; the error names the accepted type, list_distinct(ANY[]).
• No set operations by name. array_union fails with Scalar Function with name array_union does not exist!. Use list_distinct(list_concat(a, b)), as in Examples.

run("SELECT array_union([1, 2], [2, 3])")

InternalError: Catalog Error: Scalar Function with name array_union does not exist!

Similar functions

run("SELECT list_sort(list_distinct(l)), list_unique(l), list_approx_count_distinct(l), list_histogram(l) "
    "FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

[([1, 3, 10], 3, 3, [(1, 1), (3, 2), (10, 1)])]

Clean up:

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

[]
[]

See also

• List, struct and map functions: all the list functions.
• list_sort: sort the result.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.