users-list October 2012

users-list@monetdb.org

22 participants
27 discussions

[MonetDB-users] Different results with same query

by Matthieu Guamis

Hello, Running the same query on the same data leeds to different results between postgresql and monetdb. I have not been able to get a simple example to reproduce the problem. So here is what I observe: the query: select temps_mois.rfoperdmo as c1, sum((case when dwhinv.dwhinv___rfodomide = 'RH' and dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' then dwhinv.dwhinvqte else 0 end)) as m0 from rfoper_temps_mois as temps_mois, dwhinv as dwhinv, rfovsn as rfovsn_0, rrhamv as rrhamv_1, rrhcov as rrhcov_2, rfoadv as rfoadv_3 where temps_mois.rfoper___rforefide = 'HPLUS' and dwhinv.dwhinv___rforefide = 'HPLUS' and dwhinv.dwhinv___rfodomide = 'RH' and dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' and dwhinv.dwhinvdtd = temps_mois.rfoperdtd and temps_mois.rfoperyea = '2011' and rfovsn___rforefide = 'HPLUS' and dwhinv.dwhinv___rfovsnide = rfovsn_0.rfovsnide and rfovsn_0.rfovsnide = '201111_reel' and rrhamv_1.rrhamv___rrhvemide='GRACOR' AND rrhamv_1.rrhamvrvs=1 AND rrhamv_1.rrhamv___rforefide= 'HPLUS' and dwhinv.dwhinv___rrhempide = rrhamv_1.rrhamvinf and rrhamv_1.rrhamvsup = 'CEMP' and rrhcov_2.rrhcov___rrhvcoide='CONTRATS' AND rrhcov_2.rrhcovrvs=1 AND rrhcov_2.rrhcov___rforefide= 'HPLUS' and dwhinv.dwhinv___rrhcntide = rrhcov_2.rrhcovinf and rrhcov_2.rrhcovsup = 'CONTRATS' and rfoadv_3.rfoadv___rfovdeide='STRC' AND rfoadv_3.rfoadvrvs=1 AND rfoadv_3.rfoadv___rforefide= 'HPLUS' and dwhinv.dwhinv_p2rfodstide = rfoadv_3.rfoadvinf and rfoadv_3.rfoadvsup = 'HPLUS' group by c1 order by c1 Postgresql result: c1 | m0 ----+----------------- 01 | 7111.5376967750 02 | 7100.9108821426 03 | 7150.2597967742 04 | 7151.4283666667 05 | 7109.1641451610 06 | 6976.2108421239 07 | 6329.7404193564 08 | 6302.9823032247 09 | 6426.9459633351 10 | 6519.6889580648 11 | 6549.5235033402 12 | 6492.5477161292 (12 lignes) Monetdb result: +------+----------------------+ | c1 | m0 | +======+======================+ | 12 | 1.000000 | | 11 | 1.000000 | | 10 | 2.000000 | | 09 | 0.366667 | | 08 | 1.000000 | | 07 | 1.000000 | | 06 | 1.000000 | | 05 | 3.000000 | | 04 | 4.000000 | | 03 | 15.000000 | | 02 | 9.964286 | | 01 | 1.000000 | +------+----------------------+ 12 tuples (212.346ms) As you see, all "m0" values are very different. I have no idea of what is causing this but if I modify the query in order to simplify the CASE...WHEN...ELSE part of the select, it produce the expected result: replacing sum((case when dwhinv.dwhinv___rfodomide = 'RH' and dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' then dwhinv.dwhinvqte else 0 end)) as m0 by sum((case when dwhinv.dwhinv___rfoindide = 'tech_rh_effectif_rem_reel' then dwhinv.dwhinvqte else 0 end)) as m0 Its not exactly the same query but it must leeds to the same result due to my test datas (its the case in postgres) I join to this email two files with the TRACES, respectively for the request with bad/correct result (ko.txt/ok.txt) Config: Ubuntu Server 11.04 x64 MonetDB v11.7.9 (Dec2011-SP2), MonetDB Database Server v1.6 (Dec2011-SP2), MonetDB Database Server Toolkit v1.0 (Dec2011-SP2) Thank you very much for your help!! -- *Matthieu Guamis* *Logo Axège <http://www.axege.com/>* /Axège// 23,rue Saint-Simon 63000 Clermont-Ferrand/ Tél: +33 (0)4 63 05 95 40 Fax: +33 (0)4.73.70.65.29 Email: matthieu.guamis(a)axege.com <mailto:matthieu.guamis@axege.com>

9 years, 8 months

Arrows in the sql command line

by Masood Mortazavi

For some reason, when I use > mclient -u voc -d voc going into the sql command interface, I am unable to recall earlier sql commands using the arrow keys on the keyboard. Some other friends and colleagues can do this. What gives? Regards, Masood _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

9 years, 9 months

persistent MAL function

by Liwen -George- Zhang

Hi,A user MAL function created in one connection is destroyed when logoff. What's the correct way to persistent it? I finally want to create a SQL function based on this MAL function.For example: msql>function user.malhistogram(db:str,tablename:str, columnname:str):bat[:any_1,:int]; X_2 := sql.mvc(); X_3 := sql.bind(X_2,db,tablename,columnname,0); return aggr.histogram(X_3); end malhistogram; msql>h := malhistogram("voc","voyages","trip"); msql>io.print(h); #-----------------# # h t # name # int int # type #-----------------# [ 1, 3038 ] [ 2, 1907 ] [ 3, 1288 ] [ 4, 852 ] ... thanks a lot. George Zhang _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

9 years, 9 months

#GDKrealloc(21474836496) fails when try TCP-H benchmark with scale factor 100 on 64 bit ubuntun

by joe cooper

Hi, I got #GDKrealloc(21474836496) fails error when I tried to run TPC-H benchmark with a scale factor 100. The error happened during loading tables. Here are information about my configuration: The monetdb version used: changeset: 45737:bfd558546498 tag: tip user: Martin Kersten <mk(a)cwi.nl> date: Thu Oct 25 21:55:38 2012 +0200 summary: Add missing variable. The machine used: 64 bit Ubuntu natty With 7.806 GiB available main-memory. Compiler option used: 64 bit OIDs used Runtime options used: /home/borgdb/monetdb/install/bin/mserver5 --set gdk_dbfarm=/home/borgdb/monetdb/dbfarm --dbname=tpch100 --set merovingian_uri=mapi:monetdb://sc-borgdb6:50000/tpch100 --set mapi_open=false --set mapi_port=0 --set mapi_usock=/home/borgdb/monetdb/dbfarm/tpch100/.mapi.sock --set monet_vault_key=/home/borgdb/monetdb/dbfarm/tpch100/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes I am new to monetdb, so I am not sure if that is a known issue. If it is a known issue, my question is: on a 64 bit machine with 7.8GiB memory, how big a scale factor(TPC-H benchmark) can be handled by monetdb with 64 bit OID? If there any runtime option that enables monetdb to handle a big scale factor when run TPC-H benchmark? On my configuration, I succeeded in running TPC-H with scale factor 10, but failed with every scale factor >= 15. Any comments will be greatly appreciated, Thanks, Joe _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

9 years, 9 months

Funnel deployment

by Tapomay Dey

Do the funnel/"monetdb discover" features rely on UDP broadcast. I see there is no option to specify IP address-port while configuring a funnel. Also UDP broadcast is not so conducive to security in networks with large number of nodes. Is there any way discover and funnel features would work if there are VLANs on the switches that prevent UDP discovery. Regards, Tapomay. _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

9 years, 9 months

log file not found after a crash

by Medha Atre

Hi, I am running MonetDB v. 11.9.7. I have a large RDF dataset loaded in it (800+ million triples) as separate predicate tables for each unique predicate. I am running some simple one join SPAQRL queries ("?s :p1 ?o JOIN ?o :p2 ?x" kind) in bulk through a shell script (echo "<some query>" | mclient -drdf -lsql >> results). Sometime overnight when the queries were running, an error occurred in the database run, and it crashed by spitting out messages "monetdbd: an internal error has occurred, refer to the logs for details, please try again later" and "monetdbd: internal error while starting mserver, please refer to the logs" for the rest of the queries after some query caused this error. But when I tried to open "<Path To MonetDB installation>/var/log/monetdb/merovingian.log", it said "no such file or directory". I restarted the server and the particular database. It shows me its status as "health 97%" and "crash <crash date and time>". I tried to find the logfile again, but it's not there. I am wondering how I can know what the problem was. _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

9 years, 9 months

Re: PHP Connectivity

by Jeremy O'Sullivan

Hi Fabian, I changed the version of PHP from 5.4.4 to 5.2.17 and it worked. Thanks, Jeremy _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

9 years, 9 months

MAPI - check if connection is usable

by Tapomay Dey

I am using MAPI C library to talk to multiple monetdb databases. I cache the connection (struct mapi) to reduce overhead. However if a database/machine restarts then the connection is not usable. How do I determine if a cached connection is usable before actually using it. I have tried mapi_is_connected() and mapi_ping(). But mapi_ping always returns 0 while mapi_is_connected always returns 1. Thansk and Regards, Tapomay. _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

9 years, 9 months

PHP Connectivity

by Jeremy O'Sullivan

Hello, I have some issues connecting to monetDB, using PHP, see below. ==== <?php require '/usr/local/monetdb/share/php/monetdb/php_monetdb.php'; $db = monetdb_connect("sql", "localhost", 50000, "monetdb", "monetdb", "wifi_db"); $result = monetdb_query('SELECT count(*) FROM Summary_Stats'); ?> ==== I get the following error message in the browser: Fatal error: Call-time pass-by-reference has been removed in /usr/local/monetdb/share/php/monetdb/php_monetdb.php on line 302 Has anyone seen this error ? Regards, Jeremy _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

9 years, 9 months

how to load a table in MAL client

by Liwen -George- Zhang

Hi, When using monetdb, one questions that I need your suggestions is how to load a table column to MAL client and manipulate it.For example,sql>create table gzhang_test (intcol1 int,varcharcol1 varchar(50));sql>insert into gzhang_test values (1,'value1'); sql>insert into gzhang_test values (2,'value2');I know how to load a BAT by name in MAL, but for this table, I don't know how to get the name.If I do:mal>X_3 := sql.mvc();mal>X_4:bat[:oid,:int] := sql.bind(X_3,"midas","gzhang_test","intcol1",0); mal>io.print(X_4);I will get ERROR = !SQLException:mvc:SQL module not initialized. Should I load the SQL module in a MAL session, what's the correct way to get the column if not? Thanks,George Zhang _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

9 years, 9 months

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

2005

2004

users-list October 2012