--- title: "bool_and" slug: "dev-database-reference-sql-functions-aggregate-bool-and" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-aggregate-bool-and" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # bool_and *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Aggregate and analytic functions](/develop/docs/dev-database-reference-sql-functions-aggregate) › 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](/develop/docs/dev-database-reference-sql-functions-aggregate-filter-clause). | **Returns:** `BOOLEAN`. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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, "") 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](/develop/docs/dev-database-reference-sql-functions-aggregate-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](/develop/docs/dev-database-reference-sql-functions-aggregate-bool-and#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](/develop/docs/dev-database-reference-sql-functions-aggregate-bool-and#examples), or run the query in [Trino](/develop/docs/dev-database-integrations-trino) or [Spark](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-functions-aggregate-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](/develop/docs/dev-database-reference-sql-functions-aggregate-bool-or) | `true` if `b` is true on any row | no: it's the `OR` counterpart | | `min(b)` ([min](/develop/docs/dev-database-reference-sql-functions-aggregate-min)) | the smallest value; `false` sorts before `true` | yes, the same result | | `count_if(NOT b) = 0` ([count_if](/develop/docs/dev-database-reference-sql-functions-aggregate-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](/develop/docs/dev-database-reference-sql-functions-lists-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() ``` ``` [] [] ``` ## See also - [Aggregate and analytic functions](/develop/docs/dev-database-reference-sql-functions-aggregate): all the aggregates. - [SELECT](/develop/docs/dev-database-reference-sql-select#aggregates): aggregates in a query, and [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan. - [Pushdowns](/develop/docs/dev-database-reference-pushdowns): which `WHERE` conditions VAST DataBase evaluates.