SochDB SQL Surface & Compatibility

This document defines SochDB's SQL dialect support and the canonical pipeline for SQL execution.

Engine version

Reflects the SochDB core engine v2.0.3. Support levels below were verified against the volcano executor (sochdb-query/src/executor/) and the storage-backed SqlBridge (sochdb-query/src/sql/bridge.rs), which are the two production execution paths.

Multiple execution paths

SochDB has more than one SQL code path. The SqlBridge (storage-backed dispatcher) has the fullest statement coverage (DDL/DML, CREATE/DROP INDEX, ALTER TABLE, scopes and permissions). The volcano executor is the row-at-a-time operator engine that handles SELECT planning (JOIN, GROUP BY/HAVING, ORDER BY, EXPLAIN). A separate in-memory SqlExecutor (sochdb-query/src/sql/mod.rs) is a reference implementation only and is not the production path — it rejects multi-table FROM. Support levels in this page describe the bridge + volcano executor.

Architecture Overview

┌─────────────────────────────────────────────────────────────────────────┐
│                         SQL Query Lifecycle                              │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                          │
│  ┌─────────────┐     ┌─────────────┐     ┌─────────────┐                │
│  │   SQL Text  │ --> │   Lexer     │ --> │   Parser    │                │
│  │ (any dialect)│    │ (tokenize)  │     │ (parse)     │                │
│  └─────────────┘     └─────────────┘     └─────────────┘                │
│                                                 │                        │
│                                                 v                        │
│                             ┌───────────────────────────────────────┐   │
│                             │        Canonical AST                   │   │
│                             │  (dialect-normalized representation)   │   │
│                             └───────────────────────────────────────┘   │
│                                                 │                        │
│                   ┌─────────────────────────────┼─────────────────────┐ │
│                   │                             │                     │ │
│                   v                             v                     v │
│  ┌─────────────────────────┐   ┌─────────────────────────┐   ┌───────┐ │
│  │     Validator           │   │      Planner            │   │Executor│ │
│  │ (semantic checks)       │   │ (optimize + plan)       │   │ (run)  │ │
│  └─────────────────────────┘   └─────────────────────────┘   └───────┘ │
│                                                                          │
└─────────────────────────────────────────────────────────────────────────┘

Core SQL Support (Guaranteed)

Data Manipulation Language (DML)

Statement	Support	Example
SELECT	✅ Full	SELECT id, name FROM users WHERE age > 21
INSERT	✅ Full	INSERT INTO users (id, name) VALUES (1, 'Alice')
UPDATE	✅ Full	UPDATE users SET name = 'Bob' WHERE id = 1
DELETE	✅ Full	DELETE FROM users WHERE id = 1

Data Definition Language (DDL)

Statement	Support	Example
CREATE TABLE	✅ Full	CREATE TABLE users (id INT PRIMARY KEY, name TEXT)
DROP TABLE	✅ Full	DROP TABLE users
ALTER TABLE	🔄 Partial	ALTER TABLE users ADD COLUMN email TEXT (ADD/DROP COLUMN only)
CREATE INDEX	✅ Full	CREATE INDEX idx_name ON users (name)
DROP INDEX	✅ Full	DROP INDEX idx_name

CREATE INDEX and DROP INDEX are dispatched by the SqlBridge. ALTER TABLE currently supports ADD COLUMN and DROP COLUMN.

Idempotent DDL

Statement	Support	Behavior
CREATE TABLE IF NOT EXISTS	✅ Full	No-op if table exists
DROP TABLE IF EXISTS	✅ Full	No-op if table doesn't exist
CREATE INDEX IF NOT EXISTS	✅ Full	No-op if index exists
DROP INDEX IF EXISTS	✅ Full	No-op if index doesn't exist

Transactions

Statement	Support	Notes
BEGIN	✅ Full	Start transaction
COMMIT	✅ Full	Commit transaction
ROLLBACK	✅ Full	Rollback transaction
SAVEPOINT / RELEASE	❌ Not supported	Bridge returns NotImplemented

