kdb standard SQL support s) »

kdb standard SQL support s)

February 24th, 2016 by admin

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:

t:([] a:til 10; b:10?9.0; c:10#`a`b`c)

q)select * from t where a>5 and b<5
'rank
q)s)select * from t where a>5 and b<5
a b        c
------------
6 4.765327 a
8 2.066954 c


q)select from t where a>5,b<5
a b        c
------------
6 4.765327 a
8 2.066954 c

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
COUNT	Yes
DELETE	Yes
UPDATE	Yes
String matching	slightly works
NOT	NO
IN	Yes
GROUP BY	Yes
LIMIT / TOP	NO
Date Times	NO

Standard SQL Inserts work

q)s)insert into t (a, b, c) values (6, 9.0, 'symboll');
q)s)insert into t (a, b) values (6, 9.0);
q)t
a b         c
-------------------
0 4.438651  a
1 5.206683  b
2 0.7549972 c
3 1.763917  a
4 3.380742  b
5 5.523707  c
6 4.765327  a
7 6.224489  b
8 2.066954  c
9 6.227578  a
6 9         a
6 9         symboll
6 9

ORDER BY half works

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

q)s)SELECT * FROM t ORDER BY b DESC;
a b         c
-------------------
2 0.7549972 c
3 1.763917  a
8 2.066954  c
4 3.380742  b
0 4.438651  a
6 4.765327  a
1 5.206683  b
5 5.523707  c
7 6.224489  b
9 6.227578  a
6 9         a
6 9         symboll
6 9

q)s)SELECT * FROM t ORDER BY b ASC;
a b         c
-------------------
2 0.7549972 c
3 1.763917  a
8 2.066954  c
4 3.380742  b
0 4.438651  a
6 4.765327  a
1 5.206683  b
5 5.523707  c
7 6.224489  b
9 6.227578  a
6 9         a
6 9         symboll
6 9

COUNT works

q)s)SELECT count(*) FROM t
i
--
13

DELETE works

q)s)DELETE FROM t WHERE a>=6
q)t
a b         c
-------------
0 4.438651  a
1 5.206683  b
2 0.7549972 c
3 1.763917  a
4 3.380742  b
5 5.523707  c

UPDATE works

q)s)UPDATE t SET a=999 WHERE b>5;
q)t
a   b         c
---------------
0   4.438651  a
999 5.206683  b
2   0.7549972 c
3   1.763917  a
4   3.380742  b
999 5.523707  c

String matching slightly works

q)s)SELECT * FROM t where c='a'
a b        c
------------
0 4.438651 a
3 1.763917 a

q)s)SELECT * FROM t where c like '%a%'
a b        c
------------
0 4.438651 a
3 1.763917 a

// This is valid for SQL server etc.
q)s)SELECT * FROM t where c like '_'
a   b         c
---------------
0   4.438651  a
999 5.206683  b
2   0.7549972 c
3   1.763917  a
4   3.380742  b
999 5.523707  c

// This should not return results in standard SQL
q)s)SELECT * FROM t where c like '?'
a   b         c
---------------
0   4.438651  a
999 5.206683  b
2   0.7549972 c
3   1.763917  a
4   3.380742  b
999 5.523707  c

NOT fails

q)s)SELECT * FROM t where c NOT LIKE '%a%'
'type

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

IN works

q)s)SELECT * FROM t where c in ('a','b');
a   b        c
--------------
0   4.438651 a
999 5.206683 b
3   1.763917 a
4   3.380742 b
q)s)SELECT * FROM t where a in (0,3);
a b        c
------------
0 4.438651 a
3 1.763917 a

GROUP BY works

q)s)SELECT a,min(b) FROM t GROUP BY a
a   b
-------------
0   4.438651
2   0.7549972
3   1.763917
4   3.380742
999 5.206683

LIMIT / TOP does not work

q)s)select TOP 2 * FROM t;
'from
q)s)select * FROM t LIMIT 2;
a   b         c
---------------
0   4.438651  a
999 5.206683  b
2   0.7549972 c
3   1.763917  a
4   3.380742  b
999 5.523707  c

Date Times Don’t Work Right

q)a:.z.p + 1000000000*til 10
q)s:([] p:a; t:`time$a; n:`timespan$a; d:`date$a)
q)s
p                             t            n                    d
--------------------------------------------------------------------------
2016.02.24D14:27:53.095171000 14:27:53.095 0D14:27:53.095171000 2016.02.24
2016.02.24D14:27:54.095171000 14:27:54.095 0D14:27:54.095171000 2016.02.24
2016.02.24D14:27:55.095171000 14:27:55.095 0D14:27:55.095171000 2016.02.24
2016.02.24D14:27:56.095171000 14:27:56.095 0D14:27:56.095171000 2016.02.24
2016.02.24D14:27:57.095171000 14:27:57.095 0D14:27:57.095171000 2016.02.24
2016.02.24D14:27:58.095171000 14:27:58.095 0D14:27:58.095171000 2016.02.24
2016.02.24D14:27:59.095171000 14:27:59.095 0D14:27:59.095171000 2016.02.24
2016.02.24D14:28:00.095171000 14:28:00.095 0D14:28:00.095171000 2016.02.24
2016.02.24D14:28:01.095171000 14:28:01.095 0D14:28:01.095171000 2016.02.24
2016.02.24D14:28:02.095171000 14:28:02.095 0D14:28:02.095171000 2016.02.24

q)s)SELECT *  FROM  s WHERE d > '2016-02-23'
'type
// The below does work:
// q)s)SELECT *  FROM  s WHERE d > 2016.02.23

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)”

Roman Mishin

NOT works if used like this:

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

Comment on Feb 25th, 2016 at 4:00 am
SM

Any benchmarks on this vs traditional way of querying?

Comment on Apr 5th, 2016 at 1:47 pm
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

Comment on Sep 11th, 2016 at 1:21 am

TimeStored Blog