kdb q Quirks

The below is a collection of issues that are commonly asked or are just plain odd

Deleting a Namespace - you can't
Spaces in symbols - cast from string
Standard SQL issues
1. Column Naming Errors - Reserved words and spaces
2. SQL CASE WHEN statement - Vector conditional as substitute
3. SUBSTR or concatenating string columns
4. select * from t where OR AND COMMA - kdb prefers comma to AND

Deleting a Namespace

We cannot delete a namespace once it has been created, we can however delete variables from namespaces as shown below:

q)key ` / list namespaces
`q`Q`h`o

q)/ create a new variable v in namespace ns
q)\d .ns
q.ns)v:10
q.ns)a:1
q.ns)b:`w`e
q.ns)\d .

q)key ` / check ns exists
`q`Q`h`o`ns
q)key `.ns / list variables in namespace ns
``v`a`b
q)delete v from `.ns
`.ns
q)key `.ns / notice v is gone
,`

/ but can't delete namespace
q)delete ns from `
'
q)delete ns from `.
`.
q)key `
`q`Q`h`o`ns

/ delete all variables in namespace
q)key `.ns
``a`b
q)delete from `.ns
`.ns
q)key `.ns
`symbol$()

Spaces in symbols

Spaces between symbols will cause an interpreter error. If you want spaces or other special characters you should create an array of strings then cast to symbols. e.g.

q)l:`john`jim`harry trumen`a@kx.com
'kx.com
q)l:("john";"jim";"harry truman";"a@kx.com")
q)l
"john"
"jim"
"harry truman"
"a@kx.com"
q)`$l
`john`jim`harry truman`a@kx.com

Standard SQL issues

Column Naming Errors

In order to select from a table with problematic names involving spaces or reserved words, we have to use functional select. Functional select allows us to supply the column names as symbols. Which as we seen above we can cast from strings to handle any special characters.

/ Microsoft SQL select group,[customer title] from orderBook
/ standard SQL select group,"customer title" from orderBook
/ q SQL
q)?[orderBook; (); 0b; {x!x}`$("group";"customer title")]
group customer title
--------------------
1     "john"
2     "jim"
3     "harry truman"
4     "a@kx.com"

// renaming columns of an existing table
q)(`$("the-group";"the customer title")) xcol orderBook
the-group the customer title
----------------------------
1         "john"
2         "jim"
3         "harry truman"
4         "a@kx.com"

SQL CASE WHEN statement

q-SQL does not have CASE-WHEN like standard SQL, it does however have a number of other language features that provide similar functionality in a more elegant way. The most simple cases can be replaced with dictionary lookup:

select PatientName,
       case sexCode
        when 0 then 'female'
        when 1 then 'male'
        when 2 then 'unknown'
        else 'Invalid sexCode' end as "patientSex"
      from patients

q)patients
name  sexCode
-------------
jim   1
john  2
jane  0
aaron 4

q)lookup:0 1 2!`female`male`unknown
q)lookup
0| female
1| male
2| unknown

q)update patientSex:`invalid^lookup[sexCode] from patients
name  sexCode patientSex
------------------------
jim   1       male
john  2       unknown
jane  0       female
aaron 4       invalid

More complicated cases e.g. conditionally selecting which source column to pull data from depending on a clause will require the vector conditional. Vector conditional returns a result that depending on its first argument (a list of booleans) takes either values from its second parameter if true, otherwise its third parameter. Below we can see this used to return the latest date from two columns:

SELECT
    CASE
        WHEN Date1 >= Date2 THEN Date1
        ELSE Date2
    END AS MostRecentDate

q)update mostRecentDate:?[date1>=date2;date1;date2] from t
date1      date2      mostRecentDate
------------------------------------
2001.08.05 2007.09.28 2007.09.28
2011.05.21 2007.07.25 2011.05.21
2005.08.10 2008.07.06 2008.07.06
2003.02.16 2008.10.11 2008.10.11
2007.10.05 2004.03.01 2007.10.05

SUBSTR or concatenating string columns

For joining a constant string to a string column use
join-each-left ,\:"postfix"
or join-each-right "prefix",/:
For joining two string columns to each other, use join-both ,'
e.g.

q)patients:([] fname:`jim`john`jane`aaron; lname:`ash`black`church`david; sexCode:1 2 0 4)
q)p:update name:((string[fname],\:" "),'string[lname]) from patients
q)p
fname lname  sexCode name
----------------------------------
jim   ash    1       "jim ash"
john  black  2       "john black"
jane  church 0       "jane church"
aaron david  4       "aaron david"

q)/ SUBSTR - lets limit name to first 7 characters
q)select 7 sublist/: name from p
name
---------
"jim ash"
"john bl"
"jane ch"
"aaron d"

The parentheses are needed to prevent the interpreter getting confused and thinking your specifying different columns.

select * from t where OR AND COMMA

Kdb uses comma to separate where clauses. AND is also a function in kdb but it will give significantly worse performance. AND causes each of the columns contained in the clauses to be fully scanned. For the standard comma's, kdb will evaluate the first clause, store what indices evaluated true and only check those column indices for the next conditional and so on. This is why kdb queries with the comma perform much faster.

q)t:([] c:n?1000; d:n?1000; e:n?1000)
q)t
c   d   e
-----------
453 432 685
620 581 740
962 173 90
958 658 452
789 774 453
111 884 178
883 183 14
916 179 311
725 330 301
179 431 101
421 490 266
..

q) / parentheses needed due to kdb's right-to-left evaluation
q) / \t times how long a statement takes to complete
q)\t do[100;r1:select from t where ((c=99) and d<99) and e<99]
622
q)\t do[100;r2:select from t where c=99,d<99,e<99]
277
q)r1~r2
1b
q)/ second version was much faster and returned the same result.

Contents