- users-list - monetdb.org

MonetDB on GitHub
by Roberto Cornacchia 27 Jul '20

27 Jul '20

Hi, I was wondering whether the MonetDB repositories on GitHub (or any git-based platform) are, in your intentions, there to stay. That's because I'm planning to move our own fork from Mercurial to git. This would come very easy by using the GitHub repositories as upstreams. My second question though is: the git repositories are at this moment about 10 days behind. Is there a reason? Will that be improved? Thanks! Roberto

2 2

Announcement: New Jun2020-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 27 Jul '20

27 Jul '20

The MonetDB team at MonetDB BV is pleased to announce the Jun2020-SP1 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <https://www.monetdb.org/>. For details on this release, please see the release notes at <https://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <https://www.monetdb.org/downloads/>. Jun 2020-SP1 bugfix release (11.37.11) MonetDB Common * Hash buckets come in variable widths. But if a BAT grows long enough so that the BAT indexes that are stored in the buckets don't fit anymore, the buckets need to be widened. This is now fixed. Bug Fixes * 6844: sys.getUser('https://me:pw@www.monetdb.org/Doc') does not return the user: me * 6845: the url sys.get...(url) functions do not allow null as a parameter * 6858: json.keyarray(json '{ "":0 }') fails with error: Could not allocate space * 6859: only first character of the separator string in json.text(js json, sep string) is used * 6873: sys.hot_snapshot() creates incomplete snapshots if the write-ahead log is very large * 6876: tar files created by sys.hot_snapshot() produce warnings on some implementations of tar * 6877: MonetDB produces malformed LZ4 files * 6880: Left fuzzy queries are much slower than other fuzzy queries. * 6882: cgroups limits no longer respected? * 6883: SQLancer crash on delete query * 6884: SQLancer generates query with unclear error message * 6885: SQLancer causes assertion error on UTF8_strlen * 6886: SQLancer alter table add unique gives strange error message * 6887: SQLancer crash on complex query * 6888: SQLancer crash on cross join on view * 6889: SQLancer crash on long query * 6892: SQLancer crash on query with HAVING * 6893: SQLancer inner join reporting GDK error * 6894: SQLancer crash on rtrim function * 6895: SQLancer causing 'algebra.select' undefined error * 6896: SQLancer algebra.select' undefined 2 * 6897: SQLancer distinct aggregate with error on group by constant * 6898: SQLancer crash on join query * 6899: SQLancer TLP query with wrong results * 6900: SQLancer generated SIGFPE * 6901: SQLancer TLP query with wrong results 2 * 6902: SQLancer query: batcalc.between undefined * 6903: SQLancer calc.abs undefined * 6904: SQLancer aggr.subavg undefined * 6905: SQLancer TLP query with wrong results 3 * 6906: SQLancer crash on complex join * 6907: SQLancer algebra.select undefined * 6908: SQLancer inputs not the same size * 6909: SQLancer query with wrong results * 6911: SQLancer query: 'calc.bit' undefined * 6917: Decimal parsing fails * 6918: SQLancer query compilation error * 6919: SQLancer insert function doesn't handle utf-8 strings * 6920: SQLancer project_bte: does not match always * 6922: Timestamp columns not migrated to new format * 6923: Imprints data files for timestamp BAT not migrated to the new format * 6924: SQLancer query copy on unique pair of columns fails and complex query with GDK error * 6925: Count string rows in union of string tables leaks (RSS) memory * 6926: SQLancer query with wrong results * 6927: SQLancer inputs not the same size * 6928: SQLancer crash on coalesce * 6929: SQLancer calc.date undefined * 6932: Syntax error while parsing JSON numbers with exponent * 6934: sys.isauuid() returns wrong answer for some invalid uuid strings

1 0

enhancements question
by ALEX OSSIPOV 25 Jul '20

25 Jul '20

Hi MonetDB Team I am very interested in pushing bug 6702 which is in fact an enhancement (was enhancement request) but seems to be more of a miss these days since all other competing products have it already implemented. What is decision process on prioritizing enhancements and how can a specific request be expedited/elevated? Love this product but can't survive without recursive support in modern analytics world. Regards Alex

2 1

MonetDb crashes on a join using date between from and until date
by Groot, Frank (Sociale Verzekeringsbank) 22 Jul '20

22 Jul '20

