SQL language overview

KDBS speaks a small SQL dialect built for transaction processing. Every statement it accepts runs in a predictable way: the plan is fixed when the statement is parsed, and joins run in the order you write them. Forms that would make execution unpredictable are refused with a clear message, not accepted and handled differently.

Statements

Category	Statements
Schema (DDL)	CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE NAMESPACE, CREATE INDEX, CREATE ASSERTION, CREATE CABIN, and the matching DROP statements
Data (DML)	INSERT, UPDATE, DELETE, PURGE
Queries	SELECT, ANALYZE
Transactions	BEGIN, COMMIT, ROLLBACK, SET ISOLATION LEVEL, SET DURABILITY
Introspection	SHOW, DESCRIBE
Session	PING, SYNC, STOP

Lexical rules

• Keywords and identifiers are case-insensitive.
• A trailing ; is accepted and ignored. Send one statement at a time.
• String literals use single quotes: 'Kim Minjun'. There is no escape for a quote inside a string.
• A bare number with a fraction, such as 12.34, means the same as the string '12.34'. A decimal column accepts it; an integer column refuses it.
• No type is converted implicitly. A literal must be written in the form of the column it is compared with: baggage_kg = 20, not baggage_kg = '20'; name = '5', not name = 5. A mismatch is refused when the statement is compiled. See Data types.
• Only eleven words are reserved: JOIN, ON, AS, IN, EXISTS, NOT, BETWEEN, LEFT, RIGHT, FULL, OUTER. Every other word, including SELECT, COUNT and CHECK, can be used as a column or table name.

Names

A table can be written as <namespace>.<table>. Table names are unique across the whole instance, so a namespace qualifier never changes which table a name refers to: at CREATE TABLE it chooses the namespace, and everywhere else it is checked. See CREATE NAMESPACE.

The catalog is readable as views in the sys namespace, for example SELECT * FROM sys.tables. See SHOW and DESCRIBE.

Refusals

When KDBS does not accept a statement, the error says why and, where it can, gives the byte position of the part it refused. The error also says which kind of refusal it is:

Error	Meaning	What to do
InvalidArgument	The statement is wrong, for example an unknown column.	Fix the statement.
NOT_IMPLEMENTED	The form fits the design but this release has not built it, for example HAVING or outer joins.	Rewrite it, or check a later release.
UNSUPPORTED	The engine’s design rules the form out, for example updating a primary key or SAVEPOINT.	Rewrite the statement.

Only TXN_CONFLICT is retryable. Every other error fails the same way if you send the same statement again.

kdbs> SELECT * FROM passengers AS p LEFT JOIN tickets AS t ON t.passenger_id = p.id
                                    ^
ERR NOT_IMPLEMENTED retryable=0 outer joins are not supported: 'LEFT' at byte 30; only inner equi-joins (JOIN ... ON) are available

What the language leaves out

These are deliberate. Each is refused by name:

• Common table expressions (WITH) and subqueries in FROM. Subqueries are allowed in WHERE.
• Outer joins, HAVING, and ORDER BY over grouped results.
• Window functions, and expressions in a select list or WHERE. Expressions are allowed in UPDATE ... SET.
• OR and NOT over conditions in WHERE. Conditions combine with AND.
• Savepoints and chained transactions. A transaction commits or rolls back whole.
• Dialect aliases such as SERIAL, AUTO_INCREMENT and IF NOT EXISTS. Every primary key is already issued automatically when INSERT leaves it out.