* (multiplication)

Applies to VAST Cluster 5.5.

Functions › Operators and predicates › Arithmetic operators. a * b multiplies two numbers. multiply(a, b) is the same operation written as a function. On integers the result keeps the integer type, and a result that doesn't fit it stops the query; DECIMAL results are exact.

Syntax

a * b
multiply(a, b)

Returns: for two numbers of the same type, that type: INTEGER * INTEGER is an INTEGER, DOUBLE * DOUBLE a DOUBLE. A DECIMAL result adds the scales of the two arguments: DECIMAL(10,2) * DECIMAL(10,2) is a DECIMAL(18,4). An INTERVAL times an integer is an INTERVAL.

Aliases: multiply(a, b) for a * b.

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_operators_multiplication. 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 three order lines: the quantity (INTEGER), the unit price (DECIMAL(10,2)), the weight of one item in kilograms (DOUBLE) and the number of packs (UTINYINT, an unsigned 8-bit integer). The last 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_operators_multiplication"'

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}.order_lines"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, qty INTEGER, unit_price DECIMAL(10,2), weight_kg DOUBLE, packs UTINYINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 3, 19.99, 1.25, 4), "
    "(2, 12, 4.25, 0.5, 255), "
    "(3, 0, 0.00, 0.0, 0), "
    "(4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

a * b is the product of two numbers. The result depends on the types:

Integer overflow is an error. Each integer type has a fixed range, and a product outside it stops the query: Overflow in multiplication of INT32 (…)!. The unsigned types (UTINYINT, USMALLINT, UINTEGER, UBIGINT) work the same way: 255 * 2 on a UTINYINT fails with Overflow in multiplication of UINT8 (255 * 2)!. Cast one side to a wider type, such as BIGINT, before you multiply.

DECIMAL can overflow too. The result's precision grows with the arguments' precision, up to a limit; past it, the query stops with Overflow in multiplication of DECIMAL(18) (…). You might want to add an explicit cast to a bigger decimal.

FLOAT and DOUBLE don't raise errors. A product too large for the type is infinity (inf), and infinity times 0 is nan.

A numeric string is converted. In '12' * qty, the string is converted to the type of the other side, so it returns 36 for a qty of 3. A string that isn't a number fails with Could not convert string 'abc' to INT32.

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 NULL * 1, 1 * NULL, multiply(7, 2), 7 * 2")
run("SELECT 2.45 * 2.45, typeof(2.45 * 2.45), 0.1 * 3, 0.1::DOUBLE * 3")
run("SELECT 1e200::DOUBLE * 1e200::DOUBLE, 'inf'::DOUBLE * 0::DOUBLE, 3e38::FLOAT * 10::FLOAT")
run(f"SELECT qty * 2147483647 FROM {TABLE} WHERE id = 2")
run(f"SELECT packs * CAST(2 AS UTINYINT) FROM {TABLE} WHERE id = 2")
run("SELECT 99999999.99::DECIMAL(10,2) * 99999999.99::DECIMAL(10,2)")
run(f"SELECT '12' * qty, typeof('12' * qty) FROM {TABLE} WHERE id = 1")
run(f"SELECT 'abc' * qty FROM {TABLE} WHERE id = 1")
run("SELECT true * 2")

[(None, None, 14, 14)]
[(Decimal('6.0025'), 'DECIMAL(6,4)', Decimal('0.3'), 0.30000000000000004)]
[(inf, nan, inf)]
OSError: Overflow in multiplication of INT32 (12 * 2147483647)!
OSError: Overflow in multiplication of UINT8 (255 * 2)!
OSError: Overflow in multiplication of DECIMAL(18) (9999999999 * 9999999999). You might want to add an explicit cast to a bigger decimal.
[(36, 'INTEGER')]
OSError: Could not convert string 'abc' to INT32
InternalError: Binder Error: No function matches the given name and argument types '*(BOOLEAN, INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The line total, the weight of each line and the same total with multiply(). The total is an exact DECIMAL:

