Expressions and Operators

An expression is any construct that evaluates to a value. Expressions appear in SELECT projections, WHERE conditions, ORDER BY clauses, CHECK constraints, and DEFAULT values.

Operator Precedence

From highest to lowest binding (higher = evaluated first):

Use parentheses to make complex expressions explicit:

-- Without parens: AND binds tighter than OR
SELECT * FROM orders WHERE status = 'paid' OR status = 'shipped' AND total > 100;
-- Parsed as: status = 'paid' OR (status = 'shipped' AND total > 100)

-- Explicit grouping
SELECT * FROM orders WHERE (status = 'paid' OR status = 'shipped') AND total > 100;

String Literals and Identifier Quoting

AxiomDB follows MySQL session semantics for double quotes:

• Single quotes always produce string literals: 'hello'
• Backticks always produce quoted identifiers: `order`
• Double quotes depend on @@sql_mode

Default mode: ANSI_QUOTES OFF

By default, @@sql_mode does not include ANSI_QUOTES, so double quotes behave like string delimiters:

SELECT "hello";                 -- string literal
SELECT * FROM users WHERE name = "Alice";

ANSI_QUOTES enabled

When the session enables ANSI_QUOTES, double quotes switch to delimited identifiers and no longer produce string literals:

SET sql_mode = 'ANSI_QUOTES,STRICT_TRANS_TABLES';

SELECT "name" FROM "users";     -- identifier quoting
SELECT 'hello';                 -- use single quotes for strings

If ANSI_QUOTES is ON, SELECT "hello" is parsed as a column reference to the identifier hello, not as the string literal "hello".

Arithmetic Operators

Integer division truncates toward zero: 7 / 2 = 3.

Division by zero raises a runtime error (22012 division_by_zero).

SELECT
    price,
    price * 0.19        AS tax,
    price * 1.19        AS price_with_tax,
    ROUND(price, 2)     AS rounded
FROM products;

Comparison Operators

SELECT * FROM products WHERE price = 49.99;
SELECT * FROM products WHERE stock <> 0;
SELECT * FROM orders   WHERE total >= 100;

Boolean Operators

NULL Semantics — Three-Valued Logic

AxiomDB implements SQL three-valued logic: every boolean expression evaluates to TRUE, FALSE, or UNKNOWN (which SQL represents as NULL in boolean context). The rules below are critical for writing correct WHERE clauses.

AND truth table

OR truth table

NOT truth table

Key consequences

-- NULL compared to anything is UNKNOWN, not TRUE or FALSE
SELECT NULL = NULL;      -- UNKNOWN (NULL, not TRUE)
SELECT NULL <> NULL;     -- UNKNOWN
SELECT NULL = 1;         -- UNKNOWN

-- WHERE filters only rows where condition is TRUE
-- Rows where the condition is UNKNOWN are excluded
SELECT * FROM users WHERE age = NULL;    -- always returns 0 rows!
SELECT * FROM users WHERE age IS NULL;   -- correct NULL check

-- UNKNOWN in AND
SELECT * FROM orders WHERE total > 100 AND NULL;  -- 0 rows (UNKNOWN is filtered)

-- UNKNOWN in OR
SELECT * FROM orders WHERE total > 100 OR NULL;   -- rows where total > 100

IS NULL / IS NOT NULL

These predicates are the correct way to check for NULL. They always return TRUE or FALSE, never UNKNOWN.

-- Find unshipped orders
SELECT * FROM orders WHERE shipped_at IS NULL;

-- Find orders that have been shipped
SELECT * FROM orders WHERE shipped_at IS NOT NULL;

-- Combine with other conditions
SELECT * FROM users WHERE deleted_at IS NULL AND age > 18;

BETWEEN

BETWEEN low AND high is inclusive on both ends. Equivalent to >= low AND <= high.

-- Products priced between $10 and $50 inclusive
SELECT * FROM products WHERE price BETWEEN 10 AND 50;

