- users-list - monetdb.org

Query Histroy & query parsing / translation / compilation / optimization time
by Stefan Manegold 03 May '16

03 May '16

Hi, looking for ways to measure the query parsing / translation / compilation / optimization time in MonetDB, I found these two documentations on the MonetDB website: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistory and https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistoryNew While the documentation is not entirely clear on whether the latter did, will, or is supposed to replace the former (and if so, when), using the latest Jul2015-SP4 release, I get with the former: sql>set history=true; operation successful (137.545ms) sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (155.577ms) sql>select 2; +--------------+ | single_value | +==============+ | 2 | +--------------+ 1 tuple (193.633ms) sql>select * from sys.queryHistory; SELECT: no such table 'queryhistory' sql>select * from sys.callHistory; SELECT: no such table 'callhistory' sql>select * from sys.queryLog; SELECT: no such table 'querylog' sql>call sys.resetHistory(); SELECT: no such operator 'resethistory' I.e, this does not seem to work (anymore?). The latter seems to work fine: sql>call sys.querylog_enable(); sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (127.056ms) sql>select 1; +--------------+ | single_value | +==============+ | 1 | +--------------+ 1 tuple (124.303ms) sql>select 2; +--------------+ | single_value | +==============+ | 2 | +--------------+ 1 tuple (148.923ms) sql>select * from sys.querylog_catalog; +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+ | id | owner | defined | query | pipe | plan | mal | optimize | +======+=========+============================+=====================================+==============+===========+======+==========+ | 42@0 | monetdb | 2016-05-03 00:06:44.000000 | call sys.querylog_enable(); | default_pipe | user.s6_1 | 19 | 267 | | 43@0 | monetdb | 2016-05-03 00:06:47.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | | 44@0 | monetdb | 2016-05-03 00:06:48.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | | 45@0 | monetdb | 2016-05-03 00:06:50.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | | 46@0 | monetdb | 2016-05-03 00:06:56.000000 | select * from sys.querylog_catalog; | default_pipe | user.s8_1 | 68 | 665 | +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+ 5 tuples (132.446ms) sql>select * from sys.querylog_calls; +------+----------------------------+----------------------------+------------------------------------------+--------+-------+------+------+------+ | id | start | stop | arguments | tuples | run | ship | cpu | io | +======+============================+============================+==========================================+========+=======+======+======+======+ | 41@0 | 2016-05-03 00:06:38.000000 | 2016-05-03 00:06:39.000000 | user.s5_1{autoCommit=true}():void; | 1 | 85485 | 1 | 3 | 24 | | 42@0 | 2016-05-03 00:06:44.000000 | 2016-05-03 00:06:44.000000 | user.s6_1{autoCommit=true}():void; | 1 | 71 | 0 | 0 | 0 | | 43@0 | 2016-05-03 00:06:47.000000 | 2016-05-03 00:06:47.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 120 | 0 | 0 | 0 | | 44@0 | 2016-05-03 00:06:48.000000 | 2016-05-03 00:06:48.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 104 | 0 | 0 | 0 | | 45@0 | 2016-05-03 00:06:50.000000 | 2016-05-03 00:06:50.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 66 | 1 | 0 | 0 | | 46@0 | 2016-05-03 00:06:56.000000 | 2016-05-03 00:06:56.000000 | user.s8_1{autoCommit=true}():void; | 8 | 886 | 0 | 50 | 0 | +------+----------------------------+----------------------------+------------------------------------------+--------+-------+------+------+------+ 6 tuples (100.344ms) sql>select * from sys.querylog_history; +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+----------------------------+----------------------------+------------------------------------------+--------+------+------+------+------+ | id | owner | defined | query | pipe | plan | mal | optimize | start | stop | arguments | tuples | run | ship | cpu | io | +======+=========+============================+=====================================+==============+===========+======+==========+============================+============================+==========================================+========+======+======+======+======+ | 42@0 | monetdb | 2016-05-03 00:06:44.000000 | call sys.querylog_enable(); | default_pipe | user.s6_1 | 19 | 267 | 2016-05-03 00:06:44.000000 | 2016-05-03 00:06:44.000000 | user.s6_1{autoCommit=true}():void; | 1 | 71 | 0 | 0 | 0 | | 43@0 | monetdb | 2016-05-03 00:06:47.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | 2016-05-03 00:06:47.000000 | 2016-05-03 00:06:47.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 120 | 0 | 0 | 0 | | 44@0 | monetdb | 2016-05-03 00:06:48.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | 2016-05-03 00:06:48.000000 | 2016-05-03 00:06:48.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 104 | 0 | 0 | 0 | | 45@0 | monetdb | 2016-05-03 00:06:50.000000 | select 1; | default_pipe | user.s7_1 | 21 | 273 | 2016-05-03 00:06:50.000000 | 2016-05-03 00:06:50.000000 | user.s7_1{autoCommit=true}(A0:bte):void; | 1 | 66 | 1 | 0 | 0 | | 46@0 | monetdb | 2016-05-03 00:06:56.000000 | select * from sys.querylog_catalog; | default_pipe | user.s8_1 | 68 | 665 | 2016-05-03 00:06:56.000000 | 2016-05-03 00:06:56.000000 | user.s8_1{autoCommit=true}():void; | 8 | 886 | 0 | 50 | 0 | | 47@0 | monetdb | 2016-05-03 00:07:33.000000 | select * from sys.querylog_calls; | default_pipe | user.s9_1 | 73 | 282 | 2016-05-03 00:07:33.000000 | 2016-05-03 00:07:34.000000 | user.s9_1{autoCommit=true}():void; | 9 | 2515 | 0 | 0 | 0 | +------+---------+----------------------------+-------------------------------------+--------------+-----------+------+----------+----------------------------+----------------------------+------------------------------------------+--------+------+------+------+------+ 6 tuples (152.709ms) Hence, maybe the documentation should be updated to remove the former? Also, two questions remain unanswered by the current documentation: 1) What is the unit of "mal integer, -- size of MAL plan"? bytes? lines? statements? 2) What exactly does "optimize bigint -- time in microseconds" measure? Only the time taken by MAL optimizers (i.e., after SQL parsing, relational optimization and relational-to-MAL translation, but before actual execution)? If so, is there still a way to measure the time(s) for SQL parsing, relational optimization, and (or) relational-to-MAL translation? Thanks! Stefan -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

