August 2012 - users-list

[MonetDB-users] case insensitive
by li, xiaohua 22 Aug '12

22 Aug '12

Does MonetDB has some parameter to modify the case insensitive option? when end user write sql, usually we want to case insensitive. lixiaohua Shanghai,China

2 3

[MonetDB-users] perl dbd error:
by Marc Salomon 20 Aug '12

20 Aug '12

Hello, I am able to install MonetDB-11.11.5 and set up a database just fine. However when I install the perl modules from CPAN, I encounter this error when I try to connect: 2012-08-20 09:02:54 ERR merovingian[56170]: client error: client localhost:35633 sent challenge in incomplete block: Any idea what's wrong here? Are the CPAN modules and the distro perl modules stepping on one another? best, -marc

2 1

[MonetDB-users] MonetDB GDK cache
by Allen Zhang 17 Aug '12

17 Aug '12

Hi, there, We noticed when query a big set of data, the BAT storage grows in the same directory as the database itself, is that mean the temporary result gets written to disk? Is it possible we can assign the location for this cache to point to a SSD to speed up things? Best Regards Allen

1 0

[MonetDB-users] Limit memory usage
by Christian Braun 09 Aug '12

09 Aug '12

Hello, i would like to limit the memory usage of MonetDB to half of the physical available memory. This is on Debian 6.0 64-bit with 48GB memory. I added to /etc/default/monetdb: ulimit -v 25165824 On startup MonetDB still reports: 2012-08-08 13:21:30 MSG db[15678]: # Found 47.262 GiB available main-memory. cat /proc/15687/limits shows the limit: Limit Soft Limit Hard Limit Units Max address space 25769803776 25769803776 bytes Unfortunately on the first query i run the database gets killed: 2012-08-08 13:21:54 MSG merovingian[15663]: database 'db' (15678) was killed by signal SIGSEGV Is there a way to limit the memory usage of MonetDB on Linux? Package: monetdb5-server Version: 11.11.5-20120710 Regards and thanks, Christian.

3 3

Re: [MonetDB-users] MonetDB Python client
by Минц Алексей 07 Aug '12

07 Aug '12

Dear Fabian! Attached is the code, that reproduces the bug. SQL file creates tables needed, python file hangs on trying to receive data. My version of mapi2.py is free of this problem - it raises an exception instead. Regards, Alexey Mints

1 0

[MonetDB-users] Incorrect Results from Very Simple Queries
by Henry Addington 07 Aug '12

07 Aug '12