Hi all, We are using the June 2020 release, trying to query a table with 24 mio rows and join each row from this 'left' table with one row from a right table according to a specific join clause. This clause does not result in a carthesian product. The query structure is as follows: select left.* from myschema.mytable left inner join otherschema.yourtable right on left.somedatefield >= right.fromdate and left.somedatefield <= right.untildate where left.otherdatefield is null ; This query results in following error in the merovingian.log: 2020-07-17 11:58:00 MSG merovingian[401281]: database 'datalake' (-1) has crashed with signal SIGSEGV (dumped core) However, if I change the query like below, making the left table a derived table and sorting the date field used in the join, the error doesn't occur: select left.* from (select * from myschema.mytable order by somedatefield) left inner join otherschema.yourtable right on left.somedatefield >= right.fromdate and left.somedatefield <= right.untildate where left.otherdatefield is null ; When I tested the original query with a subset from the left table there was a threshold of a certain amount of rows (in this case 26 rows) below which the query ran fine. Also, repeating the test on a different 'left' table with ~200k rows didn't result in a crash. Could it be that there's been a change in the query pipeline or join handling? Kind regards, Frank

2 1

Partition pruning for merge tables
by Daniel Glöckner 22 Jul '20

22 Jul '20

Hi, looking at merge tables and the related query plans I was expecting the planner to skip tables depending on partition information and query predicates. I'm using the official example for merge tables: https://www.monetdb.org/blog/updatable-merge-tables CREATE MERGE TABLE splitted (stamp TIMESTAMP, val INT) PARTITION BY RANGE ON (stamp); CREATE TABLE first_decade (stamp TIMESTAMP, val INT); CREATE TABLE second_decade (stamp TIMESTAMP, val INT); CREATE TABLE third_decade (stamp TIMESTAMP, val INT); ALTER TABLE splitted ADD TABLE first_decade AS PARTITION FROM TIMESTAMP '2000-01-01 00:00:00' TO TIMESTAMP '2010-01-01 00:00:00'; ALTER TABLE splitted ADD TABLE second_decade AS PARTITION FROM TIMESTAMP '2010-01-01 00:00:00' TO TIMESTAMP '2020-01-01 00:00:00'; ALTER TABLE splitted ADD TABLE third_decade AS PARTITION FROM TIMESTAMP '2020-01-01 00:00:00' TO RANGE MAXVALUE WITH NULL VALUES; INSERT INTO splitted VALUES (TIMESTAMP '2000-01-01 00:00:00', 1), (TIMESTAMP '2002-12-03 20:00:00', 2), (TIMESTAMP '2012-05-12 21:01:00', 3), (TIMESTAMP '2019-12-12 23:59:59', 4); INSERT INTO splitted VALUES (TIMESTAMP '2020-01-01 00:00:00', 5), (NULL, 6); When I trigger the following query I was expecting that only table second_decade would be used as Monet would be smart enough to check the partition information for each table. Kind regards, Daniel sql>plan select * from splitted where stamp = TIMESTAMP '2020-01-01 00:00:00'; +--------------------------------------------------------------------------------------------------------------------------------------+ | rel | +======================================================================================================================================+ | union ( | | | union ( | | | | project ( | | | | | select ( | | | | | | table(sys.first_decade) [ "first_decade"."stamp" as "splitted"."stamp", "first_decade"."val" as "splitted"."val" ] COUNT | | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ] | | | | ) [ "splitted"."stamp", "splitted"."val" ], | | | | project ( | | | | | select ( | | | | | | table(sys.second_decade) [ "second_decade"."stamp" as "splitted"."stamp", "second_decade"."val" as "splitted"."val" ] COUNT | | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ] | | | | ) [ "splitted"."stamp", "splitted"."val" ] | | | ) [ "splitted"."stamp", "splitted"."val" ], | | | project ( | | | | select ( | | | | | table(sys.third_decade) [ "third_decade"."stamp" as "splitted"."stamp", "third_decade"."val" as "splitted"."val" ] COUNT | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ] | | | ) [ "splitted"."stamp", "splitted"."val" ] | | ) [ "splitted"."stamp", "splitted"."val" ] | +--------------------------------------------------------------------------------------------------------------------------------------+ 19 tuples clk: 2.428 ms

2 4

Re: Re: Implementing bulk export to Parquet
by Daniel Glöckner 20 Jul '20

20 Jul '20

Hi, thanks for the implementation hint with Arrow C++, Anton. I’ve tried a similar approach using Pyarrow () and initial tests look promising. NumPy arrays can be written to Parquet quite easily: https://www.mail-archive.com/dev@parquet.apache.org/msg04506.html <https://www.mail-archive.com/dev@parquet.apache.org/msg04506.html> Kind regards, Daniel

1 0

BUG on last version?
by Ariel Abadi 20 Jul '20

20 Jul '20