1 0

Help with json.filters
by Alberto Ferrari 02 May '16

02 May '16

Can anyone help with json.filters? I have this example table ... CREATE TABLE tblJson ( "id" INT, "json_data" JSON, ); ... with these values... INSERT INTO tblJson (id, json_data) VALUES (1, '{"data":{"time":"2015-10-09 10:36:35","others":"xxx"}}'), (2, '{"data":{"time":"2015-10-09 12:18:20","others":"yyy"}}'), (3, '{"data":{"time":"2015-12-30 16:31:22","others":"zzz"}}'); The data is ok, I can select the rows, it's ok. Also I can select only the "time" field, like this... SELECT json.filter(json_data, 'data.time') FROM tblJson; But now I want to filter the content in "json_data" specific field, i.e. SELECT all the rows where "time like '2015-10-09%'" , or maybe "time = '2015-10-09 12:18:20'" I tried but can't do it. Any help? Thanks!

3 5

Problems with aggregate UDF (subaggregate undefined error)
by Anderson, David B 02 May '16

02 May '16

All, I have been able to create and install my own aggregate UDF (weightedpercentile), but when I try to execute it via mclient I am getting the following error: sql>select key, weightedpercentile(a,b) from t group by key; TypeException:user.s9_1[20]:'udf.subweightedpercentile' undefined in: X_34:bat[:any,:dbl] := udf.subweightedpercentile(X_26:bat[:oid,:dbl],X_33:bat[:oid,:dbl],X_16:bat[:oid,:oid],r1_16:bat[:oid,:oid],true:bit); program contains errors Does anyone know what I am doing incorrectly? I am at a total loss now. The error is almost identical to the error I get if I try to execute the corr() aggregate: sql>select key, corr(a,b) from t group by key; TypeException:user.s10_1[20]:'aggr.subcorr' undefined in: X_34:bat[:any,:dbl] := aggr.subcorr(X_26:bat[:oid,:dbl],X_33:bat[:oid,:dbl],X_16:bat[:oid,:oid],r1_16:bat[:oid,:oid],true:bit); program contains errors The corr() error is referenced in a recent bug submission (which I can't find in Bugzilla) If I look in the system function catalog, I see: sql>select * from sys.functions where name like '%weight%'; +------+--------------------+---------------------------------------------------+------+------+------+-------+-------+-------+------+ | id | name | func | mod | lang | type | side_ | varre | varar | sche | : : : : : uage : : effec : s : g : ma_i : : : : : : : : t : : : d : +======+====================+===================================================+======+======+======+=======+=======+=======+======+ | 9313 | weightedpercentile | -- weighted percentile | udf | 1 | 3 | false | false | false | 2000 | : : : create aggregate weightedpercentile(e1 real, e2 r : : : : : : : : : : : eal) returns real : : : : : : : : : : : external name udf.weightedpercentile; : : : : : : : : | 9318 | weightedpercentile | -- weighted percentile | udf | 1 | 3 | false | false | false | 2000 | : : : create aggregate weightedpercentile(e1 double, e2 : : : : : : : : : : : double) returns double : : : : : : : : : : : external name udf.weightedpercentile; : : : : : : : : +------+--------------------+---------------------------------------------------+------+------+------+-------+-------+-------+------+ 2 tuples (1.107ms) So my function appears to be installed. I have in (80_udf.sql): -- weighted percentile create aggregate weightedpercentile(e1 real, e2 real) returns real external name udf.weightedpercentile; -- weighted percentile create aggregate weightedpercentile(e1 double, e2 double) returns double external name udf.weightedpercentile; and in (udf.mal): # BAT MAL signatures module batudf; command weightedpercentile(one:bat[:oid,:bte],two:bat[:oid,:bte]):lng address UDFBATweightedpercentile comment "compute the weighted percentile (bte)" command weightedpercentile(one:bat[:oid,:int],two:bat[:oid,:int]):lng address UDFBATweightedpercentile comment "compute the weighted percentile (int)" command weightedpercentile(one:bat[:oid,:lng],two:bat[:oid,:lng]):lng address UDFBATweightedpercentile comment "compute the weighted percentile (lng)"

2 3

Problem make-install'ing MonetDB to $HOME/opt/monetdb
by Eyal Rozenberg 02 May '16

02 May '16

I'm trying to upgrade to the latest release of MonetDB (Jul2015-SP4) instead of an older one. I'm building and installing as a non-root user, into $HOME/opt/monetdb . Well, * ./bootstrap SUCCEEDED * ./configure --prefix=$HOME/opt/monetdb SUCCEEDED * ./make SUCCEEDED * ./make install FAILED Here's the error message: /usr/bin/install -c -m 644 mapi.h /home/eyalroz/opt/monetdb/include/monetdb/mapi.h /bin/sh ../../libtool --mode=install /usr/bin/install -c libmapi.la '/home/eyalroz/opt/monetdb/lib' libtool: install: error: cannot install `libmapi.la' to a directory not ending in /usr/local/lib I don't see why libtool is telling me that. What business is it of libtool what I want to call my directories? Anyway, it doesn't seem to me I tried to do something esoteric... how do you suggest I get around this? Eyal PS - It's a Fedora 22 machine in case that matters.

2 2

Explain analyze
by Shmagi Kavtaradze 02 May '16

02 May '16

In postgres I could use explain analyze to see actions performed by database and their cost. I was not able to run explain analyze. I ran EXPLAIN, but can not see any plan or cost. Any ideas how to get costs and query plan like in Postgres?

4 7

Crystal bindings for MonetDB
by Brian Hood 30 Apr '16

30 Apr '16

Hi Guys, I have got my Crystal Language bindings for MonetDB now working quite well. https://github.com/puppetpies/crystal-monetdb-libmapi I've seen options in the mapi code for a variable called mode that allow you format the response data in XML but it doesn't seem to be implemented fully in the mapi code. Are there any guide lines to processing this RAW query output. % threatmonitor.fruits, threatmonitor.fruits, threatmonitor.fruits, threatmonitor.fruits, threatmonitor.fruits # table_name % name, price, weight, comments, id # name % varchar, varchar, int, clob, int # type % 9, 4, 2, 11, 1 # length [ "Apple", "9.99", 50, NULL, 1 ] [ "Bananna", "3.99", 30, NULL, 2 ] [ "Orange", "7.99", 60, NULL, 3 ] [ "Peach", "5.00", 80, NULL, 4 ] [ "Kiwi", "9.00", 20, NULL, 5 ] [ "Tomato", "2.00", 20, "Yes a fruit", 6 ] [ "Pear", "4.00", 30, "Juicy", 7 ] [ "Nectarine", "6.00", 50, "Juicy", 8 ] Regards, Brian Hood

1 0

Case sensitive
by Ariel Abadi 30 Apr '16

30 Apr '16

Hi All! We are facing a problem, we used to work with mysql with the case insensitive. Now we have moved into MonetDB and we are facing a problem with this issue. Some of our queries are not bringing any data because of the sensitive.. SELECT * FROM Country = 'jamaica' ... 0 Records SELECT * FROM Country = 'Jamaica' ... 10 Records Some cases we solved it using the ILIKE, but we still have some code on our app that cannot be modified. Does anybody know if there is any flag or setup or config for the Database in Monet to make it insensitive ? Thanks in advance for the help!

6 6

Support for multi-column aggregate functions
by Anderson, David B 30 Apr '16

30 Apr '16

All, Does the current version of MonetDB support multi-column aggregate functions? I need to create a function which returns the "quantiles" of one column using another column as a weighting. It looks like PostgreSQL supports multi-column aggregates as of 8.2. I could write an embedded R function to compute this, but want to know if it is even possible to pass in multiple columns. Thanks, Dave

4 9

回复：Re: SUM() OVER (ORDER BY..)
by chenkaijiang＠bigknow.com.cn 29 Apr '16

29 Apr '16

An alternative way is to use temp tables to get the same result: select c1, sum(c2) over(partition by c3) from t1; ==> create temp table tmp1 (sum_c2 int, c3 int); insert into tmp1 select sum(c2), c3 from t1 group by c3; select c1, sum_c2, c3 from t1 join tmp1 on t1.c3=tmp1.c3; ----- 原始邮件 ----- 发件人：Roberto Cornacchia <roberto.cornacchia(a)gmail.com> 收件人：Communication channel for MonetDB users <users-list(a)monetdb.org> 主题：Re: SUM() OVER (ORDER BY..) 日期：2016-4-29 15:46:57 No, indeed, this syntax is not supported. On 29 April 2016 at 09:41, Anthony Damico <ajdamico(a)gmail.com> wrote: > hmm, that gives me the same error.. > > SELECT mpg, wt, SUM(wt) OVER (PARTITION BY hp) AS cum_wgt, SUM(wt) over () > AS tot_wgt, hp FROM mtcars; > > > Error in .local(conn, statement, ...) : > Unable to execute statement 'SELECT > mpg, > wt, > SUM(wt) OVER (PARTITION BY hp) AS cum_wgt, > SUM(wt) over () AS tot_wgt, > hp > FROM mtc...'. > Server says 'ParseException:SQLparser:SELECT: function 'sum' not found'. > > > sorry if i'm doing something silly.. > > > > > On Fri, Apr 29, 2016 at 3:37 AM, Roberto Cornacchia < > roberto.cornacchia(a)gmail.com> wrote: > >> I think what you are looking for is SUM(..) OVER(PARTITION BY ..) >> >> Otherwise where would the groups for the sum come from? >> >> Roberto >> >> On 29 April 2016 at 09:28, Anthony Damico <ajdamico(a)gmail.com> wrote: >> >>> hi, monetdb does not support SUM() OVER commands.. does anyone have a >>> smart alternative to implement this? thanks >>> >>> >>> SELECT >>> batch_id, >>> job_count, >>> SUM(job_count) OVER (ORDER BY duration) as cumjobs, >>> SUM(job_count) over () as totjobs, >>> duration >>> FROM >>> test_data ; >>> >>> >>> _______________________________________________ >>> users-list mailing list >>> users-list(a)monetdb.org >>> https://www.monetdb.org/mailman/listinfo/users-list >>> >>> >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> >> > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > > _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

The Meaning of Constants in mapi.h
by Brian Hood 28 Apr '16

28 Apr '16

Hi Guys, #define MOK 0 #define MERROR (-1) #define MTIMEOUT (-2) #define MMORE (-3) #define MSERVER (-4) So far i've discovered MOK with value of 0 means connected to MServer and your query went well. MSERVER with value of -4 means that the server doesn't like the query and its bad SQL. I've not been able to discover what the others mean hope someone can help. I've also discovered that mapi_start_talking no longer seems to be implemented in the library guessing its deprecated. Regards, Brian Hood

1 0