On 2007-09-28, Martijn Tonies <m.tonies@upscene.removethis.com> wrote:
> Patrick,
>
>> CREATE FUNCTION super_cat (id varchar(20)) RETURNS varchar(20)
>> begin
>> declare x,y varchar(20);
>> select supervisor into x from people where userid = id;
>> if x is null then
>> return null;
>> else
>> select category into y from people where userid = x;
>> return y;
>> end if;
>> end
>
> You might want to take a loko at that first part there --
>
> if there's no resultset, what does X become? If you initialize X with
> something, eg:
>
> declare x, y varchar(20);
>
> x = 'test';
> select supervisor into x from people where userid = id;
>
> is X NULL or 'test'?
>
> I'm wondering, cause I don't think the SELECT will put anything
> into X if there's no resultset.
>
According to the online manual, the default value is NULL if there
is no DEFAULT clause. I assumed that if there is no result set
it would continue to be NULL. I also assumed that y will continue
to be NULL if category is NULL. If this behavior is not guaranteed,
then a few more tests would be justified.

My first version didn't have the "if x is null" test. It returned
the correct results, but it generated warnings in the "select
category" line when x was NULL.