run(f"SELECT id, qty * unit_price, qty * weight_kg, multiply(qty, unit_price) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, Decimal('59.97'), 3.75, Decimal('59.97')), (2, Decimal('51.00'), 6.0, Decimal('51.00')), (3, Decimal('0.00'), 0.0, Decimal('0.00')), (4, None, None, None)]

The types of the results:

run(f"SELECT typeof(qty * qty), typeof(qty * unit_price), typeof(unit_price * unit_price), typeof(qty * weight_kg), "
    f"typeof(id * qty) FROM {TABLE} WHERE id = 1")

[('INTEGER', 'DECIMAL(18,2)', 'DECIMAL(18,4)', 'DOUBLE', 'BIGINT')]

A delivery window of one day per item, as an INTERVAL:

run(f"SELECT id, INTERVAL 1 DAY * qty FROM {TABLE} ORDER BY id LIMIT 10")

[(1, MonthDayNano(months=0, days=3, nanoseconds=0)), (2, MonthDayNano(months=0, days=12, nanoseconds=0)), (3, MonthDayNano(months=0, days=0, nanoseconds=0)), (4, None)]

Keep only the lines worth more than 50:

run(f"SELECT id, qty, unit_price FROM {TABLE} WHERE qty * unit_price > 50 ORDER BY id LIMIT 10")

[(1, 3, Decimal('19.99')), (2, 12, Decimal('4.25'))]

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 product of two columns, so qty * unit_price is computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine computes the product for each row it returns.
• In WHERE, the scan sends both columns for every row, and a FILTER step in the Query Engine computes the product 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 qty * unit_price > 50")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ((CAST(qty AS DECIMAL(18,0│
│ )) * unit_price) > 50.00) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            qty            │
│         unit_price        │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• An integer column times a constant is pushed down. qty * 2 > 10, compared with a constant, is evaluated in VAST DataBase: its plan is only a scan, with no FILTER step.
• Move the constant yourself for DOUBLE and DECIMAL columns. weight_kg * 2 > 4 and unit_price * 100 > 245 run in a FILTER step. Divide the constant instead: weight_kg > 4 / 2 is computed once, before the scan, and pushed down.

plan(f"SELECT id FROM {TABLE} WHERE qty * 2 > 10")
plan(f"SELECT id FROM {TABLE} WHERE weight_kg * 2 > 4")
plan(f"SELECT id FROM {TABLE} WHERE weight_kg > 4 / 2")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ((weight_kg * 2.0) > 4.0) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│         weight_kg         │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Store a product you filter on often. A product of two columns always runs in the Query Engine. If you often filter on the line total, compute it when you write the rows and keep it in its own column.
• Combine it with a pushed-down condition. In id > 100 AND qty * unit_price > 50, id > 100 is still evaluated in VAST DataBase, so the multiplication runs only on the rows that pass it.

Limitations and errors

• Integer overflow stops the query. A product outside the range of its integer type fails with Overflow in multiplication of …!, and one such row cancels the whole query. Cast one side to a wider type first: CAST(qty AS BIGINT) * 2147483647.
• DECIMAL overflow stops the query too. Cast to DOUBLE if you don't need an exact result.
• A string that isn't a number fails with Could not convert string '…' to INT32 (or the type of the other side).
• Booleans, dates, BLOB values and lists fail with No function matches the given name and argument types.

run(f"SELECT CAST(qty AS BIGINT) * 2147483647 FROM {TABLE} WHERE id = 2")
run("SELECT 99999999.99::DOUBLE * 99999999.99::DOUBLE")

[(25769803764,)]
[(9999999997999998.0,)]

Similar functions

run("SELECT 3 * 16, multiply(3, 16), 3 << 4, 3 * pow(2, 4), 3 / 16")

[(48, 48, 48, 48.0, 0.1875)]

Clean up:

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

[]
[]

See also

• Operators and predicates: all the arithmetic operators.
• + (addition), - (subtraction) and / (division).
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.