User Defined Functions returning null

13 Nov 2013

      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

User Defined Functions returning null

Alastair McKinley