This is a simplified version of my real function, I make it simpler and stupid to locate problem, the actual one is:
CREATE FUNCTION neighbor(ra double, decl double, radius double)
RETURNS TABLE (uniqueid BIGINT)
BEGIN
DECLARE TABLE uzone (id bigint, ra_avg double, decl_avg double, zone smallint, x double, y double, z double);
DECLARE ozone double;
DECLARE zoneheig double;
DECLARE ox double;
DECLARE oy double;
DECLARE oz double;
SET zoneheig=1e1/3600;
SET ozone=decl/zoneheig;
SET ox=COS(RADIANS(decl)) * COS(RADIANS(ra));
SET oy=COS(RADIANS(decl)) * SIN(RADIANS(ra));
SET oz=SIN(RADIANS(decl));
INSERT INTO uzone select id,ra_avg,decl_avg,zone,x,y,z from uniquecatalog11 order by zone;
RETURN TABLE( SELECT id AS uniqueid
FROM uzone u0
WHERE
u0.zone BETWEEN ozone-1 AND ozone+1
AND u0.ra_avg between ra-alpha(decl, radius) and ra+alpha(decl, radius)
AND u0.decl_avg between decl-radius and decl+radius
AND u0.x*ox+u0.y*oy+u0.z*oz > cos(radians(radius))
) ;
END;
I need uzone to make the uniquecatalog11 table order by zone. I can remove the order by and don't declare temporary table, but for larger amounts of row counts in future, sorting will help, so I keep it.
On my side, it exits every time for now.
If I use existing table rather than temporary table, the above function definition will be successful!
It exits only when using temporary table and combination of conditions.
Best,
Meng
------------------ Original ------------------
From: "Stefan Manegold";<Stefan.Manegold@cwi.nl>;
Send time: Monday, Jul 20, 2015 3:32 AM
To: "Communication channel for MonetDB users"<users-list@monetdb.org>;
Subject: Re: combined conditions in user defined function made monetdb exited.
Hi Meng,
how did you define unique_seq?
It does not seem to exist by default ...
A segfault is always a bug.
Please file a bug report.
Why do you declare uzone in the functions?
Wouldn't these do exactly the same, but simple?
create function neighbor19()
returns table (ra_avg double)
begin
return table( select ra_avg
from uniquecatalog11 as u0
where id between 10 and 20
);
end;
create function neighbor20()
returns table (ra_avg double)
begin
return table( select ra_avg
from uniquecatalog11 as u0
where ra_avg between 10 and 20
);
end;
create function neighbor30()
returns table (ra_avg double)
begin
return table( select ra_avg
from uniquecatalog11 as u0
where id between 10 and 20
and ra_avg between 10 and 20
);
end;
Best,
Stefan
----- On Jul 19, 2015, at 9:13 PM, integrity 357416268@qq.com wrote:
> Hi Jennie,
>
> Thanks for your attention, both uniquecatalog11 and uzone can be empty table.
> schema is:
>
> create sequence "unique_seq" as bigint;
>
> create table uniquecatalog11(id bigint PRIMARY KEY
> DEFAULT NEXT VALUE FOR "unique_seq"
> , targetid bigint
> , ra_avg double
> , decl_avg double
> , flux_ref double
> , datapoints int
> , zone smallint
> , x double
> , y double
> , z double
> , INACTIVE BOOLEAN
> );
> uzone table is declared within the udf.
>
> Bests,
> Meng
> ------------------ Original ------------------
> From: "Ying Zhang";<Y.Zhang@cwi.nl>;
> Date: Jul 19, 2015
> To: "Communication channel for MonetDB users"<users-list@monetdb.org>;
> Subject: Re: combined conditions in user defined function made monetdb exited.
>
>
>> On Jul 19, 2015, at 15:27 , integrity <357416268@qq.com> wrote:
>>
>> Dear experts,
>>
>> I tried to create an function in monetdb, but the first one successful, the
>> second one successful, but the combo failed.
>>
>> create function neighbor19()
>> returns table (ra_avg double)
>> begin
>> declare table uzone (id bigint, ra_avg double);
>> insert into uzone select id, ra_avg from uniquecatalog11;
>> return table( select ra_avg
>> from uzone as u0
>> where id between 10 and 20
>> );
>> end;
>> successful.
>>
>> create function neighbor20()
>> returns table (ra_avg double)
>> begin
>> declare table uzone (id bigint, ra_avg double);
>> insert into uzone select id, ra_avg from uniquecatalog11;
>> return table( select ra_avg
>> from uzone as u0
>> where ra_avg between 10 and 20
>> );
>> end;
>> successful.
>>
>> create function neighbor30()
>> returns table (ra_avg double)
>> begin
>> declare table uzone (id bigint, ra_avg double);
>> insert into uzone select id, ra_avg from uniquecatalog11;
>> return table( select ra_avg
>> from uzone as u0
>> where id between 10 and 20
>> and ra_avg between 10 and 20
>> );
>> end;
>> failed with monetdb exited.
>
> Hai Meng,
>
> Can you please provide the schema of uniquecatalog11 and uzone?
> Does the crash also happen if these two tables are empty? Otherwise, weˇŻll need
> some data to reproduce the crash as well. Thanks,
>
> Jennie
>
>
>
>>
>> in merovingian.log:
>> 2015-07-19 13:34:36 MSG merovingian[25753]: database 'gwacdb' (633) was killed
>> by signal SIGSEGV
>>
>> I tried on default and Jul2015 branch, all the same with each other.
>> Any help or hints are appreciated!
>>
>> Best,
>> Meng
>> _______________________________________________
>> 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
>
> _______________________________________________
> 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