Open Q Language

qSQL

The query sublanguage: select/exec/update/delete, joins, functional forms

Required One chapter of the Open Q Language specification. README.txt states what must be implemented, how conformance is defined, and how rule IDs work.

qSQL is q's table query surface: select / exec / update / delete templates over tables, plus insert/upsert, the join family, and functional equivalents. It is a q-only surface — the k dialect does not parse it. This chapter assumes core.txt and the q layer of q.txt.

Evaluation is eager: a query produces a concrete table/dict/vector immediately. Column and predicate expressions evaluate in the TABLE'S COLUMN SCOPE — a bare column name refers to that column's vector, not a global.

QSQL-00011. The five templates and their shape

select [ distinct ] [phrase] [by phrase] from expr [where preds]
exec   [ distinct ] [phrase] [by phrase] from expr [where preds]
update            [phrase] [by phrase] from expr [where preds]
delete            [phrase]            from expr [where preds]

All four are the same shape, differing only in the operation:

op        select | exec | update | delete
distinct  flag  (select/exec only)
select    list of (optional-name, expr)   -- the leading phrase
by        list of (optional-name, expr)   -- the group-by phrase
from      expr                            -- the source table
where     list of expr                    -- the where predicates
limit     optional expr                   -- select[n] row-limit form

select/exec/update/delete are CONTEXTUAL keywords: query starters only in statement or argument position; elsewhere they are plain names. by / from / where terminate query phrases. from is mandatory in every query.

Roles by op:

select   build a new TABLE of the phrase columns
exec     like select but yield a VECTOR (one column) or scalar/dict, not a
         table
update   copy the table with phrase columns ADDED or MODIFIED
delete   remove matching ROWS (with a where clause) or drop named COLUMNS
         (with a phrase and no where)

QSQL-00022. Clauses in detail

QSQL-00032.1 The select phrase

A comma-separated list of column expressions, each optionally named:

select from t                 all columns (empty phrase)
select a, b from t            two columns
select x:a, y:a+b from t      named / derived columns

The comma is the column separator here (but see 2.4 and 5.1 for where it is NOT). Each entry is (Optional<name>, expr). Evaluated in column scope.

QSQL-00042.2 Column naming

An unnamed computed column is named from the FIRST ARGUMENT of its parse tree, when that argument references a real column. Otherwise the name is x.

