bool_or

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › True or false. An aggregate function that returns true if a BOOLEAN expression is true on at least one row of a query, and false if it's false on every row. It skips NULLs.

Syntax

bool_or(b)
bool_or(b) FILTER (WHERE condition)

Argument	Type	Description
`b`	`BOOLEAN`	A `BOOLEAN` column, or a condition such as `latency_ms > 100`.
`condition`	`BOOLEAN`	Optional. Only the rows where it's true count. See FILTER.

Returns: BOOLEAN.

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_aggregate_bool_or. 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 health checks. One check failed, and the last one has no result:

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

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}.checks"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, host VARCHAR, passed BOOLEAN, latency_ms BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'web1', true, 12), (2, 'web1', true, 30), (3, 'web2', false, 250), "
    "(4, 'web2', true, 18), (5, 'web3', NULL, NULL)")

[]
[]
[(5,)]

What it does

bool_or(b) evaluates b on every row the query keeps, skips the rows where it's NULL, and returns:

The non-NULL values of `b`	Result
At least one `true`	`true`
All `false`	`false`
None (only NULLs, or no rows)	`NULL`

It's the aggregate form of OR: "is this true for any row?". Its counterpart bool_and asks "is this true for every row?".

NULLs and edge cases

Input	Result
`false` and a NULL	`false`: the NULL is skipped
Only NULLs	`NULL`
No rows	`NULL`
`bool_or(NULL)`	`NULL`
`bool_or(true)`, no `FROM`	`true`
`bool_or(x IS NOT NULL)` on rows where `x` is NULL	`false`: the condition is false there, not NULL
`bool_or(DISTINCT b)`	runs, with the same result
A number, such as a `BIGINT` column	Error: `Binder Error: No function matches the given name and argument types 'bool_or(BIGINT)'. You might need to add explicit type casts.`
`bool_or(1)`	Error: the same, for `'bool_or(INTEGER)'`
`bool_or('true')`	Error: the same, for `'bool_or(VARCHAR)'`

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

run(f"SELECT bool_or(passed) FROM {TABLE} WHERE id IN (3, 5)")
run(f"SELECT bool_or(passed) FROM {TABLE} WHERE id = 5")
run(f"SELECT bool_or(passed) FROM {TABLE} WHERE id > 100")
run("SELECT bool_or(NULL), bool_or(true)")
run(f"SELECT bool_or(latency_ms IS NOT NULL) FROM {TABLE} WHERE id = 5")
run(f"SELECT bool_or(DISTINCT passed), bool_or(passed) FROM {TABLE}")
run(f"SELECT bool_or(latency_ms) FROM {TABLE}")
run(f"SELECT bool_or(1) FROM {TABLE}")
run(f"SELECT bool_or('true') FROM {TABLE}")

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

Examples

Did any check fail? NOT passed is true on a failed check; the check with no result is skipped:

run(f"SELECT bool_or(NOT passed) FROM {TABLE}")

[(True,)]

Was any response slower than 100 ms? The argument can be any condition:

run(f"SELECT bool_or(latency_ms > 100) FROM {TABLE}")

[(True,)]

Did any check of each host fail? GROUP BY isn't available, so each host gets its own FILTER clause:

run(f"SELECT bool_or(NOT passed) FILTER (WHERE host = 'web1') AS web1, "
    f"bool_or(NOT passed) FILTER (WHERE host = 'web2') AS web2 FROM {TABLE}")

[(False, True)]

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). An aggregate can't go in WHERE, so what matters is the plan of the aggregate query:

The scan reads the columns the query uses. A WHERE condition that VAST DataBase can evaluate, such as a comparison of a column with a constant, is applied in the scan, so the rows it rejects never reach the Query Engine. It doesn't appear as a step in the plan.
A WHERE condition the scan can't evaluate, such as one on a function of a column, appears as a FILTER step above the scan.
One UNGROUPED_AGGREGATE step in the Query Engine computes bool_or(), and every other aggregate in the SELECT list, over the rows it receives, and returns one row.

The plan of a bool_or() with a pushed-down WHERE condition has only the aggregate step and the scan:

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

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│      bool_or(passed)      │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│    Projections: passed    │
│                           │
└───────────────────────────┘

Make it faster

Restrict the rows in WHERE, not in FILTER, when every aggregate needs the same rows. A FILTER (WHERE ...) condition is evaluated in the aggregate step, after the scan has sent every row and the column the condition reads. The plan shows it inside the UNGROUPED_AGGREGATE step:

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

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│  bool_or(passed) FILTER   │
│ (WHERE (id > 1)) Filter:  │
│          (id > 1)         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│           passed          │
│                           │
└───────────────────────────┘

Ask several questions in one query. SELECT bool_or(NOT passed), bool_or(latency_ms > 100), bool_and(passed) ... reads the table once: all the aggregates of a query share one scan and one aggregate step.

Limitations and errors

BOOLEAN only. A number, a string, a date or a list fails with Binder Error: No function matches the given name and argument types 'bool_or(...)'. You might need to add explicit type casts. Turn the value into a condition first: bool_or(x <> 0), bool_or(s = 'yes').
No GROUP BY. SELECT host, bool_or(passed) ... GROUP BY host fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported. Use one FILTER clause per group, as in Examples, or run the query in Trino or Spark.
As a window function, only OVER (ROWS UNBOUNDED PRECEDING) runs, over the rows in the order the scan returns them. OVER (), OVER (ORDER BY ...) and OVER (PARTITION BY ...) fail with The operator 'WINDOW' type=6 is currently not supported. See window functions.
Not in WHERE. WHERE bool_or(passed) fails with Binder Error: WHERE clause cannot contain aggregates!

run(f"SELECT host, bool_or(passed) FROM {TABLE} GROUP BY host")
run(f"SELECT id, bool_or(passed) OVER (ORDER BY id) FROM {TABLE}")
run(f"SELECT id FROM {TABLE} WHERE bool_or(passed)")

InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
InternalError: The operator 'WINDOW' type=6 is currently not supported.
InternalError: Binder Error: WHERE clause cannot contain aggregates!

Similar functions

Function	Returns	Same as `bool_or(b)`?
bool_and	`true` if `b` is true on every row	no: it's the `AND` counterpart
`max(b)` (max)	the largest value; `true` sorts after `false`	yes, the same result
`count_if(b) > 0` (count_if)	`true` if any row is true	the same where some rows have a value; the second query below shows rows with none
list_bool_or	the same test on the elements of a list, per row	for list columns

run(f"SELECT bool_or(passed), max(passed), count_if(passed) > 0, bool_and(passed) FROM {TABLE}")
run(f"SELECT bool_or(passed), max(passed), count_if(passed) > 0 FROM {TABLE} WHERE id = 5")

[(True, True, True, False)]
[(None, None, None)]

Clean up:

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

[]
[]