Date: 2011-10-26 14:34:32 +0200
From: sellam
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2011-11-22 13:55:55 +0100

Comment 16471

Date: 2011-10-26 14:34:32 +0200
From: sellam

User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-GB; rv:1.9.2.23) Gecko/20110927 Fedora/3.6.23-1.fc14 Firefox/3.6.23
Build Identifier:

Here is an example session. We create a table, create a query function over it. The query works. We then load data in the table: after this, the function crashes.

It seems that adding LIMIT 1 in the SELECT statement of inRange makes it work.

sql>create table trips (tripid bigint, lon decimal(8,5), lat decimal(7,5), time bigint);

sql>CREATE FUNCTION inRange(xx DECIMAL(8,5), yy DECIMAL(7,5), ww INTEGER, hh INTEGER)
more> RETURNS TABLE (tripid BIGINT, lon DECIMAL(8,5), lat DECIMAL(7,5), time BIGINT)
more>BEGIN
more>RETURN SELECT T.tripid, T.lon, T.lat, T.time
more> FROM trips T
more> WHERE T.lon > xx AND T.lon < xx + ww
more> AND T.lat > yy AND T.lat < yy + hh;
more>END;
operation successful (19.205ms)

sql>SELECT * FROM inRange(0,0,1,1);
+--------+-----+-----+------+
| tripid | lon | lat | time |
+========+=====+=====+======+
+--------+-----+-----+------+
0 tuples (35.449ms)

sql>DEBUG SELECT * FROM inRange(0,0,1,1);
mdb> mdb.start();
mdb>l user.inrange
0 function user.inrange(Axx,Ayy,Aww,Ahh);
1 X_21 := nil:bat[:oid,:lng];
2 X_36:bat[:oid,:int] := nil:bat[:oid,:int];
3 X_39:bat[:oid,:int] := nil:bat[:oid,:int];
4 X_58 := nil:bat[:oid,:int];
5 X_69:bat[:oid,:int] := nil:bat[:oid,:int];
6 X_77:bat[:oid,:int] := nil:bat[:oid,:int];
7 X_86 := nil:bat[:oid,:int];
8 X_106 := nil:bat[:oid,:oid];
9 barrier X_141 := language.dataflow();
10 X_9 := sql.mvc();
11 X_11:bat[:oid,:lng] := sql.bind(X_9,"sys","trips","tripid",1);
12 X_15:bat[:oid,:lng] := sql.bind(X_9,"sys","trips","tripid",2);
13 X_17:bat[:oid,:lng] := sql.bind(X_9,"sys","trips","tripid",0);
14 X_19 := algebra.kdifference(X_17,X_15);
15 X_20 := algebra.kunion(X_19,X_15);
16 X_21 := algebra.kunion(X_20,X_11);
17 X_22 := calc.lng(5,Axx,15,5);
18 X_25 := calc.lng(Aww,15,5);
etc...

**** Until here everything works fine. Now come the problems:

sql>copy 240540781 records into trips from '/net/rig.ins.cwi.nl/export/scratch0/manegold/TomTom/data.gz' using delimiters ',','\n';

Reproducible: Always

Steps to Reproduce:

create table trips (tripid bigint, lon decimal(8,5), lat decimal(7,5), time bigint);

-- FUNCTION RETURNING POINTS IN RANGE

CREATE FUNCTION inRange(xx DECIMAL(8,5), yy DECIMAL(7,5), ww INTEGER, hh INTEGER)
RETURNS TABLE (tripid BIGINT, lon DECIMAL(8,5), lat DECIMAL(7,5), time BIGINT)
BEGIN
RETURN SELECT T.tripid, T.lon, T.lat, T.time
FROM trips T
WHERE T.lon > xx AND T.lon < xx + ww
AND T.lat > yy AND T.lat < yy + hh;
END;

SELECT * FROM inRange(0,0,1,1);

copy 240540781 records into trips from '/net/rig.ins.cwi.nl/export/scratch0/manegold/TomTom/data.gz' using delimiters ',','\n';

SELECT * FROM inRange(0,0,1,1);

Actual Results:

SQLException:sql.resultSet:Cannot access descriptorSQLException:sql.resultSet:Cannot access descriptor

Expected Results:

A result set

Comment 16472

Date: 2011-10-26 14:37:42 +0200
From: sellam

After loading data :

mdb>l user.inrange
? 0 function user.inrange(Axx,Ayy,Aww,Ahh);
? 1 return (X_9,X_10,X_11,X_12);
? 2 end inrange;

The body disappeared

Comment 16473

Date: 2011-10-26 17:04:56 +0200
From: @sjoerdmullender

Which version of MonetDB?
Since Fabian has been fixing stuff in this area, it is important to be exact here, so if you've built from source, what does "hg id" say?

Comment 16474

Date: 2011-10-26 18:03:11 +0200
From: sellam

(In reply to comment 2)

Which version of MonetDB?
Since Fabian has been fixing stuff in this area, it is important to be exact
here, so if you've built from source, what does "hg id" say?

hg id gives:
6b371872cfe4

hg parent:
changeset: 42187:6b371872cfe4
parent: 42182:5b45d264631c
parent: 42186:9db8d45b28ee
user: Stefan Manegold Stefan.Manegold@cwi.nl
date: Sat Oct 22 10:38:33 2011 +0200
summary: Merge With Dec2011 branch.

Comment 16475

Date: 2011-10-27 10:30:53 +0200
From: @sjoerdmullender

Can you try it on the Dec2011 branch? That branch contains Fabian's fixes. They have not yet been propagated to the default branch that you were using.

Comment 16478

Date: 2011-10-27 10:57:06 +0200
From: @grobian

function user.inrange is empty apart from the return, I doubt my fixes to the query cache affect this at all.

Comment 16528

Date: 2011-11-08 07:53:00 +0100
From: @njnes

This is probably fixed by the fix too return statements (ie make it a proper assignment). This fixed zones and datacell problems.

Could you repeat the test.

Comment 16534

Date: 2011-11-08 11:39:22 +0100
From: sellam

I reproduced and it worked fine. Problem solved.
Thanks for your help

copy 240540781 records into trips from
more>'/export/scratch0/sellam/documents/data-sets/tomtom-example/data.gz' using delimiters
more>',','\n';
240540781 affected rows (11m 4s)
sql>SELECT * FROM inRange(0,0,1,1);
+--------+-----+-----+------+
| tripid | lon | lat | time |
+========+=====+=====+======+
+--------+-----+-----+------+
0 tuples (1m 30s)

Comment 16535

Date: 2011-11-08 11:41:35 +0100
From: @njnes

fixed, tested by zones and datacell test, ie no extra test needed

Comment 16567

Date: 2011-11-22 13:55:55 +0100
From: @grobian

Fixed in Aug2011-SP3