We are seeing a very strange behavior from MonetDB from simple queries on a single table. In monthly_tou_avg_price table, we have monthly average aggregate data that were inserted from a very large table (> 2 billion) with hourly data. We have created the aggregate table to speed up certain queries. Strangely, we are getting incorrect results from very simple queries on the aggregate table. Q1 below returns 9,890 records with yyyy_mm column having empty value for the 2012-07 records. All of the 2012-07 records are missing the yyyy_mm value. We ran the SQL statement that inserted those records and it does not show any empty yyyy_mm values. If we check for empty string or null value in yyyy_mm column (Q2 and Q3) against the aggregate table, we do not get any hits. If we query for yyyy_mm= '2012-07' as in Q4, we get exactly 9890 records showing yyyy_mm populated with value 2012-07. How could this be possible, Q1 showing empty yyyy_mm values for the same 9,890 records and Q4 showing 2012-07 yyyy_mm values? Q1: select * from monthly_tou_avg_price where header_id = 6 Q2: select * from monthly_tou_avg_price where header_id = 6 and yyyy_mm = '' Q3: select * from monthly_tou_avg_price where header_id = 6 and yyyy_mm is null Q4: select * from monthly_tou_avg_price where header_id = 6 and yyyy_mm = '2012-07' Q5 below returns empty value for yyyy_mm even though yyyy_mm column does not have any empty or null values. Q6 does not return any empty or null yyyy_mm values and contradicts Q5 result by returning 2012-07. Furthermore, Q7 contradicts Q5 with 9,890 records with 2012-07 value in yyyy_mm column. Q5: select distinct yyyy_mm from monthly_tou_avg_price where header_id = 6 Q6: select distinct yyyy_mm from monthly_tou_avg_price Q7: select * from monthly_tou_avg_price where header_id = 6 and years = 2012 and months = 7 yyyy_mm L15 9890 2009-04 7574 2009-05 7574 2009-06 7750 2009-07 7786 2009-08 7788 2009-09 7828 2009-10 7814 2009-11 7844 2009-12 8322 2010-01 8317 2010-02 8363 2010-03 8361 2010-04 9214 2010-05 9124 2010-06 9156 2010-07 9165 2010-08 9195 2010-09 9197 2010-10 9198 2010-11 9234 2010-12 9232 2011-01 9246 2011-02 9238 2011-03 9696 2011-04 9672 2011-05 9670 2011-06 9668 2011-07 9656 2011-08 9698 2011-09 9682 2011-10 9724 2011-11 9706 2011-12 9768 2012-01 9766 2012-02 9764 2012-03 9832 2012-04 9824 2012-05 9826 2012-06 9902 We also ran an UPDATE statement to set yyyy_mm to 2012-07 for rows with header_id = 6 and years = 2012 and months = 7. And, 9,890 records were successfully updated. However, Q1 through Q8 returned same results as above with no improvement. We have not seen this kind of issues in other tables. This problem seems to be limited to a table that is populated with an INSERT INTO statement. We checked the result returned by the select portion of Q8 and yyyy_mm is populated with 2012-07. But, the aggregate table seems to end up in a weird state where it returns incorrect results. Q8: insert into "monthly_tou_avg_price" select a.header_id, extract(year from a.opr_date) as y, extract(month from a.opr_date) as m, b.yyyymm as yyyy_mm, b.svalue as TOU, a.resource_name, round(avg(a.lmp_cong_prc),5), round(avg(a.lmp_ene_prc),5), round(avg(a.lmp_loss_prc),5), round(avg(a.lmp_prc),5), round(avg(a.ns_clr_prc),5), round(avg(a.rd_clr_prc),5), round(avg(a.ru_clr_prc),5), round(avg(a.sp_clr_prc),5), round(avg(a.shadow_prc),5), round(avg(a.fuel_prc),5), count(lmp_cong_prc) as actual_lmpcong_tou_count from oasislive.price a join oasislive.time b on a.yyyymmddhh = b.yyyymmddhh where a.opr_date between '2012-07-01' and '2012-07-31' group by a.header_id, y, m, yyyy_mm, TOU, a.resource_name order by y, m, a.header_id, TOU, a.resource_name; We can see this INSERT INTO related issue with other queries. Q9 shows that we do not have any values for 2011-03 ON period which is incorrect. In fact, if we specify '2011-03' as in Q10, it shows that we do in fact have values for the 2011-03 ON period! Q9: select yyyy_mm, tou, avg(lmp_prc) from monthly_tou_avg_price group by yyyy_mm, tou order by yyyy_mm, tou yyyy_mm tou L6 . . 2011-03 OFF 16.721532797520894 2011-03 ON (null) 2011-04 OFF 15.427080074671187 . . Q10: select yyyy_mm, tou, avg(lmp_prc) from monthly_tou_avg_price where yyyy_mm = '2011-03' group by yyyy_mm, tou order by yyyy_mm, tou yyyy_mm tou L11 2011-03 OFF 16.72153279752101 2011-03 ON 30.276486575893575 I apologize for the long-winded email. I just wasn't sure how to describe what I was seeing.... Please let me know if there is any additional information that I can provide you with. By the way, we are currently using August 2011 -SP2 release, but saw this issue in older releases as well. Best regards, Henry We are currently running MonetDB August 2011 SP2 release on CentOS

2 4

[MonetDB-users] Trigger: BEFORE INSERT
by Tapomay Dey 02 Aug '12

02 Aug '12

create table tt(id int, name varchar(20), value int); create table t2(id int); CREATE TRIGGER trg BEFORE INSERT ON tt REFERENCING NEW as newrow FOR EACH ROW BEGIN ATOMIC INSERT INTO t2 (id) values (newrow.id); END insert into tt values(1, 'a', 10); insert into tt values(2, 'b', 20); insert into tt values(3, 'a', 30); insert into tt values(4, 'a', 40); The result vales in t2 are: After insert#1: EMPTY After insert#2: {1} After insert#3: {1, 1, 2} After insert#4: {1, 1, 2,1,2,3} Tested this in Mysql and the same trigger works well there. Expected t2 values after insert#4: {1,2,3,4} Regards, Tapomay.

1 1

[MonetDB-users] Monetdb compilation from source on 32 bit machine
by Tapomay Dey 01 Aug '12

01 Aug '12

