signbit

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Bits of numbers. Returns true when the sign bit of a floating-point number is set. Unlike a test such as x < 0, it tells -0.0 from 0.0, and it reads the sign of infinities and NaN: signbit(-0.0::DOUBLE) is true.

Syntax

signbit(x)

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_binary_hash_uuid_signbit. 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 sensor readings, each as a DOUBLE and as a DECIMAL. Reading 2 is a negative zero in the DOUBLE column, and the third row has no values:

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

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}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, x DOUBLE, amount DECIMAL(10,2))")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 2.5, 2.50), "
    "(2, '-0.0'::DOUBLE, 0.00), "
    "(3, NULL, NULL), "
    "(4, -2.5, -2.50)")

[]
[]
[(4,)]

What it does

A floating-point number stores its sign in one bit, separately from its value. signbit returns that bit:

An integer or a DECIMAL is accepted too: signbit is true for negative values and false for zero. A DECIMAL has no negative zero: -0.0 written as a literal is a DECIMAL, so signbit(-0.0) is false. Write -0.0::DOUBLE or '-0.0'::DOUBLE for the floating-point value.

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 signbit(NULL), signbit(-0.0::DOUBLE), signbit('-0.0'::FLOAT), signbit(-0.0), signbit(0.0 * -1), "
    "signbit(-0.0::DOUBLE * 1)")
run("SELECT signbit('inf'::DOUBLE), signbit('-inf'::DOUBLE), signbit('nan'::DOUBLE), signbit('-nan'::DOUBLE), "
    "signbit(-2), signbit(-1::TINYINT), signbit(-2.50::DECIMAL(5,2)), signbit(0)")
run("SELECT signbit('-1')")

[(None, True, True, False, False, True)]
[(False, True, False, True, True, True, True, False)]
InternalError: Binder Error: Could not choose a best candidate function for the function call "signbit(STRING_LITERAL)". In order to select one, please add explicit type casts.

Examples

The sign bit of each reading, compared with x < 0. Reading 2 is a negative zero, so only signbit sees its sign; its DECIMAL copy is a plain zero:

run(f"SELECT id, x, x < 0, signbit(x), amount, signbit(amount) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 2.5, False, False, Decimal('2.50'), False), (2, -0.0, False, True, Decimal('0.00'), False), (3, None, None, None, None, None), (4, -2.5, True, True, Decimal('-2.50'), True)]

Keep the readings whose sign bit is set, including the negative zero:

run(f"SELECT id, x FROM {TABLE} WHERE signbit(x) ORDER BY id LIMIT 10")

[(2, -0.0), (4, -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). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so signbit() is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine computes signbit() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes signbit() and keeps the rows that match. No rows are skipped in storage.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE signbit(x)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│         signbit(x)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             x             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Use x < 0 when negative zero doesn't matter. A comparison of the raw column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. It misses -0.0, which signbit(x) finds. The plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE x < 0")

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

• Combine it with a pushed-down condition. In x <= 0 AND signbit(x), x <= 0 is evaluated in VAST DataBase, so signbit() runs only on the rows that pass it: the negative values and both zeros.

Limitations and errors

• Numbers only. A string literal fails with Could not choose a best candidate function, and a BLOB, date, boolean or list fails with No function matches the given name and argument types. The error lists the types signbit accepts: signbit(FLOAT) and signbit(DOUBLE). Cast a string to DOUBLE first.

run("SELECT signbit(true)")
run("SELECT signbit('-1'::DOUBLE)")

InternalError: Binder Error: No function matches the given name and argument types 'signbit(BOOLEAN)'. You might need to add explicit type casts.
[(True,)]

• No negative zero in DECIMAL or integer columns. Only FLOAT and DOUBLE values have one. A DECIMAL zero, and a DECIMAL expression such as 0.0 * -1, have signbit false.

Similar functions

run("SELECT signbit(-0.0::DOUBLE), sign(-0.0::DOUBLE), -0.0::DOUBLE < 0, signbit(-2.5::DOUBLE), sign(-2.5::DOUBLE), "
    "-2.5::DOUBLE < 0")

[(True, 0, False, True, -1, True)]

Clean up:

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

[]
[]

See also

• Numeric and math functions: sign and signbit with the other sign and rounding functions.
• Binary, hash and UUID functions: the bit string functions.
• Where filters run and pushdowns.