MTS SQL

Enter SQL mode

SHOW SESSION;
SET mts.dialect = 'sql';
SHOW SESSION;

Namespaces, tables, and DML

CREATE DATABASE IF NOT EXISTS app;
CREATE SCHEMA IF NOT EXISTS app;
USE app;

CREATE TABLE app.docs (
  tenant text,
  id text,
  category text,
  title text,
  body text,
  doc document,
  embedding vector<float, 2>,
  PRIMARY KEY (tenant, id),
  PARTITION BY (tenant)
);

INSERT INTO app.docs (tenant, id, category, title, body, doc, embedding)
VALUES (
  'tenant-alpha',
  'd1',
  'returns',
  'refund policy alpha',
  'refund policy alpha',
  fromJson('{"category":"returns","priority":"high"}'),
  [0.0, 0.0]
);

SELECT tenant, id, title
FROM app.docs
WHERE tenant = 'tenant-alpha' AND id = 'd1';

UPDATE app.docs
SET category = 'support'
WHERE tenant = 'tenant-alpha' AND id = 'd1';

DELETE FROM app.docs
WHERE tenant = 'tenant-alpha' AND id = 'd1';

SQL prepared text

SELECT tenant, id, title
FROM app.docs
WHERE tenant = $1 AND id = $2;

Every $n placeholder must be bound exactly once. Prepared handles retain the dialect used at prepare time.

SQL access-path DDL

CREATE ACCESS PATH docs_tenant_category
ON app.docs (tenant, category)
TYPE scalar_ordered_composite;

CREATE ACCESS PATH docs_title_trigram
ON app.docs
USING TRIGRAM_TEXT_SIS(title)
WITH (
  case_mode = 'ascii_insensitive_v1',
  normalization_contract = 'raw_utf8_bytes_v1',
  regex_dialect = 'mts_regex_v1'
);

CREATE ACCESS PATH docs_body_fulltext
ON app.docs (body)
TYPE fulltext;

CREATE ACCESS PATH docs_embedding_vector
ON app.docs (embedding)
TYPE vector_hnsw;

WAIT FOR ACCESS PATH docs_tenant_category QUERYABLE TIMEOUT '60 seconds';

Some descriptor families can be descriptor-only in a preview profile. Treat descriptor-only output as valid preflight evidence, then rely on the executable descriptor form reported by EXPLAIN/ADVISE.

SQL joins

EXPLAIN (FORMAT JSON) ADMIT
SELECT o.order_id, o.status, c.email
FROM app.orders o
JOIN app.customers c
  ON c.tenant = o.tenant
 AND c.customer_id = o.customer_id
WHERE o.tenant = 't1'
  AND o.order_id = 'o1';

A join executes on the OLTP path only when each side has a bounded driver and intermediate work is bounded.

SQL write options

INSERT INTO app.docs (tenant, id, category, title, body)
VALUES ('tenant-alpha', 'd2', 'returns', 'strong write', 'write body')
WITH (durability = strong, timeout = '5 seconds');