- users-list - monetdb.org

copy into failed to import table
by Colin Foss 27 Nov '13

27 Nov '13

I created a table with 43 columns. The table contains char, varchar, and tinyint datatypes. I am running on Windows Server 2008 R2 64bit. The file contains two rows and is comma separated. The file is BCP output from MSSQL. When I tried to bulk load into the table from a file with a COPY INTO command I got an unhelpful message. "failed to import table" How can I find out why the import failed?

2 1

Sum of nulls is 0
by Pedro Salgueiro 26 Nov '13

26 Nov '13

Hi, A few weeks ago we encountered an issue with monetdb when summing nulls, grouped by some column. We managed to create a simple test case which reproduces this scenario: create table test (number int, name varchar(50)); > > insert into test values(1,'jonh'); > insert into test values(2,'mike'); > insert into test values(3,'mary'); > insert into test values(null,'austin'); > insert into test values(null,'jonh'); > insert into test values(null,'mary'); > > select name, sum(number) > from test > group by name; The result of this query is: > austin | 0 > jonh | 1 > mary | 3 > mike | 2 But the expected result is: > austin | null > jonh | 1 > mary | 3 > mike | 2 The version that we are using is Feb2013-SP5. We found a bug report that could be related to this one, but we are not sure: http://bugs.monetdb.org/show_bug.cgi?id=3388 Should we open a new issue for this one? Thank you, Pedro Salgueiro

3 7

MonetDB with OpenCL externsion (Ocelot) query fails trying to cache an empty BAT
by Edward Ching 25 Nov '13

25 Nov '13

