{"id":111128,"date":"2016-02-24T14:23:02","date_gmt":"2016-02-24T14:23:02","guid":{"rendered":"http:\/\/www.timestored.com\/b\/?p=111128"},"modified":"2016-02-25T09:31:18","modified_gmt":"2016-02-25T09:31:18","slug":"kdb-standard-sql-support","status":"publish","type":"post","link":"https:\/\/www.timestored.com\/b\/kdb-standard-sql-support\/","title":{"rendered":"kdb standard SQL support s)"},"content":{"rendered":"<p>First, in case you haven&#8217;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:<\/p>\n\r\n <textarea rows='2' cols='80' class='code' id='qcode-standard-sql-sup1'>\r\nt:([] a:til 10; b:10?9.0; c:10#`a`b`c)\r\n\r\nq)select * from t where a>5 and b<5\r\n'rank\r\nq)s)select * from t where a>5 and b<5\r\na b        c\r\n------------\r\n6 4.765327 a\r\n8 2.066954 c\r\n\r\n\r\nq)select from t where a>5,b<5\r\na b        c\r\n------------\r\n6 4.765327 a\r\n8 2.066954 c\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('qcode-standard-sql-sup1'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<p>Notice how the standard &#8220;and&#8221; syntax worked when I used s) but without it, q&#8217;s right to left evaluation causes problems. It&#8217;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&#8217;s look at what works:<\/p>\n<table>\n<tr>\n<th>Operation<\/th>\n<th>Works?<\/th>\n<\/tr>\n<tr>\n<td>Standard SQL Inserts <\/td>\n<td><span style='color:green'>Yes<\/span><\/td>\n<\/tr>\n<tr>\n<td>ORDER BY <\/td>\n<td> half works <\/td>\n<\/tr>\n<tr>\n<td>COUNT <\/td>\n<td> <span style='color:green'>Yes<\/span> <\/td>\n<\/tr>\n<tr>\n<td>DELETE <\/td>\n<td> <span style='color:green'>Yes<\/span> <\/td>\n<\/tr>\n<tr>\n<td>UPDATE <\/td>\n<td> <span style='color:green'>Yes<\/span> <\/td>\n<\/tr>\n<tr>\n<td>String matching <\/td>\n<td>slightly works<\/td>\n<\/tr>\n<tr>\n<td>NOT <\/td>\n<td> <span style='color:red'>NO<\/span> <\/td>\n<\/tr>\n<tr>\n<td>IN <\/td>\n<td> <span style='color:green'>Yes<\/span> <\/td>\n<\/tr>\n<tr>\n<td>GROUP BY<\/td>\n<td> <span style='color:green'>Yes<\/span> <\/td>\n<\/tr>\n<tr>\n<td>LIMIT \/ TOP <\/td>\n<td> <span style='color:red'>NO<\/span> <\/td>\n<\/tr>\n<tr>\n<td>Date Times <\/td>\n<td> <span style='color:red'>NO<\/span> <\/td>\n<\/tr>\n<\/table>\n<h3>Standard SQL Inserts work<\/h3>\n\r\n <textarea rows='13' cols='80' class='code' id='qcode-standard-sql-sup2'>\r\nq)s)insert into t (a, b, c) values (6, 9.0, 'symboll');\r\nq)s)insert into t (a, b) values (6, 9.0);\r\nq)t\r\na b         c\r\n-------------------\r\n0 4.438651  a\r\n1 5.206683  b\r\n2 0.7549972 c\r\n3 1.763917  a\r\n4 3.380742  b\r\n5 5.523707  c\r\n6 4.765327  a\r\n7 6.224489  b\r\n8 2.066954  c\r\n9 6.227578  a\r\n6 9         a\r\n6 9         symboll\r\n6 9\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('qcode-standard-sql-sup2'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<h3>ORDER BY half works<\/h3>\n<p>&#8220;ORDER BY&#8221; will sort the columns in ascending order, attempting to use DESC has no effect.<\/p>\n\r\n <textarea rows='13' cols='80' class='code' id='qcode-standard-sql-sup3'>\r\nq)s)SELECT * FROM t ORDER BY b DESC;\r\na b         c\r\n-------------------\r\n2 0.7549972 c\r\n3 1.763917  a\r\n8 2.066954  c\r\n4 3.380742  b\r\n0 4.438651  a\r\n6 4.765327  a\r\n1 5.206683  b\r\n5 5.523707  c\r\n7 6.224489  b\r\n9 6.227578  a\r\n6 9         a\r\n6 9         symboll\r\n6 9\r\n\r\nq)s)SELECT * FROM t ORDER BY b ASC;\r\na b         c\r\n-------------------\r\n2 0.7549972 c\r\n3 1.763917  a\r\n8 2.066954  c\r\n4 3.380742  b\r\n0 4.438651  a\r\n6 4.765327  a\r\n1 5.206683  b\r\n5 5.523707  c\r\n7 6.224489  b\r\n9 6.227578  a\r\n6 9         a\r\n6 9         symboll\r\n6 9\r\n\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('qcode-standard-sql-sup3'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<h3>COUNT works<\/h3>\n\r\n <textarea rows='13' cols='80' class='code' id='qcode-standard-sql-sup4'>\r\nq)s)SELECT count(*) FROM t\r\ni\r\n--\r\n13\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('qcode-standard-sql-sup4'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<h3>DELETE works<\/h3>\n\r\n <textarea rows='13' cols='80' class='code' id='qcode-standard-sql-sup5'>\r\nq)s)DELETE FROM t WHERE a>=6\r\nq)t\r\na b         c\r\n-------------\r\n0 4.438651  a\r\n1 5.206683  b\r\n2 0.7549972 c\r\n3 1.763917  a\r\n4 3.380742  b\r\n5 5.523707  c\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('qcode-standard-sql-sup5'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<h3>UPDATE works<\/h3>\n\r\n <textarea rows='13' cols='80' class='code' id='qcode-standard-sql-sup6'>\r\nq)s)UPDATE t SET a=999 WHERE b>5;\r\nq)t\r\na   b         c\r\n---------------\r\n0   4.438651  a\r\n999 5.206683  b\r\n2   0.7549972 c\r\n3   1.763917  a\r\n4   3.380742  b\r\n999 5.523707  c\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('qcode-standard-sql-sup6'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<h3>String matching slightly works<\/h3>\n\r\n <textarea rows='9' cols='80' class='code' id='qcode-standard-sql-sup7'>\r\nq)s)SELECT * FROM t where c='a'\r\na b        c\r\n------------\r\n0 4.438651 a\r\n3 1.763917 a\r\n\r\nq)s)SELECT * FROM t where c like '%a%'\r\na b        c\r\n------------\r\n0 4.438651 a\r\n3 1.763917 a\r\n\r\n\/\/ This is valid for SQL server etc.\r\nq)s)SELECT * FROM t where c like '_'\r\na   b         c\r\n---------------\r\n0   4.438651  a\r\n999 5.206683  b\r\n2   0.7549972 c\r\n3   1.763917  a\r\n4   3.380742  b\r\n999 5.523707  c\r\n\r\n\/\/ This should not return results in standard SQL\r\nq)s)SELECT * FROM t where c like '?'\r\na   b         c\r\n---------------\r\n0   4.438651  a\r\n999 5.206683  b\r\n2   0.7549972 c\r\n3   1.763917  a\r\n4   3.380742  b\r\n999 5.523707  c\r\n\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('qcode-standard-sql-sup7'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<h3>NOT fails<\/h3>\n\r\n <textarea rows='5' cols='80' class='code' id='qcode-standard-sql-sup8'>\r\nq)s)SELECT * FROM t where c NOT LIKE '%a%'\r\n'type\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('qcode-standard-sql-sup8'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<p>Modifying our String query slightly by adding NOT throws an error. My guess is that the interpreter has got confused.<\/p>\n<h3>IN works<\/h3>\n\r\n <textarea rows='5' cols='80' class='code' id='qcode-standard-sql-sup9'>\r\nq)s)SELECT * FROM t where c in ('a','b');\r\na   b        c\r\n--------------\r\n0   4.438651 a\r\n999 5.206683 b\r\n3   1.763917 a\r\n4   3.380742 b\r\nq)s)SELECT * FROM t where a in (0,3);\r\na b        c\r\n------------\r\n0 4.438651 a\r\n3 1.763917 a\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('qcode-standard-sql-sup9'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<h3>GROUP BY works<\/h3>\n\r\n <textarea rows='5' cols='80' class='code' id='qcode-standard-sql-sup11'>\r\nq)s)SELECT a,min(b) FROM t GROUP BY a\r\na   b\r\n-------------\r\n0   4.438651\r\n2   0.7549972\r\n3   1.763917\r\n4   3.380742\r\n999 5.206683\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('qcode-standard-sql-sup11'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<h3>LIMIT \/ TOP does not work<\/h3>\n\r\n <textarea rows='7' cols='80' class='code' id='qcode-standard-sql-sup21'>\r\nq)s)select TOP 2 * FROM t;\r\n'from\r\nq)s)select * FROM t LIMIT 2;\r\na   b         c\r\n---------------\r\n0   4.438651  a\r\n999 5.206683  b\r\n2   0.7549972 c\r\n3   1.763917  a\r\n4   3.380742  b\r\n999 5.523707  c\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('qcode-standard-sql-sup21'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<h3>Date Times Don&#8217;t Work Right<\/h3>\n\r\n <textarea rows='25' cols='80' class='code' id='qcode-standard-sql-sup13'>\r\nq)a:.z.p + 1000000000*til 10\r\nq)s:([] p:a; t:`time$a; n:`timespan$a; d:`date$a)\r\nq)s\r\np                             t            n                    d\r\n--------------------------------------------------------------------------\r\n2016.02.24D14:27:53.095171000 14:27:53.095 0D14:27:53.095171000 2016.02.24\r\n2016.02.24D14:27:54.095171000 14:27:54.095 0D14:27:54.095171000 2016.02.24\r\n2016.02.24D14:27:55.095171000 14:27:55.095 0D14:27:55.095171000 2016.02.24\r\n2016.02.24D14:27:56.095171000 14:27:56.095 0D14:27:56.095171000 2016.02.24\r\n2016.02.24D14:27:57.095171000 14:27:57.095 0D14:27:57.095171000 2016.02.24\r\n2016.02.24D14:27:58.095171000 14:27:58.095 0D14:27:58.095171000 2016.02.24\r\n2016.02.24D14:27:59.095171000 14:27:59.095 0D14:27:59.095171000 2016.02.24\r\n2016.02.24D14:28:00.095171000 14:28:00.095 0D14:28:00.095171000 2016.02.24\r\n2016.02.24D14:28:01.095171000 14:28:01.095 0D14:28:01.095171000 2016.02.24\r\n2016.02.24D14:28:02.095171000 14:28:02.095 0D14:28:02.095171000 2016.02.24\r\n\r\nq)s)SELECT *  FROM  s WHERE d > '2016-02-23'\r\n'type\r\n\/\/ The below does work:\r\n\/\/ q)s)SELECT *  FROM  s WHERE d > 2016.02.23\r\n\r\n<\/textarea><script type='text\/javascript'>var myCodeMirror = CodeMirror.fromTextArea(document.getElementById('qcode-standard-sql-sup13'), {lineNumbers: true, matchBrackets: true, indentUnit: 4, tabMode: 'default', mode: 'text\/x-plsql', readOnly:true });<\/script>\r\n\n<p><b>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.<\/b> For any form of complex queries on strings, joins etc. support is either not there or the result may not be what you expect. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>First, in case you haven&#8217;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 &#8220;and&#8221; syntax worked when I used s) but without it, q&#8217;s right to left evaluation causes problems. It&#8217;s about now [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0},"categories":[2],"tags":[],"_links":{"self":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/111128"}],"collection":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/comments?post=111128"}],"version-history":[{"count":18,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/111128\/revisions"}],"predecessor-version":[{"id":111146,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/posts\/111128\/revisions\/111146"}],"wp:attachment":[{"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/media?parent=111128"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/categories?post=111128"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.timestored.com\/b\/wp-json\/wp\/v2\/tags?post=111128"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}