Date: 2013-11-13 16:25:18 +0100
From: @mlkersten
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: @njnes

Last updated: 2013-12-03 13:59:39 +0100

Comment 19329

Date: 2013-11-13 16:25:18 +0100
From: @mlkersten

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.7; rv:24.0) Gecko/20100101 Firefox/24.0
Build Identifier:

a.mckinley@analyticsengines.com produced a bug report on the mailinglist.

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.

Reproducible: Always

Comment 19330

Date: 2013-11-13 16:27:43 +0100
From: @mlkersten

A condensed version of this bug has been added to the repository as BugTracker_2013/Tests/udf_error.Bug-3396

Comment 19334

Date: 2013-11-14 15:29:08 +0100
From: @njnes

the 200.000 rows aren't needed to trigger the bug. Just setting the --forcemito
will do. This clearly points into the merge table direction. Its likely related to handling if/then else in the mergetable code.

Comment 19337

Date: 2013-11-15 15:50:38 +0100
From: MonetDB Mercurial Repository <>

Changeset 9aa381c5d6bf made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=9aa381c5d6bf

Changeset description:

fixed bug #3396. Make sure mergetable overwrites the mat result, like the
original plan does.

Comment 19341

Date: 2013-11-16 13:24:44 +0100
From: MonetDB Mercurial Repository <>

Changeset 045ed91ead43 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=045ed91ead43

Changeset description:

fixed crash in BATappend (cannot use memcpy on void bat)


approved output
	after addition of quantile aggregate
	after readded groups optimizer

approved output for bug #3396 (fix came from merge with feb2013, proper handling of variable overwriting in mergetable optimizer)

Comment 19386

Date: 2013-12-03 13:59:39 +0100
From: @sjoerdmullender

Feb2013-SP6 has been released.