Re: User Defined Functions returning null

Hi You can check for different plans by pre-pending the SQL query with EXPLAIN. http://www.monetdb.org/Documentation/Manuals/SQLreference/Explain Performance progress and the cause can be traced using the TRACE command. http://www.monetdb.org/Documentation/Manuals/SQLreference/Trace or the stethoscope regards, Martin On 11/13/13 2:24 PM, Alastair McKinley wrote:

Hi Stefan,

Thanks for you response.

I am not actually sure because I changed the test data script to output constant values of 0.1 and 0.2 for each row.

The problem still remains with these values in the test data, at 200000 records, I get a null.

If it is any clue, running mclient with --interactive shows that the time taken for this statement drops when going from 199,999 -> 200,000 as it is have decided to execute a different plan.

Best regards,

Alastair ________________________________________ From: users-list on behalf of Stefan Manegold Sent: 13 November 2013 13:14 To: Communication channel for MonetDB users Subject: Re: User Defined Functions returning null

what are the x & y values of your the last of your 200000 tuples, i.e., the one omitted if you only load the first 199999 tuples?

----- Original Message -----

...

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

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list