Dialect Compatibility (Conflict/Upsert Family)

SochDB normalizes dialect-specific INSERT variants to a canonical AST representation.

Conflict action	Support
ON CONFLICT DO NOTHING, INSERT IGNORE, INSERT OR IGNORE	✅ Full
ON CONFLICT DO UPDATE, INSERT OR REPLACE, ON DUPLICATE KEY UPDATE	🔄 Partial

PostgreSQL Style

-- Do nothing on conflict
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT DO NOTHING;

-- Update on conflict (specific columns)
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = 'Bob';

MySQL Style

-- Ignore on duplicate (equivalent to ON CONFLICT DO NOTHING)
INSERT IGNORE INTO users (id, name) VALUES (1, 'Alice');

-- Update on duplicate key
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE name = 'Bob';

SQLite Style

-- Ignore on conflict
INSERT OR IGNORE INTO users (id, name) VALUES (1, 'Alice');

-- Replace on conflict (delete + insert)
INSERT OR REPLACE INTO users (id, name) VALUES (1, 'Alice');

-- Abort on conflict (default behavior)
INSERT OR ABORT INTO users (id, name) VALUES (1, 'Alice');

-- Fail on conflict (fail but continue batch)
INSERT OR FAIL INTO users (id, name) VALUES (1, 'Alice');

Internal Representation

All dialect forms normalize to:

InsertStmt {
    on_conflict: Some(OnConflict {
        target: Option<ConflictTarget>,  // (id) or ON CONSTRAINT name
        action: ConflictAction,          // DoNothing, DoUpdate(...), DoReplace, etc.
    })
}

Parameterized Queries

SochDB supports two placeholder styles:

Positional Placeholders ($1, $2, ...)

SELECT * FROM users WHERE id = $1 AND name = $2

Question Mark Placeholders (?)

SELECT * FROM users WHERE id = ? AND name = ?

Question marks are automatically indexed (1, 2, 3...) during lexing.

Parameter Binding

use sochdb_client::ast_query::AstQueryExecutor;
use sochdb_core::soch::SochValue;

let executor = AstQueryExecutor::new(&conn);
let result = executor.execute_with_params(
    "SELECT * FROM users WHERE id = $1",
    &[SochValue::Int(42)],
)?;

Query Features

SELECT Clauses

Clause	Support	Notes
FROM with table aliases	✅ Full
WHERE with complex predicates	✅ Full	Three-valued NULL logic
GROUP BY	✅ Full	HashAggregate, preserves insertion order
HAVING	✅ Full	Filter on aggregate output
ORDER BY (ASC/DESC/NULLS FIRST/NULLS LAST)	✅ Full	nulls_first defaults to !asc
LIMIT / OFFSET	✅ Full	Must be integer literals
DISTINCT	❌ Not supported	No DistinctNode in the planner
UNION	🔄 Partial
INTERSECT / EXCEPT	📋 Planned

Aggregate Functions

Function	Support	Notes
COUNT (incl. COUNT(DISTINCT ...))	✅ Full
SUM, AVG (alias MEAN), MIN, MAX	✅ Full
MEDIAN	✅ Full	Via the sql/aggregate.rs path only
STDDEV (aliases STDDEV_SAMP, STDEV, SD)	✅ Full	Sample (n-1), Welford online variance; sql/aggregate.rs path only

note

There are two aggregate implementations. The volcano operator (executor/aggregate.rs) covers COUNT/SUM/AVG/MIN/MAX (and COUNT DISTINCT). MEDIAN and STDDEV are implemented only in the separate sql/aggregate.rs engine. STDDEV is the sample standard deviation (matching R's sd() and DuckDB's stddev). NULL inputs are skipped by all aggregates except COUNT(*).

Expressions

