- users-list - monetdb.org

question about arrays in SciQL
by Gabor Urbanics 03 May '13

03 May '13

Hi All, I have a question about MonetDB/SciQL, in particular regards to the unbounded arrays and their updates. I've created two arrays (one bounded and an unbounded) essentially with the same schemata in my database as follows: """ sql>\d ARRAY sys.facts ARRAY sys.facts_bounded sql>\d sys.source CREATE TABLE "sys"."source" ( "x" INTEGER, "y" INTEGER, "v" VARCHAR(32) ); sql>\d sys.facts CREATE ARRAY "sys"."facts" ( "x" INTEGER DIMENSION [0:1:1], "y" INTEGER DIMENSION [0:1:*], "v" VARCHAR(32) ); """ My goal at the moment is to use the unbounded array as it would fit very well the domain of my application: several coordinates-like columns and a couple of attributes, but the ranges of the coordinates are not known beforehand. Unfortunately, I am facing some problems when I try to insert values into the unbounded array: """ sql>insert into sys.facts values (0,0,'42'); 0 affected rows (0.938ms) """ On the other hand, the same insert operation works just fine with the bounded array: """ sql>insert into sys.facts_bounded values (0,0,'42'); 1 affected row (20.594ms) sql>select * from sys.facts_bounded; +------+------+------+ | x | y | v | +======+======+======+ | 0 | 0 | 42 | | 0 | 1 | null | ... (5 lines omitted for brevity) | 1 | 3 | null | +------+------+------+ 8 tuples (0.612ms) """ I would like to know if this is the supposed way one can insert values into unbounded arrays or I am doing something wrong. I compiled the source from the branch "sciql" from the mercurial repository 2 days ago, as my understanding was that the built versions do not support SciQL. Is there any other way I can get a MonetDB with SciQL support? If it helps anything, the beginning of the welcome message of the mserver5 is as follows: """ # MonetDB 5 server v11.16.0 # This is an unreleased version # Serving database 'monetdb5', using 4 threads # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked """ Any help or suggestion is appreciated! Best regards, Gabor

2 3

column alias combined with ORDER BY returns wrong data
by Budulinku Dejmihrasku 03 May '13

03 May '13

Hi, please note the test cases below - it should be self-explanatory i am on MonetDB v11.15.7 (Feb2013-SP2), i run the test before the upgrade with the same results please advice thanks a lot milan *test case 1, no ORDER BY - correct** **----------------------------------* WITH SAWITH0 AS ( select 'a' as c1, 'b' as c2, 'c' as c3, 1 as c4 ) select 0 as c1, D1.c1 as c2, D1.c2 as c3, D1.c3 as c4, D1.c4 as c5 from SAWITH0 D1 +------+------+------+------+------+ | c1 | c2 | c3 | c4 | c5 | +======+======+======+======+======+ | 0 | a | b | c | 1 | +------+------+------+------+------+ *test case 2, add ORDER BY - wrong result (repeats the 1st value for all columns)* *--------------------------------------------------------------------------------* WITH SAWITH0 AS ( select 'a' as c1, 'b' as c2, 'c' as c3, 1 as c4 ) select 0 as c1, D1.c1 as c2, D1.c2 as c3, D1.c3 as c4, D1.c4 as c5 from SAWITH0 D1 order by 5, 4, 3, 2 --order by c5,c4,c3,c2 +------+------+------+------+------+ | c1 | c2 | c3 | c4 | c5 | +======+======+======+======+======+ | 0 | a | a | a | a | +------+------+------+------+------+ Note that the result is the same even if you use the column alias instead of the ord.num. * **test case 3, use completely different alias with order by - correct ** **-------------------------------------------------------------------* WITH SAWITH0 AS ( select 'a' as c1, 'b' as c2, 'c' as c3, 1 as c4 ) select 0 as c1, D1.c1 as a2, D1.c2 as a3, D1.c3 as a4, D1.c4 as a5 from SAWITH0 D1 order by 5, 4, 3, 2 +------+------+------+------+------+ | c1 | a2 | a3 | a4 | a5 | +======+======+======+======+======+ | 0 | a | b | c | 1 | +------+------+------+------+------+

4 6

Re: should I be able to CAST a BOOLEAN type to a DOUBLE ( zero-one ) variable?
by Stefan Manegold 02 May '13