Hi! Maybe somebody can help me to understand if there is a bug on the latest version or if it is a better way to solve this issue. Linux Centos 7 Monetdb version 11.37.7 (Jun2020) I have created a new fresh database to avoid any issue on the upgrade, and imported all the data. The DB seems to be working fine. We are using creating some dynamic queries, so the final query we need to run is the following: select sum(nrm_realmin) as nrm_realmin from sb_traf.nrm_20200716 as t0 where (case when (nrm_tim_date || '@' || nrm_vep_id || '@' || nrm_rby_brn_id) in ( select distinct cast(curdate() - cast(value as interval day) as varchar(10)) || '@' || pbd_ven_id || '@' || pbd_deb_id as xx from sb_agre.agre_agr_agreement as agr join sb_agre.agre_prb_product_buy as prb on agr_id = prb_agr_id join sb_agre.agre_pbd_product_buydetail as psd on prb_id = pbd_prb_id left join sys.generate_series(0, 2) on (curdate() - cast(value as interval day)) between coalesce(prb_dstart, agr_dstart) and coalesce(prb_dend, agr_dend) and (curdate() - cast(value as interval day)) <= curdate() ) then 'BILAT' else 'AVAILABLE' end) not ilike '%BILAT%' I am using this exact query on some other versions and they are all working, but on this version the query keeps running with no answer, even worse, I tried to stop the query via CALL sys.stop(tag) ... and it never stops, I had to restart the database to stop that query. Does anybody have a clue how I can solve this issue? Thanks for the help in advance! Rgds Ariel <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam…> Virus-free. www.avg.com <http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_cam…> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

2 1

Issues with None values handling in Python UDFs returning TABLE
by Daniel Glöckner 20 Jul '20

20 Jul '20

Hi, when testing TABLE-returning UDFs, I noticed that there are some issues with handling of None values from Python. Is there any magic approach on how to return NULLS / None values from such a UDF? Please see my examples below which are all not working ;) Kind regards, Daniel # 1 Does not work - Henk is just repeated in the second row drop function if exists python_table; CREATE FUNCTION python_table() RETURNS TABLE(name STRING, country STRING, age INTEGER) LANGUAGE PYTHON { result = dict() result['name'] = ['Henk', None, 'Elizabeth'] result['country'] = ['NL', 'USA', 'UK'] result['age'] = [25, 30, 33] return result }; SELECT * FROM python_table() ; # 2 Also does not work - Henk is just repeated in the second row drop function if exists python_table; CREATE FUNCTION python_table() RETURNS TABLE(name STRING, country STRING, age INTEGER) LANGUAGE PYTHON { result = dict() names = numpy.empty(3, dtype=object) names[0] = 'Henk' names[2] = 'Elizabeth' result['name'] = names result['country'] = ['NL', 'USA', 'UK'] result['age'] = [25, 30, 33] return result }; SELECT * FROM python_table() ; # 3 Also does not work - Henk is just repeated in the second row drop function if exists python_table; CREATE FUNCTION python_table() RETURNS TABLE(name STRING, country STRING, age INTEGER) LANGUAGE PYTHON { result = dict() names = numpy.full_like(numpy.arange(3, dtype=object), None) #names[0] = 'Henk' names[2] = 'Elizabeth' result['name'] = names result['country'] = ['NL', 'USA', 'UK'] result['age'] = [25, 30, 33] return result }; SELECT * FROM python_table() ; # 4 breaks if all values for a column are None # "Error converting dict return value "name": An array of size 3 was returned, yet we expect a list of 1 columns. The result is invalid.. " drop function if exists python_table; CREATE FUNCTION python_table() RETURNS TABLE(name STRING, country STRING, age INTEGER) LANGUAGE PYTHON { result = dict() names = numpy.full_like(numpy.arange(3, dtype=object), None) result['name'] = names result['country'] = ['NL', 'USA', 'UK'] result['age'] = [25, 30, 33] return result }; SELECT * FROM python_table() ;

1 0

Implementing bulk export to Parquet
by Daniel Glöckner 19 Jul '20

19 Jul '20

Hi, the project which I'm involved in would require us to integrate MonetDB with "big data technologies" so exporting data from MonetDB to Parquet for further processing in the big data tools would be an obvious choice. Can anyone guide me what it would mean to extend MonetDB with a writer for Parquet? It would be awesome if COPY INTO would be able to produce Parquet efficiently, e.g. using Arrow library: https://arrow.apache.org/docs/cpp/parquet.html Kind regards, Daniel

3 2

MonetDB/e and R
by Mark Clements 17 Jul '20

17 Jul '20

It is exciting to see the release of MonetDB/e - congratulations. I have two questions to offer. First, has anyone begun work on an R package? As a follow-up question if not, which of MonetDB.R, MonetDBLite or DuckDB packages for R would be a better starting point? Second, could someone kindly explain the potential advantages and disadvantages of using either MonetDB/e or DuckDB, please? They both seem to come from CWI, so this is a little confusing. Sincerely, Mark. När du skickar e-post till Karolinska Institutet (KI) innebär detta att KI kommer att behandla dina personuppgifter. Här finns information om hur KI behandlar personuppgifter<https://ki.se/medarbetare/integritetsskyddspolicy>. Sending email to Karolinska Institutet (KI) will result in KI processing your personal data. You can read more about KI’s processing of personal data here<https://ki.se/en/staff/data-protection-policy>.

3 2