Welcome to the new DOCS.vastdata.com. We've added new menus and a version selector, plus new persona and resource pages to help customers get started. Looking for a previous article? Use the docs search.

Press CTRL+K to open search

array_negative_inner_product

Prev Next

Applies to VAST Cluster 5.5.

Functions › Vector search SQL › Distance functions. Returns minus the inner (dot) product of two vectors: -(a1 × b1 + a2 × b2 + …). The sign is flipped so that, as with the other distance functions, a smaller result means more similar and ORDER BY ... LIMIT k returns the best matches first.

Syntax

array_negative_inner_product(a, b)
Argument Type Description
a, b FLOAT[n], DOUBLE[n] or HALF[n] fixed-size arrays of the same n, or a VECTOR(FLOAT, n) column The two vectors.

Returns: FLOAT; DOUBLE if either argument is a DOUBLE array.

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_vector_array_negative_inner_product. 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 documents, each with a 3-dimensional embedding in a VECTOR(FLOAT, 3) column and the same values in a plain list column, raw. Document 3 has no embedding:

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

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).replace(BUCKET, "<bucket>"))

TABLE = f"{SCHEMA}.docs"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, title VARCHAR, e VECTOR(FLOAT, 3), raw FLOAT[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'same', [1.0, 2.0, 3.0]::FLOAT[3], [1.0, 2.0, 3.0]), "
    "(2, 'zero', [0.0, 0.0, 0.0]::FLOAT[3], [0.0, 0.0, 0.0]), "
    "(3, 'none', NULL, NULL), "
    "(4, 'opposite', [-1.0, -2.0, -3.0]::FLOAT[3], [-1.0, -2.0, -3.0]), "
    "(5, 'axis', [1.0, 0.0, 0.0]::FLOAT[3], [1.0, 0.0, 0.0]), "
    "(6, 'three-four', [3.0, 4.0, 0.0]::FLOAT[3], [3.0, 4.0, 0.0])")

[]
[]
[(6,)]

What it does

array_negative_inner_product(a, b) multiplies the elements in the same positions, adds the products, and changes the sign:

Call Result
array_negative_inner_product([1.0, 2.0, 3.0]::FLOAT[3], [1.0, 2.0, 3.0]::FLOAT[3]) -14.0
array_negative_inner_product([1.0, 2.0, 3.0]::FLOAT[3], [2.0, 4.0, 6.0]::FLOAT[3]) -28.0
array_negative_inner_product([1.0, 0.0]::FLOAT[2], [0.0, 1.0]::FLOAT[2]) -0.0 (at right angles)
array_negative_inner_product([1.0, 2.0, 3.0]::FLOAT[3], [-1.0, -2.0, -3.0]::FLOAT[3]) 14.0

The result depends on the lengths of the vectors as well as on their directions: [2.0, 4.0, 6.0] scores better against [1.0, 2.0, 3.0] than [1.0, 2.0, 3.0] itself does. To compare directions only, use array_cosine_distance. A zero result is printed as -0.0: zero with its sign changed.

Both arguments must be fixed-size arrays of floating-point values with the same number of elements. A VECTOR(FLOAT, n) column is one already, so pass it as it is. Write a query vector as a list literal cast to the array type, [1.0, 1.0, 1.0]::FLOAT[3]. A list column (FLOAT[]) must be cast to FLOAT[n] first.

With two FLOAT arrays the result is a FLOAT. If either argument is a DOUBLE array, the result is a DOUBLE. A HALF array gives a FLOAT, and an integer array is accepted as long as the other argument is a floating-point array.

NULLs and edge cases

Input Result
A NULL vector (either argument) NULL
A zero vector, or vectors at right angles -0.0
Very large FLOAT values, such as 1e38 against -1e38 inf; with DOUBLE arrays, 3e+76
A NULL element, such as [1.0, NULL, 3.0]::FLOAT[3] Error: array_negative_inner_product: left argument can not contain NULL values (or right argument)
Arrays of different sizes Error: Binder Error: array_negative_inner_product: Array arguments must be of the same size
An uncast list literal, such as [1.0, 2.0, 3.0] Error: No function matches the given name and argument types 'array_negative_inner_product(DECIMAL(2,1)[], DECIMAL(2,1)[])'
array_negative_inner_product(NULL, NULL) Error: Could not choose a best candidate function; give the NULL a type, such as NULL::FLOAT[3]

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

run("SELECT array_negative_inner_product([1.0, 2.0, 3.0]::FLOAT[3], NULL), "
    "array_negative_inner_product([3.0, 4.0]::FLOAT[2], [0.0, 0.0]::FLOAT[2]), "
    "array_negative_inner_product([1.0, 0.0]::FLOAT[2], [0.0, 1.0]::FLOAT[2])")