02 May '13

cast syntax is "cast( <expression> as type )" Anthony Damico <ajdamico(a)gmail.com> wrote: I tried lots of different things (pasted below) and none of them worked. I ended up using a CASE statement which worked around the issue, but I would've thought something like the command.. SELECT ( ( COLNAME > 0 )*1.000 ) AS ANY_VALUE FROM TABLENAME ; ..should give a valid response instead of an error? But I'm not sure if MonetDB wants to support this type of CAST/CONVERT operation? (None of the things I tried worked, not just the on-the-fly multiplication by 1.000). Using Feb2013-SP2 on Windows x86_64 If MonetDB should be able to convert TRUE/FALSE values to numerics, I'll submit a bug report with some PLAN and EXPLAIN results? :) Thanks for the great software!! sql>select ( mpg > 10 ) from mtcars ; +-------+ | >_mpg | +=======+ | null | | null | | true | | null | | true | | null | | true | | true | | true | | null | | null | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | null | | true | | true | +-------+ 32 tuples (15.010ms) sql>select ( mpg > 10 )*1.000 from mtcars ; TypeException:user.s11_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s11_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg > 10 )*1.000 ) from mtcars ; TypeException:user.s12_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s12_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg >= 10 )*1.000 ) from mtcars ; TypeException:user.s13_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s13_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg >= 10 ) ) from mtcars ; +--------+ | >=_mpg | +========+ | null | | null | | true | | null | | true | | null | | true | | true | | true | | null | | null | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | null | | true | | true | +--------+ 32 tuples (103.019ms) sql>select CAST( ( mpg >= 10 ) ) as DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( ( mpg >= 10 ) )" sql>select CAST( ( mpg >= 10 ) ) AS DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( ( mpg >= 10 ) )" sql>select CAST( mpg >= 10 ) AS DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( mpg >= 10 )" sql>select ( CAST( mpg >= 10 ) AS DOUBLE ) from mtcars ; syntax error, unexpected ')', expecting AS in: "select ( cast( mpg >= 10 )" sql>select ( CAST( mpg >= 10 AS DOUBLE ) from mtcars ; syntax error, unexpected FROM, expecting ')' in: "select ( cast( mpg >= 10 as do uble ) from" sql>select ( CAST( mpg >= 10 AS DOUBLE ) as x from mtcars ; syntax error, unexpected AS, expecting ')' in: "select ( cast( mpg >= 10 as doub le ) as" sql>select ( CAST( mpg >= 10 ) AS DOUBLE as x from mtcars ; syntax error, unexpected ')', expecting AS in: "select ( cast( mpg >= 10 )" sql>select CONVERT( double , mpg > 10 ) as x from mtcars; syntax error, unexpected sqlDOUBLE in: "select convert( double" sql>select CAST( TRUE as double ) from mtcars ; types boolean(1,0) and double(53,0) are not equal sql>select CAST( TRUE*1.000 as double ) from mtcars ; TypeException:user.s15_2[11]:'calc.sht' undefined in: _21:any := calc.sht(_20:bi t, _22:int, _23:int) program contains errors sql>select CAST( 'TRUE' as bit ) as z from mtcars; type (bit) unknown in: "select cast( 'TRUE' as bit )" syntax error, unexpected AS in: "as" sql>select CAST( TRUE as bit ) as z from mtcars; type (bit) unknown in: "select cast( true as bit )" syntax error, unexpected AS in: "as" sql>select CAST( TRUE as double ) as z from mtcars; types boolean(1,0) and double(53,0) are not equal sql>

3 3

should I be able to CAST a BOOLEAN type to a DOUBLE ( zero-one ) variable?
by Anthony Damico 02 May '13

02 May '13