I'm running into a problme with Ocelot, the MonetDB with OpenCL extension. I apologize if this is not the right forum to post. I'm seeing a query failure which was caused by MoneDB-OpenCL trying to cache an empty BAT and ended up calling clCreateBuffer with a 0 size buffer and failed. Any select with a where clause would trigger it. Has anyone seen this too? It fails whether I use 32 bit or 64 bit oid, Nvidia or AMD. I'm trying to figure if it's a real issue or if I'm missing anything. Any input greatly appreciated. The database was generated by TPC-H dbgen (details below). The query I used was "select c_custkey, c_name from customer where c_custkey < 2", and here's a trace of the sequence of calls leading to the failure starting from the MAL statement: 1) X_52 := ocelot.thetasubselect(r1_10,A0,"<",0);(see log below) 2) ocl_select.c::ocl_subselect() line 142: ocl_cacheBAT 3) ocl_memmanager.c::ocl_cacheBAT() line 771: allocateBuffer 4) ocl_memmanager.c::allocateBuffer() line 432: clCreateBuffer ===== Source version and environment ===== 1) Ocelot src installed from zip downloaded from monetdb-opencl: msaecker-monetdb-opencl-b513b35eb84a.zip 2) Ocelot configure options: ./configure --prefix=/usr/local --enable-oid32 --enable-optimize --disable-test ing --disable-developer --disable-debug --disable-assert --enable-sql --enable-m onetdb5 --with-opencl=/usr 3) Operating System: Fedora 17 kernel 3.9.10-100 4) Hardware: NVIDIA GTX570 on Intel i7-3820 w/ 16GB mem 5) OpenCL: Nvidia OpenCL that came with CUDA 5.5 SDK 6) Database: generated w/ TPC-H dbgen using size factor s=0.01 ===== operation log follows ===== [root@gen3-2 ocelot]# mclient -ddb001 -lmal mal>ocelot.init(); mal>ocelot.listDevices(); # Device 0: NVIDIA Corporation - GeForce GTX 570 (default device) mal>\q [root@gen3-2 ocelot]# mclient -ddb001 -lsql sql>\d TABLE sys.customer TABLE sys.lineitem TABLE sys.nation TABLE sys.orders TABLE sys.part TABLE sys.partsupp TABLE sys.region TABLE sys.supplier sql>\d customer CREATE TABLE "sys"."customer" ( "c_custkey" INTEGER NOT NULL, "c_name" VARCHAR(25) NOT NULL, "c_address" VARCHAR(40) NOT NULL, "c_nationkey" INTEGER NOT NULL, "c_phone" VARCHAR(15) NOT NULL, "c_acctbal" DECIMAL(15,2) NOT NULL, "c_mktsegment" VARCHAR(10) NOT NULL, "c_comment" VARCHAR(117) NOT NULL, CONSTRAINT "customer_c_custkey_pkey" PRIMARY KEY ("c_custkey"), CONSTRAINT "customer_c_nationkey_fkey" FOREIGN KEY ("c_nationkey") REFERENCES "sys"."nation" ("n_nationkey") ); sql>select count(*) from customer; +------+ | L1 | +======+ | 1500 | +------+ 1 tuple (1.311ms) sql>select c_custkey, c_name from customer where c_custkey < 2; +-----------+--------------------+ | c_custkey | c_name | +===========+====================+ | 1 | Customer#000000001 | +-----------+--------------------+ 1 tuple (1.776ms) sql>set optimizer='cl_pipe'; operation successful (0.981ms) sql>select c_custkey, c_name from customer where c_custkey < 2; invalid/unknown response from server, ignoring output ? #Could not allocate OpenCL buffer of size: 0. Error caching input BAT. sql> sql>explain select c_custkey, c_name from customer where c_custkey < 2; +------------------------------------------------------------------------------+ | mal +==============================================================================+ | function user.s9_1{autoCommit=true}(A0:int):void; | | ocelot.beginQuery(); | | X_3 := sql.mvc(); | | X_7 := sql.bind(X_3,"sys","customer","c_custkey",0); | | X_4:bat[:oid,:oid] := sql.tid(X_3,"sys","customer"); | | X_51 := ocelot.thetasubselect(X_7,X_4,A0,"<",0); | | (X_10,r1_10) := sql.bind(X_3,"sys","customer","c_custkey",2); | | X_52 := ocelot.thetasubselect(r1_10,A0,"<",0); | | X_13 := sql.bind(X_3,"sys","customer","c_custkey",1); | | X_53 := ocelot.thetasubselect(X_13,X_4,A0,"<",0); | | ocelot.sync(X_51); | | ocelot.sync(X_52); | | ocelot.sync(X_53); | | X_15 := sql.subdelta(X_51,X_4,X_10,X_52,X_53); | | X_17 := sql.projectdelta(X_15,X_7,X_10,r1_10,X_13); | | X_18 := sql.bind(X_3,"sys","customer","c_name",0); | | (X_20,r1_23) := sql.bind(X_3,"sys","customer","c_name",2); | | X_22 := sql.bind(X_3,"sys","customer","c_name",1); | | X_23 := sql.projectdelta(X_15,X_18,X_20,r1_23,X_22); | | X_24 := sql.resultSet(2,1,X_17); | | sql.rsColumn(X_24,"sys.customer","c_custkey","int",32,0,X_17); | | sql.rsColumn(X_24,"sys.customer","c_name","varchar",25,0,X_23); | | X_32 := io.stdout(); | | sql.exportResult(X_32,X_24); | | ocelot.endQuery(); | | end s9_1; | | # querylog.define("explain select c_custkey, c_name from customer where c_cu | : stkey < 2;","cl_pipe") : +------------------------------------------------------------------------------+ 27 tuples (1.408ms) sql> =======================================

2 3

monetdbd hangs from Python subprocess
by Christopher Nelson 23 Nov '13

23 Nov '13