• Arithmetic: +, -, *, /, % (division by zero errors)
• Comparison: =, !=, <>, <, <=, >, >=
• Logical: AND, OR, NOT (short-circuit)
• String concatenation: ||
• Bitwise: &, |, ^, <<, >>
• IS NULL, IS NOT NULL
• IN (...), NOT IN (...)
• BETWEEN ... AND ... (and NOT BETWEEN)
• LIKE with wildcards (case-sensitive; % = zero-or-more, _ = exactly one)
• CASE WHEN ... THEN ... ELSE ... END
• COALESCE, NULLIF
• Function calls: COUNT(), SUM(), AVG(), etc.

CAST is a pass-through

CAST(expr AS type) parses and runs, but the executor currently passes the inner value through unchanged — there is no real type coercion yet. Do not rely on CAST to convert between SQL types at runtime.

Case-sensitive LIKE

LIKE is case-sensitive (SQL-92). All characters other than % and _ match literally, including regex metacharacters such as ., *, and [ — there is no regex engine behind LIKE, so 'file.txt' LIKE 'file.txt' matches but 'fileXtxt' does not.

SochDB Extensions

• VECTOR(dimensions) data type — e.g. VECTOR(768)
• EMBEDDING(dimensions) data type — e.g. EMBEDDING(1536)
• VECTOR_SEARCH(column, query_vector, k, metric) function, where metric is one of COSINE, EUCLIDEAN, or DOT_PRODUCT
• CONTEXT_WINDOW(tokens, priority_expr) for LLM context management

-- k-nearest-neighbor search over an embedding column
SELECT id, title
FROM documents
ORDER BY VECTOR_SEARCH(embedding, $1, 10, COSINE);

note

VECTOR_SEARCH is the SQL entry point for vector search and is routed to the vector index. The lower-level SimilarTo (column SIMILAR TO 'query text') predicate is a SochQL construct, not a raw SQL token. In scalar (row-by-row) expression evaluation, VECTOR_SEARCH, subqueries, EXISTS, JSON access, and graph traversal operators (->, <-, <->) are not evaluated and return errors; vector search must go through the index path.

JOINs

The volcano executor implements the full set of join types, and the SqlBridge handles TableRef::Join (emitting NULLs for unmatched outer rows).

Join type	Support	Operator
INNER JOIN	✅ Full	HashJoin (equi), NestedLoopJoin (theta)
LEFT JOIN	✅ Full	HashJoin
RIGHT JOIN	✅ Full	HashJoin
FULL [OUTER] JOIN	✅ Full	HashJoin
CROSS JOIN (and implicit comma-join)	✅ Full	NestedLoopJoin
NATURAL JOIN	⚠️ Approximate	Falls back to CROSS JOIN, not a true natural join

The planner routes ON a = b to a HashJoin, non-equi ON predicates to a NestedLoopJoin, and USING (col) to a HashJoin on col = col.

Internal matrix is stale

The engine's internal compatibility.rs feature matrix still reports multi-table JOINs as Partial/Planned. That matrix is out of date relative to the executor and bridge, which fully implement INNER/LEFT/RIGHT/FULL/CROSS joins. The table above reflects the actual code.

EXPLAIN

EXPLAIN is supported through the volcano path. The planner emits a textual plan tree (under a QUERY PLAN column) covering SeqScan, Filter (WHERE), HashAggregate, Filter (HAVING), Project, Sort, Limit, and JOIN nodes (INNER/LEFT/RIGHT/FULL/CROSS).

note

EXPLAIN is not served by the SqlBridge — the bridge returns NotImplemented for Statement::Explain. Route EXPLAIN queries through the volcano executor path.

Explicit Limitations

Feature	Status	Notes
DISTINCT	❌ Not supported	No DistinctNode in the planner
Window functions	📋 Planned
CTEs (WITH clause)	📋 Planned
Subqueries in WHERE / SELECT	📋 Planned	Scalar/correlated subqueries not evaluated
Subquery in FROM	🔄 Partial
UNION	🔄 Partial
INTERSECT / EXCEPT	📋 Planned
Real CAST coercion	❌ Not supported	CAST is a value pass-through
True NATURAL JOIN	⚠️ Approximate	Falls back to CROSS JOIN
Graph traversal operators (->, <-, <->)	❌ Not supported	Error in scalar evaluation
Stored procedures	❌	Out of scope
Triggers	❌	Out of scope

Complexity Analysis

Operation	Time Complexity	Notes
Lexing	O(n)	n = input length
Parsing	O(n)	n = token count
AST rewriting	O(|AST|)	Linear in AST size
SELECT (no index)	O(N)	N = table rows
SELECT (with index)	O(log N + K)	K = result rows
INSERT	O(log N)	B-Tree index update
INSERT (conflict check)	O(log N)	Uniqueness check
UPDATE (no index)	O(N)	Full scan
UPDATE (with index)	O(log N + K)	K = affected rows

Client Usage

Using the AST-Based Query Executor

The recommended way to execute SQL is via the AST-based query executor, which uses the real SQL parser (not string heuristics) and normalizes all supported dialects to one canonical AST.

use sochdb_client::connection::SochConnection;
use sochdb_client::ast_query::{AstQueryExecutor, QueryResult};
use sochdb_core::soch::SochValue;

// open_persistent() gives a durable, on-disk connection at the given path.
// (SochConnection::open() exists too, but it is ephemeral — it ignores the
// path and uses a temporary directory cleaned up on drop.)
let conn = SochConnection::open_persistent("./data")?;
let executor = AstQueryExecutor::new(&conn);

// SELECT returns QueryResult::Select(Vec<HashMap<String, SochValue>>)
match executor.execute("SELECT * FROM users WHERE active = true")? {
    QueryResult::Select(rows) => {
        for row in rows {
            println!("{:?}", row);
        }
    }
    _ => {}
}

// Execute with parameters ($1, $2, ... or ?)
let result = executor.execute_with_params(
    "INSERT INTO users (id, name) VALUES ($1, $2)",
    &[SochValue::Int(1), SochValue::Text("Alice".to_string())],
)?;

// INSERT/UPDATE/DELETE return QueryResult::Insert/Update/Delete with affected-row info
let result = executor.execute("DELETE FROM users WHERE id = 1")?;

QueryResult variants: Select, Insert, Update, Delete, CreateTable, DropTable, CreateIndex, and Empty (for BEGIN/COMMIT/SET, etc.).

Dialect Support

The AST-based executor automatically normalizes dialect-specific syntax:

// All of these normalize to the same canonical AST:

// MySQL style
executor.execute("INSERT IGNORE INTO users VALUES (1, 'Alice')")?;

// PostgreSQL style
executor.execute("INSERT INTO users VALUES (1, 'Alice') ON CONFLICT DO NOTHING")?;

// SQLite style
executor.execute("INSERT OR IGNORE INTO users VALUES (1, 'Alice')")?;

Files

• sochdb-query/src/sql/compatibility.rs - Internal feature matrix and dialect detection (note: its JOIN entries are stale)
• sochdb-query/src/sql/token.rs - Token types including dialect keywords
• sochdb-query/src/sql/lexer.rs - Tokenizer with placeholder indexing
• sochdb-query/src/sql/ast.rs - Canonical AST definitions (incl. VECTOR/EMBEDDING types, VECTOR_SEARCH)
• sochdb-query/src/sql/parser.rs - Recursive descent parser
• sochdb-query/src/sql/bridge.rs - Storage-backed dispatcher (fullest statement coverage)
• sochdb-query/src/sql/aggregate.rs - Aggregate engine with MEDIAN/STDDEV
• sochdb-query/src/executor/ - Volcano operator engine (SELECT planning, JOINs, EXPLAIN)
• sochdb-query/src/sql/error.rs - Error types
• sochdb-query/src/sql/mod.rs - Module exports + in-memory reference SqlExecutor
• sochdb-client/src/ast_query.rs - AST-based client query executor