I tried lots of different things (pasted below) and none of them worked. I ended up using a CASE statement which worked around the issue, but I would've thought something like the command.. SELECT ( ( COLNAME > 0 )*1.000 ) AS ANY_VALUE FROM TABLENAME ; ..should give a valid response instead of an error? But I'm not sure if MonetDB wants to support this type of CAST/CONVERT operation? (None of the things I tried worked, not just the on-the-fly multiplication by 1.000). Using Feb2013-SP2 on Windows x86_64 If MonetDB should be able to convert TRUE/FALSE values to numerics, I'll submit a bug report with some PLAN and EXPLAIN results? :) Thanks for the great software!! sql>select ( mpg > 10 ) from mtcars ; +-------+ | >_mpg | +=======+ | null | | null | | true | | null | | true | | null | | true | | true | | true | | null | | null | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | null | | true | | true | +-------+ 32 tuples (15.010ms) sql>select ( mpg > 10 )*1.000 from mtcars ; TypeException:user.s11_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s11_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg > 10 )*1.000 ) from mtcars ; TypeException:user.s12_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s12_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg >= 10 )*1.000 ) from mtcars ; TypeException:user.s13_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s13_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg >= 10 ) ) from mtcars ; +--------+ | >=_mpg | +========+ | null | | null | | true | | null | | true | | null | | true | | true | | true | | null | | null | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | null | | true | | true | +--------+ 32 tuples (103.019ms) sql>select CAST( ( mpg >= 10 ) ) as DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( ( mpg >= 10 ) )" sql>select CAST( ( mpg >= 10 ) ) AS DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( ( mpg >= 10 ) )" sql>select CAST( mpg >= 10 ) AS DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( mpg >= 10 )" sql>select ( CAST( mpg >= 10 ) AS DOUBLE ) from mtcars ; syntax error, unexpected ')', expecting AS in: "select ( cast( mpg >= 10 )" sql>select ( CAST( mpg >= 10 AS DOUBLE ) from mtcars ; syntax error, unexpected FROM, expecting ')' in: "select ( cast( mpg >= 10 as do uble ) from" sql>select ( CAST( mpg >= 10 AS DOUBLE ) as x from mtcars ; syntax error, unexpected AS, expecting ')' in: "select ( cast( mpg >= 10 as doub le ) as" sql>select ( CAST( mpg >= 10 ) AS DOUBLE as x from mtcars ; syntax error, unexpected ')', expecting AS in: "select ( cast( mpg >= 10 )" sql>select CONVERT( double , mpg > 10 ) as x from mtcars; syntax error, unexpected sqlDOUBLE in: "select convert( double" sql>select CAST( TRUE as double ) from mtcars ; types boolean(1,0) and double(53,0) are not equal sql>select CAST( TRUE*1.000 as double ) from mtcars ; TypeException:user.s15_2[11]:'calc.sht' undefined in: _21:any := calc.sht(_20:bi t, _22:int, _23:int) program contains errors sql>select CAST( 'TRUE' as bit ) as z from mtcars; type (bit) unknown in: "select cast( 'TRUE' as bit )" syntax error, unexpected AS in: "as" sql>select CAST( TRUE as bit ) as z from mtcars; type (bit) unknown in: "select cast( true as bit )" syntax error, unexpected AS in: "as" sql>select CAST( TRUE as double ) as z from mtcars; types boolean(1,0) and double(53,0) are not equal sql>

1 0

Understanding strides in memory accesses
by Hassan, Ahmad 01 May '13

01 May '13

Hi All, I am analysing the monetdb memory access pattern to identify various strides in LLC accesses. But most of the accesses per object are being observed as non-strided which is not what I expected. As monetdb involves BATs and these are arrays so I was thinking that there would be more strided pattern rather than random accesses unless BAT arrays are being treated as trees and random access is being used to access the items. To understand it further, I have now retrieved the execution plan of TPCH Q1 and the plan has various segments like follows: X_455:bat[:oid,:str] := sql.bind(X_7,"sys","lineitem","l_linestatus",2,5,16); X_1346 := algebra.kdifference(X_400,X_455); X_1379 := algebra.kunion(X_1346,X_455); X_1412 := algebra.leftjoin(X_1180,X_1379); (X_1452,X_1453) := group.multicolumns(X_1293,X_1412); X_1514 := bat.mirror(X_1452); X_1516 := algebra.leftjoin(X_1514,X_1412); I have two questions: 1) As there are many repeated segments like above in whole query plan, so what is the context behind multiple repeated segments please. 2) I want to understand what is the input to most common operators like leftjoin, kdifference, kunion and whether they process those inputs sequentially or randomly. What is the best way to get this understanding please? From the plan it is obvious that the input is the BAT(s), but how do the operators access the BAT is really a question. Thanks for the help. Kind Regards, Ahmad