-- Orders placed in Q1 2026
SELECT * FROM orders
WHERE placed_at BETWEEN '2026-01-01 00:00:00' AND '2026-03-31 23:59:59';

-- NOT BETWEEN
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 50;

LIKE — Pattern Matching

LIKE matches strings against a pattern.

Pattern matching is case-sensitive by default. Use CITEXT columns or ILIKE for case-insensitive matching.

-- Emails from example.com
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Names starting with 'Al'
SELECT * FROM users WHERE name LIKE 'Al%';

-- Exactly 5-character codes
SELECT * FROM products WHERE sku LIKE '_____';

-- NOT LIKE
SELECT * FROM users WHERE email NOT LIKE '%@test.%';

-- Escape a literal %
SELECT * FROM products WHERE description LIKE '50\% off' ESCAPE '\';

IN — Membership Test

IN checks whether a value matches any element in a list.

-- Multiple status values
SELECT * FROM orders WHERE status IN ('pending', 'paid', 'shipped');

-- Numeric list
SELECT * FROM products WHERE category_id IN (1, 3, 7);

-- NOT IN
SELECT * FROM orders WHERE status NOT IN ('cancelled', 'refunded');

NOT IN (list) returns UNKNOWN (no rows) if any element in the list is NULL. Use NOT EXISTS or explicit NULL checks when the list may contain NULLs.

-- Safe: explicit list with no NULLs
SELECT * FROM orders WHERE status NOT IN ('cancelled', 'refunded');

-- Dangerous if user_id can be NULL:
SELECT * FROM orders WHERE user_id NOT IN (SELECT id FROM banned_users);
-- If banned_users contains even one NULL user, this returns 0 rows!
-- Safe alternative:
SELECT * FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM banned_users b WHERE b.id = o.user_id AND b.id IS NOT NULL
);

MySQL-Compatible Operators (G2 / G4)

IS TRUE / IS FALSE

-- Non-zero integers count as TRUE; NULL is never TRUE
SELECT * FROM flags WHERE active IS TRUE;
SELECT * FROM flags WHERE active IS NOT FALSE;

-- NULL IS TRUE  → FALSE
-- NULL IS FALSE → FALSE
-- 0 IS FALSE    → TRUE
-- 1 IS TRUE     → TRUE

REGEXP / RLIKE — Regular Expression Match

REGEXP (alias RLIKE) tests whether a string matches a POSIX regular expression.

-- Emails matching a pattern
SELECT * FROM users WHERE email REGEXP '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$';

-- Rows where code starts with a digit
SELECT * FROM products WHERE sku RLIKE '^[0-9]';

-- NOT REGEXP
SELECT * FROM products WHERE name NOT REGEXP '[0-9]';

XOR — Exclusive OR

SELECT a XOR b FROM flags;   -- true if exactly one of a, b is true

DIV — Integer Division

DIV performs integer (truncated) division. Returns NULL if the divisor is zero.

SELECT 7 DIV 2;      -- 3
SELECT -7 DIV 2;     -- -3  (truncates toward zero)
SELECT 5 DIV 0;      -- NULL  (MySQL-compatible, not an error)

<=> — Null-Safe Equality

<=> is like = but never returns NULL — it returns TRUE when both sides are NULL.

-- Regular = returns NULL for NULL comparisons
SELECT NULL = NULL;    -- NULL (unknown)

-- Null-safe operator always returns TRUE/FALSE
SELECT NULL <=> NULL;  -- 1 (TRUE)
SELECT NULL <=> 1;     -- 0 (FALSE)
SELECT 1    <=> 1;     -- 1 (TRUE)

Bitwise Operators

-- Check if bit 0 is set
SELECT * FROM events WHERE type & 0x01 = 1;

-- Combine flags
SELECT (read_perm | write_perm) AS combined FROM permissions;

Hex Literals

SELECT 0xFF;        -- 255
SELECT 0x1A2B;      -- 6699
SELECT flags & 0xFF FROM events;   -- bitmask with hex constant

Scalar Functions

