From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Adam Mackler <adam(at)mackler(dot)email> |
Cc: | "pgsql-sql(at)lists(dot)postgresql(dot)org" <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Possible bug (or at least unexpected behavior) |
Date: | 2022-08-07 23:31:41 |
Message-ID: | 291122.1659915101@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Adam Mackler <adam(at)mackler(dot)email> writes:
> Briefly, given the following function:
> CREATE FUNCTION runs(input int[], output int[] DEFAULT '{}')
> RETURNS int[] AS $$
> SELECT
> CASE WHEN cardinality(input) = 0 THEN output
> ELSE runs(input[2:],
> array_append(output, CASE
> WHEN input[1] = 0 THEN 0
> ELSE output[cardinality(output)] + input[1]
> END)
> )
> END
> $$ LANGUAGE SQL;
> I expect the following invocation to return an array with the same number of elements as the passed-in argument array:
> # select runs('{0,1,1,1,1,0,-1,-1,-1,0}');
> runs
> ----------------------------------------
> {0,1,2,3,4,5,6,0,0,0,-1,-2,-3,-4,-5,0}
> (1 row)
Yeah, there's a bug in here somewhere. If you transpose the logic
into plpgsql, it behaves fine:
CREATE FUNCTION runs_p(input int[], output int[] DEFAULT '{}')
RETURNS int[] AS $$
begin
return
CASE WHEN cardinality(input) = 0 THEN output
ELSE runs_p(input[2:],
array_append(output, CASE
WHEN input[1] = 0 THEN 0
ELSE output[cardinality(output)] + input[1]
END)
)
END;
end
$$ LANGUAGE plpgsql;
so that might do as a workaround. It looks like memory management
in SQL functions is not coping well with expanded arrays, but I'm
not quite sure where it's going off the rails.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Shaozhong SHI | 2022-08-08 08:24:14 | select items based on 2 columns |
Previous Message | Adam Mackler | 2022-08-07 20:06:21 | Possible bug (or at least unexpected behavior) |