Hi, I am writing some automation to control monetdbd programmatically from Python. While the rest of the monetdbd commands work fine, the command: monetdbd start <farm> never returns control to Python. I have tried this on both CPython 2.7 and PyPy 2.0.4. cnelson@hpvm:~/workspace/cql-db-py/cql $ python store.py DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set passphrase=letmein /tmp/test_warehouse' DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set control=yes /tmp/test_warehouse' DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set discovery=yes /tmp/test_warehouse' DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd set port=60000 /tmp/test_warehouse' INFO:root:Starting warehouse at '/tmp/test_warehouse' cnelson@hpvm:~/workspace/cql-db-py/cql $ ps a PID TTY STAT TIME COMMAND 31608 pts/3 S 0:00 python store.py 31613 pts/3 Z 0:00 [monetdbd] <defunct> You can see that the process appears to have finished, but never returns control to Python. If I run the 'stop' command from the prompt, everything clears up and finishes: cnelson@hpvm:~/workspace/cql-db-py/cql $ /home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd stop /tmp/test_warehouse cnelson@hpvm:~/workspace/cql-db-py/cql $ DEBUG:root:'/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd start /tmp/test_warehouse' INFO:root:Stopping warehouse at '/tmp/test_warehouse' ERROR:root:Unable to run command: '/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd stop /tmp/test_warehouse' DEBUG:root:unable to open merovingian.pid: No such file or directory ERROR:root:None Traceback (most recent call last): File "store.py", line 198, in <module> w.stop() File "store.py", line 192, in stop self._control_warehouse("stop") File "store.py", line 98, in _control_warehouse raise subprocess.CalledProcessError(p.returncode, cmd, out) subprocess.CalledProcessError: Command '['/home/cnelson/workspace/cql-db-py/cql/storage_engine/bin/monetdbd', 'stop', '/tmp/test_warehouse']' returned non-zero exit status 1 The error here occurs because the next thing that the script will try to do is 'stop' the monetdbd instance. Which of course I already did by hand. The point is that once I stop the monetdbd instance OOB, the original monetdbd where 'start' was called finally does return control to Python. Which indicates that it is probably not a Python problem. Any ideas?

2 1

LIMIT clause in sub-query?
by Dennis Pallett 22 Nov '13

22 Nov '13

Hello all, I'm trying to use a LIMIT clause in a sub-query but MonetDB doesn't seem to be able to handle it. A very simple example query would be: SELECT COUNT(*) AS gt_result FROM ( SELECT * FROM nyc_buildings LIMIT 10 ) AS temp; The result of this query should be 10 and works fine in PostgreSQL. However MonetDB gives the following error: syntax error, unexpected LIMIT, expecting INTERSECT or EXCEPT or UNION or ')' in: "<original query>" Is this a bug in MonetDB? Any way to work around it? Best regards, Dennis Pallett

2 1

Enum data type?
by Dennis Pallett 22 Nov '13

22 Nov '13

Hello all, Does MonetDB support some kind of enum type, similar to MySQL's enum type (http://dev.mysql.com/doc/refman/5.0/en/enum.html) i.e. a column that only allows a limited set of values? I couldn't find it in the documentation and I'm unable to simulate it with triggers or constraints. Best regards, Dennis Pallett

2 2

CREATE CLUSTERED INDEX fails
by Michael Wittig 20 Nov '13

20 Nov '13

Hi, I am just getting started with monetdb. I have a table like this: CREATE TABLE values ( "date" DATE, "datetime" TIMESTAMP, "origin" VARCHAR(30), "value" REAL ); Normally I want to do queries like SELECT date, AVG(value) FROM values WHERE date>='2013-01-01' AND origin='sensor1' GROUP BY date; date and datetime are sorted so I would assume that an index on origin will be useful. The possible values of origin are limited so I tried: (Version Feb2013-SP5) CREATE CLUSTERED INDEX index_origin ON values (origin); But the result is syntax error, unexpected IDENT, expecting INDEX in "create clustered" What am I missing? Or is there even a better solution for data that looks like this where origin is sorted within a date. date | datetime | origin | value 2013-01-01 | 2013-01-01 10:00:00.000 | sensor1 | 10 2013-01-01 | 2013-01-01 12:00:00.000 | sensor1 | 12 2013-01-01 | 2013-01-01 14:00:00.000 | sensor1 | 15 2013-01-01 | 2013-01-01 16:00:00.000 | sensor1 | 10 2013-01-01 | 2013-01-01 10:00:00.000 | sensor2 |111 2013-01-01 | 2013-01-01 12:00:00.000 | sensor2 | 222 2013-01-01 | 2013-01-01 14:00:00.000 | sensor2 | 333 2013-01-01 | 2013-01-01 16:00:00.000 | sensor2 | 222 2013-01-02 | 2013-01-02 10:00:00.000 | sensor1 | 10 2013-01-02 | 2013-01-02 12:00:00.000 | sensor1 | 12 2013-01-02 | 2013-01-02 14:00:00.000 | sensor1 | 15 2013-01-02 | 2013-01-02 16:00:00.000 | sensor1 | 10 2013-01-02 | 2013-01-02 10:00:00.000 | sensor2 | 111 2013-01-02 | 2013-01-02 12:00:00.000 | sensor2 | 222 2013-01-02 | 2013-01-02 14:00:00.000 | sensor2 | 333 2013-01-02 | 2013-01-02 16:00:00.000 | sensor2 | 222 Regards, Michael