Numeric Functions

String Functions

String Concatenation — ||

The || operator concatenates two string values. It is the SQL-standard alternative to CONCAT() and works in any expression context.

-- Build a full name from two columns
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- Append a suffix
SELECT sku || '-v2' AS new_sku FROM products;

-- NULL propagates: if either operand is NULL the result is NULL
SELECT 'hello' || NULL;   -- NULL

Use COALESCE to guard against NULL operands:

SELECT COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name
FROM users;

CAST — Explicit Type Conversion

CAST(expr AS type) converts a value to the specified type. Use it when an implicit coercion would be rejected in strict mode (the default).

-- Text-to-number: always works when the text is a valid number
SELECT CAST('42' AS INT);        -- 42
SELECT CAST('3.14' AS REAL);     -- 3.14
SELECT CAST('100' AS BIGINT);    -- 100

-- Use CAST to store a text literal in a numeric column
INSERT INTO users (age) VALUES (CAST('30' AS INT));

Supported CAST pairs:

Conditional Functions

-- COALESCE: display a fallback when the column is NULL
SELECT name, COALESCE(phone, 'N/A') AS contact FROM users;

-- NULLIF: convert 'unknown' to NULL (for aggregate functions to ignore)
SELECT AVG(NULLIF(rating, 0)) AS avg_rating FROM products;

-- CASE: categorize order size
SELECT
    id,
    total,
    CASE
        WHEN total < 50   THEN 'small'
        WHEN total < 200  THEN 'medium'
        WHEN total < 1000 THEN 'large'
        ELSE                   'enterprise'
    END AS order_size
FROM orders;

CASE WHEN — Conditional Expressions

CASE WHEN is a general-purpose conditional expression that can appear anywhere an expression is valid: SELECT projections, WHERE clauses, ORDER BY, GROUP BY, HAVING, and as arguments to aggregate functions.

AxiomDB supports two forms: searched CASE (any boolean condition per branch) and simple CASE (equality comparison against a single value).

Searched CASE

Evaluates each WHEN condition left to right and returns the THEN value of the first condition that is TRUE. If no condition matches and an ELSE is present, the ELSE value is returned. If no condition matches and there is no ELSE, the result is NULL.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ELSE default_result]
END

-- Categorize orders by total amount
SELECT
    id,
    total,
    CASE
        WHEN total < 50    THEN 'small'
        WHEN total < 200   THEN 'medium'
        WHEN total < 1000  THEN 'large'
        ELSE                    'enterprise'
    END AS order_size
FROM orders;

-- Compute a human-readable status label, including NULL handling
SELECT
    id,
    CASE
        WHEN shipped_at IS NULL AND status = 'paid' THEN 'awaiting shipment'
        WHEN shipped_at IS NOT NULL                 THEN 'shipped'
        WHEN status = 'cancelled'                   THEN 'cancelled'
        ELSE                                             'unknown'
    END AS display_status
FROM orders;

Simple CASE

Compares a single expression against a list of values. Equivalent to a searched CASE using = for each WHEN comparison.

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    [ELSE default_result]
END

-- Map status codes to display labels
SELECT
    id,
    CASE status
        WHEN 'pending'   THEN 'Pending Payment'
        WHEN 'paid'      THEN 'Paid'
        WHEN 'shipped'   THEN 'Shipped'
        WHEN 'delivered' THEN 'Delivered'
        WHEN 'cancelled' THEN 'Cancelled'
        ELSE                  'Unknown'
    END AS status_label
FROM orders;

NULL Semantics in CASE

In a searched CASE, a WHEN condition that evaluates to UNKNOWN (NULL in boolean context) is treated the same as FALSE — it does not match, and evaluation continues to the next branch. This means a NULL condition never triggers a THEN clause.

In a simple CASE, the comparison expression = value uses standard SQL equality, which returns UNKNOWN when either side is NULL. As a result, WHEN NULL never matches. Use a searched CASE with IS NULL to handle NULL values explicitly.