3 3

Fwd: How to get deleted space back from monetdb
by Gaurav Meena 01 May '13

01 May '13

I have searched entire archive, and this question have been asked many times i.e. to get the space back from monetdb after deleting some rows in monetdb. The answer I found was not consistent one of them was COPY SELECT * FROM <table> INTO <file>; DELETE FROM <table>; COPY INTO <table> FROM <file>; Which suggest that deleting rows (or cleaning??) table will get back the space. One was from this question http://mail.monetdb.org/pipermail/users-list/2011-July/005036.html 1) copy all table contents to another table, drop old table, copy again I have tried both deleting rows and then droping the table but the result was inconsistant. May be monetdb cleans up after some interval Other Related Questions were 1. http://mail.monetdb.org/pipermail/users-list/2010-March/003952.html 2. FEB 23, 2010 Reeves, Matthew J. wrote: >* Hello,*>* *>* I have a MonetDB database that contains a table of rolling events.*>* There are 200 million records with 500,000 bulk loaded each day with the*>* oldest 500,000 deleted at midnight. I'm finding the database gets slower*>* as time goes on. It gets to the point of being almost unusable after a*>* week. Is there something I should be doing different?*Hello Matthew, Thanks for using MonetDB. Interesting application target. This is a known issue. The system keeps the deleted tuples and rebuilds the table. A vacuum-like feature is needed and on the wishlist. For now, taking a copy of the table would 'solve' your problem. regards, Martin >* *>* Thanks,*>* Matt*>* * Gaurav Meena

2 1

Inner join.
by Bouslah Ahmed 30 Apr '13

30 Apr '13

Hello, I'm working on MonetDB under win7, and I encountered some problems that hinder me 1 - I need to make an amendment on the inner join; 2 - To do this, I ask you to explain how the inner join works; 3 - In what part of the program (source code) is implemented the inner join. Cordially.

2 1

sql.clients function removed
by Pete Hollobon 29 Apr '13

29 Apr '13

Hi, It looks like the sql.clients() function was removed (changeset 45a4e2205363). I was wondering why, and whether there is a suitable alternative? Regards, Pete.

1 1

Announcement: New Feb2013-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 29 Apr '13

29 Apr '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013-SP2 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>.

1 0

All tested on the same column, some functions work, some don't?
by Anthony Damico 24 Apr '13

24 Apr '13

Hi, I'm using MonetDB on Windows with the MonetDB.R connector. I'm getting some very strange errors on a particular column (called `rx_cso_num`) where *some* of the math functions work and others do not? I tested the median() function using mclient - I get the same error - so this has nothing to do with the fact that I'm connecting through R. I'm running these queries on a confidential data set, so unfortunately I cannot provide a reproducible example with this.. I'm sorry for the hassle, and hoping the error messages below might provide enough of a clue to resolve the issue? If this really isn't enough, I can send some snipped mclient -X output and/or try my best to create some fake data that also creates some of these problems? Thanks!! :) Here's a list of functions that *work* on the column without a problem: "count","distinct","min", "max", "sum","avg","abs","sqrt","floor","ceiling","exp","log","cos","sin","tan","acos","asin","atan","cosh","sinh","tanh" Here's the errors I get for each of the functions that do not: [1] "median" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select median(rx_cso_num) from x07'. Server says '!22003!overflow in conversion of 133 to bte.'. [1] "sign" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select sign(rx_cso_num) from x07'. Server says '!TypeException:user.s5_1[25]:'bat.insert' undefined in: _93:any := bat.insert(_83:bat[:oid,:int], _91:oid, _90:bte)'. [1] "stddev_pop" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select stddev_pop(rx_cso_num) from x07'. Server says '!SELECT: no such unary operator 'stddev_pop(wrd)''. [1] "stddev" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select stddev(rx_cso_num) from x07'. Server says '!SELECT: no such unary operator 'stddev(wrd)''. [1] "prod" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select prod(rx_cso_num) from x07'. Server says '!22003!overflow in conversion of 133 to bte.'. Here's my mserver window: # MonetDB 5 server v11.15.3 "Feb2013-SP1" # Serving database 'medicare_sample', using 8 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.860 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:49800/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded > Thanks!!!

2 9