bool_and

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 every row of a query, and false if it's false on at least one. It skips NULLs.

Syntax

bool_and(b)
bool_and(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_and. 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_and"'

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_and(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
All `true`	`true`
At least one `false`	`false`
None (only NULLs, or no rows)	`NULL`

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

NULLs and edge cases

Input	Result
`true` and a NULL	`true`: the NULL is skipped
Only NULLs	`NULL`
No rows	`NULL`
`bool_and(NULL)`	`NULL`
`bool_and(true)`, no `FROM`	`true`
`bool_and(x IS NOT NULL)` on rows where `x` is NULL	`false`: the condition is false there, not NULL
`bool_and(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_and(BIGINT)'. You might need to add explicit type casts.`
`bool_and(1)`	Error: the same, for `'bool_and(INTEGER)'`
`bool_and('true')`	Error: the same, for `'bool_and(VARCHAR)'`

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

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

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

Examples

Did every check pass? The check with no result is skipped:

run(f"SELECT bool_and(passed) FROM {TABLE}")

[(False,)]

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

run(f"SELECT bool_and(latency_ms < 100) FROM {TABLE}")

[(False,)]

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

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

[(True, False)]

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_and(), and every other aggregate in the SELECT list, over the rows it receives, and returns one row.

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

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

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│      bool_and(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_and(passed) FILTER (WHERE id > 1) FROM {TABLE}")

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

Ask several questions in one query. SELECT bool_and(passed), bool_or(passed), bool_and(latency_ms < 100) ... 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_and(...)'. You might need to add explicit type casts. Turn the value into a condition first: bool_and(x <> 0), bool_and(s = 'yes').
No GROUP BY. SELECT host, bool_and(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_and(passed) fails with Binder Error: WHERE clause cannot contain aggregates!

run(f"SELECT host, bool_and(passed) FROM {TABLE} GROUP BY host")
run(f"SELECT id, bool_and(passed) OVER (ORDER BY id) FROM {TABLE}")
run(f"SELECT id FROM {TABLE} WHERE bool_and(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_and(b)`?
bool_or	`true` if `b` is true on any row	no: it's the `OR` counterpart
`min(b)` (min)	the smallest value; `false` sorts before `true`	yes, the same result
`count_if(NOT b) = 0` (count_if)	`true` if no row is false	the same where some rows have a value; the second query below shows rows with none
list_bool_and	the same test on the elements of a list, per row	for list columns

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

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

Clean up:

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

[]
[]