-- Simple CASE: WHEN NULL never matches (NULL <> NULL in equality)
SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'no match' END;
-- Result: 'no match'

-- Correct way to handle NULL in a simple CASE: use searched form
SELECT
    CASE
        WHEN status IS NULL THEN 'no status'
        ELSE status
    END AS safe_status
FROM orders;

CASE in ORDER BY — Controlled Sort Order

CASE can produce a sort key that cannot be expressed with a single column reference.

-- Sort orders: unshipped first (status='paid'), then by recency
SELECT id, status, placed_at
FROM orders
ORDER BY
    CASE WHEN status = 'paid' AND shipped_at IS NULL THEN 0 ELSE 1 END,
    placed_at DESC;

CASE in GROUP BY — Dynamic Grouping

-- Group products by price tier and count items per tier
SELECT
    CASE
        WHEN price < 25   THEN 'budget'
        WHEN price < 100  THEN 'mid-range'
        ELSE                   'premium'
    END      AS tier,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
WHERE deleted_at IS NULL
GROUP BY
    CASE
        WHEN price < 25   THEN 'budget'
        WHEN price < 100  THEN 'mid-range'
        ELSE                   'premium'
    END
ORDER BY avg_price;

Design note: AxiomDB evaluates CASE expressions during row processing in the executor’s expression evaluator. Short-circuit evaluation guarantees that branches after the first matching WHEN are never evaluated, which prevents side effects (e.g., division by zero in an unreachable branch).

Date / Time Functions

Current date / time

Date component extractors

val accepts DATE, TIMESTAMP, or a text string coercible to a date. Returns NULL if the input is NULL or not a valid date type.

SELECT year(NOW()),  month(NOW()),  day(NOW());   -- e.g. 2025, 3, 25
SELECT hour(NOW()),  minute(NOW()), second(NOW()); -- e.g. 14, 30, 45

DATE_FORMAT — format a date as text

DATE_FORMAT(ts, format_string) → TEXT

Formats a DATE or TIMESTAMP value using MySQL-compatible format specifiers. Returns NULL if either argument is NULL or the format string is empty.

Unknown specifiers are passed through literally (%X → %X).

-- Format a stored timestamp as ISO date
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM orders;
-- '2025-03-25'

-- European date format
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');
-- '25/03/2025'

-- Full datetime
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
-- '2025-03-25 14:30:45'

-- NULL input → NULL output
SELECT DATE_FORMAT(NULL, '%Y-%m-%d');  -- NULL

STR_TO_DATE — parse a date string

STR_TO_DATE(str, format_string) → DATE | TIMESTAMP | NULL

Parses a text string into a date or timestamp using MySQL-compatible format specifiers (same table as DATE_FORMAT above).

• Returns DATE if the format contains only date components.
• Returns TIMESTAMP if the format contains any time components (%H, %i, %s).
• Returns NULL on any parse failure — never raises an error (MySQL behavior).
• Returns NULL if either argument is NULL.

2-digit year rule (%y): 00–69 → 2000–2069; 70–99 → 1970–1999.

-- Parse ISO date → Value::Date
SELECT STR_TO_DATE('2025-03-25', '%Y-%m-%d');

-- Parse European date → Value::Date
SELECT STR_TO_DATE('25/03/2025', '%d/%m/%Y');

-- Parse datetime → Value::Timestamp
SELECT STR_TO_DATE('2025-03-25 14:30:00', '%Y-%m-%d %H:%i:%s');

-- Extract components from a parsed date
SELECT year(STR_TO_DATE('2025-03-25', '%Y-%m-%d'));  -- 2025

-- Round-trip: parse then format
SELECT DATE_FORMAT(STR_TO_DATE('2025-03-25', '%Y-%m-%d'), '%d/%m/%Y');
-- '25/03/2025'

-- Invalid date → NULL (Feb 30 does not exist)
SELECT STR_TO_DATE('2025-02-30', '%Y-%m-%d');  -- NULL

