pi

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Angles and constants. Returns the constant π, 3.141592653589793, as a DOUBLE. It takes no arguments.

Syntax

pi()

Returns: DOUBLE.

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_numeric_pi. 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 four circles with their radius in metres. Row 3 has no radius:

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

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}.circles"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, radius DOUBLE)")
run(f"INSERT INTO {TABLE} VALUES (1, 1.0), (2, 2.5), (3, NULL), (4, 0.0)")

[]
[]
[(4,)]

What it does

pi() returns π rounded to the nearest DOUBLE, 3.141592653589793. It's the same value as acos(-1) and 4 * atan(1). Use it in formulas such as pi() * radius * radius (the area of a circle), or to convert angles; radians and degrees do that for you.

NULLs and edge cases

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

run("SELECT pi(), typeof(pi()), 2 * pi(), pi() = acos(-1), pi() = 4 * atan(1)")
run(f"SELECT id, radius, pi() FROM {TABLE} WHERE id = 3")
run("SELECT pi(1)")
run("SELECT pi")

[(3.141592653589793, 'DOUBLE', 6.283185307179586, True, True)]
[(3, None, 3.141592653589793)]
InternalError: Binder Error: No function matches the given name and argument types 'pi(INTEGER_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: Referenced column "pi" not found in FROM clause!

Examples

The area and circumference of each circle. Row 3 has no radius, so both are NULL:

run(f"SELECT id, pi() * radius * radius, 2 * pi() * radius FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 3.141592653589793, 6.283185307179586), (2, 19.634954084936208, 15.707963267948966), (3, None, None), (4, 0.0, 0.0)]

Keep the circles whose radius is more than π/2:

run(f"SELECT id, radius FROM {TABLE} WHERE radius > pi() / 2 ORDER BY id LIMIT 10")

[(2, 2.5)]

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). pi() is a constant, so the Query Engine computes it once, before the scan:

• Compared with a column, as in radius > pi() / 2, the whole right-hand side is a constant. The condition is pushed down to VAST DataBase, and the plan has no FILTER step.
• In arithmetic on a column, as in pi() * radius * radius, the Query Engine computes the expression for each row: in a PROJECTION step in the SELECT list, or in a FILTER step in WHERE.

The plan of the WHERE example has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE radius > pi() / 2")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Make it faster

• Keep the column alone on one side of a comparison. pi() * radius * radius > 5 makes the Query Engine compute the area for every row, in a FILTER step. radius > sqrt(5 / pi()) keeps the same rows, and the scan in VAST DataBase can evaluate it, because the right-hand side is a constant:

plan(f"SELECT id FROM {TABLE} WHERE pi() * radius * radius > 5")
plan(f"SELECT id FROM {TABLE} WHERE radius > sqrt(5 / pi())")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   (((3.141592653589793 *  │
│  radius) * radius) > 5.0) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           radius          │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Limitations and errors

• Parentheses are required. pi alone is read as a column name and fails with Referenced column "pi" not found in FROM clause!.
• No arguments. pi(1) fails with No function matches the given name and argument types 'pi(INTEGER_LITERAL)'.
• There's no e() constant. It fails with Scalar Function with name e does not exist!; use exp(1):

run("SELECT e()")
run("SELECT exp(1)")

InternalError: Catalog Error: Scalar Function with name e does not exist!
[(2.718281828459045,)]

Similar functions

run("SELECT acos(-1) = pi(), 4 * atan(1) = pi(), radians(180), degrees(pi()), exp(1)")

[(True, True, 3.141592653589793, 180.0, 2.718281828459045)]

Clean up:

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

[]
[]

See also

• Numeric and math functions: pi, degrees and radians.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.