December 2017 - users-list

too many nested operators - Query size 4MB
by Sharma, Sreejith 16 Dec '17

16 Dec '17

All, Have a quick question- I have been trying to perform a test on bigger SQL and I see SQL's are failing which are above 4 MB with error "too many nested operators". There are many AND and OR operators. Not sure if there is any limitations. Regards, Sreejith

1 0

Announcement: New Jul2017-SP3 Bugfix release of MonetDB suite
by Sjoerd Mullender 14 Dec '17

14 Dec '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2017-SP3 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/>. Jul 2017-SP3 bugfix release (11.27.11) MonetDB Common * Reimplemented summing of a column of floating point (flt and dbl) values. The old code could give wildly inaccurate results when adding up lots and lots of values due to lack of precision. Try SELECT sum(c) FROM t; where t is 100,000,000 rows, c is of type REAL and all values are equal to 1.1. (The old code returned 33554432 instead of 1.1e8.) Bug Fixes * 3898: Deadlock on insertion * 6429: ROUND produces wrong data type * 6436: Query sequence with 2x ifthenelse() and next nullif() causes mserver5 Segmentation fault * 6437: System schemas "profiler" and "json" shouldn't be allowed to be dropped. * 6439: Invalid references to sys.columns.id from sys.statistics.column_id * 6442: SEGFAULT with COPY INTO BEST EFFORT and skipping input columns * 6443: complex(?) query forgets(?) column name * 6444: Using 'with' keyword with table returning function crashes monetdb * 6445: Sqlitelogictest crash in MySQL query * 6446: sql_parser.y bug? * 6448: 'insert into' with multiple rows containing subqueries crashes * 6449: Assertion error in rel_dce_refs (sqlsmith) * 6450: Assertion error in exp_bin (sqlsmith) * 6451: Assertion error in sql_ref_dec (sqlsmith) * 6453: Assertion error in rel_rename_exps (sqlsmith) * 6454: SQL lexical error * 6455: Assertion error in rel_apply_rewrite (sqlsmith) * 6456: NULL becomes 0 in outer join * 6459: Assertion error in exp_bin (sqlsmith) * 6462: large virtual memory spike on BLOB column select * 6465: appending to variables sized atom bats other than str bats with force flag may result in corrupted heap * 6467: date_to_str formatter is wrong * 6470: mitosis gets in the way of simple select * 6471: calls to sys.generate_series should auto-convert arguments * 6472: Assertion failure in rel_rename (Sqlsmith) * 6477: assertion eror rel_push_project_up (sqlsmith) * 6478: Crash with nested order by/ limit offset * 6479: Mserver receives an assertion error on a procedure call * 6480: Segfault in mvc_find_subexp (sqlsmith)

1 0

Excessive slowness in query
by Alfio Bettini 14 Dec '17

14 Dec '17

Hi all, we're testing MonetDB performances in order to understand if it could be possible to use it as data mart in our BI software. We're considering a set of queries executed in our test environment (CentOS 7, 32 GB RAM, 4 cpu). All the executed queries have quite good performances (between 10 and 45 seconds) except the following one that is taking several minutes in order to be executed: SELECT id, count(distinct value) FROM table GROUP BY id; The table contains 100 millions of data. It has about 130 columns (varchar, int, bigint, smallint, tinyint, timestamp, datetime). We have no BLOB/CLOB/Text types. Can you suggest us any optimizations in order to have this query run faster? Do you know some possible reasons why it's so slow? Kind Regards, Alfio Bettini The information contained in this message, together with any attachments, may be privileged and confidential and is intended for the use of the addressee(s) only. Any review, copying, use or distribution of this email by others (including any of its attachments) is strictly prohibited. If you are not the intended recipient, please destroy this email and its attachments and contact the sender immediately. This email was sent by EidosMedia SpA<http://www.eidosmedia.com>

1 0

Excessive slowness in a query
by Alfio Bettini 14 Dec '17

14 Dec '17

Hi all, we're testing MonetDB performances in order to understand if it could be possible to use it as data mart in our BI software. We're considering a set of queries executed in our test environment (CentOS 7, 32 GB RAM, 4 cpu). All the executed queries have quite good performances (between 10 and 45 seconds) except the following one that is taking several minutes in order to be executed: SELECT id, count(distinct value) FROM table GROUP BY id; The table contains 100 millions of data. It has about 130 columns (varchar, int, bigint, smallint, tinyint, timestamp, datetime). We have no BLOB/CLOB/Text types. The fields used in the query have these data types: id: int value: bigint Can you suggest us any optimizations in order to have this query run faster? Do you know some possible reasons why it's so slow? Kind Regards, Alfio Bettini The information contained in this message, together with any attachments, may be privileged and confidential and is intended for the use of the addressee(s) only. Any review, copying, use or distribution of this email by others (including any of its attachments) is strictly prohibited. If you are not the intended recipient, please destroy this email and its attachments and contact the sender immediately. This email was sent by EidosMedia SpA<http://www.eidosmedia.com>

1 0

Re: algebra.join heapextend crash
by Stefan Manegold 13 Dec '17

13 Dec '17