-- Bad format → NULL (never an error)
SELECT STR_TO_DATE('not-a-date', '%Y-%m-%d');  -- NULL

FIND_IN_SET — search a comma-separated list

FIND_IN_SET(needle, csv_list) → INT

Returns the 1-indexed position of needle in the comma-separated string csv_list. Returns 0 if not found. Comparison is case-insensitive. Returns NULL if either argument is NULL.

SELECT FIND_IN_SET('b', 'a,b,c');   -- 2
SELECT FIND_IN_SET('B', 'a,b,c');   -- 2  (case-insensitive)
SELECT FIND_IN_SET('z', 'a,b,c');   -- 0  (not found)
SELECT FIND_IN_SET('a', '');        -- 0  (empty list)
SELECT FIND_IN_SET(NULL, 'a,b,c'); -- NULL

Useful for querying rows where a column holds a comma-separated tag list:

SELECT * FROM articles WHERE FIND_IN_SET('rust', tags) > 0;

-- DATE_TRUNC and DATE_PART (PostgreSQL-compatible aliases)
SELECT DATE_TRUNC('month', placed_at) AS month, COUNT(*) FROM orders GROUP BY 1;
SELECT DATE_PART('year', created_at) AS signup_year FROM users;

Session Functions

Session functions return state that is specific to the current connection and is not visible to other sessions.

-- Commonly called by ORMs on connect to verify server identity
SELECT version();             -- '8.0.36-AxiomDB-0.1.0'
SELECT current_user();        -- 'root'
SELECT current_database();    -- 'axiomdb'

Semantics:

• Returns 0 if no AUTO_INCREMENT INSERT has occurred in the current session.
• For a single-row INSERT, returns the generated ID.
• For a multi-row INSERT (INSERT INTO t VALUES (...), (...), ...), returns the ID generated for the first row of the batch (MySQL semantics). Subsequent rows receive consecutive IDs.
• Inserting an explicit non-NULL value into an AUTO_INCREMENT column does not advance the sequence and does not update LAST_INSERT_ID().
• TRUNCATE TABLE resets the sequence to 1 but does not change the session’s LAST_INSERT_ID() value.

CREATE TABLE items (id BIGINT PRIMARY KEY AUTO_INCREMENT, name TEXT);

-- Single-row INSERT
INSERT INTO items (name) VALUES ('Widget');
SELECT LAST_INSERT_ID();      -- 1
SELECT lastval();             -- 1

-- Multi-row INSERT
INSERT INTO items (name) VALUES ('Gadget'), ('Gizmo'), ('Doohickey');
SELECT LAST_INSERT_ID();      -- 2 (first generated ID in the batch)

-- Explicit value — does not change LAST_INSERT_ID()
INSERT INTO items (id, name) VALUES (99, 'Special');
SELECT LAST_INSERT_ID();      -- still 2

-- Use inside the same statement (e.g., insert a child row)
INSERT INTO orders (user_id, item_id) VALUES (42, LAST_INSERT_ID());

Aggregate Functions

SELECT
    COUNT(*)        AS total_rows,
    COUNT(email)    AS rows_with_email,   -- excludes NULL
    SUM(total)      AS gross_revenue,
    AVG(total)      AS avg_order_value,
    MIN(placed_at)  AS first_order,
    MAX(placed_at)  AS last_order
FROM orders
WHERE status != 'cancelled';

GROUP_CONCAT — String Aggregation

GROUP_CONCAT concatenates non-NULL values across the rows of a group into a single string. It is MySQL’s most widely-used aggregate function for collecting tags, roles, categories, and comma-separated lists without a client-side join.

string_agg(expr, separator) is the PostgreSQL-compatible alias.

Syntax

GROUP_CONCAT([DISTINCT] expr [ORDER BY col [ASC|DESC], ...] [SEPARATOR 'str'])

string_agg(expr, separator)

Behavior

