kdb standard SQL support s)

First, in case you haven’t heard about it kdb has a standard SQL mode, you can send queries prefixed with s) and they will be interpreted as standard SQL like so:

Notice how the standard “and” syntax worked when I used s) but without it, q’s right to left evaluation causes problems. It’s about now that a lot of people get very excited, they think great I can skip learning that q-sql and use my standard SQL. Sometimes the look of joy on their faces transforms to frustration once they start using it. So let’s look at what works:

Operation Works?
Standard SQL Inserts Yes
ORDER BY half works
String matching slightly works
IN Yes
Date Times NO

Standard SQL Inserts work

ORDER BY half works

“ORDER BY” will sort the columns in ascending order, attempting to use DESC has no effect.

COUNT works

DELETE works

UPDATE works

String matching slightly works

NOT fails

Modifying our String query slightly by adding NOT throws an error. My guess is that the interpreter has got confused.

IN works

GROUP BY works

LIMIT / TOP does not work

Date Times Don’t Work Right

Overall standard SQL support in kdb has got much better. However I would still recommend only using the s) syntax for plugging into an existing jdbc/odbc visualization tool and getting some immediate simple results. For any form of complex queries on strings, joins etc. support is either not there or the result may not be what you expect.

3 Responses to “kdb standard SQL support s)”

  1. Roman Mishin

    NOT works if used like this:

    q)s)SELECT * FROM t WHERE NOT c LIKE ‘%a%’

  2. SM

    Any benchmarks on this vs traditional way of querying?

  3. l1t

    //if joined table has same column names, the result is wrong
    q)\l sp.q
    q)s)select s.name,p.name,sp.qty from s,p,sp where sp.s=s.s and sp.p=p.p
    name name qty
    smith smith 300
    smith smith 200
    smith smith 400

    //the second name displays s.name, not p.name