Allocating "only" about 2.8 GB does not ring alarms that the plan night be degenerated - assuming your data is not tiny. Could you share:OS, full query, data sizes, free disk space at time of failure, mserver5 process size at time of failure? Thanks,Stefan -------- Original message --------From: Anthony Damico <ajdamico(a)gmail.com> Date: 11/22/17 12:13 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: algebra.join heapextend crash hi, i'm using monetdblite-r 0.5.0 on a confidential dataset. my syntax worked on monetdblite 0.3.1 on the same machine ftruncate: Invalid argument Error in .local(conn, statement, ...) : Unable to execute statement 'CREATE TABLE ccaed123_b_svcmon_xwalk AS ( SELECT a.enrolid , a.yr , a.dataty...'. Server says 'MALException:algebra.join:GDK reported error. !ERROR: GDKextendf: c !ERROR: HEAPextend: failed to extend to 2890530816 for 02\57\25766.tail: GDKmre map() failed '. it's a local drive, the hard disk isn't close to full and the machine has 700gb of ram, but the table it's trying to create might be bigger than that. dunno if related to https://www.monetdb.org/bugzilla/show_bug.cgi?id=3791 thanks

2 4

MonetDB Blob Inserts
by Brian Hood 07 Dec '17

07 Dec '17

Hi Guys, I don't seem to be able to INSERT into a blob field is there any information how to achieve this. Regards, Brian Hood

3 6

What is better JSON or TEXT field in terms of performance ???
by Ariel Abadi 07 Dec '17

07 Dec '17

Hi All! Does anybody has worked with json fields? We are planning to use json fields with very large text on it (the hierarchy of our json would be plain (only one level), we are doing some tests over 7MM records, it took like 1m30s to retrieve the information. In this case to get the information we used the function json.filter(name_of_field, '$..xxxx') We have also tested using a TEXT field (and using some string functions) we parsed the information inside the field and we found that sometimes it was quicker it took from 1m30s to 35s. I dont know exactly why the difference on that delta. We need to implement the processes, and need to decide how to move forward.... Does anybody can help me on the decision ??? We know and is a fact, that Monet can beat any DB in terms of performance, and that is why we decided to use it, but now Im worried about this type of queries (against json/text fields) and how long this queries would take if we increase the amount of records.... What would you suggest ?? Thanks in advance! Rgds Ariel

3 3

mserver5 crash on startup in Ubuntu Xenial
by Manuel Pagliai 06 Dec '17

06 Dec '17

Hello, I get this error when I try to start monetdb on Ubuntu Xenial 16.04.03 mserver5 visokio # MonetDB 5 server v11.27.9 "Jul2017-SP2" # Serving database 'demo', using 12 threads # Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers # Found 7.702 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2017 MonetDB B.V., all rights reserved # Visit https://www.monetdb.org/ for further information !SyntaxException:parseError:include sqlcatalog; !SyntaxException:parseError:^LoaderException:loadLibrary:Loading error failed to open library sql (from within file '/usr/lib/monetdb5/lib_sql.so'): /usr/lib/monetdb5/lib_sql.so: undefined symbol: mnstr_writeFlt !SyntaxException:parseError:include sqlcatalog; !SyntaxException:parseError: ^';' expected Segmentation fault (core dumped) any ideas ? Thanks, Best regards, *Manuel Pagliai* Visokio Ltd www.visokio.com +44 20 7351 4646

1 0

Issue with SUM(DISTINCT) in 11.27.9
by Sharma, Sreejith 05 Dec '17

05 Dec '17

All, We have noticed when SQL pattern has SUM(DISTINCT... ) there is a degradation of SQL timings when compared to 11.27.5 build. Anyone is aware of this? Regards, Sreejith

1 0

Re: MAL query "sql.resultSet"
by dennis.fassl 01 Dec '17

01 Dec '17

Hi all, can you help me with the below query? Is there an other way to return query results using the jdbc interface (without using sql.resultSet)? Thanks in advance! Best regards,Dennis -------- Ursprüngliche Nachricht --------Von: Dennis Fassl <dennis.fassl(a)uni-konstanz.de> Datum: 25.11.17 16:55 (GMT+01:00) An: users-list(a)monetdb.org Betreff: MAL query "sql.resultSet" Hi all, I am trying to execute the MAL query below. But the command "sql.resultSet" is terminating mclient. When using the JDBC interface I get "SQLException: Connection to server lost! (mserver still alive?)". Do you have an idea why this is happening? Thanks and regards, Dennis sql.init(); table_names := bat.new(nil:str); bat.append(table_names, "t0"); bat.append(table_names, "t1"); table_attrs := bat.new(nil:str); bat.append(table_attrs, "int_col"); bat.append(table_attrs, "str_col"); table_types := bat.new(nil:str); bat.append(table_types, "int"); bat.append(table_types, "str"); table_lens := bat.new(nil:int); bat.append(table_lens, 1); bat.append(table_lens, 1); table_scales := bat.new(nil:int); bat.append(table_scales, 0); bat.append(table_scales, 0); ints := bat.new(nil:int); bat.append(ints, 42); bat.append(ints, 23); strs := bat.new(nil:str); bat.append(strs, "foo"); bat.append(strs, "bar"); sql.resultSet(table_names, table_attrs, table_types, table_lens, table_scales, ints, strs); _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

4 6