a*b     -> a        (*;`a;`b)   first arg is a column
b*a     -> b        (*;`b;`a)
a*2     -> a        (*;`a;2)
a+b*2   -> a        (+;`a;(*;`b;2))  nesting does not change it
sum a   -> a        (sum;`a)    a function head is skipped
neg a   -> a
2*a     -> x        (*;2;`a)    first arg is a literal, NOT a column
count i -> x        i is virtual, not a real column

A dotted name keeps its last segment. An explicit alias (name:expr) overrides all of this.

An expression referencing NO column at all is a 'rank error in select and exec, not a constant column: select c:1 from t signals rank. update DOES broadcast a constant: update c:1 from t adds a column of 1s. select computes over column vectors; update assigns into an existing row count.

QSQL-00052.3 The by phrase (group-by)

by turns the query into a grouped aggregation. Rows are grouped by the by columns; the select phrase is evaluated per group.

select avg price by sym from t
select sum price, max price by sym, date from t

In select, the by columns become the KEY columns of a keyed result table. In exec, by produces a dictionary from group key to aggregated value.

QSQL-00062.4 The from clause

from expr is the source. Inside from a comma is a TABLE JOIN, not a column separator (from (enlist d),t). The from expression stops at where or end.

QSQL-00072.5 The where clause (predicates)

A comma-separated list of predicate expressions, applied as a CASCADE: each predicate filters the rows that survived the previous one (so later predicates see fewer rows and can be cheaper). All are evaluated in column scope and must yield boolean vectors.

select from t where price > 100
select from t where sym=`AAPL, price > 100, size < 1000

delete with a where removes the matching rows; delete with a phrase and no where drops those columns.

QSQL-00082.6 select distinct

distinct immediately after select/exec dedupes the result rows. It is a modifier, not a column expression.

QSQL-00092.7 The special column i

i is the virtual row-index column (0 1 2 ... within the table, or within each group under by):

select i, sym from t where i < 3
select last i by sym from t        last row index per group

QSQL-00103. Row-limit form: select[n]

A bracket immediately after select/exec is kdb's row-limit / top-n grammar (the 6th slot of the functional parse tree):

select[n]              first n rows        (negative n: last -n)
select[m n]            offset m, count n
select[>col]           sort by col descending, then all
select[<col]           sort by col ascending
select[n;>col]         sort by col (desc), then take first n  ("n best")

An order term is >key (descending) or <key (ascending). The combined form sorts FIRST, then limits.

QSQL-00114. insert and upsert

Not query templates — ordinary dyadic operations, listed here because they are part of the table-mutation surface.

`t insert row            mutate the global table named by the symbol `t;
                         append a row (or a table of rows); returns the new
                         row count / indices
`t insert ([]c:..;..)    insert multiple rows

t upsert row             insert or, for a keyed table, replace by key;
                         returns the updated table (value, not mutation)
`t upsert ...            with a symbol left arg, mutates the named table

insert takes a symbol table NAME and mutates in place; upsert on a value returns a new table, on a symbol name mutates. A row may be a list matching the columns, or a dict / table.

QSQL-00125. Functional forms

When column names are computed at runtime, use the functional equivalents. These are NOT special syntax — they are the ? and ! verbs applied with list arguments, evaluated as queries. A parser sees ordinary application; the evaluator dispatches on the argument shapes.

QSQL-00135.1 Functional select / exec (the ? verb)

?[t; c; b; a]

t   the table
c   where: a list of constraint expressions (parse trees), ANDed as a
    cascade; () for none
b   by: a dict of group-column-name -> column expression; 0b for none
    (a symbol/name resolves the grouping)
a   select: a dict of result-column-name -> aggregation expression

?[t; c; b; a; n] optional 5th arg is the row limit (select[n]). Each expression in c/b/a is a PARSE TREE: a list whose head is a function and whose tail are column-name symbols, e.g. (>;`price;100) for price>100, or (avg;`price) for avg price.

QSQL-00145.2 Functional update / delete (the ! verb)

![t; c; b; a]

same argument roles as ?; ! writes back into the table.
a as a dict of name->expr is an UPDATE; a as a list of column-name
symbols (with empty b) is a DELETE of those columns; c selects the rows.

QSQL-00155.3 fby (filter-by)

(aggr;data) fby group — apply the aggregation aggr to data within each group of group, broadcasting the group result back to every row. Used inside a where or select to compare a row against its group aggregate:

select from t where price > (avg;price) fby sym

QSQL-00166. Joins

All joins are eager; multi-column keys are supported. Common-key joins take two tables; keyed/asof/window joins take explicit key columns.

verb   kind             notes
lj     left join        keep all left rows; null-fill non-matches
ij     inner join       matching rows only
uj     union join       all rows of both; append non-matching, widen columns
ej     equi join        exact match on named key columns
pj     plus join        add matching right numeric columns into the left
aj     asof join        last prevailing value at or before the key (time
                        series); aj[`sym`time; trades; quotes]
aj0    asof exact       last match at the exact time only
wj     window join      aggregate right rows within a window around each left
                        key; wj[win; `sym`time; trades; (quotes;(max;`bid);(min;`ask))]
wj1    window join 1    like wj but excludes the exact-time boundary
asof   as-of lookup     scalar/row as-of form

Suffix f variants (ljf ijf ujf ajf ajf0) are the "fill"/variant forms of the same joins.

lj / ij / uj are dyadic on common key columns: trades lj quotes aj / wj are bracket-applied with an explicit key-column list and the tables.

QSQL-00177. Parse-tree summary for implementers

  • One node covers all four templates; op distinguishes them. The six pieces map directly to the functional form: (op, from, where, by, select, limit) ~ ![/?[ t; c; b; a; n].
  • Parse order inside a query: keyword, optional [limit], optional distinct, select phrase, optional `by` phrase, mandatory `from` expr, optional `where` predicates.
  • Two contextual rules to track with flags while inside a query:
* inside from, a comma is a join, not a column separator;
* inside select/by/where phrases, a comma separates columns/predicates.
  • Column/predicate expressions parse with the ordinary expression grammar; the only qSQL-specific tokens are the contextual keywords select/exec/update/ delete/by/from/where and the [limit] bracket right after the keyword.
  • The i virtual column and column-name inference are evaluator concerns, not grammar; a parser just records names and expressions.
  • Functional ?[...] / ![...] need no query-specific parsing at all: they are plain verb application. Only select/exec/update/delete templates need the contextual keyword machinery.

END

Source: spec/qsql.txt