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

Overview

Prev Next

Applies to VAST Cluster 5.5.

The VAST Query Engine evaluates functions and operators in SELECT lists, WHERE clauses and UPDATE ... SET. A function on a column is computed in the Query Engine, after the scan; only comparisons of a column with a constant are evaluated in VAST DataBase (where filters run).

The pages for each family list the functions that run in the VAST Query Engine on VAST Cluster 5.5, with an entry for each: what it does, its arguments and result type, what it does with NULLs, and an example. Each page also says how its functions run and how to make queries that use them faster. For the aggregate and vector distance functions, see also VAST Query Engine and Vector Search.

A function applied to a column is usually evaluated in the Query Engine, after VAST DataBase has read the rows. Some conditions are evaluated in VAST DataBase itself and skip rows before they're sent, such as a comparison of a column with a constant, a widening CAST, or a comparison on a struct field; operators and predicates has the full list.

Family Covers Page
Aggregate and analytic count, sum, avg, min, max and the other aggregates, FILTER (WHERE ...), row_number() OVER () Aggregate and analytic functions
String Length, case, concatenation, substrings, replacing, formatting, similarity and paths Text basics
Prefix, suffix and substring tests, LIKE, splitting strings Search and split
trim, ltrim, rtrim, lpad, rpad Trimming and padding
regexp_* functions, SIMILAR TO, ~, pattern syntax and options Regular expressions
Parsing and JSON JSON text in VARCHAR columns: extract, paths, types, validity Parsing and JSON functions
Date and time DATE, TIMESTAMP and INTERVAL functions, and what fails without time zones Date and time functions
Numeric and math Rounding, arithmetic, math, bit counting and number formatting Numeric and math functions
Conditional and conversion CAST, ::, TRY_CAST, CASE, coalesce, nullif, greatest, least Conditional and conversion functions
Lists, structs and maps List functions and lambdas, list aggregates, struct access, maps, unnest List, struct and map functions
Table functions range, generate_series, unnest in FROM, and what replaces file readers Table functions
Binary, hash and UUID Bit strings, BLOB data and encodings, hashes, UUIDs, enums Binary, hash and UUID functions
Operators and predicates Comparisons, BETWEEN, IN, LIKE, IS NULL, AND, OR, arithmetic and bitwise operators, and which predicates push down Operators and predicates

Vector distance functions are in vector search SQL.

Rules that apply to every family:

  • Aggregates work without GROUP BY; see SELECT.
  • Time-zone functions aren't available. current_date, today(), timezone(), AT TIME ZONE, and date_trunc() on a TIMESTAMP WITH TIME ZONE value fail. now() and current_timestamp work; cast them to TIMESTAMP (data types in SQL).
  • Functions that read files fail, such as read_parquet() and read_csv(): the Query Engine has no file system access. To load files, import Parquet files.

Trino and Spark

Trino and Spark have their own function libraries; see Trino and Spark.

Related articles