I checked the code at location and found that "value" is of type long and its bit precision is 61. I am trying to perform make on an Ubuntu 10.04 i686 architecture which is 32-bit In file included from xid.c:23: xid.h:42: error: width of ‘value’ exceeds its type xid.c: In function ‘XIDcompress’: xid.c:138: warning: format ‘%ld’ expects type ‘long int’, but argument 4 has type ‘BUN’ xid.c:161: warning: format ‘%ld’ expects type ‘long int’, but argument 4 has type ‘BUN’ xid.c: In function ‘XIDcolumndump’: xid.c:298: warning: format ‘%lo’ expects type ‘long unsigned int’, but argument 4 has type ‘oid’ make[7]: *** [libmal_la-xid.lo] Error 1

2 1

[MonetDB-users] group by cube
by DADOU 01 Aug '12

01 Aug '12

hello, Have monetdb operator OLAP like group by cube as oracle have i don't find any operator from CUBE or ROLLUP. Thunks -- View this message in context: http://old.nabble.com/group-by-cube-tp34241097p34241097.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] Slowdown in Jul2012 release
by Viktor Rosenfeld 01 Aug '12

01 Aug '12

Hi, I've noticed a slowdown in the Jul2012 which appears to be due to disk I/O. Printed below is the output of TRACE on the Jul2012 (11.11.5) release and the Apr2012-SP2 release (11.9.7) of the following query: #+BEGIN_EXAMPLE WITH span1 AS ( SELECT node.id FROM node ), solutions AS ( SELECT DISTINCT span1.id AS id1 FROM span1 ) SELECT count(*) FROM solutions #+END_EXAMPLE Jul2012: #+BEGIN_EXAMPLE +---------+-----------------------------------------------------------------------------------+ | ticks | stmt | +---------+-----------------------------------------------------------------------------------+ | 3 | X_1 := sql.mvc(); | | 11 | X_2:bat[:oid,:int] =<tmp_1230>[1262014] := sql.bind(X_1=0,"annis","node","id",0); | | 5 | X_7:bat[:oid,:oid] =<tmp_1366>[0] := sql.bind_dbat(X_1=0,"annis","node",1); | | 6 | X_9=<tmpr_1366>[0] := bat.reverse(X_7=nil:bat[:oid,:oid]); | | 13 | X_10=<tmp_2126>[1262014] := algebra.kdifference(X_2=nil:bat[:oid,:int],X_9=nil); | | 3543662 | (ext22=<tmp_2127>[1262014],grp20) := group.done(X_10=<tmp_2126>[1262014]); | | 18 | X_13=<tmp_2130>[1262014] := bat.mirror(ext22=nil); | | 82193 | X_14=<tmp_2131>[1262014] := algebra.leftjoin(X_13=nil,X_10=nil); | | 2185 | X_15 := aggr.count(X_14=nil); | | 15 | sql.exportValue(1,"annis.solutions","L1","wrd",64,0,6,X_15=1262014:wrd,""); | | 8 | end s0_1; | | 18 | function user.s0_1(); | | 3629118 | X_5:void := user.s0_1(); | +---------+-----------------------------------------------------------------------------------+ #+END_EXAMPLE This query is accompanied by a lot of disk I/O (both reading and writing) Apr2012-SP2: #+BEGIN_EXAMPLE +-------+-----------------------------------------------------------------------------------+ | ticks | stmt | +-------+-----------------------------------------------------------------------------------+ | 4 | X_1 := sql.mvc(); | | 12 | X_2:bat[:oid,:int] =<tmp_1471>[1262014] := sql.bind(X_1=0,"annis","node","id",0); | | 6 | X_7:bat[:oid,:oid] =<tmp_1365>[0] := sql.bind_dbat(X_1=0,"annis","node",1); | | 6 | X_9=<tmpr_1365>[0] := bat.reverse(X_7=nil:bat[:oid,:oid]); | | 14 | X_10=<tmp_2074>[1262014] := algebra.kdifference(X_2=nil:bat[:oid,:int],X_9=nil); | | 12116 | (ext22=<tmp_2107>[1262014],grp20) := group.done(X_10=<tmp_2074>[1262014]); | | 2427 | X_13=<tmp_2106>[1262014] := bat.mirror(ext22=nil); | | 46 | X_14=<tmp_2111>[1262014] := algebra.leftjoin(X_13=nil,X_10=nil); | | 6 | X_15 := aggr.count(X_14=nil); | | 11 | sql.exportValue(1,"annis.solutions","L1","wrd",64,0,6,X_15=1262014:wrd,""); | | 6 | end s0_1; | | 16 | function user.s0_1(); | | 15566 | X_5:void := user.s0_1(); | +-------+-----------------------------------------------------------------------------------+ #+END_EXAMPLE There is no disk I/O during this query. Notice the difference in runtime of the group.done statement in line 5. This statement is introduced by the DISTINCT in the query. Cheers, Viktor

1 0