run("SELECT array_negative_inner_product([1e38, 1e38, 1e38]::FLOAT[3], [-1e38, -1e38, -1e38]::FLOAT[3]), "
    "array_negative_inner_product([1e38, 1e38, 1e38]::DOUBLE[3], [-1e38, -1e38, -1e38]::DOUBLE[3])")
run("SELECT array_negative_inner_product([1.0, NULL, 3.0]::FLOAT[3], [1.0, 2.0, 3.0]::FLOAT[3])")
run("SELECT array_negative_inner_product([1.0, 2.0, 3.0]::FLOAT[3], [1.0, 2.0, 3.0, 4.0]::FLOAT[4])")
run("SELECT array_negative_inner_product([1.0, 2.0, 3.0], [1.0, 2.0, 3.0])")
run("SELECT array_negative_inner_product(NULL, NULL)")

[(None, -0.0, -0.0)]
[(inf, 3e+76)]
OSError: array_negative_inner_product: left argument can not contain NULL values
InternalError: Binder Error: array_negative_inner_product: Array arguments must be of the same size
InternalError: Binder Error: No function matches the given name and argument types 'array_negative_inner_product(DECIMAL(2,1)[], DECIMAL(2,1)[])'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "array_negative_inner_product("NULL", "NULL")". In order to select one, please add explicit type casts.

Examples

The negative inner product of each document's embedding with a query vector. Document 3 has no embedding, so its result is NULL:

run(f"SELECT id, title, array_negative_inner_product(e, [1.0, 1.0, 1.0]::FLOAT[3]) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'same', -6.0), (2, 'zero', -0.0), (3, 'none', None), (4, 'opposite', 6.0), (5, 'axis', -1.0), (6, 'three-four', -7.0)]

The three best matches for the query vector, best first. three-four comes before same because its dot product with [1, 1, 1] is larger (7 against 6). ORDER BY on the result needs a LIMIT:

run(f"SELECT id, title, array_negative_inner_product(e::FLOAT[3], [1.0, 1.0, 1.0]::FLOAT[3]) AS score "
    f"FROM {TABLE} WHERE e IS NOT NULL ORDER BY score LIMIT 3")

[(6, 'three-four', -7.0), (1, 'same', -6.0), (5, 'axis', -1.0)]

The documents whose dot product with the query vector is more than 5, that is, whose result is below -5:

run(f"SELECT id, title FROM {TABLE} WHERE array_negative_inner_product(e, [1.0, 1.0, 1.0]::FLOAT[3]) < -5 ORDER BY id LIMIT 10")

[(1, 'same'), (6, 'three-four')]

The same result from the list column, cast to a fixed-size array, as FLOAT and as DOUBLE:

run(f"SELECT id, array_negative_inner_product(raw::FLOAT[3], [1.0, 1.0, 1.0]::FLOAT[3]), "
    f"array_negative_inner_product(raw::DOUBLE[3], [1.0, 1.0, 1.0]::DOUBLE[3]) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, -6.0, -6.0), (2, -0.0, -0.0), (3, None, None), (4, 6.0, 6.0), (5, -1.0, -1.0), (6, -7.0, -7.0)]

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

  • In the SELECT list, the scan reads the vector column and the Query Engine computes the result for each row it returns.
  • In WHERE, the scan sends the vector column for every row, and a FILTER step computes the result and keeps the rows that match.
  • In ORDER BY ... LIMIT k, a TOP_N step computes the result for the rows the scan sends and keeps the k smallest.

The plan of the best-matches example shows the TOP_N step above the scan, which reads id and e:

plan(f"SELECT id, array_negative_inner_product(e::FLOAT[3], [1.0, 1.0, 1.0]::FLOAT[3]) AS score FROM {TABLE} ORDER BY score LIMIT 3")

┌───────────────────────────┐
│           TOP_N           │
│    ────────────────────   │
│           Top: 3          │
│                           │
│         Order By:         │
│array_negative_inner_produc│
│   t(CAST(vastdb."<bucket>   │
│/docs_sql_fn_vector_array_n│
│   egative_inner_product"  │
│ .docs.e AS FLOAT[3]), CAST│
│ (main.list_value(1.0, 1.0,│
│   1.0) AS FLOAT[3])) ASC  │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│           score           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│             e             │
│                           │
└───────────────────────────┘

For tables with a vector index: which index metric goes with array_negative_inner_product, and the query shape that uses the index, are in the VAST Vector Search section: distance functions and query shape that uses the index.

Make it faster

  • Search large tables through a vector index. Create the table with a vector index and write the query in the shape the index uses; see query design.
  • Add pushed-down conditions. A comparison of a column with a constant, such as title <> 'zero', can be evaluated in VAST DataBase (pushdowns), so only the rows that pass it reach the computation. The plan has no FILTER step for it:
run(f"SELECT id, array_negative_inner_product(e::FLOAT[3], [1.0, 1.0, 1.0]::FLOAT[3]) AS score "
    f"FROM {TABLE} WHERE title <> 'zero' ORDER BY score LIMIT 3")
plan(f"SELECT id, array_negative_inner_product(e::FLOAT[3], [1.0, 1.0, 1.0]::FLOAT[3]) AS score "
     f"FROM {TABLE} WHERE title <> 'zero' ORDER BY score LIMIT 3")

[(6, -7.0), (1, -6.0), (5, -1.0)]
┌───────────────────────────┐
│           TOP_N           │
│    ────────────────────   │
│           Top: 3          │
│                           │
│         Order By:         │
│array_negative_inner_produc│
│   t(CAST(vastdb."<bucket>   │
│/docs_sql_fn_vector_array_n│
│   egative_inner_product"  │
│ .docs.e AS FLOAT[3]), CAST│
│ (main.list_value(1.0, 1.0,│
│   1.0) AS FLOAT[3])) ASC  │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│           score           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│             e             │
│                           │
└───────────────────────────┘

Limitations and errors

  • Fixed-size arrays only. A list, such as a FLOAT[] column or an uncast literal, fails with No function matches the given name and argument types. Cast it: raw::FLOAT[3].
  • The dimension must match. Arrays of different sizes fail, and so does casting a vector to another size.
  • No NULL elements. A vector with a NULL element fails; a NULL vector gives NULL.
  • Floating-point arrays only. Two integer arrays fail with Binder Error: array_negative_inner_product: Arguments must be arrays of FLOAT16, FLOAT or DOUBLE.
  • FLOAT can overflow. Very large values give inf; cast both arguments to DOUBLE[n].
  • ORDER BY needs a LIMIT. Without one it fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.
  • Other SQL dialects' names aren't available. inner_product and dot_product fail with Scalar Function with name ... does not exist!.
run(f"SELECT id, array_negative_inner_product(raw, [1.0, 1.0, 1.0]::FLOAT[3]) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT array_negative_inner_product([1, 2, 3]::INTEGER[3], [1, 2, 3]::INTEGER[3])")
run(f"SELECT id FROM {TABLE} ORDER BY array_negative_inner_product(e::FLOAT[3], [1.0, 1.0, 1.0]::FLOAT[3])")
run("SELECT inner_product([1.0, 2.0]::FLOAT[2], [1.0, 2.0]::FLOAT[2])")

InternalError: Binder Error: No function matches the given name and argument types 'array_negative_inner_product(FLOAT[], FLOAT[3])'. You might need to add explicit type casts.
InternalError: Binder Error: array_negative_inner_product: Arguments must be arrays of FLOAT16, FLOAT or DOUBLE
InternalError: The operator 'ORDER_BY' type=1 is currently not supported for this usage.
InternalError: Catalog Error: Scalar Function with name inner_product does not exist!

Similar functions

Function Measures [1, 2, 3] and [2, 4, 6] (same direction) [1, 2, 3] and [-1, -2, -3] (opposite)
array_negative_inner_product minus the dot product -28.0 14.0
array_inner_product the dot product: larger means more similar, so sort it descending 28.0 -14.0
array_cosine_distance the angle between the vectors, not their length 0.0 2.0
array_distance straight-line distance 3.7416574954986572 7.4833149909973145

array_inner_product returns the same value with the opposite sign. For searches on a table with a vector index, see the note on array_inner_product in distance functions.

run("SELECT array_negative_inner_product([1.0, 2.0, 3.0]::FLOAT[3], [2.0, 4.0, 6.0]::FLOAT[3]), "
    "array_inner_product([1.0, 2.0, 3.0]::FLOAT[3], [2.0, 4.0, 6.0]::FLOAT[3]), "
    "array_cosine_distance([1.0, 2.0, 3.0]::FLOAT[3], [2.0, 4.0, 6.0]::FLOAT[3]), "
    "array_distance([1.0, 2.0, 3.0]::FLOAT[3], [2.0, 4.0, 6.0]::FLOAT[3])")
run("SELECT array_negative_inner_product([1.0, 2.0, 3.0]::FLOAT[3], [-1.0, -2.0, -3.0]::FLOAT[3]), "
    "array_inner_product([1.0, 2.0, 3.0]::FLOAT[3], [-1.0, -2.0, -3.0]::FLOAT[3]), "
    "array_cosine_distance([1.0, 2.0, 3.0]::FLOAT[3], [-1.0, -2.0, -3.0]::FLOAT[3]), "
    "array_distance([1.0, 2.0, 3.0]::FLOAT[3], [-1.0, -2.0, -3.0]::FLOAT[3])")

[(-28.0, 28.0, 0.0, 3.7416574954986572)]
[(14.0, -14.0, 2.0, 7.4833149909973145)]

Clean up:

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

[]
[]

See also

Related articles
  • array_distance
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Vector distance functions
  • array_cosine_distance
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Vector distance functions
  • product
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Aggregate and analytic functions