• NULL values are skipped — they do not appear in the result and do not add a separator.
• An empty group (no rows) or a group where every value is NULL returns NULL.
• A single value returns that value with no separator added.
• Result is truncated to 1 MB (1,048,576 bytes) maximum.

-- Basic: comma-separated tags per post
SELECT post_id, GROUP_CONCAT(tag ORDER BY tag ASC)
FROM post_tags
GROUP BY post_id;
-- post 1 → 'async,db,rust'
-- post 2 → 'rust,web'
-- post 3 (all NULL tags) → NULL

-- Custom separator
SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ' | ')
FROM post_tags
WHERE post_id = 1;
-- → 'async | db | rust'

-- DISTINCT: deduplicate before joining
SELECT GROUP_CONCAT(DISTINCT tag ORDER BY tag ASC)
FROM tags;
-- Duplicate 'rust' rows → 'async,db,rust' (appears once)

-- string_agg PostgreSQL alias
SELECT string_agg(tag, ', ')
FROM post_tags
WHERE post_id = 2;
-- → 'rust, web' (or 'web, rust' — insertion order)

-- HAVING on a GROUP_CONCAT result
SELECT post_id, GROUP_CONCAT(tag ORDER BY tag ASC) AS tags
FROM post_tags
GROUP BY post_id
HAVING GROUP_CONCAT(tag ORDER BY tag ASC) LIKE '%rust%';
-- Only posts that have the 'rust' tag

-- Collect integers as text
SELECT GROUP_CONCAT(n ORDER BY n ASC) FROM nums;
-- 1, 2, 3 → '1,2,3'

BLOB / Binary Functions

AxiomDB stores binary data as the BLOB / BYTES type and provides functions for encoding, decoding, and measuring binary values.

Usage examples

-- Store binary data encoded as base64
INSERT INTO files (name, data)
VALUES ('logo.png', FROM_BASE64('iVBORw0KGgoAAAANSUhEUgAA...'));

-- Retrieve as base64 for transport
SELECT name, TO_BASE64(data) AS data_b64 FROM files;

-- Check byte size of a blob
SELECT name, OCTET_LENGTH(data) AS size_bytes FROM files;

-- Hex encoding (PostgreSQL / MySQL ENCODE style)
SELECT ENCODE(data, 'hex') FROM files;          -- → 'deadbeef...'
SELECT DECODE('deadbeef', 'hex');               -- → binary bytes

-- OCTET_LENGTH vs LENGTH for text
SELECT LENGTH('héllo');       -- 5 (characters)
SELECT OCTET_LENGTH('héllo'); -- 6 (UTF-8 bytes: é = 2 bytes)

UUID Functions

AxiomDB generates and validates UUIDs server-side. No application-level library needed — the DB handles UUID primary keys directly.

Usage

-- Auto-generate a UUID primary key at insert time
CREATE TABLE events (
    id   UUID NOT NULL,
    name TEXT NOT NULL
);

INSERT INTO events (id, name)
VALUES (gen_random_uuid(), 'page_view');

-- Use UUID v7 for tables that benefit from time-ordered inserts
INSERT INTO events (id, name)
VALUES (uuid_generate_v7(), 'checkout');

-- Validate an incoming UUID string before inserting
SELECT is_valid_uuid('550e8400-e29b-41d4-a716-446655440000');  -- TRUE
SELECT is_valid_uuid('not-a-uuid');                             -- FALSE
SELECT is_valid_uuid(NULL);                                     -- NULL

UUID v4 vs UUID v7 — which to use?

-- UUID v4: fully random, best for security-sensitive IDs
-- Format: xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx (122 random bits)
SELECT gen_random_uuid();
-- → 'f47ac10b-58cc-4372-a567-0e02b2c3d479'

-- UUID v7: time-ordered prefix, best for primary keys on B+ Tree indexes
-- Format: [48-bit ms timestamp]-[12-bit rand]-[62-bit rand]
SELECT uuid_generate_v7();
-- → '018e2e3a-1234-7abc-8def-0123456789ab'
--    ^^^^^^^^^^^ always increasing