Statements
Select
The Select clause maps the current binding stream through a constructor: an expression, an object literal, a list of named expressions, * to spread bound variables, or . to wrap each binding under its alias.
select <constructor>
[from <source> [, <source> …]]
[where <expr>]
[order by <expr> [asc|desc] [, …]]
[limit <n> | limit <n>.. | limit <n>..<m>];
Envelope Object
Select . emits the binding tuple as an envelope object.
SQL
create table T;
insert into T ({x: 1});
select . from T as t;
Result
[
{ "t": { "x": 1 } }
]
Bindings Flat
Select * spreads bindings flat.
SQL
create table T;
insert into T ({x: 1, y: 2});
select * from T as t;
Result
[
{ "x": 1, "y": 2 }
]
Per Row
Select
SQL
create table T;
insert into T ({x: 1}, {x: 2});
select t.x from T as t order by t.x;
Result
[
1,
2
]
Per Row
Select
SQL
create table T;
insert into T ({x: 1}, {x: 2});
select 7 from T as t order by t.x;
Result
[
7,
7
]
Per Row
Select
SQL
create table T;
insert into T ({x: 1});
"select {a: t.x} from T as t;"
Result
[
{ "a": 1 }
]
Named Field
Select
SQL
create table T;
insert into T ({x: 10});
select t.x as a from T as t;
Result
[
{ "a": 10 }
]
Named Member
A list of items emits an object with each named member.
SQL
create table T;
insert into T ({x: 1, y: 2});
select t.x as a, t.y as b from T as t;
Result
[
{ "a": 1, "b": 2 }
]
List Items
List items may be arbitrary expressions, not only paths.
SQL
create table T;
insert into T ({x: 1});
select 1 as a, 'hi' as b from T as t;
Result
[
{ "a": 1, "b": "hi" }
]
From
From
SQL
create table T;
insert into T ({x: 1});
select T.x from T;
Result
[
1
]
From
From
SQL
create table T;
insert into T ({x: 7});
select t.x from T as t;
Result
[
7
]
From
From
SQL
create table T;
insert into T ({x: 9});
select t.x from T t;
Result
[
9
]
An Array
An array literal builds an array from its element expressions.
SQL
create table T;
insert into T ({x: 7});
select [1, 2, t.x] as a from T as t;
Result
[
{ "a": [ 1, 2, 7 ] }
]
Array Literals
Array literals may nest.
SQL
create table T;
insert into T ({x: 1});
select [[1, 2], [3]] as a from T as t;
Result
[
{ "a": [ [ 1, 2 ], [ 3 ] ] }
]
Single Row
Select
SQL
create table T;
select 1;
Result
[
1
]
Nothing Spread
Select * requires a From clause (nothing to spread).
SQL
create table T;
select *;
Expected error: static
Tuple Envelope
Select . requires a From clause (no binding tuple to envelope).
SQL
create table T;
select .;
Expected error: static
From
The From clause iterates sources — table scans, array literals, and lateral unnest paths — binding each row under an alias for use in later clauses.
Scanning An
Scanning an empty table yields no rows.
SQL
create table T;
select * from T;
Result
[]
Insertion Order
A table scan returns all rows in insertion order.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
select * from T;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Omitting As
Omitting as uses the table name as the alias.
SQL
create table T;
insert into T ({x: 42});
select T.x from T;
Result
[
42
]
An Explicit
An explicit as alias names the binding.
SQL
create table T;
insert into T ({x: 10});
select t.x from T as t;
Result
[
10
]
Row Unwrapped
Select * over one source emits the row unwrapped.
SQL
create table T;
insert into T ({x: 1, y: 2});
select * from T as t;
Result
[
{ "x": 1, "y": 2 }
]
Its Alias
Select . wraps the binding under its alias.
SQL
create table T;
insert into T ({x: 1});
select . from T as t;
Result
[
{ "t": { "x": 1 } }
]
Two Comma
Two comma sources form a Cartesian product, merged by select *.
SQL
create table T;
create table S;
insert into S ({b: 10}, {b: 20});
insert into T ({a: 1}, {a: 2});
select * from T as t, S as s;
Result
[
{ "a": 1, "b": 10 },
{ "a": 1, "b": 20 },
{ "a": 2, "b": 10 },
{ "a": 2, "b": 20 }
]
From Cross Projection
A projection list may reference both cross-joined bindings.
SQL
create table T;
create table S;
insert into S ({b: 10}, {b: 20});
insert into T ({a: 1}, {a: 2});
select t.a as a, s.b as b from T as t, S as s;
Result
[
{ "a": 1, "b": 10 },
{ "a": 1, "b": 20 },
{ "a": 2, "b": 10 },
{ "a": 2, "b": 20 }
]
Dot Envelope
Select . over two sources keys each binding by its alias.
SQL
create table T;
create table S;
insert into S ({b: 10});
insert into T ({a: 1});
select . from T as t, S as s;
Result
[
{ "t": { "a": 1 }, "s": { "b": 10 } }
]
Both Bindings
A where predicate filters the product across both bindings.
SQL
create table T;
create table S;
insert into S ({b: 10}, {b: 20});
insert into T ({a: 1}, {a: 2});
select t.a as a, s.b as b from T as t, S as s where t.a = 1;
Result
[
{ "a": 1, "b": 10 },
{ "a": 1, "b": 20 }
]
An Empty
An empty inner source makes the whole product empty.
SQL
create table T;
create table S;
insert into T ({a: 1}, {a: 2});
select * from T as t, S as s;
Result
[]
Referencing An
Referencing an undeclared table is a static error.
SQL
create table T;
select * from Ghost;
Expected error: static
Referencing An
Referencing an alias not in scope is a static error.
SQL
create table T;
select x.foo from T;
Expected error: static
Earlier Binding
A later source may unnest a collection path on an earlier binding.
SQL
create table T;
insert into T ({items: [1, 2, 3]});
select t.items as items, item as item from T as t, t.items as item;
Result
[
{ "items": [ 1, 2, 3 ], "item": 1 },
{ "items": [ 1, 2, 3 ], "item": 2 },
{ "items": [ 1, 2, 3 ], "item": 3 }
]
Star Scalar
Select * keeps a non-object (scalar) lateral binding under its alias.
SQL
create table T;
insert into T ({items: [1, 2, 3]});
select * from T as t, t.items as item;
Result
[
{ "items": [ 1, 2, 3 ], "item": 1 },
{ "items": [ 1, 2, 3 ], "item": 2 },
{ "items": [ 1, 2, 3 ], "item": 3 }
]
The Unnested
The unnested element binds under its alias in the envelope.
SQL
create table T;
insert into T ({items: [1, 2]});
select . from T as t, t.items as item;
Result
[
{ "t": { "items": [ 1, 2 ] }, "item": 1 },
{ "t": { "items": [ 1, 2 ] }, "item": 2 }
]
Unnest Flattens
Unnest flattens across every outer row in order.
SQL
create table T;
insert into T ({k: 1, items: [10, 11]}, {k: 2, items: [20]});
select t.k as k, item as v from T as t, t.items as item;
Result
[
{ "k": 1, "v": 10 },
{ "k": 1, "v": 11 },
{ "k": 2, "v": 20 }
]
An Empty
An empty collection contributes no rows for that outer binding.
SQL
create table T;
insert into T ({items: []});
select item as v from T as t, t.items as item;
Result
[]
Missing Path
A missing path is treated as empty (inner-join-like).
SQL
create table T;
insert into T ({x: 1});
select item as v from T as t, t.items as item;
Result
[]
Non Array
A non-array source value contributes no rows.
SQL
create table T;
insert into T ({items: 5});
select item as v from T as t, t.items as item;
Result
[]
An Array
An array literal may serve as a From source, scanned element-wise.
SQL
create table T;
select x from [1, 2, 3] as x;
Result
[
1,
2,
3
]
Self Reference
A lateral source may not reference its own alias.
SQL
create table T;
select * from T as t, item.x as item;
Expected error: static
Requires Alias
A lateral collection source requires an alias.
SQL
create table T;
select * from T as t, t.items;
Expected error: static
An Empty
An empty array literal source yields no rows.
SQL
create table T;
select x from [] as x;
Result
[]
Its Alias
Select . wraps each scanned element under its alias.
SQL
create table T;
select . from [1, 2] as x;
Result
[
{ "x": 1 },
{ "x": 2 }
]
Into Them
A value source iterates object elements and may path into them.
SQL
create table T;
select x.a as a from [{a: 1}, {a: 2}] as x;
Result
[
{ "a": 1 },
{ "a": 2 }
]
Non Array
A non-array value source contributes no rows.
SQL
create table T;
select x from 5 as x;
Result
[]
Table Row
A value source re-iterates for every outer table row.
SQL
create table T;
insert into T ({a: 1}, {a: 2});
select . from T as t, [10, 20] as n;
Result
[
{ "t": { "a": 1 }, "n": 10 },
{ "t": { "a": 1 }, "n": 20 },
{ "t": { "a": 2 }, "n": 10 },
{ "t": { "a": 2 }, "n": 20 }
]
Unnest An
Unnest an array of objects and path into each element.
SQL
create table T;
insert into T ({s: [{x: 1}, {x: 2}]}, {s: [{x: 3}]});
select s.x as x from T as t, t.s as s;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Where
The Where clause filters binding tuples by a boolean predicate. Only rows for which the predicate is true pass through.
Constant True
Constant true keeps all rows.
SQL
create table T;
insert into T ({x: 1}, {x: 2});
select * from T where true;
Result
[
{ "x": 1 },
{ "x": 2 }
]
Constant False
Constant false drops all rows.
SQL
create table T;
insert into T ({x: 1});
select * from T where false;
Result
[]
Null Predicate
Null predicate is not-true and drops all rows.
SQL
create table T;
insert into T ({x: 1});
select * from T where null;
Result
[]
Numeric Greater-than
Numeric greater-than filters by oid insertion order.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
select * from T where T.x > 1;
Result
[
{ "x": 2 },
{ "x": 3 }
]
Numeric Equality
Numeric equality matches a single row.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
select * from T where T.x = 2;
Result
[
{ "x": 2 }
]
Numeric Inequality
Numeric inequality excludes matching value.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
select * from T where T.x != 1;
Result
[
{ "x": 2 },
{ "x": 3 }
]
String Equality
String equality in where.
SQL
create table T;
create table S;
insert into S ({name: 'alice'}, {name: 'bob'});
select * from S where S.name = 'bob';
Result
[
{ "name": "bob" }
]
Boolean Equality
Boolean equality in where.
SQL
create table T;
insert into T ({flag: true}, {flag: false});
select * from T where T.flag = true;
Result
[
{ "flag": true }
]
Predicate May
Predicate may use an explicit from alias.
SQL
create table T;
insert into T ({x: 0}, {x: 1});
select * from T as t where t.x > 0;
Result
[
{ "x": 1 }
]
Null Member
Null member compares equal to null.
SQL
create table T;
insert into T ({x: null});
select * from T where T.x = null;
Result
[
{ "x": null }
]
Null Member
Null member fails inequality against non-null.
SQL
create table T;
insert into T ({x: null});
select * from T where T.x != 1;
Result
[]
Absent Field
Absent field reads as null and matches null.
SQL
create table T;
insert into T ({});
select * from T where T.x = null;
Result
[
{ }
]
Absent Field
Absent field reads as null and fails ordering comparison.
SQL
create table T;
insert into T ({});
select * from T where T.x > 0;
Result
[]
Only Rows
Only rows with present matching field pass equality.
SQL
create table T;
insert into T ({x: 1}, {});
select * from T where T.x = 1;
Result
[
{ "x": 1 }
]
Scalar Projection
Scalar projection with where (moved from from-clause suite).
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
select T.x from T where T.x > 1;
Result
[
2,
3
]
Order by
The Order by clause sorts the binding-tuple stream by one or more keys. Ascending is the default; nulls sort last in ascending order and first in descending order.
Order By
Order by sorts ascending by default.
SQL
create table T;
insert into T ({x: 3}, {x: 1}, {x: 2});
select t.x from T as t order by t.x;
Result
[
1,
2,
3
]
Explicit Asc
Explicit asc matches the default.
SQL
create table T;
insert into T ({x: 3}, {x: 1}, {x: 2});
select t.x from T as t order by t.x asc;
Result
[
1,
2,
3
]
Desc Sorts Descending
Desc sorts descending.
SQL
create table T;
insert into T ({x: 3}, {x: 1}, {x: 2});
select t.x from T as t order by t.x desc;
Result
[
3,
2,
1
]
Order By
Order by reorders whole rows under select *.
SQL
create table T;
insert into T ({x: 3}, {x: 1}, {x: 2});
select * from T as t order by t.x;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Multiple Keys
Multiple keys sort left-to-right with per-key direction.
SQL
create table T;
insert into T ({a: 1, b: 2}, {a: 1, b: 1}, {a: 2, b: 5});
select * from T as t order by t.a, t.b desc;
Result
[
{ "a": 1, "b": 2 },
{ "a": 1, "b": 1 },
{ "a": 2, "b": 5 }
]
Null Sorts
Null sorts after all values in ascending order.
SQL
create table T;
insert into T ({x: 2}, {x: null}, {x: 1});
select t.x from T as t order by t.x;
Result
[
1,
2,
null
]
Null Sorts
Null sorts before all values in descending order.
SQL
create table T;
insert into T ({x: 2}, {x: null}, {x: 1});
select t.x from T as t order by t.x desc;
Result
[
null,
2,
1
]
Ints And
Ints and floats interleave by numeric value.
SQL
create table T;
insert into T ({x: 2}, {x: 1.5}, {x: 1});
select t.x from T as t order by t.x;
Result
[
1,
1.5,
2
]
Strings Sort Lexicographically
Strings sort lexicographically.
SQL
create table T;
insert into T ({s: "banana"}, {s: "apple"}, {s: "cherry"});
select t.s from T as t order by t.s;
Result
[
"apple",
"banana",
"cherry"
]
Order By
Order by then limit yields the top N.
SQL
create table T;
insert into T ({x: 3}, {x: 1}, {x: 2}, {x: 5}, {x: 4});
select t.x from T as t order by t.x desc limit 2;
Result
[
5,
4
]
Order Sorts
Order sorts the post-where stream.
SQL
create table T;
insert into T ({x: 3}, {x: 1}, {x: 2}, {x: 4});
select t.x from T as t where t.x > 1 order by t.x;
Result
[
2,
3,
4
]
Order By
Order by sorts the cross product of two sources.
SQL
create table T;
create table S;
insert into T ({x: 2}, {x: 1});
insert into S ({y: 9});
select * from T as t, S as s order by t.x;
Result
[
{ "x": 1, "y": 9 },
{ "x": 2, "y": 9 }
]
Limit
The Limit clause slices the stream by row position: limit n takes the first n rows, limit n.. skips the first n, and limit n..m selects the half-open index range [n, m).
Limit N
Limit N takes the first N rows in scan order.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3}, {x: 4}, {x: 5});
select * from T limit 2;
Result
[
{ "x": 1 },
{ "x": 2 }
]
Limit 0
Limit 0 emits no rows.
SQL
create table T;
insert into T ({x: 1}, {x: 2});
select * from T limit 0;
Result
[]
Limit Greater
Limit greater than the row count returns all rows.
SQL
create table T;
insert into T ({x: 1}, {x: 2});
select * from T limit 10;
Result
[
{ "x": 1 },
{ "x": 2 }
]
Limit N..
Limit N.. skips the first N rows and keeps the rest.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3}, {x: 4}, {x: 5});
select * from T limit 2..;
Result
[
{ "x": 3 },
{ "x": 4 },
{ "x": 5 }
]
Skipping Past
Skipping past the end yields no rows.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
select * from T limit 5..;
Result
[]
Limit 0..
Limit 0.. skips nothing and returns all rows.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
select * from T limit 0..;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Limit N..M
Limit N..M is half-open over indices [N, M).
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3}, {x: 4}, {x: 5});
select * from T limit 1..3;
Result
[
{ "x": 2 },
{ "x": 3 }
]
Limit Slice Empty
A slice with M == N emits nothing.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3}, {x: 4}, {x: 5});
select * from T limit 3..3;
Result
[]
Last Row
A slice whose end runs past the data takes through the last row.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3}, {x: 4});
select * from T limit 1..10;
Result
[
{ "x": 2 },
{ "x": 3 },
{ "x": 4 }
]
Limit Slices
Limit slices the post-where stream, not the raw scan.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3}, {x: 4});
select * from T where T.x > 1 limit 2;
Result
[
{ "x": 2 },
{ "x": 3 }
]
Limit Applies
Limit applies to a scalar projection.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
select T.x from T limit 2;
Result
[
1,
2
]
Insert
Insert adds one or more values to a table. The values list is parenthesised and comma-separated; a trailing comma is permitted.
insert into <table> (<value>, …);
One Object
One object in the values list produces one row.
SQL
create table T;
insert into T ({x: 1});
select * from T;
Result
[
{ "x": 1 }
]
Two Objects
Two objects in one statement produce two rows.
SQL
create table T;
insert into T ({x: 1}, {x: 2});
select * from T;
Result
[
{ "x": 1 },
{ "x": 2 }
]
Five Objects
Five objects in one statement are all persisted.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3}, {x: 4}, {x: 5});
select * from T;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 },
{ "x": 4 },
{ "x": 5 }
]
One Multi-value
One multi-value insert persists all rows.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
select * from T;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Three Single-value
Three single-value inserts produce the same table as one multi-value insert.
SQL
create table T;
insert into T ({x: 1});
insert into T ({x: 2});
insert into T ({x: 3});
select * from T;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Values In
Values in one insert may differ in shape.
SQL
create table T;
insert into T ({x: 1, y: 2}, {x: 3});
select * from T;
Result
[
{ "x": 1, "y": 2 },
{ "x": 3 }
]
Object Rejected
A stored row must be an object; a scalar value is rejected.
SQL
create table T;
insert into T (1, 2, 3);
Expected error: schema
Values List
Values list may span multiple lines.
SQL
create table T;
insert into T (
{x: 1},
{x: 2},
{x: 3}
);
select * from T;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Inserting Into
Inserting into an undeclared table is a static error.
SQL
create table T;
insert into Ghost ({x: 1});
Expected error: static
Empty Values
Empty values list is a no-op.
SQL
create table T;
insert into T ();
select * from T;
Result
[]
An Array
An array value as a row is rejected with a schema error (not a panic).
SQL
create table T;
insert into T ([1, 2, 3]);
Expected error: schema
Delete
Delete removes rows from a table. Without a Where clause, every row is removed.
delete from <table> [as <alias>] [where <expr>];
Empties Table
Delete with no where removes every row.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
delete from T;
select * from T;
Result
[]
Matching Rows
Delete with a predicate removes only matching rows.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
delete from T where T.x > 1;
select * from T;
Result
[
{ "x": 1 }
]
An Explicit
An explicit as alias binds the predicate's references.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
delete from T as r where r.x = 2;
select * from T;
Result
[
{ "x": 1 },
{ "x": 3 }
]
Table Unchanged
A predicate that matches nothing leaves the table unchanged.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
delete from T where T.x > 100;
select * from T;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Deleting From
Deleting from an empty table succeeds and yields nothing.
SQL
create table T;
delete from T;
select * from T;
Result
[]
Reuses Table
A table is reusable after a delete-all.
SQL
create table T;
insert into T ({x: 1}, {x: 2});
delete from T;
insert into T ({x: 9});
select * from T;
Result
[
{ "x": 9 }
]
Deleting From
Deleting from an undeclared table is a static error.
SQL
create table T;
delete from Ghost;
Expected error: static
Create Table
Create Table declares a table with an optional list of key columns. Key columns must be int or string and define the physical sort order; keyless tables keep surrogate ids and return rows in insertion order.
create table <name> [(<key> int|string, …)];
Whole Objects
A keyless table stores and returns whole objects.
SQL
create table t;
insert into t ({x: 1, y: 2, z: 3});
select * from t;
Result
[
{ "x": 1, "y": 2, "z": 3 }
]
Ones X
A keyless table accepts any object, including ones with no x.
SQL
create table t;
insert into t ({y: 2, z: 3});
select * from t;
Result
[
{ "y": 2, "z": 3 }
]
Surrogate Ids
Surrogate ids increment, so rows come back in insertion order.
SQL
create table t;
insert into t ({x: 3}, {x: 1}, {x: 2});
select * from t;
Result
[
{ "x": 3 },
{ "x": 1 },
{ "x": 2 }
]
Int Key
Int key with payload round-trips whole object.
SQL
create table t (x int);
insert into t ({x: 1, z: 9});
select * from t;
Result
[
{ "x": 1, "z": 9 }
]
Rows Inserted
Rows inserted out of order come back sorted by the int key.
SQL
create table t (x int);
insert into t ({x: 3}, {x: 1}, {x: 2});
select * from t;
Result
[
{ "x": 1 },
{ "x": 2 },
{ "x": 3 }
]
Negative Ints
Negative ints sort before zero and positives (sign-flip encoding).
SQL
create table t (x int);
insert into t ({x: 1}, {x: -5}, {x: 0}, {x: -1});
select * from t;
Result
[
{ "x": -5 },
{ "x": -1 },
{ "x": 0 },
{ "x": 1 }
]
Re-inserting The
Re-inserting the same key overwrites (last write wins).
SQL
create table t (x int);
insert into t ({x: 1, v: 100});
insert into t ({x: 1, v: 200});
select * from t;
Result
[
{ "x": 1, "v": 200 }
]
Inserting Without
Inserting without the key field is a schema error.
SQL
create table t (x int);
insert into t ({z: 9});
Expected error: schema
Wrong Type
A string where an int key is declared is a schema error.
SQL
create table t (x int);
insert into t ({x: "a"});
Expected error: schema
Non Integral
A non-integral number for an int key is a schema error.
SQL
create table t (x int);
insert into t ({x: 1.5});
Expected error: schema
String Key
String key with payload round-trips whole object.
SQL
create table t (x string);
insert into t ({x: "a", z: 9});
select * from t;
Result
[
{ "x": "a", "z": 9 }
]
Rows Come
Rows come back in lexicographic key order.
SQL
create table t (x string);
insert into t ({x: "c"}, {x: "a"}, {x: "b"});
select * from t;
Result
[
{ "x": "a" },
{ "x": "b" },
{ "x": "c" }
]
Inserting Without
Inserting without the key field is a schema error.
SQL
create table t (x string);
insert into t ({z: 9});
Expected error: schema
Wrong Type
A number where a string key is declared is a schema error.
SQL
create table t (x string);
insert into t ({x: 1});
Expected error: schema
Composite (int,
Composite (int, string) key round-trips whole object.
SQL
create table t (a int, b string);
insert into t ({a: 1, b: "x", z: 9});
select * from t;
Result
[
{ "a": 1, "b": "x", "z": 9 }
]
Sort By
Sort by first component, tie-break on the second.
SQL
create table t (a int, b string);
insert into t ({a: 2, b: "a"}, {a: 1, b: "y"}, {a: 1, b: "x"});
select * from t;
Result
[
{ "a": 1, "b": "x" },
{ "a": 1, "b": "y" },
{ "a": 2, "b": "a" }
]
Missing The
Missing the first key field is a schema error.
SQL
create table t (a int, b string);
insert into t ({b: "x"});
Expected error: schema
Missing The
Missing the second key field is a schema error.
SQL
create table t (a int, b string);
insert into t ({a: 1});
Expected error: schema
Wrong Type
Wrong type for the first key field is a schema error.
SQL
create table t (a int, b string);
insert into t ({a: "q", b: "x"});
Expected error: schema
Wrong Type
Wrong type for the second key field is a schema error.
SQL
create table t (a int, b string);
insert into t ({a: 1, b: 2});
Expected error: schema
Composite (string,
Composite (string, int) key round-trips whole object.
SQL
create table t (a string, b int);
insert into t ({a: "x", b: 1, z: 9});
select * from t;
Result
[
{ "a": "x", "b": 1, "z": 9 }
]
Sort By
Sort by string first, tie-break on the int.
SQL
create table t (a string, b int);
insert into t ({a: "b", b: 1}, {a: "a", b: 2}, {a: "a", b: 1});
select * from t;
Result
[
{ "a": "a", "b": 1 },
{ "a": "a", "b": 2 },
{ "a": "b", "b": 1 }
]
Missing The
Missing the int component is a schema error.
SQL
create table t (a string, b int);
insert into t ({a: "x"});
Expected error: schema
Type Second
A string where the int component is declared is a schema error.
SQL
create table t (a string, b int);
insert into t ({a: "x", b: "y"});
Expected error: schema
Composite (int,
Composite (int, int) key round-trips whole object.
SQL
create table t (a int, b int);
insert into t ({a: 1, b: 2, z: 9});
select * from t;
Result
[
{ "a": 1, "b": 2, "z": 9 }
]
Sort By
Sort by first int, tie-break on the second int.
SQL
create table t (a int, b int);
insert into t ({a: 2, b: 1}, {a: 1, b: 2}, {a: 1, b: 1});
select * from t;
Result
[
{ "a": 1, "b": 1 },
{ "a": 1, "b": 2 },
{ "a": 2, "b": 1 }
]
Missing A
Missing a key component is a schema error.
SQL
create table t (a int, b int);
insert into t ({a: 1});
Expected error: schema
Composite (string,
Composite (string, string) key round-trips whole object.
SQL
create table t (a string, b string);
insert into t ({a: "x", b: "y", z: 9});
select * from t;
Result
[
{ "a": "x", "b": "y", "z": 9 }
]
Sort By
Sort by first string, tie-break on the second.
SQL
create table t (a string, b string);
insert into t ({a: "b", b: "a"}, {a: "a", b: "b"}, {a: "a", b: "a"});
select * from t;
Result
[
{ "a": "a", "b": "a" },
{ "a": "a", "b": "b" },
{ "a": "b", "b": "a" }
]
Before Ab
A shorter first component sorts before a longer one that shares its prefix, regardless of the second component — proves the string terminator. ("a","z") must sort before ("ab","a").
SQL
create table t (a string, b string);
insert into t ({a: "ab", b: "a"}, {a: "a", b: "z"});
select * from t;
Result
[
{ "a": "a", "b": "z" },
{ "a": "ab", "b": "a" }
]
Missing A
Missing a key component is a schema error.
SQL
create table t (a string, b string);
insert into t ({a: "x"});
Expected error: schema
Float Key
A float key column is rejected at create.
SQL
create table t (x float);
Expected error: static
Bool Key
A bool key column is rejected at create.
SQL
create table t (x bool);
Expected error: static
Drop Table
Drop Table removes a table and all its contents from the catalog.
drop table <name>;
After Drop,
After drop, the table can no longer be selected from.
SQL
create table T;
insert into T ({x: 1});
drop table T;
select * from T;
Expected error: static
Is Empty
A table re-created after drop is fresh and empty.
SQL
create table T;
insert into T ({x: 1}, {x: 2});
drop table T;
create table T;
select * from T;
Result
[]
Dropping An
Dropping an undeclared table is a static error.
SQL
drop table Ghost;
Expected error: static
Clear
Clear removes every row from a table but keeps the table definition.
clear table <name>;
Table Place
Clear removes every row but leaves the table in place.
SQL
create table T;
insert into T ({x: 1}, {x: 2}, {x: 3});
clear table T;
select * from T;
Result
[]
New Rows
A cleared table is still resolvable and accepts new rows.
SQL
create table T;
insert into T ({x: 1});
clear table T;
insert into T ({x: 9});
select * from T;
Result
[
{ "x": 9 }
]
Clearing An
Clearing an undeclared table is a static error.
SQL
clear table Ghost;
Expected error: static