Skip to content

SQL

Support for ANSI SQL is built into the KX Insights Core

Our ANSI SQL functionality is a full RDBMS frontend provided with the KX Insigths Core using a new s.k_ implementation.

Using SQL

SQL is invoked by using .s.e at the KX Insights Core process cmdline. Or it can conveniently be entered interactively by prefixing the statement with s) such as:

q)r:.s.e"select a from t"
q)s)select a from t

Alternatively when pgwire is used in conjunction with KX Insights Core and s.k_, you can use SQL clients like Tableau, HeidiSQL, PowerBI, DBVisualizer or Grafana. The query builders within these clients make it very easy for an end user to visualise the tables and automatically build the ANSI SQL statements.

The following versions of those clients are supported. As new versions of these popular clients are released we endevour to add support for those newer versions over time.

client version
Tableau 2021.4.1
HeidiSQL 11.3.0.6295
PowerBI 2.95 (July 2021)
DBVisualizer 13.0.2
Grafana 9.0.0

Parameters

  • .s.sp[query;param list] : simple parameters, compiled into a SQL statement and may be used in compile-time qt().

    q)r:.s.sp["select a from t where s in $1 and p>$2"](`AAPL`GOOG;12.34)
  • .s.sq[query;prototype list] : prepare a parametrized query, deducing parameter types from prototype

  • .s.sx[prepared query;param list] : execute a query prepared with .s.sq

    a:.s.sq["select a from t where s in $1 and p>$2"](``;0n);
    r:.s.sx[a](`AAPL`GOOG;12.34)

    parameters can be tables, or even partitioned tables:

    a:.s.sq["select price from $1 where sym in $2";(([]date:0#0Nd;sym:0#`;price:0#0);``)];
    r:.s.sx[a](trade;`AAPL`GOOG) / or (`trade;`AAPL`GOOG)

If you wish to get a rough estimate of the performance of a particular SQL statement you can look at the different stages of parse, parse and compile, and execute.

  • The parse stage using .s.prx will show the parse tree and can be timed separately.
  • The combined parse and compile step using the prepare .s.sq.
  • The execute step of .s.sx can then be timed.

Datatype conversions

SQL type              KX SQL  q   name
---------------------------------------------------------------------------
text                  a       0   list of character vectors
varchar, char(n>1)    s       11  symbol
char(1)               c       10  char
boolean               b       1   boolean
uuid                  q       2   guid
tinyint               x       4   byte
smallint              h       5   short
integer               i       6   int
bigint                j       7   long
real                  e       8   real
float,double,numeric  f       9   float
date                  d       14  date
time                  t       19  time
timestamp (w/o tz)    p       12  timestamp
varchar               q       20  enum
                      l       20  link (enum used for linked table access)
                      w           null' sql value

SQL language support

Operators

The following operators are supported:

 + - * / div
 = <> != < > >= <=
 is [not] distinct from
 a between x and y
 not and or
 [not]in [not]like is[not]null

SELECT

select [distinct] ns from t (left|right|inner|cross)
  join ..join q on f(t)=g(q)
  where c
  group by 1,g
  having h
  order by 1,o asc|desc
  limit n

Join types include: left, right, inner, and cross

For the join condition on tables t, q must be of the form

f(cols of t)=g(cols of q)

Joins may be nested, but natural, using and lateral are not yet implemented.

order by and group by expressions may be column names or ordinal numbers of output column, or arbitrary expressions.

limit with an offset is not yet implemented.

CTE/WITH SELECT

SQL has support for common table expressions. These queries take the form:

with t1 as (select...), t2 as (select... t1) select... t2

The results are materialized, unless they can be expressed as a union of operations on individual partitions.

Data

The following literals are supported:

false
null
true

Type literals are supported, e.g date'2001-01-01'

String literals may be converted to types date,time,timestamp automatically when possible, e.g.

q)s)select * from t where date in ('2001-01-01','2002-02-02') and time>'12:03'

cast is also supported to convert from one datatype to another, e.g

cast(x as typename) x::typename

Aggregates

The following aggregates are supported:

avg         last
count       max
count(*)    min
first       sum
q)t:([] s:`AAPL`GOOG`BARC`VOD; p:13.0 12.1 4.0 5.4; a:1 2 3 4)
q)s)select avg(p), sum(a), min(s) from t
p     a  s
-------------
8.625 10 AAPL

sum, avg, count, min, max are also supported with distinct.

For example

q)q:([] s:`AAPL`AAPL`GOOG`GOOG; p:4?10.0)
q)s)select count(distinct s) from q
s
-
2

String functions

The following string functions are supported:

||          position(x in y)
left        position(x,y)
right       substring(x from y)
lower       substring(x from y for z)
upper       substring(x,y,z)
Note, there is no pattern matching on substring

Datetime functions

The following datetime functions are supported:

extract
date_trunc
now
q)s)select extract(hour from timestamp '2002-09-17 19:27:45')
extract
-------
19
q)s)select date_trunc('hour', timestamp '2017-03-17 02:09:30');

date_trunc
-----------------------------
2017.03.17D02:00:00.000000000

Math functions

The following math functions are supported:

div
floor
round
stddev
trunc
q)t:([] a:1.123 1.456 2.432)
q)s)select round(a) from t
round
-----
1
1
2

Conditional operators

The following conditional operators are supported:

case [a] when b then x ... else y
coalesce
nullif

Both the general and ‘simple’ comparison forms of case are supported.

Subquery

Scalar subquery and correlated subquery are supported.

The following operators support subquery arguments

<     [not] in
>     [not] exists
=

Combining queries

The following operators are supported for combining queries:

union[all]
intersect[all]
except[all]

Other SQL statements

CREATE TABLE t (name type ...)

create a regular kdb+ table in memory

INSERT INTO t VALUES (...)

insert rows into a table

DROP TABLE t

delete the in-memory table

Compliance with ANSI SQL

SQL compliance

function description status notes
E011 Numeric data types
E011-01 INTEGER and SMALLINT data types (including all spellings) Yes
E011-02 REAL, DOUBLE PRECISION, and FLOAT data types Yes
E011-03 DECIMAL and NUMERIC data types No
E011-04 Arithmetic operators Yes
E011-05 Numeric comparison Yes
E011-06 Implicit casting among the numeric data types Yes
E021 Character string types
E021-01 CHARACTER data type (including all its spellings) Yes
E021-02 CHARACTER VARYING data type (including all its spellings) Yes
E021-03 Character literals Yes
E021-04 CHARACTER_LENGTH function Yes
E021-05 OCTET_LENGTH function Pending
E021-06 SUBSTRING function Yes
E021-07 Character concatenation Yes
E021-08 UPPER and LOWER functions Yes
E021-09 TRIM function Pending
E021-10 Implicit casting among the fixed-length and variable-length character string types Yes
E021-11 POSITION function Yes
E021-12 Character comparison Yes
E031 Identifiers
E031-01 Delimited identifiers Yes
E031-02 Lower case identifiers Yes
E031-03 Trailing underscore Yes
E051 Basic query specification
E051-01 SELECT DISTINCT Yes
E051-02 GROUP BY clause Yes
E051-04 GROUP BY can contain columns Not in Yes
E051-05 Select list items can be renamed Yes
E051-06 HAVING clause Yes
E051-07 Qualified * in select list Yes
E051-08 Correlation names in the FROM clause Yes
E051-09 Rename columns in the FROM clause No
E061 Basic predicates and search conditions
E061-01 Comparison predicate Yes
E061-02 BETWEEN predicate Yes
E061-03 IN predicate with list of values Yes
E061-04 LIKE predicate Partial Uses q-like syntax, replacing % with *: No underscore
E061-05 LIKE predicate: ESCAPE clause No
E061-06 NULL predicate Yes
E061-07 Quantified comparison predicate Pending
E061-08 EXISTS predicate Yes
E061-09 Subqueries in comparison predicate Yes
E061-11 Subqueries in IN predicate Yes
E061-12 Subqueries in quantified comparison predicate Pending
E061-13 Correlated subqueries Yes
E061-14 Search condition Yes
E071 Basic query expressions
E071-01 UNION DISTINCT table operator Yes
E071-02 UNION ALL table operator Yes
E071-03 EXCEPT DISTINCT table operator Yes
E071-05 Columns combined via table operators need Not have exactly the same data type Yes
E071-06 Table operators in subqueries Yes
E081 Basic Privileges No
E081-01 SELECT privilege at the table level No
E081-02 DELETE privilege No
E081-03 INSERT privilege at the table level No
E081-04 UPDATE privilege at the table level No
E081-05 UPDATE privilege at the column level No
E081-06 REFERENCES privilege at the table level No
E081-07 REFERENCES privilege at the column level No
E081-08 WITH GRANT OPTION No
E081-09 USAGE privilege No
E081-10 EXECUTE privilege No
E091 Set functions
E091-01 AVG Yes
E091-02 COUNT Yes
E091-03 MAX Yes
E091-04 MIN Yes
E091-05 SUM Yes
E091-06 ALL quantifier Pending
E091-07 DISTINCT quantifier Yes
E101 Basic data manipulation
E101-01 INSERT statement Yes
E101-03 Searched UPDATE statement No
E101-04 Searched DELETE statement No
E111 Single row SELECT statement Yes
E121 Basic cursor support No
E121-01 DECLARE CURSOR No
E121-02 ORDER BY columns need Not be in select list Yes
E121-03 Value expressions in ORDER BY clause Yes
E121-04 OPEN statement No
E121-06 Positioned UPDATE statement No
E121-07 Positioned DELETE statement No
E121-08 CLOSE statement No
E121-10 FETCH statement: implicit NEXT No
E121-17 WITH HOLD cursors No
E131 Null value support (nulls in lieu of values) Partial See 'nulls' in compatibility Notes
E141 Basic integrity constraints No
E141-01 NoT NULL constraints No
E141-02 UNIQUE constraints of NoT NULL columns No
E141-03 PRIMARY KEY constraints No
E141-04 Basic FOREIGN KEY constraint with the No ACTION default for both referential delete action and referential update action No
E141-06 CHECK constraints No
E141-07 Column defaults No
E141-08 NoT NULL inferred on PRIMARY KEY No
E141-10 Names in a foreign key can be specified in any order No
E151 Transaction support No
E151-01 COMMIT statement No
E151-02 ROLLBACK statement No
E152 Basic SET TRANSACTION statement No
E152-01 SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause No
E152-02 SET TRANSACTION statement: READ ONLY and READ WRITE clauses No
E* Other
E153 Updatable queries with subqueries No
E161 SQL comments using leading double minus Pending
E171 SQLSTATE support No
E182 Host language binding (previously "Module Language") Yes Called from q, can call q
F021 Basic information schema
F021-01 COLUMNS view Pending
F021-02 TABLES view Yes
F021-03 VIEWS view Pending
F021-04 TABLE_CONSTRAINTS view No
F021-05 REFERENTIAL_CONSTRAINTS view No
F021-06 CHECK_CONSTRAINTS view No
F031 Basic schema manipulation
F031-01 CREATE TABLE statement to create persistent base tables Partial No persistence
F031-02 CREATE VIEW statement No
F031-03 GRANT statement No
F031-04 ALTER TABLE statement: ADD COLUMN clause No
F031-13 DROP TABLE statement: RESTRICT clause Partial No restrict
F031-16 DROP VIEW statement: RESTRICT clause No
F031-19 REVOKE statement: RESTRICT clause No
F041 Basic joined table
F041-01 Inner join (but Not necessarily the INNER keyword) Yes
F041-02 INNER keyword Yes
F041-03 LEFT OUTER JOIN Yes
F041-04 RIGHT OUTER JOIN Yes
F041-05 Outer joins can be nested Yes
F041-07 The inner table in a left or right outer join can also be used in an inner join Yes
F041-08 All comparison operators are supported (rather than just =) No
F051 Basic date and time
F051-01 DATE data type (including support of DATE literal) Yes
F051-02 TIME data type (including support of TIME literal) with fractional seconds precision of at least 0 Yes
F051-03 TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6 Yes
F051-04 Comparison predicate on DATE, TIME, and TIMESTAMP data types Yes
F051-05 Explicit CAST between datetime types and character string types Yes
F051-06 CURRENT_DATE Pending
F051-07 LOCALTIME Pending
F051-08 LOCALTIMESTAMP Pending
F081 UNION and EXCEPT in views No
F131 Grouped operations No
F131-01 WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views No
F131-02 Multiple tables supported in queries with grouped views No
F131-03 Set functions supported in queries with grouped views No
F131-04 Subqueries with GROUP BY and HAVING clauses and grouped views No
F131-05 Single row SELECT with GROUP BY and HAVING clauses and grouped views No
F* Other
F181 Multiple module support No
F201 CAST function Yes
F221 Explicit defaults No
F261 CASE expression
F261-01 Simple CASE Yes
F261-02 Searched CASE Yes
F261-03 NULLIF Yes
F261-04 COALESCE Yes
F311 Schema definition statement No
F311-01 CREATE SCHEMA No
F311-02 CREATE TABLE for persistent base tables No
F311-03 CREATE VIEW No
F311-04 CREATE VIEW: WITH CHECK OPTION No
F311-05 GRANT statement No
F471 Scalar subquery values Yes
F481 Expanded NULL predicate No
F501 Features and conformance views No
F501-01 SQL_FEATURES view No
F501-02 SQL_SIZING view No
F501-03 SQL_LANGUAGES view No
F812 Basic flagging No
S011 Distinct data types No
S011-01 USER_DEFINED_TYPES view No
T321 Basic SQL-invoked routines
T321-01 User-defined functions with No overloading Yes q fns can be converted to SQL fns with .s.fs and added to .s.F
T321-02 User-defined stored procedures with No overloading No
T321-03 Function invocation Yes
T321-04 CALL statement No
T321-05 RETURN statement No
T321-06 ROUTINES view No
T321-07 PARAMETERS view No
T631 IN predicate with one list element Yes