2 2

COPY INTO with foreign key
by Treffer, Arian 20 Nov '13

20 Nov '13

Hello, I want to import data into a table which has a foreign key to itself: CREATE TABLE "Type" ( "id" INT NOT NULL , "name" VARCHAR(255) NOT NULL , "file" VARCHAR(255) NULL , "componentTypeId" INT NULL , PRIMARY KEY ("id") , CONSTRAINT "fk_Type_componentType" FOREIGN KEY ("componentTypeId" ) REFERENCES "Type" ("id" ) ON DELETE NO ACTION ON UPDATE NO ACTION) ; Now, if I try to import some data, eg: 0;a;a;NULL 1;b;b;0 the second line causes an error: sql>COPY INTO "Type" FROM 'C:/data.csv' USING DELIMITERS ';','\n'; INSERT INTO: FOREIGN KEY constraint 'Type.fk_Type_componentType' violated What is the correct way to import the data? Since I have a lot of tables, I would rather not have to drop and re-create all constraints. Best regards, Arian

2 1

User Defined Aggregations in MonetDB?
by Alastair McKinley 20 Nov '13

20 Nov '13

Dear all, I was wondering if it is possible to build a User Defined Aggregation in MonetDB. For example, rather than do SUM(myfunction(a,b)) I would like to do MYAGG(a,b) where MYAGG internally holds a small buffer of a and b values so that they can be calculated in a batch fashion with a high performance batch implementation of the function. Could MYAGG be implemented in C? Best regards, Alastair

3 6

User Defined Functions returning null
by Alastair McKinley 13 Nov '13

13 Nov '13

Hi all, I am trying to evaluate the performance of MonetDB for an analytical workload. I am using MonetDB-11.15.17 built from source on Ubuntu 13.04. I have encountered a problem with user using defined functions in SQL which I have reduced to this test case. I appears that whenever I have over 200,000 rows in the table, the UDF returns null. First create a test data set with the following python: import random with open("test_data.csv","w") as f: for i in range(0,1000000): f.write("%s,%s\n" % (random.random(),random.random())) Then I use the following to load the data, and try using a simple UDF in SQL. CREATE TABLE TEST ( x float, y float ); COPY 200000 RECORDS INTO TEST from '/home/alastair/test_data.csv' USING DELIMITERS ',','\n','"' NULL AS ''; CREATE FUNCTION MY_UDF (x float, y float) RETURNS float BEGIN DECLARE ret float; set ret = LOG(x/y); RETURN ret; END; SELECT 'udf alone',MY_UDF(0.1,0.2); SELECT 'udf from table',MY_UDF(t.x,t.y) FROM TEST as t limit 1; -- this returns NULL with 200000 records DROP FUNCTION MY_UDF; DROP TABLE TEST; The value returned my MY_UDF is null with 200,000 records, changing to 199,999 records in COPY INTO produces the correct result. If I use a UDF built in C, the problem does not exist at all. I feel like I might have missed something silly but I can't think of anything. Best regards, Alastair

3 5